在不同的表单提交后触发不同的Google脚本功能

问题描述

限时删除！！

目前我有五种不同的表单连接到同一个Google表格。我的最终目标是，在有人向任何表单提交回复后，他们会收到一封包含回复的电子邮件。

我知道Google表单通过回复收据，但它不是可选的。如果我有这个功能，他们就不能在没有填写电子邮件的情况下提交表格（即使他们不需要收据）。

我在现在是：
$ b $ pre $ 函数ActivityFunction（）{
var spreadsheet = SpreadsheetApp.openById（FORM ID）;
SpreadsheetApp.setActiveSheet（spreadsheet.getSheets（）[4]）;
var sheet = spreadsheet.getActiveSheet（）;

var lastRow = sheet.getLastRow（）;

//电子邮件信息

//名称
var range = sheet.getRange（lastRow，2,1,1）;
var Name = range.getValues（）;

//电子邮件地址
var range = sheet.getRange（lastRow，7,1,1）;
var Email = range.getValues（）;

//日期
var range = sheet.getRange（lastRow，3,1,1）;
var Day = range.getValues（）;

//时间
var range = sheet.getRange（lastRow，4,1,1）;
var ExerciseTime = range.getValues（）;

// Activity
var range = sheet.getRange（lastRow，5,1,1）;
var Activity = range.getValues（）;

//强度
var range = sheet.getRange（lastRow，6,1,1）;
var Intensity = range.getValues（）;

var msg =Hello+姓名+！
感谢您填写今天的活动表单，以下是您的回复：
姓名：+姓名+\ n \\ \\日期：+ Day +\\\
\Time：+ ExerciseTime +\\\
\Activity：+ Activity +\\\
\Intensity：+ Intensity +\\\
\\\
\Have a很好的一天！

MailApp.sendEmail（电子邮件，
活动形式：+日，味精）;
}


当我有一个表单提交触发器时，用另一个表单提交（这个表单路由到同一个Google Spreadsheet中的另一个工作表），它不会向他们发送他们的新提交。

我在代码中找到了一条评论这个：

 函数FormSubmitFunction（）{
 var form = FormApp.openById（'FORM ID'）; 
 ScriptApp.newTrigger（'ActivityFunction'）
 .forForm（form）
 .onFormSubmit（）
 .create（）; 
} 
  

但每次运行时都会收到一个错误。

$ b $ p TL; DR如何将活动电子表格设置为用户表单路由并触发其各自功能的工作表？

谢谢！

解决方案

这可能会帮助您更好地理解。每当一个事件（onFormSubmit，onOpen ..etc）触发一个google函数时，它会传递一个事件对象作为参数（下面例子中的参数e）。此事件对象具有可访问的属性，如此处所述：

提交表单时会触发下面的函数（下面的触发器设置细节） ，我们使用values属性来访问表单中提交的值（再次参考上面的链接）

pre $ c $ function functionFunction（e）{

//名称
var Name = e.values [2];
//值2表示在表单中输入的第二个字段。

//电子邮件地址
var Email = e.values [7];
//同样，值7表示在表单中输入的第七个字段。

//日期
var Day = e.values [3];

// Time
var ExerciseTime = e.values [4];

// Activity
var Activity = e.values [5];

//强度
var Intensity = e.values [6];

var msg =Hello+姓名+！
感谢您填写今天的活动表单，以下是您的回复：
姓名：+姓名+\ n \\ \\日期：+ Day +\\\
\Time：+ ExerciseTime +\\\
\Activity：+ Activity +\\\
\Intensity：+ Intensity +\\\
\\\
\Have a很好的一天！

MailApp.sendEmail（电子邮件，
活动形式：+日，味精）;



$ b现在为了设置触发器，电子表格的脚本编辑器接受表单。并在表单提交中为资源>当前项目触发器>从电子表格添加>添加>为活动函数设置触发器

提交它的非形式化表单将创建一个事件对象e，并且应该有你想要的所有细节，你不必担心访问表单来获取响应。

下面的代码设置针对给定表单（.forForm）触发，因此只有在提交特定表单时触发，因此在提交其他表单时未触发代码：

 函数FormSubmitFunction（）{
 var form = FormApp.openById（'FORM ID'）; 
 ScriptApp.newTrigger（'ActivityFunction'）
 .forForm（form）//设置特定表单的触发器。 
 .onFormSubmit（）
 .create（）; 
} 
  

I currently have five different forms connected to the same Google Sheet. My end goal is that after someone submits a response to any of the forms, they will receive an email with their responses.

I know that Google Forms has that ability through response receipts, but it's not optional. If I have that feature on, they cannot submit the form without filling in their email (even if they don't want the receipt).

