I have some data over Google Sheets file, i want to get Google Forms dropdown list to be updated automatically from that list.

Is it possible?



Thanks for the spell check unfortunately this is was a direct copy paste of the codes from developers.google.com guess they need to correct thier site.

Yes its possible

This can be done via google app script .setChoiceValues(values)

see full script here https://developers.google.com/apps-script/reference/forms/list-item#setchoicevaluesvalues

sample code:

var item = form.addListItem();
item.setTitle('Do you prefer cats or dogs?')

Instead of typing in the actual choices into this bracketed part of the script like the code sample above (cats) - use a cell & column range instead (4,3) that points to data range on the sheet where your dynamic dropdown choices are located, like this see here, this is piece of code is referencing an active sheet but you can modify the code to get cell ranges by referencing the sheet id instead>

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var range = sheet.getRange("B2:D4");

// The row and column here are relative to the range
// getCell(1,1) in this code returns the cell at B2, B2
var cell = range.getCell(1, 1);

You can set the choice of the dropdown list items by referencing the google sheet cell range that the form is linked to or any other sheet by referencing the google sheet id & sheet name where your dropdown choices are located. you can get sheet id by looking at web link of your google sheet in your browser its the long string of characters & numbers or how to get sheet id by code > https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getid

Once done. to get the form to auto update use sheet or form triggers to call this update list script so that the form will be updated with new choices once it reloads.

I did something similar recently- auto populating & auto updating a google form with data values from the google sheet after a response has been submitted, here is the working live sample form: https://support.google.com/docs/thread/48344518?hl=en

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.