根据您的情况,进行以下修改如何?
From:
function sendMailEdit(e) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
To:
function sendMailEdit(e) {
if (e.range.isBlank()) return; // Added
var sheet = SpreadsheetApp.getActiveSpreadsheet();
- 通过此修改,当单元格为空时,以下脚本
if (e.range.isBlank()) return;
没有运行。
Added:
关于您的以下回复,
我希望在满足条件时不触发电子邮件,但特定行中没有任何值。当前场景:我在单元格 5 中输入一个值,出现警报提示(是/否),当我选择“是”时,将发送一封电子邮件。当特定行中没有值时,即使我在警报提示上选择“是”,我也想限制发送电子邮件
如果你的实际预期结果是你在评论中的回复,那么下面的修改如何?
修改后的脚本:
function sendMailEdit(e) {
const sheetName = "Response";
const { range } = e;
const sheet = range.getSheet();
if (sheet.getSheetName() != sheetName || range.columnStart != 5 || range.isBlank()) return;
const sh = SpreadsheetApp.getUi();
const response = sh.alert("Do you want to send an email?", sh.ButtonSet.YES_NO);
if (response != sh.Button.YES || !["Approved", "Rejected", "Duplicate"].includes(range.getValue())) return;
const rData = e.source.getActiveSheet().getRange(e.range.rowStart, 1, 1, 5).getValues();
let EmployeeName = rData[0][2];
let Reviewer = rData[0][4];
let Status = rData[0][5];
Logger.log(Status);
var UpdateEmail =
('<html>' +
'<body>' +
'<head>' +
'<style>table, th,tr{border: 2px solid black;}</style>' +
'</head>' +
'<tr style="background-color:#d3ecdc;"><td width=500 height=auto; colspan=6><br>Hi ' + EmployeeName + ',<br><br> Your suggestion has been ' + Status + ' by ' + Reviewer + '<br> you can check the comments here <br><br>Regards,<br>"Rajanee"<br><br></td></tr>' +
'</body>' +
'</html>')
MailApp.sendEmail({ to: EmployeeName, subject: 'Idea Status!', htmlBody: UpdateEmail, name: 'Ideas', cc: ccmail });
}
- 在此修改中,当将值放入“响应”表的“E”列时,将打开一个对话框。单击“确定”按钮时,会检查该值,当该值被选中时
"Approved", "Rejected", "Duplicate"
,发送电子邮件。当该值不是"Approved", "Rejected", "Duplicate"
,电子邮件未发送。