google-sheetsgoogle-apps-scriptdropdowngoogle-forms

Multiple dropdown questions on a google-form tied to spreadsheet


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);

Solution

  • 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]);
      });
    
    }