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

How to send full row and list name from Google Sheet, not only specific (fixed) cells and list name? – Telegram API

So I have a script, that sends msg to group chat, if in Column ( 2 ) in any cell’s some one printed "Yanson" bot sends only fixed cell – .getRange(row,8). In my case this cell holds link to document.

Bot msg looks like this – Link to document New Added Document List Name ( This time I get List name coz it fixed in var ws, if script work’s in another list , I don’t receive the right list Name I still receive the fixed one in var ws)
If we delete === ws and print "Yanson" in another list – I’ll receive only info from .getRange(row,8) and "Added New Document.

But I need to send full string ( row ) with all the cell inside it, not only cell 8 with link. And I also need to see in msg from bot list name where "Yanson" was printed. Because I have more then 10+ list in Sheet. Sheet looks like this Tablepicture

 const token = "Token";
    
    function onEdit(e) {
      sendTelegram(e)
    }
    
    function sendTelegram(e){
    
    var row = e.range.getRow();
    var col = e.range.getColumn();
    
    var startRow = 2; // Starting row
    var targetColumn = 2; // If in this column, cell changes to Yanson - send to Telegram
    var ws = "List name"; //List name
    
    let chatId = "ChatId";
    let Company = e.source.getActiveSheet().getRange(row,8).getValue();
    var text = encodeURIComponent(Company + " New Document Added" + ws)
    
    var currentDate = new Date();
    
    var url = "https://api.telegram.org/bot" + token + "/sendMessage?chat_id=" + chatId + "&text=" + text;
    
    if (col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === ws){
      if(e.source.getActiveSheet().getRange(row,2).getValue() == "Yanson"){ //Yanson - Trigger. If  Yanson printed in cell in column 2  - send to telegram
        sendText(chatId,Company + "    New Document Added" +"        "+ ws);
    
        //Doing nothig right now.
        // e.source.getActiveSheet().getRange(row,4).setValue(currentDate);
        // if(e.source.getActiveSheet().getRange(row,3).getValue() == ""){
        //     e.source.getActiveSheet().getRange(row,3).setValue(currentDate)
        // }
    
    
      }
    }
    }
Tags:

2

Answers


  1. 0 
    function onEdit(e) {
  const sh = e.range.getSheet();
  const row = sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).getDisplayValues()[0].join(',');//current row of active sheet
  const name = e.source.getName();//spreadsheet name
  //const name = sh.getName();//sheet name not sure which one you want
  sendText('chatId', `${name)n ${row}`);
}

    You probably want to limit the trigger to a certain sheet and given row and column but I’ll leave that up to you.

    Login or Signup to reply.
  2. 0

    Based on what I could gather from your description, you are looking for a way to send the entire contents of the row as a string.

    To do that, you get the range of that row, which looks like this:

    sheet.getRange(starting row, starting column, # of rows, # of cols)

    Sheets uses a two dimensional array that looks like this:

    [[row1Col1, row1Col2, row1Col3], [row2Col1, row2Col2, row2Col3], etc]

     const token = "Token";
    
function onEdit(e) {
  sendTelegram(e)
}

function sendTelegram(e){

    var row = e.range.getRow();
    var col = e.range.getColumn();

    var startRow = 2; // Starting row
    var targetColumn = 2; // If in this column, cell changes to Yanson - send to Telegram
    var ws = "List name"; //List name

    
    /*--- Updated this section ----*/
        //Adding variables to improve readiblity
        var sheet = e.source.getActiveSheet();
        var sheetName = e.source.getActiveSheet().getName();
        let company = e.source.getActiveSheet().getRange(row,8).getValue();
        var listName = ; //Is the list name the same as the sheet name? if not, reference the list names location here
        
        
        //Define the range of the whole row
        var firstCol = 1;
        var numOfCols = 8;
        var fullRowValues = sheet.getRange(row, firstCol, 1, numOfCols).getValues();
        
        //since this is a single row, you can use .flat() to make it a 1D array
        //Then convert it to a string
        var fullRowString = fullRowValues.flat().toString();

    /*---- End updates ---*/
    
    let chatId = "ChatId";
    
    var text = encodeURIComponent(Company + " New Document Added" + ws)

    var currentDate = new Date();

    var url = "https://api.telegram.org/bot" + token + "/sendMessage?chat_id=" + chatId + "&text=" + text;

    if (col === targetColumn && row >= startRow && sheetName === ws){
      if(company == "Yanson"){ //Yanson - Trigger. If  Yanson printed in cell in column 2  - send to telegram
        
        // Not sure what the output is supposed to look like, 
        // so I just added it to the end of your existing output
        sendText(chatId,Company + "    New Document Added" +"        "+ ws + " All Values: " + fullRowString);

        //Doing nothig right now.
        // e.source.getActiveSheet().getRange(row,4).setValue(currentDate);
        // if(e.source.getActiveSheet().getRange(row,3).getValue() == ""){
        //     e.source.getActiveSheet().getRange(row,3).setValue(currentDate)
        // }


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

Back To Top
Search