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