How to update a Google Forms dropdown list based off an existing spreadsheet?

Question

So I've been looking for a way to do this and found many interesting answers about Google App Scripts, but none seem to get at what I am trying to do. I am looking to have a Google Sheet (Spreadsheet) with a column of choices. Then I have multiple forms which has a question that uses a drop down menu of those same choices. However, this list of choices gets updated semi often, so we currently find ourselves manually updating 6+ forms with the new information based off of the sheet. I'd like to know if there is a way to take the information from a spreadsheet and have it automatically update the drop down list.

I haven't really done any Google Script stuff, but I can hold my own in scripting generally. I just need help finding the right direction.

KRR KRR · Accepted Answer · 2014-11-20T19:31:14

You can try getting a range of values from the sheet and loop through a column until last row as below code.

var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues()

for(n=0;n<values.length;++n){
var cell = values[n][x] ; // x is the index of the column starting from 0, replace x with some value
}

Once you get the values into cell variable, you can simple add them to the form drop down list.

// Open a form by ID and add a new list item.
 var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz');
 var item = form.addListItem();
 item.setTitle('Do you prefer cats or dogs?')
     .setChoices([
         item.createChoice('Cats'),
         item.createChoice('Dogs')
     ]);

You can refer to this documentation.

Hope that helps!

How to update a Google Forms dropdown list based off an existing spreadsheet?

2 Answers