Created
November 7, 2022 08:02
-
-
Save sjgknight/60e5783113d5778c2a2671ae38995367 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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