CODEWITHSUNDEEP
drop-down-menugoogle-apps-scriptgoogle-sheetsgoogle-appsgoogle-forms
B-Stewart
B-Stewart

Reputation: 697

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

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.

Upvotes: 2

Views: 12946

Answers (2)

Angelking
Angelking

Reputation: 47

You should be able to use a formRanger AddOn which would allow you to update a spreadsheet.

This would then automatically update all the drop down dependent on the data you have added to the spreadsheet.

Upvotes: 0

KRR
KRR

Reputation: 4927

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!

Upvotes: 2

Related Questions