如何使用Google SpreadSheet中的唯一项作为Google表单列表项的选择? [英] How to use unique items from Google SpreadSheet as a choice to Google Forms List Item?

问题描述

我是Google脚本的新手，现在我必须制作一个带有选择列表的表单.这些选择应从Google工作表中选取. 因此，第一个问题是如何从电子表格的某些范围中仅选择唯一值? 第二个是如何传递此列表，以便它们成为列表中的项目?

I'm new with Google scripts and now I have to make a form with a list of choices. These choices should be picked up from the Google sheet. So the first question is how to chose only unique values from some range of my spreadsheet? The second is how to pass this list so that they will be the items in the list?

我尝试过的代码是:

function getMembranesList() {
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/......");
  var itemList = ss.getSheetByName('Answers').getRange("Q1:Q").getValues();
  var form = FormApp.getActiveForm();
  var item = form.addListItem()
  item.setTitle('test question');
  item.createChoice(itemList);
}

推荐答案

查看可用于填充ListItem的方法，您必须选择一个并设置数据，使其与期望的输入匹配.在我的示例中，我选择了setChoiceValues方法，该方法查找一个数组.所以我必须将这些项目操纵成一个数组.

Looking at the methods available to populate the ListItem, you have to choose one and set your data up so it matches the expected input. For my example, I chose the setChoiceValues method, which looks for an array. So I have to manipulate the items into an array.

getRange.getValues()方法不能使您得到的一件事是列表中返回了多少非空白项目.我使用了这种快速方法来获得这些项目的计数，因此我对循环有最大的限制.然后，我形成了itemArray，并仅在其中添加了非空白项目.

One thing the getRange.getValues() method does NOT get you is how many non-blank items are returned in the list. I used this quick way to get a count of those items, so I have a maximum bound for my loops. Then, I formed the itemArray and added only the non-blank items to it.

之后，只需创建ListItem并添加值即可.

After that, it's just a matter of creating the ListItem and adding the values:

function getMembranesList() {
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/...");
  var itemList = ss.getSheetByName('Answers').getRange("Q1:Q").getValues();
  var itemCount = itemList.filter(String).length;
  var itemArray = [];
  for (var i = 0; i < itemCount; i++) {
    itemArray[i] = itemList[i];
  }
  var form = FormApp.getActiveForm();
  var item = form.addListItem();
  item.setTitle('test question');
  item.setChoiceValues(itemArray);
}

这篇关于如何使用Google SpreadSheet中的唯一项作为Google表单列表项的选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！