根据两个或多个标准从谷歌表单发送电子邮件

Question

我希望你能帮忙，我在谷歌里有一个电子表格，可以跟踪新的请求，当请求者将单元格更改为yes时，第一个scrip会发送一封电子邮件，通知审批者一个新请求，还会向管理员发送一份副本。

在批准时，批准者可以将另一个单元格设置为"是"，这会触发向高级管理员发送第二封电子邮件，以确认更改已被批准，并确认现在可以执行操作。

我已经让两份草稿独立工作，但我无法让它们在同一个项目中工作，你能建议实现这一目标的最佳方式吗？

这是我的批准er代码

var admin_email='[e-mail]'; //<- update your email address here
function triggerOnEdit(e)
{
sendEmailOnApproval(e);
}
function showMessageOnApproval(e)
{
var edited_row = checkStatusIsApproved(e);
if(edited_row > 0)
{
SpreadsheetApp.getUi().alert("Row # "+edited_row+" Yes!");
}
}
function showMessageOnUpdate(e)
{
var range = e.range;

SpreadsheetApp.getUi().alert("range updated " +   range.getA1Notation());  
}
function checkStatusIsApproved(e)
{
var range = e.range;

if(range.getColumn() <= 9 && 
range.getLastColumn() >=9 )
{
var edited_row = range.getRow();

var status = SpreadsheetApp.getActiveSheet().getRange(edited_row,9).getValue();
if(status == 'Yes')
{
return edited_row;
}
}
return 0;
}
function sendEmailOnApproval(e)
{
var approved_row = checkStatusIsApproved(e);

if(approved_row <= 0)
{
return;
}

sendEmailByRow(approved_row);
}
function sendEmailByRow(row)
{
var values = SpreadsheetApp.getActiveSheet().getRange(row,1,row,4).getValues();
var row_values = values[0];

var mail = composeApprovedEmail(row_values);

//Uncomment this line for testing
//SpreadsheetApp.getUi().alert(" subject is "+mail.subject+"n message "+mail.message);

MailApp.sendEmail(admin_email,mail.subject,mail.message);
var candidate_email = composeCandidateEmail(row_values);

MailApp.sendEmail(candidate_email.email,candidate_email.subject,candidate_email.message);
}
function composeApprovedEmail(row_values)
{
var first_name = row_values[0];

var last_name = row_values[1];

var email = row_values[2];
var message = "A new Post 16 Request has been log by: "+first_name+ "nhttps://docs.google.com/spreadsheets/d/15a1f2GJMOSHQop3vdmGS8eH9-Uq_9KFD1sY7S_8Dhaw/edit?usp=sharing";
var subject = "New Post 16 Change Request ";

return({message:message,subject:subject});
}
function composeCandidateEmail(row_values)
{
var first_name = row_values[1];

var last_name = row_values[0];

var email = row_values[2];
var subject =  "New Post 16 Change Request";

var message = "Dear "+first_name+"nn"+
"A new request has been logged on the Post 16 Change Log by "+last_name+".nnPlease can you approve this request and update the Post 16 Database if required.nnnRegardsnnThe Data Teamn";
//... etc


return({message:message,subject:subject, email:email });
}

这是第二个代码。

var admin_email='[e-mail]'; //<- update your email address here

function triggerOnEdit(e)
{
sendEmailOnApproval(e);
}

function showMessageOnApproval(e)
{
var edited_row = checkStatusIsApproved(e);
if(edited_row > 0)
{
SpreadsheetApp.getUi().alert("Row # "+edited_row+" Yes!");
}
}

function showMessageOnUpdate(e)
{
var range = e.range;

SpreadsheetApp.getUi().alert("range updated " +   range.getA1Notation());  
}

function checkStatusIsApproved(e)
{
var range = e.range;

if(range.getColumn() <= 11 && 
range.getLastColumn() >=11 )
{
var edited_row = range.getRow();

var status = SpreadsheetApp.getActiveSheet().getRange(edited_row,11).getValue();
if(status == 'Yes')
{
return edited_row;
}
}
return 0;
}

function sendEmailOnApproval(e)
{
var approved_row = checkStatusIsApproved(e);

if(approved_row <= 0)
{
return;
}

sendEmailByRow(approved_row);
}

function sendEmailByRow(row)
{
var values = SpreadsheetApp.getActiveSheet().getRange(row,1,row,4).getValues();
var row_values = values[0];

var mail = composeApprovedEmail(row_values);

//Uncomment this line for testing
//SpreadsheetApp.getUi().alert(" subject is "+mail.subject+"n message "+mail.message);

MailApp.sendEmail(admin_email,mail.subject,mail.message);

}

function composeApprovedEmail(row_values)
{
var first_name = row_values[0];

var last_name = row_values[1];

var email = row_values[2];

var message = "A new Post 16 Request has been log by: "+first_name+ " and the Post 16 Database has been updated. nnCan you please confirm that his is the case and update the change log.";

var subject = "New Post 16 Change Request | Database updated ";

return({message:message,subject:subject});
}

Answer 1

答案：

您可以检查哪个单元格已被编辑，并根据该单元格运行函数

代码示例：

您将希望使用绑定到工作表的onEdit(e)简单触发函数，并且只有在编辑了正确的单元格时才运行相应的代码：

function onEdit(e) {
const cellForEditRequest = "C8" // for example
const cellForApproverEdit = "D8" // for example
const editedCell = e.range.getA1Notation()
if (editedCell == cellForEditRequest) {
// edit request code
}
else if (editedCell == cellForApproverEdit) {
// administrator email code
}
}

记住：

• 您需要确保所有函数都在同一个文件中
• 您需要确保所有函数都有不同的名称
• 您需要在onEdit()函数的前两行中设置要检查的单元格
• 您需要将其作为可安装的触发器，因为需要授权的方法(例如发送电子邮件(不能通过简单的触发器运行

参考文献：

• 可安装触发器|应用程序脚本|谷歌开发者