如何从Google表单提取响应内容 [英] How to extract response items from a Google Form
问题描述
- 电子邮件地址。
- 用户想要订阅。
每个组都有自己的电子表格。 [根据发布的代码，每个都有自己的表在同一个电子表格中。]
当用户提交表单，表单应该捕获电子邮件地址，并将数据发送到用户订阅的相应电子表格。
这是我迄今为止所做的。我卡住了..
有没有一种方法可以从特定的文本框/选项中检索数据..etc？
我知道的唯一方法是循环所有数据并逐个检索它，这使我很难将两个数据链接在一起......例如：johndoh @ email .com订阅Group 1
函数onFormSubmit（）{
var form = FormApp.getActiveForm（）;
var formResponses = form.getResponses（）;
for（var i = 0; i< formResponses.length; i ++）{
var formResponse = formResponses [i];
var itemResponses = formResponse.getItemResponses（）; （var j = 0; j< itemResponses.length; j ++）{
var itemResponse = itemResponses [j];
//检查它是否是多项选择选项
if（itemResponse.getItem（）。getType（）== FormApp.ItemType.MULTIPLE_CHOICE）{
/ /如果用户选择组1，打开电子表格并将用户的电子邮件存储在第一列
if（itemResponse.getResponse（）==1）{
var ss = SpreadsheetApp.openById（id goes here ）;
var sheet = ss.getSheetByName（团体订阅电子邮件）;
}
}
}
}
}
有没有一种方法可以从特定的文本框/选项中检索数据..etc？
是的，它会简化您的任务。相关文档是事件对象。
您的触发器功能将在触发时与事件对象一起提供。在你的情况下，一个表单表单提交事件，该事件包括一个 FormResponse < a>对象，可由响应属性访问。无需循环回复，或打开表单。
由于我们可以从单个回复中获得电子邮件和组，因此将两者联系起来就显得微不足道了。你会看到一个辅助函数被用来获取相应表单上的句柄来添加订阅。
简单的方法 - 需要所有响应
只要您的问题是必需的，由 getItemResponses（）返回的数组将包含项目响应
/ **
* Google FormForm submit事件的触发器函数。
*简单，假设提供了所有的答案，没有错误检查。
* /
函数onFormSubmit（e）{
var formResponse = e.response;
//如果所有问题都是必需的，getItemResponses以form-order返回响应
var itemResponses = formResponse.getItemResponses（）;
var email = itemResponses [0] .getResponse（）; //返回一个字符串
var group = itemResponses [1] .getResponse（）;
var sheet = getGroupSheet（group）; //使用帮助函数获取正确的表单
if（sheet）{
sheet.appendRow（[email]）; //将订户添加为单元格行
}
}
/ **
*获取指定组的表单。
* onFormSubmit（）的辅助函数。
*
* @param {string} group从用户响应中得到的组ID，
*
* @returns {Sheet}给定组的
*如果组未知，则返回null。
* /
function getGroupSheet（group）{
var ssId =id goes here;
switch（group）{
case1：
var name =团体订阅电子邮件;
休息;
// case2：//添加案例以处理其他组
// var name =其他工作表名称;
// break;
default：//如果该组未知，则记录它
name =;
Logger.log（Unexpected group ID（+ group +））;
休息;
}
if（name）{
var result = SpreadsheetApp.openById（ssId）.getSheetByName（name）;
}
else {
result = null; //为未知组返回null
}
返回结果;
}
适应性方法＃1 - 项目索引
知道所有回答项目的存在确实使事情变得简单，但我们不能总是依赖于此。如果有可能将答复项目留空，我们将需要获得具体答案。
有几种方法可以做到这一点。我们会看看两个。首先，使用项目索引：
/ **
* Google FormForm submit事件的触发器函数。
*灵活 - 通过为每个响应项目获取项目索引
*来检查特定响应项目。例如：如果用户回答问题1和3，但不是2，
* e.response将包含项目0和2的itemResponses，但不包含1.
* /
函数onFormSubmit2（e） {
var formResponse = e.response;
var itemResponses = formResponse.getItemResponses（）;
for（var i = 0; i< itemResponses.length; i ++）{
switch（itemResponses [i] .getItem（）。getIndex（））{
case 0 ：
var email = itemResponses [i] .getResponse（）; //返回一个字符串
break;
案例1：
var group = itemResponses [i] .getResponse（）;
休息;
}
}
var sheet = getGroupSheet（group）; //使用帮助函数获取正确的表单
if（sheet）{
sheet.appendRow（[email]）; //将订阅者添加为单元格行
code
$ h
$ b 可修改方法＃2 - 项目标题（问题文本）
使用索引让我们摆脱了提供所有答案但仍然脆弱的要求。如果表单已被修改，则需要维护，以确保索引保持一致。
我们可以使用的一个改进是使用文本的问题来挑选我们的回应。如果问题被重新编写，我们仍然需要小心 - 但这种方法对订单或问题的更改或添加非问题项目（如图像，分页符，视频或标题）具有适应性。
b
$ b / **
* Google窗体表单提交事件的触发器函数。
*更灵活 - 通过为每个响应项目获取项目标题
*来检查特定响应项目。只要我们知道问题是什么，就不需要以这种方式知道问题的确切顺序，
*。 （理想的是，如果表单是以编程方式创建的
*。）
* /
function onFormSubmit3（e）{
var formResponse = e.response;
var itemResponses = formResponse.getItemResponses（）;
for（var i = 0; i< itemResponses.length; i ++）{
switch（itemResponses [i] .getItem（）。getTitle（））{
case电子邮件地址：
var email = itemResponses [i] .getResponse（）; //返回一个字符串
break;
caseGroup：
var group = itemResponses [i] .getResponse（）;
休息;
}
}
var sheet = getGroupSheet（group）; //使用帮助函数获取正确的表单
if（sheet）{
sheet.appendRow（[email]）; //将订阅者添加为单元格行
}
}
I have made a form to capture 2 data.
- Email address.
- Group which user wants to subscribe to.
Each group has their own spreadsheet. [According to the posted code, each has their own sheet within the same spreadsheet.]
When a user submits the form, the form should capture the email address, and sends the data to the respective spreadsheet which the user subscribed to.
This is what I've done so far. I'm stuck..
Is there a way to retrieve data specifically from a particular textbox/options ..etc?
The only way I know is to loop all the data and retrieve it 1 by 1.. and that makes it difficult for me to link 2 data together... eg: "johndoh@email.com" subscribed to "Group 1"
function onFormSubmit() {
var form = FormApp.getActiveForm();
var formResponses = form.getResponses();
for (var i = 0; i < formResponses.length; i++) {
var formResponse = formResponses[i];
var itemResponses = formResponse.getItemResponses();
for (var j = 0; j < itemResponses.length; j++) {
var itemResponse = itemResponses[j];
// Checks if it is multiple choice option
if (itemResponse.getItem().getType() == FormApp.ItemType.MULTIPLE_CHOICE) {
// If user chooses group 1, open spreadsheet and store user's email in 1st column
if (itemResponse.getResponse() == "1") {
var ss = SpreadsheetApp.openById("id goes here");
var sheet = ss.getSheetByName("Group subscription email");
}
}
}
}
}
解决方案
Is there a way to retrieve data specifically from a particular textbox/options ..etc?
Yes, and it will simplify your task. The relevant documentation is Event Objects.
Your trigger function will be provided with an event object when it is triggered. In your case, a Forms form submission event, the event includes a FormResponse object, accessible by the response attribute. No need to loop through responses, or open the form.
Since we can get the email and the group from a single response, relating the two becomes trivial. You'll see a helper function being used to get a handle on the appropriate sheet to add a subscription to.
Simple approach - all responses required
As long as your questions are required, the array returned by getItemResponses() will contain the item responses in the order they appear in the form.
/**
* Trigger function for Google Forms "Form submit" event.
* Simple, assumes all answers are provided, no error checking.
*/
function onFormSubmit(e) {
var formResponse = e.response;
// If all questions are required, getItemResponses returns responses in form-order
var itemResponses = formResponse.getItemResponses();
var email = itemResponses[0].getResponse(); // returns a string
var group = itemResponses[1].getResponse();
var sheet = getGroupSheet( group ); // use helper function to get right sheet
if (sheet) {
sheet.appendRow([email]); // Add subscriber as a single-cell row
}
}
/**
* Gets the sheet for the indicated group.
* Helper function for onFormSubmit().
*
* @param {string} group The ID of the group, from user's response
*
* @returns {Sheet} Sheet object for the given group,
* null if group is unknown.
*/
function getGroupSheet( group ) {
var ssId = "id goes here";
switch (group) {
case "1":
var name = "Group subscription email";
break;
// case "2": // Add cases to handle other groups
// var name = "Other sheet name";
// break;
default: // If the group is unknown, log it
name = "";
Logger.log("Unexpected group ID ("+group+")");
break;
}
if (name) {
var result = SpreadsheetApp.openById(ssId).getSheetByName(name);
}
else {
result = null; // Return null for unknown groups
}
return result;
}
Adaptable approach #1 - item indexes
Knowing that all response items were present did make things simple, but we can't always rely on that. If there is a chance that response items could be left blank, we will need to get to specific answers.
There are a few ways to do this. We'll look at two. First, using the item indexes:
/**
* Trigger function for Google Forms "Form submit" event.
* Flexible - checks for specific response items by getting the item index
* for each response item. Example: if user answered questions 1 and 3, but not 2,
* e.response would contain itemResponses for items 0 and 2, but not 1.
*/
function onFormSubmit2(e) {
var formResponse = e.response;
var itemResponses = formResponse.getItemResponses();
for (var i=0; i<itemResponses.length; i++) {
switch (itemResponses[i].getItem().getIndex()) {
case 0:
var email = itemResponses[i].getResponse(); // returns a string
break;
case 1:
var group = itemResponses[i].getResponse();
break;
}
}
var sheet = getGroupSheet( group ); // use helper function to get right sheet
if (sheet) {
sheet.appendRow([email]); // Add subscriber as a single-cell row
}
}
Adaptable approach #2 - item titles (question text)
Using indexes freed us from the requirement that all answers be provided, but is still brittle; it would require maintenance effort if the form were modified, to ensure the indexes remain aligned.
An improvement we can use is to use the text of the questions to pick our responses. We'll still have to careful if questions are reworded - but this approach is resilient to changes in the order or questions or the addition of non-question items such as images, page-breaks, videos, or headers.
/**
* Trigger function for Google Forms "Form submit" event.
* More Flexible - checks for specific response items by getting the item title
* for each response item. No need to know the exact order of questions this way,
* as long as we know what the question was. (Ideal if the form is
* created programmatically.)
*/
function onFormSubmit3(e) {
var formResponse = e.response;
var itemResponses = formResponse.getItemResponses();
for (var i=0; i<itemResponses.length; i++) {
switch (itemResponses[i].getItem().getTitle()) {
case "Email Address":
var email = itemResponses[i].getResponse(); // returns a string
break;
case "Group":
var group = itemResponses[i].getResponse();
break;
}
}
var sheet = getGroupSheet( group ); // use helper function to get right sheet
if (sheet) {
sheet.appendRow([email]); // Add subscriber as a single-cell row
}
}
这篇关于如何从Google表单提取响应内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
