这是您的脚本中的代码,已修改为当J列上的日期与J1中的日期匹配时发送电子邮件。我建议您将代码更改为使用getValues()[1]并循环生成的数组,而不是每次都使用getValue(),这样会优化得多,并且会阻止您达到Google配额[2]。
function sendEmails() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("H&S Reviews").activate();
var lastRow = sheet.getLastRow();
var message = spreadsheet.getSheetByName("Email Alerts").getRange(1,1).getValue();
for (var i = 3;i<=lastRow;i++){
var emailAddress = sheet.getRange(i,8).getValue();
var firstName = sheet.getRange(i,9).getValue();
var todaysDate = sheet.getRange(1,10).getValue();
var date = sheet.getRange(i,6).getValue();
date = Utilities.formatDate(date,'GMT+0200','dd MMMM yyyy');
var task = sheet.getRange(i,2).getValue();
var description = sheet.getRange(i,3).getValue();
var messageBody = message.replace("{name}",firstName).replace("{Task}",task).replace("{Description}",description).replace("{Date}",date);
var subject = "Health & Safety Review Task";
var sendDate = sheet.getRange(i,10).getValue();
var sheetDate = new Date(sendDate);
Sdate=Utilities.formatDate(todaysDate,'yyyy:MM:dd')
SsheetDate=Utilities.formatDate(sheetDate,'yyyy:MM:dd')
Logger.log(Sdate+' =? '+SsheetDate)
if (Sdate == SsheetDate){
var subject = "Health & Safety Review Task";
MailApp.sendEmail(emailAddress,subject,messageBody);
Logger.log('SENT :'+emailAddress+' '+subject+' '+messageBody)
}
}
}
[1] https://developers.google.com/apps-script/reference/spreadsheet/range#getValues()
[2] https://developers.google.com/apps-script/guides/services/quotas
本文链接:https://www.f2er.com/3126899.html