我有一个包含三个工作表的Google Sheet。表1是一个信息页，不会更新。表2和表3是相似的，只是列中包含的数据有一些不同，表3多了一列。
对于表2和表3有单独的脚本，每次添加一行数据时发送一封电子邮件。脚本是相同的，它们只是引用不同的列和表名。他们运行良好，电子邮件发送没有问题，然而，当一行数据被输入到表3中，脚本也发送了一封与表2相关的电子邮件，引用与表3相同的行号，我不知道为什么。我不经常使用脚本，并通过各种在线搜索和帮助从这里和其他论坛拼凑这些脚本。
这是表2的脚本:var Notified = "Notified";
function sendPASSNotification(event){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("PASS Profile");
var row = sheet.getActiveRange().getRow();
//var cellvalue = ss.getActiveCell().getValue().toString();
if(event.range.getA1Notation().indexOf("J") > -1 && sheet.getRange("Q" + row).getDisplayValue() != "Notified")
{
Utilities.sleep (30000)
var rowVals = getActiveRowValuesPASS(sheet);
var aliases = GmailApp.getAliases();
Logger.log(aliases);
var bodyHTML,o,sendTO,subject;
o = {};
bodyHTML = "There has been a new PASS Profile created by " + rowVals.plannername + " in the " + rowVals.team + " team. The reason for the PASS Profile is:<br > <br >"
+ "<i><b>" + rowVals.reason + ".</i></b>" +"<br > <br >"
+ "<table border = "1" cellpadding="10" cellspacing="0"><tr bgcolor=#d9d2e9><th>Row</th><th>Layout Group</th><th>Items Affected</th><th>Location</th><th>Start Date</th><th>End Date</th><th>Cover</th></tr><tr><td align = center bgcolor = #ebb044>"+rowVals.row+"</td><td align = center>"+rowVals.layout+"</td><td align = center>"+rowVals.items+"</td><td align = center>"+rowVals.location+"</td><td align = center>"+rowVals.start+"</td><td align = center>"+rowVals.end+"</td><td align = center>"+rowVals.cover+"</td></tr></table>"
+ "<br >To view the full details of the request, use the link below.<br > <br >" +
"<a href="https://docs.google.com/spreadsheets">PASS Profile</a>"
+"<br > <br ><i><b>This is an automated email.</i>";
o.htmlBody = bodyHTML;
o.from = aliases[0];
sendTO = "email@email.com";
subject = "New PASS Profile created"
GmailApp.sendEmail(sendTO,subject,"",o);
sheet.getRange("Q" + row).setValue(Notified);
}
}
function getActiveRowValuesPASS(sheet){
var cellRow = sheet.getActiveRange().getRow();
var layoutCell = sheet.getRange("E" + cellRow);
var layout = layoutCell.getDisplayValue();
var itemsCell = sheet.getRange("F" + cellRow);
var items = itemsCell.getDisplayValue();
var locationCell = sheet.getRange("G" + cellRow);
var location = locationCell.getDisplayValue();
var startCell = sheet.getRange("H" + cellRow);
var start = startCell.getDisplayValue();
var endCell = sheet.getRange("I" + cellRow);
var end = endCell.getDisplayValue();
var coverCell = sheet.getRange("J"+ cellRow);
var cover = coverCell.getDisplayValue();
var plannernameCell = sheet.getRange("O" + cellRow);
var plannername = plannernameCell.getDisplayValue();
var teamCell = sheet.getRange("C" + cellRow);
var team = teamCell.getDisplayValue();
var reasonCell = sheet.getRange("D" + cellRow);
var reason = reasonCell.getDisplayValue();
return {
layout: layout,
items: items,
location: location,
start: start,
end: end,
cover: cover,
plannername: plannername,
team: team,
row: cellRow,
reason: reason
} }
var Notified = "Notified";
function sendSSNotification(event){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sssheet = ss.getSheetByName("Sheet 3");
var row = sssheet.getActiveRange().getRow();
if(event.range.getA1Notation().indexOf("K") > -1 && sssheet.getRange("S" + row).getDisplayValue() != "Notified")
{
Utilities.sleep (30000)
var rowVals = getActiveRowValuesSS(sssheet);
var aliases = GmailApp.getAliases();
Logger.log(aliases);
var bodyHTML,o,sendTO,subject;
o = {};
bodyHTML = "There has been new SS created by " + rowVals.plannername + " in the " + rowVals.team + " team. The reason for the SS is:<br > <br >"
+ "<i><b>" + rowVals.reason + ".</i></b>" +"<br > <br >"
+ "<table border = "1" cellpadding="10" cellspacing="0"><tr bgcolor=#cfe2f3><th>Row</th><th>Layout Group</th><th>Items Affected</th><th>Location</th><th>End Date</th><th>Cases or Cover</th><th>Value</th></tr><tr><td align = center bgcolor = #ebb044>"+rowVals.row+"</td><td align = center>"+rowVals.layout+"</td><td align = center>"+rowVals.items+"</td><td align = center>"+rowVals.location+"</td><td align = center>"+rowVals.end+"</td><td align = center>"+rowVals.casescover+"</td><td align = center>"+rowVals.cover+"</td></tr></table>"
+ "<br >To view the full details of the request, use the link below.<br > <br >" +
"<a href="https://docs.google.com/spreadsheets">SS</a>"
+"<br > <br ><i><b>This is an automated email.</i>";
o.htmlBody = bodyHTML;
o.from = aliases[0];
sendTO = "email@email.com";
subject = "New SS created"
GmailApp.sendEmail(sendTO,subject,"",o);
sssheet.getRange("S" + row).setValue(Notified);
}
}
function getActiveRowValuesSS(sssheet){
var cellRow = sssheet.getActiveRange().getRow();
var layoutCell = sssheet.getRange("E" + cellRow);
var layout = layoutCell.getDisplayValue();
var itemsCell = sssheet.getRange("F" + cellRow);
var items = itemsCell.getDisplayValue();
var locationCell = sssheet.getRange("G" + cellRow);
var location = locationCell.getDisplayValue();
var casescoverCell = sssheet.getRange("J" + cellRow);
var casescover = casescoverCell.getDisplayValue();
var endCell = sssheet.getRange("H" + cellRow);
var end = endCell.getDisplayValue();
var coverCell = sssheet.getRange("K"+ cellRow);
var cover = coverCell.getDisplayValue();
var plannernameCell = sssheet.getRange("Q" + cellRow);
var plannername = plannernameCell.getDisplayValue();
var teamCell = sssheet.getRange("C" + cellRow);
var team = teamCell.getDisplayValue();
var reasonCell = sssheet.getRange("D" + cellRow);
var reason = reasonCell.getDisplayValue();
var daysCell = sssheet.getRange("L" + cellRow);
var days = daysCell.getDisplayValue;
return {
layout: layout,
items: items,
location: location,
casescover: casescover,
end: end,
cover: cover,
plannername: plannername,
team: team,
row: cellRow,
reason: reason,
days: days
} }
这些是通过onEdit触发器执行的。
长期我需要添加第四页，也将发送电子邮件的数据输入，但需要先解决这个问题。
在你的逻辑if(event.range.getA1Notation().indexOf("J")不限制它的列J的PASS Profile，但任何表。如果你想限制它，你应该添加一个检查。
在脚本2中添加:
if( event.range.getSheet().getName() !== "PASS Profile" ) return;
if(event.range.getA1Notation().indexOf("J") > -1 && sheet.getRange("Q" + row).getDisplayValue() != "Notified")
类似于Script3:
if( event.range.getSheet().getName() !== "Sheet 3" ) return;
if(event.range.getA1Notation().indexOf("K") > -1 && sssheet.getRange("S" + row).getDisplayValue() != "Notified")