Google-sheets – How to get data from Google Sheets to a Google Forms dropdown list

google sheetsgoogle-apps-scriptgoogle-forms

I have some data in a Google Sheets file.
I want to get a Google Forms dropdown list to be updated automatically from that list.

Is it possible?

Best Answer

Thanks for the spell check. Unfortunately this is a direct copy paste of the codes from developers.google.com
I guess they need to correct their 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?')
    .setChoices([
        item.createChoice('Cats'),
        item.createChoice('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 here.
This 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);
Logger.log(cell.getValue());

You can set a choice for 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 the sheet id by looking at the web link of your Google sheet in your browser. Its the long string of characters & numbers. (or see how to get the 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 the update list script so that the form can be updated with the 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 was submitted. Here is the working live sample form: https://support.google.com/docs/thread/48344518?hl=en

Related Topic