在Google表单中提交后访问用户输入的数据 [英] Accessing user entered data upon submit in google forms
问题描述
我有一个Google表单，它有以下两个字段：
用户输入他的电子邮件地址并选择一个工具然后点击提交。我希望出现以下消息：
感谢您的回复。已发送电子邮件至输入的电子邮件地址以下载选定的工具。
脚本编辑器中有以下一段代码：
function emailFormSubmission（）{
var form = FormApp.getActiveForm（）; //当前表单
var dest_id = form.getDestinationId（）; //存储表单响应的目标电子表格
var ss = SpreadsheetApp.openById（dest_id）; //打开电子表格
var theFormSheet = ss.getSheets（）[0]; //读取电子表格中的第一张图片
var row = theFormSheet.getLastRow（）; //获取最后一行
var emailid = theFormSheet.getRange（row，2,1,1）.getValue（）; //获得与电子邮件ID相对应的第2列。第1列是时间戳。所以，跳过这一点。
var tool = theFormSheet.getRange（row，3,1,1）.getValue（）; //获得对应于所选工具的第3列。
form.setConfirmationMessage（'谢谢回复。电子邮件已发送给您'+ emailid +'下载'+工具）;
}
我还设置了触发器要运行 - > emailFormSubmission ，Events - > from Form ， onFormSubmit 。
会发生什么：假设第一个用户（'A'）输入他的信息并点击提交。他输入的信息得到正确显示。当第二个用户（'B'）输入他的信息并点击提交时，A的信息被显示。当第三个用户（'C'）输入他的信息并点击提交时，B的信息就会显示出来。我发现这个问题与getlastrow（）有关，因为电子表格在处理完 emailFormSubmission之后被更新。
上述代码有什么问题？如何解决这个问题？
更新
基于@ wchiquito的评论，
$ b
function emailFormSubmission（e）{
var form = FormApp。 getActiveForm（）;
//查看如何访问表单回复的链接：
//https://developers.google.com/apps-script/understanding_events?hl=zh_CN
var response = e.response; // e是类型的formresponse。
var emailid = respond.getItemResponses（）[0] .getResponse（）;
var tool = respond.getItemResponses（）[1] .getResponse（）;
Logger.log（emailid）;
Logger.log（tool）;
form.setConfirmationMessage（'Thanks for respond。一封电子邮件已发送至'+ emailid +'并附有说明以下载'+ tool +'。如果您没有在收件箱中找到我们的电子邮件，请检查您的垃圾邮件文件夹）;
Logger.log（form.getConfirmationMessage（））;
请记住事件表单提交（了解活动）收到具有以下结构的参数：
您可以执行以下操作：
function emailFormSubmission（e）{
...
var row = e。 range.getRow（）;
...
}
请尝试以下代码以观察结构参数 e ：
$ b
function emailFormSubmission （e）{
...
Logger.log（e）;
...
}
更新
首先，请原谅我的困惑，当您真正使用 Spreadsheet表单提交事件时，我向您展示了结构表单提交事件。
果然，一个表单提交事件具有以下结构：
返回一个 FormResponse code> 。
因此，定义事件：在提交表单（表单提交事件），您可以执行以下操作：
function emailFormSubmission（e）{
var itemResponses = e.response.getItemResponses（）;
for（var i = 0，len = itemResponses.length; i< len; ++ i）{
Logger.log（'回答＃％s到问题％s为％ s），
（i + 1）.toString（），
itemResponses [i] .getItem（）。getTitle（），
itemResponses [i] .getResponse（））;
}
}
但是，根据发送的数据设置确认信息作为表单的响应，看起来不太清楚，你可以设置消息，但不会显示活动响应，如果不是下一个。
I have a google-form that has the following two fields:
- Email address: - A text box
- Tool: - A radio button
- Tool 1
- Tool 2
- Tool 3
The user would enter his email address and select a tool and click submit. I would like the following message to appear:
Thanks for responding. An email has been sent to you to at entered email address to download selected tool.
I have the following piece of code in the script editor
function emailFormSubmission() {
var form = FormApp.getActiveForm();//the current form
var dest_id = form.getDestinationId(); //the destination spreadsheet where form responses are stored
var ss = SpreadsheetApp.openById(dest_id);//open that spreadsheet
var theFormSheet = ss.getSheets()[0]; //read the first sheet in that spreadsheet
var row = theFormSheet.getLastRow(); //get the last row
var emailid = theFormSheet.getRange(row,2,1,1).getValue();//get column 2 corresponding to the email id. column 1 is timestamp. so, skip that.
var tool = theFormSheet.getRange(row,3,1,1).getValue();//get column 3 corresponding to the selected tool.
form.setConfirmationMessage('Thanks for responding. An email has been sent to you '+ emailid + ' to download' + tool);
}
I have also set the triggers to be Run -> emailFormSubmission, Events -> from Form , onFormSubmit.
What happens is: Suppose the first user ('A') enters his information and clicks submit. His entered information gets displayed correctly. When second user ('B') enters his information and clicks submit, A's information is displayed. When third user ('C') enters his information and clicks submit, then B's information is displayed. I found that the issue is with "getlastrow()" since the spreadsheet is updated after emailFormSubmission is processed.
Whats wrong with the above code? How do I fix this?
UPDATE
Based on @wchiquito's comments, I changed the code to following to make it work.
function emailFormSubmission(e) {
var form = FormApp.getActiveForm();
//Check this link on how to access form response:
//https://developers.google.com/apps-script/understanding_events?hl=en
var responses = e.response;//e is of type formresponse.
var emailid = responses.getItemResponses()[0].getResponse();
var tool = responses.getItemResponses()[1].getResponse();
Logger.log(emailid);
Logger.log(tool);
form.setConfirmationMessage('Thanks for responding. An email has been sent to '+ emailid + ' with instructions to download ' + tool +'. If you do not find our email in your inbox, please check your spam folder');
Logger.log(form.getConfirmationMessage());
}
Remember that the event On form submit (Understanding Events) receives a parameter that has the following structure:
- values
- range
- namedValues
and you can do something like:
function emailFormSubmission(e) {
...
var row = e.range.getRow();
...
}
Try the following code to observe the structure of the parameter e:
function emailFormSubmission(e) {
...
Logger.log(e);
...
}
UPDATE
First, excuse my confusion, I showed you the structure of a Spreadsheet form submit event when you really are using a Form submit event.
Sure enough, a Form submit event has the following structure:
- response
Returning an object of type FormResponse.
Therefore, defining the event: On submit form (Form submit event), you can do something like the following:
function emailFormSubmission(e) {
var itemResponses = e.response.getItemResponses();
for (var i = 0, len = itemResponses.length; i < len; ++i) {
Logger.log('Response #%s to the question "%s" was "%s"',
(i + 1).toString(),
itemResponses[i].getItem().getTitle(),
itemResponses[i].getResponse());
}
}
However, the confirmation message set according to the data sent as responses of the form, does not seem very clear, you can set the message, but will not display for the active response, if not for the next.
这篇关于在Google表单中提交后访问用户输入的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