The code I have at the moment is:

function ActivityFunction() {
  var spreadsheet = SpreadsheetApp.openById("FORM ID");
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[4]);
  var sheet = spreadsheet.getActiveSheet();

  var lastRow = sheet.getLastRow();

  //Email Info

  //Name
  var range = sheet.getRange(lastRow,2,1,1);
  var Name = range.getValues();

  //Email Address
  var range = sheet.getRange(lastRow,7,1,1);
  var Email = range.getValues();

  //Date
  var range = sheet.getRange(lastRow,3,1,1);
  var Day = range.getValues();

  //Time
  var range = sheet.getRange(lastRow,4,1,1);
  var ExerciseTime = range.getValues();

  //Activity
  var range = sheet.getRange(lastRow,5,1,1);
  var Activity = range.getValues();

  //Intensity
  var range = sheet.getRange(lastRow,6,1,1);
  var Intensity = range.getValues();

  var msg = "Hello "+Name+"!
  Thank you for filling out today's activity form. Here are your responses.
  Name: "+Name+"\n\Date: "+Day+"\n\Time: "+ExerciseTime+"\n\Activity: "+Activity+"\n\Intensity: "+Intensity+"\n\n\Have a great day!"

  MailApp.sendEmail(Email,
                    "Activity Form: "+Day, msg);
}

This code works when I have a form submit trigger on, however, if someone submits with another form (which routes to another worksheet in the same Google Spreadsheet), it won't send them their new submission.

I found a comment somewhere with code like this:

function FormSubmitFunction() {
  var form = FormApp.openById('FORM ID');
 ScriptApp.newTrigger('ActivityFunction')
     .forForm(form)
     .onFormSubmit()
     .create();
}

But I get an error every time it runs.

TL;DR How do I set the active spreadsheet to be the worksheet that the user's form routes to and trigger it's respective function?

Thanks!

解决方案

This might help you understand better. Whenever a google function is triggered by an event (onFormSubmit, onOpen ..etc) it is passed an event object as an argument (The argument "e" in the below example). This event Object has attribute which can be accessed as mentioned here: https://developers.google.com/apps-script/guides/triggers/events

The below function is triggered when a form is submitted (details of trigger setup below), we access the values submitted in the form use the values attribute (again refer the link above)

function ActivityFunction(e) {

  //Name
  var Name = e.values[2];
  //value 2 represent the second field entered in your form.

  //Email Address
  var Email = e.values[7];
  //similarly, value 7 represent the seventh field entered in your form.

  //Date
  var Day = e.values[3];

  //Time
  var ExerciseTime = e.values[4];

  //Activity
  var Activity = e.values[5];

  //Intensity
  var Intensity = e.values[6];

  var msg = "Hello "+Name+"!
  Thank you for filling out today's activity form. Here are your responses.
  Name: "+Name+"\n\Date: "+Day+"\n\Time: "+ExerciseTime+"\n\Activity: "+Activity+"\n\Intensity: "+Intensity+"\n\n\Have a great day!"

  MailApp.sendEmail(Email,
                    "Activity Form: "+Day, msg);
}

Now for setting up the trigger, make sure the above code is written in the script editor of the spreadsheet accepting the form. And setup up a trigger for the activity function from the resources > current project trigger> add> from spreadsheet> on form submit

Immaterial of which form is submitted it will create an event object "e" and should have all the detail you want and you don't have to worry about accessing the sheet to get the responses.

The below code setups trigger specific for a given form (.forForm) so only triggers when that specific form is submitted, hence your code was not triggered when other forms were submitted:

function FormSubmitFunction() {
  var form = FormApp.openById('FORM ID');
 ScriptApp.newTrigger('ActivityFunction')
     .forForm(form)  //This sets the trigger for a specific form.
     .onFormSubmit()
     .create();
}

这篇关于在不同的表单提交后触发不同的Google脚本功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..