Question 59698 in Telegram API Question posted in
A comprehensive official documentation can be found here.

How to send a Telegram message for every row with data in a Google Sheet – Telegram API

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’.
enter image description here

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.
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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);
  }
}

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search