Skip to content

Instantly share code, notes, and snippets.

@sjgknight
Created November 7, 2022 08:02
Show Gist options
  • Save sjgknight/60e5783113d5778c2a2671ae38995367 to your computer and use it in GitHub Desktop.
Save sjgknight/60e5783113d5778c2a2671ae38995367 to your computer and use it in GitHub Desktop.
// with thanks to http://wafflebytes.blogspot.com/2018/12/google-script-revisiting-making-form.html and http://wafflebytes.blogspot.com/2016/10/google-script-create-drop-down-list.html
// that script has some minor errors, notably asMultipleChoiceItem should be asCheckboxItem
// to find the question ID open the form in preview, and inspect element on the question. Scroll up the divs and you should see something like data-params='%.@.[ - the ID should be in that (and then the question text)
// In the googlesheet you can use the following formula to pull from the relevant column in FormResponses1 AND prepopulate this with your own responses (E2:E20) in GSOptions sheet
// =unique(query({GSOptions!E2:E20;'FormResponses1'!G2:G}))
// In the googlesheet if you want to concatenate two fields for question options you can do this
// =transpose(query(transpose(UNIQUE(query('Form Responses 1'!A2:C, "select C,A order by C asc",1))),,COLUMNS(UNIQUE(query('Form Responses 1'!A2:C, "select C,A order by C asc",1)))))
// w/ thanks https://stackoverflow.com/questions/42571114/how-to-use-concat-in-query
function updateForm(){
// call your form and connect to the multiple choice item
// find the google form ID in edit mode between /forms/d/ and /edit
var form = FormApp.openById("your-identifier-here");
// identify the sheet where the data resides needed to populate the multiple choice
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("GSOptions");
// get the question IDs you want to update/set
// if you're doing this for multiple questions you could declare multiple things here, just keep track of them
var namesList = form.getItemById("1234567").asCheckboxItem();
var namesList2 = form.getItemById("7654321").asCheckboxItem();
var nameSingle = form.getItemById("123654").asMultipleChoiceItem();
// get the data from the sheet (identified above by name)
// grab the values in the first column of the sheet - use 2 in first position (i.e., (2, x) to skip header row.
// change the 1 (i.e., (x, 1), to the column you want (2 for column B, 3 for column C, etc).
var namesValues = names.getRange(2, 1, names.getMaxRows() - 1).getValues();
var namesValues2 = names.getRange(2, 2, names.getMaxRows() - 1).getValues();
var namesValues3 = names.getRange(2, 3, names.getMaxRows() - 1).getValues();
var principles = [];
var context = [];
var cases = [];
///////////////////////////////////////////////
// convert the arrays ignoring empty cells
///////////////////////////////////////////////
// First for principles
for(var i = 0; i < namesValues.length; i++)
if(namesValues[i][0] != "")
principles[i] = namesValues[i][0];
// populate the drop-down with the array data
namesList.setChoiceValues(principles);
// Second for context
for(var i = 0; i < namesValues2.length; i++)
if(namesValues2[i][0] != "")
context[i] = namesValues2[i][0];
// populate the drop-down with the array data
namesList2.setChoiceValues(context);
// Third for cases
for(var i = 0; i < namesValues3.length; i++)
if(namesValues3[i][0] != "")
cases[i] = namesValues3[i][0];
// populate the drop-down with the array data
nameSingle.setChoiceValues(cases);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment