当回答特定的 Google 表单项时，如何发送审批者电子邮件

How do I send approver email when a specific Google Form item is answered

本文关键字：何发送 电子邮件 表单 Google      更新时间：2023-09-26

我在使用Google Forms/Sheets时遇到了问题 - 只是为了让你知道我从一开始就不是一个程序员，而且我对使用Apps Script非常陌生。

我正在尝试实现以下情况：

1） 让用户回答表单上的一个问题2）如果回答了该问题，并且将值写入了相关工作表中的单元格列，则自动向2个预定义的审批者电子邮件地址发送电子邮件3） 如果未回答该问题，并且没有将任何值写入单元格列，则不会向 2 个审批者发送电子邮件

如果我使用下面包含的脚本手动将值添加到工作表中的相应列，我可以完成这项工作，但如果写入工作表的值直接来自表单，则它对我不起作用（这就是我需要它做的！

下面是我在表单工作表上的脚本编辑器中编写的代码，它允许我手动更新列并将通知发送给审批者（同样，我的问题只是如果列从表单动态更新，则不会发送电子邮件）。正如现在所写，如果我手动更新第 5 列和/或第 7 列，则会向指定的审批者组发送电子邮件。

但同样，如果表单动态更新第 5 列或第 7 列，它将不会发送通知。

请帮忙，我已经困在这个上面好几天了 - 谢谢!!

这是我当前的脚本： 

/* 
 * This function sends an email when a specific Google Sheets column is edited
 * The spreadsheets triggers must be set to onEdit for this function to work
 */
function sendNotification() {
    //var ss = SpreadsheetApp.openById('1N6dqSXs8hdhCi1vrOGT7I7tD2yDMtgK7BSddMPqmuo0');  
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //Get Active cell
    var mycell = ss.getActiveSelection();
    var cellcol = mycell.getColumn();
    var cellrow = mycell.getRow();
    //Define Notification Details for vars
    var recipients = "joseph@XXXXX.com,jmang@XXXXX.com";
    var recipients1 = "joseph2@XXXXX.com"
    var subject = "NEW SENSITIVE DATA REQUEST REQUIRES APPROVAL";
    var subject1 = "ALERT: SENSITIVE DATA REQUEST";
    var body = ss.getName() + " has been updated.  Visit " + ss.getUrl() + " to view the changes.";
    //Check to see which column will trigger the email - this script sends an email to "var recipients" if 
    //column 5 is updated and sends and email to "var recipients1" when colum 7 is updated.
    if (cellcol == 5) {
        //Send the Email to recipients defined in "var recipients"
        MailApp.sendEmail(recipients, subject, body);
    }
    if (cellcol == 7) {
        //Send the Email to recipients defined in "var recipients1"
        MailApp.sendEmail(recipients1, subject1, body);
    }
    //End sendNotification
}

这是我在工作表上的触发器的设置方式：发送来自电子表格的通知 1） 更改时、2） 编辑时和 3） 表单提交时

最后一件事 - 我的公司不允许附加组件，所以我必须使用应用程序脚本来执行此操作......

设置单独的触发器以分隔事件;

对于工作表编辑事件： 

function sendNotificationAtEdit(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
//get edited range
var range = event.range;
var cellcol = range.getColumn();
var cellrow = range.getRow();
//Define Notification Details for vars
var recipients = "joseph@XXXXX.com,jmang@XXXXX.com";
var recipients1 = "joseph2@XXXXX.com"
var subject = "NEW SENSITIVE DATA REQUEST REQUIRES APPROVAL";
var subject1 = "ALERT: SENSITIVE DATA REQUEST";
var body = ss.getName() + " has been updated.  Visit " + ss.getUrl() + " to view the changes.";
    if (cellcol == 5) {
        //Send the Email to recipients defined in "var recipients"
        MailApp.sendEmail(recipients, subject, body);
    }
    if (cellcol == 7) {
        //Send the Email to recipients defined in "var recipients1"
        MailApp.sendEmail(recipients1, subject1, body);
    }
    //End sendNotification
}

对于表单提交事件：

function sendNotificationAtFormSubmit(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
//Define Notification Details for vars
var recipients = "joseph@XXXXX.com,jmang@XXXXX.com";
var recipients1 = "joseph2@XXXXX.com"
var subject = "NEW SENSITIVE DATA REQUEST REQUIRES APPROVAL";
var subject1 = "ALERT: SENSITIVE DATA REQUEST";
var body = ss.getName() + " has been updated.  Visit " + ss.getUrl() + " to view the changes.";
//if form submitted 5th question's answer is not empty
    if (event.values[4] !== '') {
        //Send the Email to recipients defined in "var recipients"
        MailApp.sendEmail(recipients, subject, body);
    }
    if (event.values[6] !== '') {
        //Send the Email to recipients defined in "var recipients1"
        MailApp.sendEmail(recipients1, subject1, body);
    }
    //End sendNotification
}
执行

所需操作的最安全方法是检查表单响应，而不是电子表格中的单元格。 至少对于"表单上提交"触发器而言。 您可能需要两者、onEdit()触发器和表单提交触发器。

因此，在"表单提交"触发器中，您需要获取当前的表单响应，获取所需的项目，然后从电子表格中对应于第 5 列的项目获取单个响应。

您可以检查响应表中的最后一行，并得到第 5 列，如果您从来没有很多人同时提交表单请求，这将完美运行。 在这种情况下，您需要锁定文档，您可能仍希望这样做。

为了使用提交时触发器获取最后一个响应，您需要将"event"对象添加到函数中。

function sendNotification(e) {

然后，您需要获取当前响应：

function sendNotification(e) {
  /* Logger.log('e: ' + e);
  for (var key in e) {
    Logger.log('key: ' + key)  
    Logger.log('value: ' + e[key]);
  };  */
  var allAnswers = e.response.getItemResponses();
  var howManyQuestionsInForm = allAnswers.length;
  Logger.log('howManyQuestionsInForm: test again: ' + howManyQuestionsInForm);
  /* for (var key in allAnswers) {
    Logger.log('key: ' + key)  
    Logger.log('value: ' + allAnswers[key]);
  }; */
  var questionThree = allAnswers[2];
  var theAnswerIs = questionThree.getResponse();
  Logger.log('theAnswerIs: ' + theAnswerIs);
  if (theAnswerIs !== "") {
    //Send email
    Logger.log('it got to here')
  };
};

Google 文档 - 表单事件

相关文章：