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);
The goal is to create a google-form that references more than one dropdown selection on a googlesheet. If my understanding is correct, what you'd like to do is to set the values of the spreadsheet as question options.
The reason why there are changes to the second dropdown and the first will not is because the logic of the script is to work on a single question.
Here's a modified version of your script that should achieve what you'd like:
I have also added some explanations of what the code does
function updateForm() {
// Gets your current active spreadsheet
var ss = SpreadsheetApp.getActive();
// Gets the spreadsheet named "Key"
var names = ss.getSheetByName("Key");
// Filters out rows from Column A2:A where the value is an empty string
var namesValues = names.getRange(2, 1, names.getMaxRows() - 1).getValues().filter(vl => vl[0] != "");
// Opens the form by its ID
var form = FormApp.openById("1Y6gm6Di2E3bmH4mlABu6ub5OnCODVHLRsavpSn-V6IA");
// Gets the form items and returns their IDs
var id = form.getItems().map(itm => itm.getId());
// Loops through all the form item IDs
id.forEach(itms => {
var namesList = form.getItemById(itms).asListItem();
namesList.setChoiceValues(namesValues);
});
}
Keep in mind that this is only a sample script that assumes both of the question items in the form are drop-downs.
To make it work only for the first two questions, it can be modified to:
function updateForm() {
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("Key");
var namesValues = names.getRange(2, 1, names.getMaxRows() - 1).getValues().filter(vl => vl[0] != "");
var form = FormApp.openById("1Y6gm6Di2E3bmH4mlABu6ub5OnCODVHLRsavpSn-V6IA");
var id = form.getItems().map(itm => itm.getId());
id.slice(0, 2).forEach(itms => {
var namesList = form.getItemById(itms).asListItem();
namesList.setChoiceValues(namesValues);
});
}
To make the code work only for all drop-down questions, regardless of their location, it can be modified to:
function updateForm() {
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("Key");
var namesValues = names.getRange(2, 1, names.getMaxRows() - 1).getValues().filter(vl => vl[0] != "");
var form = FormApp.openById("1Y6gm6Di2E3bmH4mlABu6ub5OnCODVHLRsavpSn-V6IA");
var id = form.getItems();
id.forEach(itms => {
if (itms.getType() == FormApp.ItemType.LIST) {
var namesList = itms.asListItem();
namesList.setChoiceValues(namesValues);
}
});
}
If you only have two drop-down questions, you may use the above script. However, if you decide to add similar questions in the future and don't want the code to work on them, it can be modified to:
function updateForm() {
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("Key");
var namesValues = names.getRange(2, 1, names.getMaxRows() - 1).getValues().filter(vl => vl[0] != "");
var form = FormApp.openById("1Y6gm6Di2E3bmH4mlABu6ub5OnCODVHLRsavpSn-V6IA");
// var id = form.getItems().map(itm => itm.getId());
// console.log(id);
["**********", "**********"].forEach(itms => {
var namesList = form.getItemById(itms).asListItem();
namesList.setChoiceValues(namesValues);
});
}
For questions that would have their options in adjacent columns, like Columns A and B in the spreadsheet, it can be modified to:
function updateForm() {
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("Key");
var namesValues = names.getRange(2, 1, names.getMaxRows() - 1, 2).getValues().filter(vl => vl[0] != "");
var op = namesValues[0].map((_, i) => namesValues.map(r => r[i]));
var form = FormApp.openById("1Y6gm6Di2E3bmH4mlABu6ub5OnCODVHLRsavpSn-V6IA");
// var id = form.getItems().map(itm => itm.getId());
// console.log(id);
["**********", "**********"].forEach((itms, i) => {
var namesList = form.getItemById(itms).asListItem();
namesList.setChoiceValues(op[i]);
});
}
Note: You may uncomment var id = form.getItems().map(itm => itm.getId()); and console.log(id); to retrieve the question IDs of the entire form for the ["**********", "**********"] placeholder.