返回“未定义"的变量或电子邮件脚本中没有数据 [英] Variables returning "undefined" or no data in email script

问题描述

我正在尝试使脚本从工作表发送确认电子邮件，该电子邮件由在表单提交时"触发. 除了2个似乎无法执行脚本的变量之外，其他所有命令均有效. 这些变量之一来自另一个函数，并粘贴到工作表中. 另一个变量是参考数字，每次输入新数据时，该参考数字都会在工作表中创建.

I am trying to make a script send an confirmation email from a sheet, triggered by "On form submit". It all works apart from 2 variables which it seems the script doesn't pick up. One of these variables comes from another function and is pasted into the sheet. The other variable is a reference number which is created in the sheet each time new data is entered.

代码:

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 + "."

推荐答案

e.values是绑定到事件onFormSubmit

的事件对象

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

e.values is an event object bound to the event onFormSubmit

• Event object means that those values are tied to the event, or in other words, for formSubmit it's the values that have beens ubmitted through the form
• If you populate the row of freshly submitted values from the form response with additional values, those values cannot be retrieved with e.values
• Instead, you can use e.range.getRow() to retrieve the row number contianing the newest form submission and retrieve the manually added values with e.g. getRange(e.range.getRow(), 21).getValue()
• Mind that if the values in column U and W are added posteriorly once the the form data has been submitted, they might not be avaialble directly on form submit but slightly delayed
• In this case they might be undefined onFormSubmit and the best approach would be to wait e.g. by incorporating Utilities.sleep() before retrieving those values

示例:

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 + "."
  ...
}

这篇关于返回“未定义"的变量或电子邮件脚本中没有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！