发送电子邮件正确的触发器设置

Question

我需要一些帮助来设置触发器...我得到了一个连接到一个获取调查的表单的工作表，但是在不同的选项卡中，我仅从 2 个特定用户中提取数据，并尝试设置一个触发器，该触发器会在这 2 个用户提交表单时发送电子邮件。

我已经尝试过：从电子表格(更改时，编辑时和表单提交时(，但什么都没有... 我不想设置时间驱动或从日历，因为或者他们会多次收到同一封电子邮件，或者如果他们一个接一个地提交，只有最后一个会收到电子邮件。

脚本如下：

function SendEmail() {
  var Spreadsheet = SpreadsheetApp.getActive();
  var Sheet = Spreadsheet.getSheetByName("Email test");
  var counter = Sheet.getRange("AB1").getValue();
  var email = ""+ Sheet.getRange(counter, 25).getValue();
  Logger.log(email);
  var Subject = "Summary Of Your Coaching Session";
  var Session = Sheet.getRange(counter, 15).getValue();
  var KPI = Sheet.getRange(counter, 9).getValue();
  var exception = Sheet.getRange(counter, 6).getValue();
  var casenum = Sheet.getRange(counter, 8).getValue();
  var wins = Sheet.getRange(counter, 10).getValue();
  var coachwins = Sheet.getRange(counter, 11).getValue();
  var timeframe = Sheet.getRange(counter, 14).getValue();
  var smartplan = Sheet.getRange(counter, 18).getValue();
  var nickname = Sheet.getRange(counter, 27).getValue();
  var evenbetter = Sheet.getRange(counter, 16).getValue();
  var type = Sheet.getRange(counter, 26).getValue();
  Logger.log(type);
  if (type == 'Win'){
    var body = ('Hello '+ nickname +','+'<br><br>Here is the summary of your '+ Session +':'+'<br><br>Your coaching was based on <b>'+ KPI +'</b> and case number '+ casenum +' and we identified the following wins '+ wins +', '+ coachwins +' and we are committed to '+ smartplan +' that we will be following on ' + timeframe +'.<br><br>');
  } else if (type == "Full"){
    var body = ('Hello '+ nickname +','+'<br><br>Here is the summary of your '+ Session +':'+'<br><br>Your coaching was based on <b>'+ KPI +'</b>, from case number '+ casenum +' and the skill that we are focusing on mastering is '+ evenbetter +'. We are committing to "'+ smartplan +'" that we will be following on ' + timeframe +'.<br><br>');
  } else if (type == "Exception"){
    var body = ('Hello '+ nickname +','+'<br><br> A Coaching for Excellence exception has been submitted due to <b>'+ exception +'</b>, so we will meet next week. Your development is highly important for us, we encourage you to keep on working on your previous action plan. <br><br> If you have any questions regarding your previous action plan contact me.<br><br><br><br>');
  }
  MailApp.sendEmail(email, Subject, body, {htmlBody : body, noReply : true})
}

Answer 1

您应该使用"表单响应"触发器，然后使用该触发器提供的事件对象。事件对象提供您需要的所有表单响应数据。如果您需要访问未在表单中提交但与表单数据位于同一行的信息，则可以使用 formResponse.range.getRow() 获取行号。

这是正确使用事件对象时代码的外观：

function SendEmail(formResponse) {
  var email = formResponse.values[24];
  Logger.log(email);
  var subject = "Summary Of Your Coaching Session";
  var session = formResponse.values[14];
  var kpi = formResponse.values[9];
  var exception = formResponse.values[5];
  var casenum = formResponse.values[7];
  var wins = formResponse.values[9];
  var coachwins = formResponse.values[10];
  var timeframe = formResponse.values[13];
  var smartplan = formResponse.values[17];
  var nickname = formResponse.values[26];
  var evenbetter = formResponse.values[15];
  var type = formResponse.values[25];
  Logger.log(type);
  if (type == 'Win'){
    var body = ('Hello '+ nickname +','+'<br><br>Here is the summary of your '+ session +
                ':'+'<br><br>Your coaching was based on <b>'+ kpi +'</b> and case number '+
                casenum +' and we identified the following wins '+ wins +', '+ coachwins +
                ' and we are committed to '+ smartplan +' that we will be following on ' +
                timeframe +'.<br><br>');
  } else if (type == "Full") {
    var body = ('Hello '+ nickname +','+'<br><br>Here is the summary of your '+ session +
                ':'+'<br><br>Your coaching was based on <b>'+ kpi +'</b>, from case number '+
                casenum +' and the skill that we are focusing on mastering is '+ evenbetter +
                '. We are committing to "'+ smartplan +'" that we will be following on ' +
                timeframe +'.<br><br>');
  } else if (type == "Exception"){
    var body = ('Hello '+ nickname +','+'<br><br> A Coaching for Excellence exception has been submitted due to <b>'+
                exception +'</b>, so we will meet next week. Your development is highly important for us, we encourage '+
                'you to keep on working on your previous action plan. <br><br> If you have any questions regarding your '+
                'previous action plan contact me.<br><br><br><br>');
  }
  MailApp.sendEmail(email, Subject, body, {htmlBody : body, noReply : true})
}