确保表单提交触发器只运行一张表 [英] Ensure form submission trigger runs only one sheet
问题描述
我在特定的电子表格中放置了两张表格，这两张表格都与表格提交相关联。第一张表格/表格应该发送一封包含表单提交数据的电子邮件（原件由Amit Agarwal创建，这里是历史链接）。第二张表格没有做任何特殊的事情，因为它只是从表格中收集数据。有问题的脚本被设置为 On Form Submit 触发器。
我遇到的问题是脚本有时会从表格/表格2 。我想指定脚本需要触发哪个表单/表单来运行。我创建的修改后的代码基于大量环顾四周。下面是代码片段：
function Initialize（）{
var triggers = ScriptApp.getProjectTriggers（） ;
for（var i in triggers）{
ScriptApp.deleteTrigger（triggers [i]）;
$ b $ ScriptApp.newTrigger（SendConfirmationMail）
.forSpreadsheet（SpreadsheetApp.getActiveSpreadsheet（））
.onFormSubmit（）
.create（） ;
}
函数SendConfirmationMail（e）{
尝试{
var ss，bcc，sendername，subject，列;
var message，value，textbody，sender;
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var s = ss.getSheetByName（'Help Request Tickets'）;
var rowNumber = s.getActiveRange（）。getRowIndex（）;
var row = e.range.getRow（）;
//这是您的电子邮件地址，您将在BCC
bcc =email，email;
//这将显示为发件人姓名
sendername =sendername;
//可选，但更改以下变量
//为Google Docs电子邮件设置自定义主题
subject =subject
//这是自动回复的正文
message =message
ss = SpreadsheetApp.getActiveSheet（）;
columns = ss.getRange（1，1，1，ss.getLastColumn（））。getValues（）[0];
我应该完成这两行：
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var s = ss.getSheetByName（'Sheet1'）;
我想脚本认为Sheet2是活动的（如果我打开电子表格的话可能会是这样） 。
表单提交触发器将被调用用于执行此操作，我缺少的是什么？ 提交到电子表格的所有表单。曾几何时，只有一种形式可以与电子表格相关联，但现在，如果可能有多种形式关联，则需要考虑到这种可能性。您无法指定触发功能适用于哪种形式，但您可以检查事件的来源并作出适当的响应。
执行此操作的一种有效方法是使用一个 director 函数，它将接收所有表单提交事件，并根据接收到响应的表单将其引导到唯一的触发器函数。
我们正在将Form Responses 1与 SendConfirmationMail（）关联起来，并且假设Form Responses 2有它自己的表单提交处理函数 handleForm2（） 。 （如果没有该表单的处理程序，那么可以删除特定的情况，并且提交将在默认的情况下结束。）
/ **
*这个导演函数应该被用作最高级别表单提交触发
*函数接收来自多个表单的响应的电子表格。根据收到当前响应的
*表的名称，事件是
*指向适当的触发器子功能。
*
*从：https://stackoverflow.com/a/37839189/1677912
* /
函数formSubmitted（e）{
var sheetName = e。 。range.getSheet（）的getName（）;
switch（sheetName）{
caseForm Responses 1：
SendConfirmationMail（e）;
休息;
caseForm Responses 2：
handleForm2（e）;
休息;
默认值：
//不做任何操作
break;
$ b如果你使用Forms表单提交触发器，可以完全避免这种情况，因为目标电子表格不会是一个直接的考虑因素。
我想脚本认为Sheet2是活动的（如果我电子表格打开可能是这种情况）
不完全。触发器函数在任何电子表格UI的上下文之外调用，因此任何用户在电子表格中执行的操作都不会对其产生影响。相反，活动表单与正在处理的提交事件有关。无论如何，引用事件对象本身是一个更好的主意，而不是依赖正常操作。测试和调试确实变得更加棘手，但并非如此。有关测试触发器功能的更多信息，请参阅如何测试触发器功能在GAS？
I have two sheets in a particular spreadsheet both of which have form submissions tied to them. The first sheet/form is supposed to send an email containing the form submission data (the original was created by Amit Agarwal, here's an historic link). The second form/sheet doesn't do anything special as it just collects data from the form. The script in question is set to a On Form Submit trigger.
The issue I am having is that the script sometimes runs from form/sheet2. I would like to specify which sheet/form the script needs to be triggered from to run on. The modified code that I have created was based on lots of looking around. Here is the snippet:
function Initialize() {
var triggers = ScriptApp.getProjectTriggers();
for (var i in triggers) {
ScriptApp.deleteTrigger(triggers[i]);
}
ScriptApp.newTrigger("SendConfirmationMail")
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onFormSubmit()
.create();
}
function SendConfirmationMail(e) {
try {
var ss, bcc, sendername, subject, columns;
var message, value, textbody, sender;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Help Request Tickets');
var rowNumber = s.getActiveRange().getRowIndex();
var row = e.range.getRow();
// This is your email address and you will be in the BCC
bcc = "email", "email";
// This will show up as the sender's name
sendername = "sendername";
// Optional but change the following variable
// to have a custom subject for Google Docs emails
subject = "subject"
// This is the body of the auto-reply
message = "message"
ss = SpreadsheetApp.getActiveSheet();
columns = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
These two lines I though were supposed to accomplish this:
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Sheet1');
I suppose the script thinks Sheet2 is active (which if I have the spreadsheet open could be the case). Surely there is way to work around/accomplish this, what I am missing?
解决方案 A Sheets form submission trigger will be invoked for all forms submitted to the spreadsheet. Once upon a time, only one form could be associated with a spreadsheet, but now, with multiple form associations possible, you need to allow for that possibility. You can't specify which form a trigger function is for, but you can check the source of the event and respond appropriately.
One effective way to do this is to use a director function which will receive all form submission events, and direct them to unique trigger functions depending on which sheet received the response.
Here, we are associating "Form Responses 1" with SendConfirmationMail(), and assuming that "Form Responses 2" has its own form submission handler, handleForm2(). (If there is no handler for that form, then the specific case can be deleted, and submissions will end up in the default case.)
/**
* This director function should be used as the "top level" form submission trigger
* function for spreadsheets accepting responses from multiple forms. Events are
* directed to the appropriate trigger sub-functions according to the name of the
* sheet which received the current response.
*
* From: https://stackoverflow.com/a/37839189/1677912
*/
function formSubmitted(e) {
var sheetName = e.range.getSheet().getName();
switch (sheetName) {
case "Form Responses 1":
SendConfirmationMail(e);
break;
case "Form Responses 2":
handleForm2(e);
break;
default:
// do nothing
break;
}
}
If you use a Forms form submission trigger instead, you can avoid this altogether, since the destination spreadsheet would not be a direct consideration.
I suppose the script thinks Sheet2 is active (which if I have the spreadsheet open could be the case)
Not quite. The trigger function is invoked outside of the context of any spreadsheet UI, so what any user is doing in the spreadsheet has no effect on it. Rather, the "active" sheet is related to the submission event being handled. Regardless, it is a much better idea to reference the event object itself, rather than rely on "normal" operations. It does become trickier to test and debug, but not terribly so. For more about testing trigger functions, see How can I test a trigger function in GAS?
这篇关于确保表单提交触发器只运行一张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
