通过应用脚本发送仅包含电子表格中新行值的电子邮件

Question

我正在尝试使用脚本在将新行添加到审阅电子表格时发送电子邮件。关于"发送带有电子表格中所有新行值的单个电子邮件"有一个很棒的线程，但我发现在以下脚本使用触发器onEdit时，即使进行任何编辑时，我仍然会收到一封空白电子邮件(通知已发送列(标记为"已发送"。

有没有办法添加一个if语句，以便在有新行带有空白列 9 时才发送电子邮件(所以我不会收到带有onEdit触发器的空白电子邮件(？

function sendEmail() {
//setup function
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
if (ActiveSheet.getName() == 'Review Tracker') {
var StartRow = 6;
var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
var AllValues = WholeRange.getValues();
for (i in AllValues) {
var row = AllValues[i];
if (row[7] === "Ready for Review") {  
var message = "";
//iterate loop
for (i in AllValues) {
//set current row
var CurrentRow = AllValues[i];
//define column to check if sent
var EmailSent = CurrentRow[10];
//if row has been sent, then continue to next iteration
if (EmailSent == "sent") 
continue;
//set HTML template for information
message +=
"<p><b>Name: </b>" + CurrentRow[0] + "</p>" +
"<p><b>Client: </b>" + CurrentRow[1] + "</p>" +
"<p><b>Deliverable Title: </b>" + CurrentRow[2] + "</p>" +
"<p><b>Link to Review Thread: </b>" + CurrentRow[3] + "</p>" +
"<p><b>Deadline for Review: </b>" + CurrentRow[4] + "</p>" +
"<p><b>Delivery to Client: </b>" + CurrentRow[5] + "</p>" +
"<p><b>Notes: </b>" + CurrentRow[6] + "</p>" +
"<p><b>Status: </b>" + CurrentRow[7] + "</p>" + "</p><br><br>";
//set the row to look at
var setRow = parseInt(i) + StartRow;
//mark row as "sent"
ActiveSheet.getRange(setRow, 10).setValue("sent");
}
//define who to send emails to 
var SendTo = "[e-mail]";
//set subject line
var Subject = "New Deliverable to Review for " + CurrentRow[1];
//send the actual email  
MailApp.sendEmail({
to: SendTo,
subject: Subject,
htmlBody: message,
});
}
}
}
}

Answer 1

更改自

var Subject = "New Deliverable to Review for " + CurrentRow[1];
//send the actual email  
MailApp.sendEmail({
to: SendTo,
subject: Subject,
htmlBody: message,
});

自

var Subject = "New Deliverable to Review for " + CurrentRow[1];
//send the actual email If message is not empty
if (message) {// Added
MailApp.sendEmail({
to: SendTo,
subject: Subject,
htmlBody: message,
});
}//Added

修改后的脚本：

function sendEmail() {
//setup function
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
if (ActiveSheet.getName() == 'Review Tracker') {
var StartRow = 6;
var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
var AllValues = WholeRange.getValues();
var message = "";
//iterate loop
for (i in AllValues) {
//set current row
var CurrentRow = AllValues[i];
if (CurrentRow[7] == "Ready for Review" && CurrentRow[9] != "sent") {

//define column to check if sent
//var EmailSent = CurrentRow[10];
//if row has been sent, then continue to next iteration
//if (EmailSent == "sent") {
//continue;}
//set HTML template for information
message +=
"<p><b>Name: </b>" + CurrentRow[0] + "</p>" +
"<p><b>Client: </b>" + CurrentRow[1] + "</p>" +
"<p><b>Deliverable Title: </b>" + CurrentRow[2] + "</p>" +
"<p><b>Link to Review Thread: </b>" + CurrentRow[3] + "</p>" +
"<p><b>Deadline for Review: </b>" + CurrentRow[4] + "</p>" +
"<p><b>Delivery to Client: </b>" + CurrentRow[5] + "</p>" +
"<p><b>Notes: </b>" + CurrentRow[6] + "</p>" +
"<p><b>Status: </b>" + CurrentRow[7] + "</p>" + "</p><br><br>";
//set the row to look at
var setRow = parseInt(i) + StartRow;
//mark row as "sent"
ActiveSheet.getRange(setRow, 10).setValue("sent");
}//if review ready
}//For loop close
//define who to send emails to 
var SendTo = "[e-mail]";
//set subject line
var Subject = "New Deliverable to Review for " + CurrentRow[1];
//send the actual email   if message is not empty
if (message) {
MailApp.sendEmail({
to: SendTo,
subject: Subject,
htmlBody: message,
});
}//if message
}//if sheetName Review
}//End Func