Dynamically update Google Form 'Choose from list' options from column in Spreadsheet

Question

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...
       ])
}

Answer 1

This statement:

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

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 - 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();

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.

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);
};

The complete function would look something like this:

//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)
};