将自定义电子表格列传递给onFormSubmit触发器 [英] Passing custom Spreadsheets column to onFormSubmit trigger

问题描述

我有一个带有两个数字字段的Google表单，所以在Answer目标电子表格中，我有三列：SUBMIT-DATE，NUM1，NUM2

列表TOT，而不是表格中的字段，用以下公式：

  ARRAYFORMULA（B2：B + C2：C）
  

所以在第四列中，每行都有一个SUM

我将这个脚本绑定到电子表格：

  function onFormSubmit（e）{
 var subject =TRY GAS ; 
 var body = e.namedValues ['NUM1'] +++ e.namedValues ['NUM2'] +=+ e.namedValues [TOT]; 
 MailApp.sendEmail（[e-mail]，subject，body）; 
} 
  

我为onFormSubmit设置触发器

但我收到的电子邮件是：

  34 + 43 = 
  

在电子表格TOTAL栏下显示77。

我认为问题是var e.namedValues不包含TOT，因为它不是一个表单字段

如何在表单上传递此值提交？

解决方案

我刚刚找到了@Mogsdad的相同解决方案，所以我写了这个：

函数onFormSubmit（e）{
 //存储响应的表单
 var sheet = e.range.getSheet（）; 
 
 //页眉标题
 var headers = sheet.getRange（1：1）。getValues（）[0]; 
 
 //纸张宽度和形式列之间的差异
 var off = headers.length; 
 
 //等待数据
 Utilities.sleep（30000）; 
 
 //偏移表单右边的范围加上所有自定义列
 var data = e.range.offset（0，0，1，off）.getValues（）[0] ; 
 
 //使用命名值创建对象
 var namedValues = {}; 
 for（var col in headers）
 namedValues [headers [col]] = data [col]; 
 
 // LOG 
 var subject =TRY GAS; 
 var body = namedValues [NUM1] +'+'+ namedValues [NUM2] +'='+ namedValues [TOT]; 
 MailApp.sendEmail（[e-mail]，subject，body）; 
} 
  

感谢所有人，我需要使用replaceText（）从模板创建PDF

I have a Google Form with two numbers field, so in the Answer destination spreadsheets I have three columns: SUBMIT-DATE, NUM1, NUM2

I add a fourth column TOT, not field in the form, with this formula:

ARRAYFORMULA(B2:B+C2:C)

So in the fourth column i have a SUM for every row

I bound this script to spreadsheet:

function onFormSubmit(e){
  var subject = "TRY GAS";
  var body = e.namedValues['NUM1']+ "+" +e.namedValues['NUM2']+ "=" +e.namedValues[TOT];
  MailApp.sendEmail("[e-mail]", subject, body);
}

And I set trigger for onFormSubmit

But the email i received is:

34+43=

While in the spreadsheet under column TOTAL, 77 is displayed.

I think the problem is var e.namedValues that doesn't contain TOT because it isn't a form field

How can I pass this value on form submit??

解决方案

I just found the same solution of @Mogsdad, so i write this:

function onFormSubmit(e){
  //THE SHEETS WHERE RESPONSE IS STORED
  var sheet = e.range.getSheet();

  //SHEET HEADERS
  var headers = sheet.getRange("1:1").getValues()[0];

  //THE DIFFERENCE BETWEEN SHEET WIDTH AND FORM COLUMNS
  var off = headers.length;

  //WAIT DATA
  Utilities.sleep(30000);

  //OFFSET FORM RANGE ON RIGHT BY off TO ADD ALL CUSTOM COLUMN
  var data = e.range.offset(0, 0, 1, off).getValues()[0];

  //CREATE OBJECT WITH NAMED VALUES
  var namedValues = {};
  for(var col in headers)
      namedValues[headers[col]] = data[col]; 

  //LOG
  var subject = "TRY GAS";
  var body = namedValues["NUM1"] + ' + ' + namedValues["NUM2"] + ' = ' +    namedValues["TOT"];
  MailApp.sendEmail("[e-mail]", subject, body);
}

Thanks all, I need this to create PDF from template using replaceText()

这篇关于将自定义电子表格列传递给onFormSubmit触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！