How to send a Telegram message for every row with data in a Google Sheet-CodePudding

I'm actually using this working code and setting a trigger to repeat the task every 4 hours, but I would like to short the code checking every row, let's say look for non empty rows in the range 'A1:A20'.

What can be the short path to run the script without the use of this code?

function sendTelegramNotification() {
var sheet = SpreadsheetApp.getActive().getSheetByName("TELEGRAM");

if (sheet.getRange("A1").isBlank()) return;
var api = sheet.getRange("A1").getValues();
var group = sheet.getRange("B1").getValues();
var message = sheet.getRange("C1").getValues();

  var url = 'https://api.telegram.org/bot'   api
      '/sendMessage?chat_id='   group
      '&text='   encodeURIComponent(message)
  UrlFetchApp.fetch(url);

if (sheet.getRange("A2").isBlank()) return;
  SpreadsheetApp.flush();
  Utilities.sleep(80 * 100);
  SpreadsheetApp.flush();
var api = sheet.getRange("A2").getValues();
var group = sheet.getRange("B2").getValues();
var message = sheet.getRange("C2").getValues();

  var url = 'https://api.telegram.org/bot'   api
      '/sendMessage?chat_id='   group
      '&text='   encodeURIComponent(message)
  UrlFetchApp.fetch(url);

if (sheet.getRange("A3").isBlank()) return;

//etc., etc.
}

CodePudding user response：

I achieve it by using this code:

function sendWeater() {
var sheet = SpreadsheetApp.getActive().getSheetByName("TELEGRAM");
var data = sheet.getDataRange().getValues();

  //Wait some seconds to send every message
  SpreadsheetApp.flush();
  Utilities.sleep(80 * 100);
  SpreadsheetApp.flush();

  //Loop for all the rows in the sheet 'TELEGRAM', i = 1 because I've a header row
  for(var i = 1; i < data.length; i  ) {
    var row = data[i];
    var api = row[0];
    var group = row[1];
    var message = row[2];

  var url = 'https://api.telegram.org/bot'   api
      '/sendMessage?chat_id='   group
      '&text='   encodeURIComponent(message)
  UrlFetchApp.fetch(url);
  }
}

CodePudding user response：

You could use getRange instead of getDataRange() for var data = sheet.getDataRange().getValues(); and specify the rows and columns. Here is an example implementation

function sendWeater() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
var data = sheet.getRange(1,1, sheet.getLastRow(), sheet.getLastColumn()).getValues();

 //Wait some seconds to send every message
  SpreadsheetApp.flush();
  Utilities.sleep(80 * 100);
  SpreadsheetApp.flush();

  //Loop for all the rows in the sheet 'TELEGRAM', i = 1 because I've a header row
  for(var i = 1; i < data.length; i  ) {
    var row = data[i];
    var api = row[0];
    var group = row[1];
    var message = row[2];

  var url = 'https://api.telegram.org/bot'   api
      '/sendMessage?chat_id='   group
      '&text='   encodeURIComponent(message)
  UrlFetchApp.fetch(url);
  } 
}

Unfortunately I wasn't able to test this due to limited access to Telegram Bot, but this should get you started.