Skip to content

Instantly share code, notes, and snippets.

@gildotdev
Created January 22, 2016 21:35
Show Gist options
  • Save gildotdev/c0b97a1ee5842916b919 to your computer and use it in GitHub Desktop.
Save gildotdev/c0b97a1ee5842916b919 to your computer and use it in GitHub Desktop.
Google Script to populate a list in Google Forms from Google Sheets
/**
After any change in the sheet, update the combobox options in the Form
*/
function onChange(e) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
var range = sheet.getDataRange();
var values = range.getValues();
var comboValues = []; // <-- cheddar will go here
// in this example we are interested in column 0 and discarding row 1 (the titles)
for (var i = 1; i <= values.length; i++) {
var v = values[i] && values[i][0];
v && comboValues.push(v)
}
// Sort the values alphabetically, case-insensitive
comboValues.sort(
function(a, b) {
if (a.toLowerCase() < b.toLowerCase()) return -1;
if (a.toLowerCase() > b.toLowerCase()) return 1;
return 0;
}
);
Logger.log(comboValues);
// Use your form ID here. You can get it from the URL
var form = FormApp.openById('<my-form-id>');
/*
Uncomment this to display the item IDs
and pick the one that you want to modify
var items = form.getItems();
for (i = 0; i < items.length; i++) {
Logger.log("ID: " + items[i].getId(), ': ' + items[i].getType());
}
*/
form.getItemById(807137578).asListItem().setChoiceValues(comboValues);
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment