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?
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.Sign up to join this community
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
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 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(); 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 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