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

Answer 2

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

Answer 3

I've edited the functions in the answer above to works both from Sheet and from Form:

var sheetId = '<YOUR_SHEET_ID>';
var sheetName = 'sheet_1';
var column = 'C';
var formId = '<YOUR_FORM_ID>';
var listItemId = 12345678;

function spreadSheetGetter() {
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var docId = sheet.getRange(sheetName+"!"+column+"2:"+column+numRows).getValues();
  fillFormData([].concat.apply([],docId));
}

function fillFormData(docId) {
  var form = FormApp.openById(formId);
  var item = form.getItemById(listItemId).asListItem();
  item.setChoiceValues(docId);
};

I've found ids for Sheets and Form from URLs, instead for the ListItem id I've used the code provided here:

var form = FormApp.getActiveForm();
var items = form.getItems();
for (var i in items) { 
  Logger.log(items[i].getTitle() + ': ' + items[i].getId());
}

Answer 4

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.