google-apps-script - 在电子邮件脚本中返回“未定义”或没有数据的变量

Question

我正在尝试让脚本从工作表中发送一封确认电子邮件，由“提交表单”触发。除了 2 个变量之外，这一切都有效，脚本似乎没有选择。其中一个变量来自另一个函数并粘贴到工作表中。另一个变量是每次输入新数据时在工作表中创建的参考编号。

代码：

function myFunction(e) {

  var userName = e.values[2];  //this info comes from form and works
  var teamName = e.values[1];  //this info comes from form and works
  var recipient = e.values[4]; //this info comes from form and works
  var refNumber = e.values[22]; //this info comes from an array in a column and returns "undefined"
  var NetballWeekend = e.values[6]; //this info comes from form and works
  var editURL = e.values[20]; //this info comes from another function which pastes it into column "U" and returns blank

  var subject = "SunSports Netball Weekend - Your Booking for " + teamName + "." ;
  var body = "Hello " + userName + "."
 + "\n" + "We have received your booking form for the " + NetballWeekend + "."

标签： google-apps-scripttriggersundefinedgoogle-forms

Answer 1

e.values是绑定到事件的事件对象onFormSubmit

• 事件对象意味着这些值与事件相关联，或者换句话说，对于 formSubmit 它是通过表单提交的值
• 如果您使用附加值填充表单响应中新提交的值的行，则无法使用这些值检索这些值e.values
• 相反，您可以使用e.range.getRow()检索包含最新表单提交的行号并检索手动添加的值，例如 getRange(e.range.getRow(), 21).getValue()
• 请注意，如果在提交表单数据后将 U 和 W 列中的值添加到后面，则它们可能无法直接在表单提交时使用，但会略有延迟
• 在这种情况下，它们可能是undefinedonFormSubmit，最好的方法是等待，例如在检索这些值之前合并Utilities.sleep()

样本：

function myFunction(e) {
  Utilities.sleep(1000);
  var range = e.range;
  var row = range.getRow();
  var sheet = range.getSheet();
  var userName = e.values[2];  //this info comes from form and works
  var teamName = e.values[1];  //this info comes from form and works
  var recipient = e.values[4]; //this info comes from form and works
  var refNumber = sheet.getRange(row, 23).getValue();
  var NetballWeekend = e.values[6]; //this info comes from form and works
  var editURL = sheet.getRange(row, 21).getValue();

  var subject = "SunSports Netball Weekend - Your Booking for " + teamName + "." ;
  var body = "Hello " + userName + "."
 + "\n" + "We have received your booking form for the " + NetballWeekend + "."
  ...
}