动态更新电子表格中列的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)

• 行-整数范围的起始行
• 列-整数范围的起始列
• numRows-整数要返回的行数
• numColumns-整数要返回的列数
• row - Integer the starting row of the range
• column - Integer the starting column of the range
• numRows - Integer the number of rows to return
• numColumns - Integer the number of columns to return

因此，需要像这样设置参数:

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屋！