I'm trying to create a google-form that references more than one dropdown selection on a Google Sheet. I used the script below which added the dropdown to the first question. I created a second script for the second question, changing column location, and added triggers to update upon changing/editing the sheet. When performing changes the second dropdown updates but the first will not. I wanted both dropdown sources to be on the same sheet but I thought that was causing the issue so I made them separate sheets (and in the code) with no improvement. If you could point me to a way to accomplish this thank you.
function updateForm(){
// call your form and connect to the drop-down item
// This script if for the special donaations block.
var form = FormApp.openById("FormApp_Id");
//To find this with the form open right click and hit inspect
//search for data-item-id untill the dropdown box is highlighted
var namesList = form.getItemById("Item Id").asListItem();
// identify the sheet where the data resides needed to populate the drop-down
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("Key");
// grab the values in the first column of the sheet - use 2 to skip header row
var namesValues = names.getRange(2, 1, names.getMaxRows() - 1).getValues();
var studentNames = [];
// convert the array ignoring empty cells
for(var i = 0; i < namesValues.length; i++)
if(namesValues[i][0] != "")
studentNames[i] = namesValues[i][0];
// populate the drop-down with the array data
namesList.setChoiceValues(studentNames);
My solution is multiple google-form dropdowns linked to a spreadsheet that updates via a trigger. Dropdown sources are on one sheet but the columns can be defined:
function updateForm() {
var ss = SpreadsheetApp.getActive();
//Use source sheet name
var names = ss.getSheetByName("Key");
//Get location for dropdown row, colum (2,1)
var namesValues = names.getRange(2, 1, names.getMaxRows() - 1, 1).getValues().filter(vl => vl[0] != "");
var op = namesValues[0].map((_, i) => namesValues.map(r => r[i]));
var form = FormApp.openById("1qRQOHO0Mb5yVvLgBraxTUN4ScIGfNigdfrNOCRYrSJk");
//id of first dropdown, get by inspect form, search for date-item-id
["1644576493"].forEach((itms, i) => {
var namesList = form.getItemById(itms).asListItem();
namesList.setChoiceValues(op[i]);
});
//location and id for second dropdown question
namesValues = names.getRange(2, 4, names.getMaxRows() - 1, 1).getValues().filter(vl => vl[0] != "");
op = namesValues[0].map((_, i) => namesValues.map(r => r[i]));
["1962682243"].forEach((itms, i) => {
var namesList = form.getItemById(itms).asListItem();
namesList.setChoiceValues(op[i]);
});
}