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

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

I am not sure if this will help you, but there is an easier way than coding this operation yourself. There is a google forms add-on called FormRanger that will auto-populate the fields in a "Choose from List" question based upon info form the rows of another spreadsheet. You can install it by going to the Add-on's menu of the form and then adding a new add-on.

Here is a link to the description of FormRanger. There is a tutorial in this document as well.

https://docs.google.com/document/d/1qidnsrTShKU9kPnYz4nubHs2cK9yWnQ2z_XBoqgdIhs/edit

Hope I'm not too late for this but I managed to get the solution mentioned in the question to work and it seems a better option since it updates a form that's already built, there's no need to create a new one or a new option for this. I also modified the code presented by Allnatural and ChrsF on the original post so that it accepts named ranges (or regular ranges, for that matter), therefore reducing the number of sheets you would need on your spreadsheet.

You should place this script in the spreadsheet with the options, so it can run whenever you change it or in set intervals:

//Here you set the options for the question on your form you want to dinamically change and which named range will populate it
var LIST_DATA = [{formFieldTitle:"Who's your tutor", namedRange:"reg_opcoes"}]

//Inserts a menu entry that allows you to run the function to update the options of the form
function onOpen(e){
  var menuEntries = [];
  menuEntries.push({name: "Update Lists", functionName: "updateLists"});
  SpreadsheetApp.getActiveSpreadsheet().addMenu("List Updater", menuEntries)
}

//The update function itself, it will run for as many options as you set in the LIST_DATA list
function updateLists() {
  var form = FormApp.openById('1Pl3i5Qr8Kq5C2UbBUqA0iZCsmaHXum5eWR1RfIaEL6g'); //Insert your form ID here
  var items = form.getItems();
  for (var i = 0; i < items.length; i += 1){
    for (var j = 0; j < LIST_DATA.length; j+=1) {
      var item = items[i]
      if (item.getTitle() === LIST_DATA[j].formFieldTitle){ //Here the titles of the questions are called
        updateListChoices(item.asListItem(), LIST_DATA[j].namedRange); //Here the range that populates que question with options is called
        break;
      }
    }
  }
}

//Function that actually gets the values from the range you defined
function updateListChoices(item, sheetName){
  var data = (SpreadsheetApp.getActiveSpreadsheet()
              /* 
                 If you want, you might change the option here to .getDataRange() and work with defined ranges.
                 For that, yoiu should also place a .getSheetName() before it, so that'll know of what interval you're talking about
              */
              .getRange(sheetName) 
              .getValues());
  var choices = [];
  for (var i = 0; i < data.length; i+=1){
    choices.push(item.createChoice(data[i][0]));
  }
  item.setChoices(choices);
}

After you got it to work, you may seta trigger to run the "updateLists" function on every change made in your spreadsheet, then there's no need to manually update the option with the menu button.

I have been searching for this solution for quite some time now and I'm glad I found it. Hope it helps you too.