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

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

CodePudding user response：

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


      }
    }
}

CodePudding user response：

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.