基于表单响应将选项卡添加到Google表格 [英] Adding Tabs to Google Sheets Based on Form Response

问题描述

我试图根据为我的表单问题响应（变更位置）选择的值，将我的结构和值组织为Google表单中多个表单所需的值。我有一个清单，完成以下。我还列出了其他步骤以及我认为需要创建的内容才能实现我所寻找的目标。有人能指导我指出我可能遇到的问题或更好的方法吗？

1. 打开表格 - 完成
   
2. 打开电子表格 - 完成


3. 表格以适当的格式将回复保存到Spreadsheet - 完成


4. 设置表格对某个变量的回应 - 完成
li>

5. 将表单问题设置为变量 - 完成


6. 在for循环中查找更改位置问题 - 完成
li>根据更改位置的值，在Google表格的新选项卡中获取响应和组响应

要处理步骤7 ，我最初的想法是基于表单响应在我的for循环中创建一个for循环，然后在for循环中创建一个if / else语句来创建一个新选项卡（如果它不存在并将值保存到该表中），否则只需将值添加到适当的选项卡名称即可。

这听起来是对的吗？我接近这个错误的方式吗？

更新：

 函数onSubmit（）{
 
 //开放式营销 - 发现和变化 -  v1 
 var form = FormApp.openById（'id'）; 
 
 //开放式营销 - 发现和变化 -  v1 
 var sheet = SpreadsheetApp.openById（'id'）; 
 
 //捕获表单响应
 var formResponses = form.getResponses（）; 
 
 //捕获问题
 var items = form.getItems（）; 
 
 for（var i in items）{
 
 var title = items [7] .getTitle（）; 
 Logger.log（title）; 
 
 
 $ b  

解决方案

您的一般思考过程很好，但是您对用于解决问题的特定对象和方法做出了不恰当的选择。

您已经开始通过命名您的函数 onSubmit（），这意味着它是某种类型的Form Submission触发器函数。只要你打算在表单回复到达时处理，这是正确的方法。这些触发函数有两种风格，目前还不清楚你打算如何，所以你需要在那里作出决定。

使用Event对象的属性

触发函数响应事件，当它们被调用时，触发函数被赋予一个事件对象，其中包含有关该事件的最相关信息。

您的功能当前未使用Event对象，但它应该带来直接的好处：在您的初始脚本中，您对表单和电子表格的ID进行了硬编码，但您并不需要。

根据表单提交触发器功能的类型，您可以修改前导码以利用表单和电子表格之间的链接，使用事件数据是pas调用时调用触发器函数。 （假设您已将电子表格与表单关联起来 - 对于您的应用程序，这可能不是必需的。）

如果您的脚本是 Google表单表单提交用事件参数 e 提交事件：

  var form = e.source; 
 var ssId = form.getDestinationId（）; 
 var ss = SpreadsheetApp.openById（sheetId）; 
  

如果您的脚本是带有Event参数的 Google表格表单提交事件 e ：

  var sheet = e .range.getSheet（）; 
 var ss = sheet.getParent（）; 
 var form = FormApp.openByUrl（ss.getFormUrl（））; 
  

使用这两种方法都可以生成更多的可移植代码，因为它们可以适应事件的上下文处理。

但这不是全部......您打开表单的响应也作为Event对象的属性提供！因此，您可能会发现根本无需打开表格。

根据输入数据在特定工作表中存储回复

为了处理第7步，我最初的想法是在我的for循环中根据表单响应创建一个for循环，然后在for循环中创建一个if / else语句创建一个新选项卡（如果它不存在）并将值保存到该表中，否则只需将值添加到适当的选项卡名称中即可。

这里的语言有点令人困惑，但我的理解是：选择目标电子表格中的目标工作表，并附加新的响应，具体取决于对变更位置的响应。 不要担心循环等细节。

从我们已经看到的关于Event对象的所有信息中，您需要操作可供您使用。例如，在 Google表格表单提交触发器功能中，您可以执行以下操作：

  //根据变更位置选择目的工作表
 var destSheet = getSheet（ss，e.namedValues ['Location of Change']）; 
 //将当前响应存储在目标工作表中
 destSheet.appendRow（e.values）; 
  

通过使用实用函数 getSheet（） code>。



获取或创建工作表

该实用程序函数将逻辑封装为获取对现有工作表的引用如果有必要，可以创建一个。

/ **
*获取给定名称的工作表。如果它不存在，创建工作表。
*如果提供了新的工作表，将使用可选标题创建。
*
*发件人：
*
* @param {Spreadsheet}电子表格Google Spreadsheet对象。
* @param {字符串} sheetName要获取的图纸名称。
* @param {String []}标题可选数组标题（用于新工作表）。
*
* @returns {Sheet}表单对象。
* /
函数getSheet（电子表格，sheetName，标题）{
spreadsheet = SpreadsheetApp.getActive（）;
var sheet = spreadsheet.getSheetByName（sheetName）;
if（sheet == null）{
// Not found，so add new sheet
sheet = spreadsheet.insertSheet（sheetName）;
if（headings&& amp; headings.constructor === Array）{
sheet.getRange（1,1,1，headings.length）.setValues（[headings]）;
}
}

退货单;
}


表格替代

您可以在不使用脚本的情况下完成所有这些工作，只需使用Google表格的功能即可。

• 从当前表单开始，使用电子表格响应目的地。


• 在电子表格中，表单响应通常会转到名为表单响应的表单中，可能后跟一个数字。


• 插入为您感兴趣的类别添加附加表单。在每个类别中，使用电子表格QUERY函数选择相关响应的子集。如果你使用一个公共单元格A1来存储类别，然后把QUERY函数放在后续行中，这就简化了，参考A1中的值。这样，可以从这个复制附加表单，并通过修改A1的内容进行调整。

I am trying to conceptualize my structure and values needed to organize form responses in multiple sheets within a Google Sheet based on the value selected for one of my form question responses ("Location of Change"). I have a checklist that is completed so far as below. I also listed the additional steps and what I think I need to create to achieve what I'm looking for. Can someone guide me in pointing out where I might have issues or a better approach?

1. Open Form - Completed
2. Open Spreadsheet - Completed
3. Form saves responses in proper format to Spreadsheet - Completed
4. Set form responses to a variable - Completed
5. Set form questions to a variable - Completed
6. Find "Location of Change" Questions in a for loop - Completed
7. Get Responses and group response within new tabs in Google Sheets based on value for "Location of Change"

To work on step 7, my original thought is to create a for loop within my for loop based on the form response and then within that for loop create an if/else statement to create a new tab if it doesn't exist and save values to that sheet, otherwise just add values to appropriate tab name.

Does this sound right? Am I approaching this the wrong way?

UPDATE:

function onSubmit() {

  //Open Marketing - Discoveries and Changes - v1
  var form = FormApp.openById('id');

  //Open Marketing - Discoveries and Changes - v1
  var sheet = SpreadsheetApp.openById('id');

  //Capture Form Responses
  var formResponses = form.getResponses();

  //Capture Questions 
  var items = form.getItems();

  for (var i in items) {

    var title = items[7].getTitle();
    Logger.log(title);

  }
}

解决方案

Your general thought process is fine, but you are making inappropriate choices for the specific objects and methods that you're using to address the problem.

You've started by naming your function onSubmit(), which implies that it is some type of Form Submission trigger function. As long as you're intending to process form responses as they arrive, this is the right approach. There are two flavours of these trigger functions, and it's not clear which you intend this to be, so you need to make a decision there.

Use properties of the Event object

Trigger functions respond to events, and when they are invoked trigger functions are given an Event object that includes the most relevant information about the event.

Your function isn't currently using the Event object, but it should, with an immediate benefit: In your initial script you are hard-coding the IDs for the Form and Spreadsheet, but you don't need to.

Depending on the type of Form Submission trigger function this is, you can modify the preamble to take advantage of the linkage between forms and spreadsheets, using the event data that is passed to the trigger function when it is invoked. (This assumes that you have linked a spreadsheet with your form - for your application this may not be required.)

If your script is a Google Forms Form Submit Submit Event with an Event parameter e:

var form = e.source;
var ssId = form.getDestinationId();
var ss = SpreadsheetApp.openById(sheetId);

If your script is a Google Sheets Form Submit Event with an Event parameter e:

var sheet = e.range.getSheet();
var ss = sheet.getParent();
var form = FormApp.openByUrl(ss.getFormUrl());

Using either of those approaches will produce more portable code, since they adapt to the context of the event being handled.

But that's not all... the responses that you're opening the form for are provided as properties on the Event object as well! Because of that, you may find that you don't need to go opening the form at all.

Storing responses in specific sheets according to input data

To work on step 7, my original thought is to create a for loop within my for loop based on the form response and then within that for loop create an if/else statement to create a new tab if it doesn't exist and save values to that sheet, otherwise just add values to appropriate tab name.

The language here is a bit confusing, but here's what I understand it to mean: Select a target sheet within the destination spreadsheet and append the new responses, depending on what the response to "Location of Change" is. Don't worry about the details of looping etc. just yet.

From what we've already seen about Event objects, all the info you need to operate is available to you. For example, in a Google Sheets Form Submission trigger function you could do this:

// Choose destination sheet according to Location of Change
var destSheet = getSheet(ss, e.namedValues['Location of Change']);
// Store the current response in destination sheet
destSheet.appendRow(e.values);

That is simplified by use of a utility function, getSheet().

Get or create a sheet

This utility function encapsulates the logic to either get a reference to an existing sheet with a given name, or create one if necessary.

/**
 * Get the sheet with given name. Create sheet if it doesn't exist.
 * New sheet will be created with optional headings, if provided.
 *
 * From: 
 *
 * @param {Spreadsheet} spreadsheet   Google Spreadsheet object.
 * @param {String}      sheetName     Sheet name to get.
 * @param {String[]}    headings      Optional array of headings (for new sheet).
 *
 * @returns {Sheet}                   Sheet object.
 */
function getSheet( spreadsheet, sheetName, headings ) {
  spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName(sheetName);
  if (sheet == null) {
    // Not found, so add new sheet
    sheet = spreadsheet.insertSheet(sheetName);
    if (headings && headings.constructor === Array) {
      sheet.getRange(1,1,1,headings.length).setValues([headings]);
    }
  }

  return sheet;
}

Sheets Alternative

You could accomplish almost all this without a script, just using features of Google Sheets.

• Start with your current form, with a Spreadsheet response destination.
• In the spreadsheet, form response will typically go to a sheet named "Form Responses", perhaps with a number after it.
• Insert additional sheets for the categories you're interested in. In each of these, use a spreadsheet QUERY function to select the sub-set of responses that are relevant. This is simplified if you use a common cell, A1 say, to store the category, then put the QUERY function in a subsequent row, referring to the value in A1. That way, additional sheets can be copied from this one, and adjusted by modifying just the content of A1.

这篇关于基于表单响应将选项卡添加到Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！