所以我有一個腳本，它將 msg 發送到群聊，如果在任何單元格的第 (2) 列中，某個列印的“Yanson”機器人僅發送固定單元格 - .getRange(row,8)。在我的情況下，此單元格包含指向檔案的鏈接。
Bot msg 看起來像這樣 - 鏈接到檔案新添加的檔案串列名稱（這次我得到串列名稱是因為它已修復 var ws，如果腳本作業在另一個串列中，我沒有收到正確的串列名稱我仍然收到固定的串列名稱var ws）如果我們=== ws在另一個串列中洗掉并列印“Yanson” - 我只會收到來自.getRange(row,8)“添加新檔案”的資訊。
但是我需要發送包含其中所有單元格的完整字串（行），而不僅僅是帶有鏈接的單元格 8。而且我還需要在 msg 中查看列印“Yanson”的機器人串列名稱。因為我在 Sheet 中有超過 10 個串列。作業表看起來像這個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)
// }
}
}
}
uj5u.com熱心網友回復：
根據我從您的描述中收集到的資訊，您正在尋找一種將行的全部內容作為字串發送的方法。
為此，您將獲得該行的范圍，如下所示：
sheet.getRange(starting row, starting column, # of rows, # of cols)
Sheets 使用如下所示的二維陣列：
[[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)
// }
}
}
}
uj5u.com熱心網友回復：
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}`);
}
您可能希望將觸發器限制在某個作業表和給定的行和列，但我會將其留給您。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/314896.html