skip to Main Content

So I used this source code to build my telegram bot in GAS, and also used message handler to grab message.message.chat.id in a Google spreadsheet

so now what I want is:

  1. remove the duplicated chat_ids, because it repeated itself in every time a user sends a message
  2. send a message to those chat_ids

What I have tried:

I used this function, it worked fine but the problem is it will be nightmare to change the chat_id value manually!

function SendTest() {
  var token = "xxx";
  var telegramUrl = "https://api.telegram.org/bot" + token;
  var url = telegramUrl + "/sendMessage?chat_id=xxx&text=Hello+World";
  var response = UrlFetchApp.fetch(url);
  Logger.log(response.getContentText());
}

and here is a photo of how my spreadsheet looks like

spreedsheet

"as you see there is a duplicated chat_ids like in row 3,4,5 I just want one of them, so when I send them a message it doesn’t send several times – what I want number 1"

and this the message handler that I used

function saveMessage(message) {
  let file = SpreadsheetApp.openById(sheetLogId);
  // first tab of the file
  let sheet = file.getSheets()[0];
  // get last row
  let lastRow = sheet.getLastRow() + 1;
  
  sheet.setActiveSelection('A' + lastRow).setValue(Date(message.message.date)); // date
  sheet.setActiveSelection('B' + lastRow).setValue(message.message.chat.id); // chat id
  sheet.setActiveSelection('C' + lastRow).setValue(message.message.from.username); // username
  sheet.setActiveSelection('E' + lastRow).setValue(message.message.text); // message
  sheet.setActiveSelection('D' + lastRow).setValue(message.message.chat.first_name+ " " + message.message.chat.last_name); // message
  
}

— edit —

so I used Nikko J. editd function saveMessage(message) to help my first problem (no more duplicated chat_id!)

after that I had found this form that talked about Retrieve Rows, so I used it to select all the chat_id form the spreadsheet and loop it in my send text function

and it represntet here :

function sendtext(){
 var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
 var botSecret = "the bot token";
 rows.forEach(function(row, index) {
   Logger.log(row[1] + ":" + index)
 var id = row[1];
 UrlFetchApp.fetch("https://api.telegram.org/bot" + botSecret + "/sendMessage?text=" + "the text that you want "  + "&chat_id=" + id + "&parse_mode=HTML");
 });
} 

2

Answers


    1. Instead of harcoding the chat_id, pass the chat_id as a function argument and build the URL either by using string concatenation or string template
    2. Make a function that do the following:
      1. Read the chat_ids from your spreadsheet
      2. Get a list of unique chat_ids
      3. Iterate over the list of unique chat_ids to to send the messages to the unique list of chat_ids (call your the improved version of your function from 1.

    Related

    Login or Signup to reply.
  1. Problem:

    • You want to ignore the incoming message if the chat id exists in the sheets to avoid duplicates
    • Send message to user using UrlFetchApp.fetch()

    Here I copied your code and made some modifications to replicate the scenario:

    function sendMessage(body ,chatId){
      var botSecret = "xxx";
      var response = UrlFetchApp.fetch("https://api.telegram.org/bot" + botSecret + "/sendMessage?text=" + encodeURIComponent(body) + "&chat_id=" + chatId + "&parse_mode=HTML");
    }
    
    function testFunction(){
      var message = {
        message : {
          date: "01/01/20",
          text: "This is jus a random text",
          chat: {
            first_name: "FNAME",
            last_name: "LNAME",
            id: 1234
          },
          from: {
          username: "abcd"
          }
        }
      }
      saveMessage(message);
    }
    
    function saveMessage(message) {
      let file = SpreadsheetApp.getActiveSpreadsheet();
      // first tab of the file
      let sheet = file.getSheets()[0];
      // get last row
      let lastRow = sheet.getLastRow() + 1;
      var newArr = [];
      //insert data into array. By using this, we can use setValues() and lessen the API calls
      newArr.push(Date(message.message.date));
      newArr.push(message.message.chat.id);
      newArr.push(message.message.from.username);
      newArr.push(message.message.text);
      newArr.push(message.message.chat.first_name+ " " + message.message.chat.last_name);
      //get all chat ids in sheets
      var ids = sheet.getRange('B1:B').getValues().filter(val => val != "");
      //convert chat ids array into 1 dimensional array so we can easily use Array.includes()
      var newArr2 = [].concat(...ids);
      //check if message.message.chat.id exists in the list
      if(!newArr2.includes(message.message.chat.id)){
        //update sheet and message user if chat id does not exists in sheet
        sheet.getRange(lastRow,1, 1, newArr.length).setValues([newArr]);
        var message = "Hello world";
        sendMessage(message, message.message.chat.id)
      }
    }
    

    References:

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search