我正在嘗試創建代碼，該代碼將使用谷歌表格中特定列的地址發送電子郵件。我希望代碼在其他用戶編輯作業表后發送電子郵件。例如，有人在作業表的一行中輸入請求 - 然后將電子郵件發送給請求的經理。這是我到目前為止所擁有的...
function SendEmail(){
// Fetch the email address
var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("HLP REQUESTS").getRange("K:K");
var emailAddress = emailRange.getValues()[0][0];
// Send Alert Email.
var message = 'A request has been submitted for professional learning related to an HLP you champion. Please check the Design Team Notes document in case follow-up is required.'; // Second column
var subject = 'HLP Request for Professional Learning';
MailApp.sendEmail(emailAddress, subject, message);
}
當我運行上面的代碼時，我收到一個錯誤 - 例外：發送電子郵件失敗：沒有收件人。K列中有一個有效的電子郵件地址，所以我有點困惑。
uj5u.com熱心網友回復：
如果您想從 K 列的最后一個單元格中獲取電子郵件地址，可以通過以下方式完成：
function SendEmail(){
var emailRange = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName("HLP REQUESTS").getRange("K:K");
var emailAddress = emailRange.getValues()
.flat() // convert a 2d array into a flat array
.filter(String) // remove empty elements from the array
.pop(); // get the last element from the array
var message = 'A request has been submitted for professional learning related to an HLP you champion. Please check the Design Team Notes document in case follow-up is required.';
var subject = 'HLP Request for Professional Learning';
MailApp.sendEmail(emailAddress, subject, message);
}
更新
這是帶有可安裝觸發器的完整實作，該觸發器onEdit在選中復選框（在 L 列中）后立即發送電子郵件：
// global variables
var SS = SpreadsheetApp.getActiveSpreadsheet(); // get the srpreadsheet
var checkbox_col = 12; // column with checkboxes (L in this case)
// the main function
function sendEmail(e) {
try {
var {rowStart, columnStart} = e.range; // get row and column of the cell that was edited
if (columnStart != checkbox_col) return; // do nothing if it was not column with checkboxes
if (e.value != 'TRUE') return; // do nothing if the checkboxs was unchecked
e.range.setValue(false); // else uncheck the chekbox
var sheet = SS.getSheetByName('HLP REQUESTS'); // get the sheet
var emailAddress = sheet.getRange('K' rowStart).getValue(); // get email addres from current row, column K
var message = 'A request has been submitted for professional learning related to an HLP you champion. Please check the Design Team Notes document in case follow-up is required.';
var subject = 'HLP Request for Professional Learning';
MailApp.sendEmail(emailAddress, subject, message); // send the message
SS.toast('Email to ' emailAddress ' has been sent');
}
catch(e) { SpreadsheetApp.getUi().alert(e) }
}
// additional functions -------------------------------------------------------------------------
// insatll the trigger
function install_onEidt_trigger() {
ScriptApp.newTrigger('sendEmail').forSpreadsheet(SS).onEdit().create();
SS.toast('Trigger was installed');
}
// remove all triggers
function remove_all_triggers() {
ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));
SS.toast('All triggers were remoded');
}
// custom menu to install and remove triggers
function onOpen() {
SpreadsheetApp.getUi().createMenu('?? Scripts')
.addItem('Install trigger', 'install_onEidt_trigger')
.addItem('Remove all triggers', 'remove_all_triggers')
.addToUi();
}
為了使它作業，你有：
- 重新加載電子表格（或手動運行函式
onEdit()）以獲取自定義選單腳本 - 在自定義選單中運行專案安裝觸發器
- 之后，只要用戶單擊 L 列中的復選框，它就會嘗試將訊息從當前行的 K 列發送到地址
我的測驗表如下所示：
uj5u.com熱心網友回復：
發送電子郵件
function SendEmail() {
const ss = SpreadsheetApp.getActive();
const rsh = ss.getSheetByName("HLP REQUESTS");
const emails = rsh.getRange("K1:K" rsh.getLastRow()).getDisplayValues().flat();
var message = 'A request has been submitted for professional learning related to an HLP you champion. Please check the Design Team Notes document in case follow-up is required.';
var subject = 'HLP Request for Professional Learning';
emails.forEach(e => {
MailApp.sendEmail(e, subject, message);
});
}
如果您希望將此附加到 onEdit，您將不得不重新考慮該程序，因為 onEdit 觸發器會在對任何作業表的每次編輯時觸發，并且很可能您將需要使用可安裝的 onEdit，以便您可以執行需要權限的操作。我建議您暫時使用 onEdit 簡單觸發器。查看事件物件并以低開銷成本查看可用的內容。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/477104.html
上一篇：如何使用1個公式按日期計算專案？