从电子表格中的列动态更新 Google 表单“从列表中选择"选项 [英] Dynamically update Google Form 'Choose from list' options from column in Spreadsheet

问题描述

我正在尝试设置一个 Google 表单，该表单将创建一个从列表中选择"问题，每个选项都是从特定列中的行中提取的.

I am trying to setup a Google Form that will create a 'choose from list' question, with each option being drawn from the rows in a specific column.

我试过 此代码，但它对我根本不起作用.

I have tried this code, but it has not worked for me at all.

我到目前为止的代码如下.我能够使用特定列中仅一行的数据(在本例中为列的最后一行)创建所需的表单问题.

The code that I have so far is below. I am able to create the desired form question with the data from only one row in the specific column (in this case the last row of the column).

如果有人可以帮助我指出如何将每个选定的行仅分配给一个问题选项的正确方向.此外，如果这些是每次更新电子表格时动态更新问题选项的某种方式.

If anyone can help by pointing me the right direction as to how to assign each selected row to only one questions option. Also if these is some kind of way to dynamically update the question options every time the spreadsheet is updated.

var idColumn = 1; // ID of the selected column

//gets document ID from spreadsheet 
//(not 100% sure the role of all of these lines, have worked it out from other examples of code online)
function spreadSheetGetter() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var lr = rows.getLastRow();  

  var docId = sheet.getRange(lr,idColumn,1,1).getValue(); //gets the data from the last row in selected column

fillFormData(docId);
}


//fills for with document ID
function fillFormData(docId) {
var form = FormApp.openById('MY FORMS ID');
var item = form.addListItem();

item.setTitle('Select Existing Customer') //creates the choose from list question
     .setChoices([
         item.createChoice(docId), //data from row 1
         item.createChoice(docId) //data from row 2 etc...
       ])
}

推荐答案

本声明:

var docId = sheet.getRange(lr,idColumn,1,1).getValue();

只获取一个值.我认为您需要列中的所有值，因此请使用 getValues()(末尾的's')方法来获取二维值数组.并将 numRows 参数(要获取的行数)更改为 lr 变量.此外，您可能想从第 2 行开始.语法是:

gets only one value. I think you want all the values in the column, so use the getValues() ('s' on the end) method to get a two dimensional array of values. And change the numRows parameter (number of rows to get) to the lr variable. Also, you want to start in row 2 probably. The syntax is:

getRange(row, column, numRows, numColumns)

• row - 整数范围的起始行
• column - 整数范围的起始列
• numRows - 要返回的整数行数
• numColumns - 要返回的列数的整数

所以，参数需要这样设置:

So, the parameters need to be set like this:

var docId = sheet.getRange(2,idColumn,lr,1).getValues();

如果在电子表格中，第一行有标题名称，您可能不想在项目列表中包含标题.这就是为什么第一个参数设置为数字 2，从第 2 行开始.

If, in the spreadsheet, row one has heading names, you may not want to include heading in the item list. That's why the first parameter is set to the number 2, to start from row 2.

然后，您需要处理二维数组中的数据，并按照setChoices(choices) 方法所需的格式创建一个新数组，以便添加列表中的每个值.你需要一个编程循环.这将每次创建一个新的项目值数组，因此列中的当前值将更新到列表中.

Then, you'll need to process the data that is in the two dimensional array, and create a new array that is in the format needed for setChoices(choices) method, in order to add each value to the list. You'll need a programming loop. This will create a new array of item values each time, so the current values in the column will be updated to the list.

var thisValue = "";
var arrayOfItems = [];
var newItem = "";

for (var i=0;i<docId.length;i++) {
  thisValue = docId[i][0];
  newItem = "item.createChoice('" + thisValue + "')";
  arrayOfItems.push(newItem);
};

完整的函数如下所示:

//fills item with document ID
function fillFormData(docId) {
  var form = FormApp.openById('MY FORMS ID');
  var item = form.addListItem();

  var thisValue = "";
  var arrayOfItems = [];
  var newItem = "";

  for (var i=0;i<docId.length;i++) {
    thisValue = docId[i][0];
    Logger.log('thisValue: ' + thisValue);
    newItem = item.createChoice(thisValue);
    arrayOfItems.push(newItem);
  };

  item.setTitle('Select Existing Customer') //creates the choose from list question
     .setChoices(arrayOfItems)
};

这篇关于从电子表格中的列动态更新 Google 表单“从列表中选择"选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！