Google-sheets – Is it possible to feed multiple form drop down lists

google sheetsgoogle-forms

I am using Tom Horwood's code which has worked for me for updating one drop-down. But my form has multiple drop-down lists which I would like to make dynamic. Is this possible?

Tom Horwood's code is: 

var FORMID = "YOUR FORM ID HERE";
var LIST_DATA = [{title:"Who's your tutor", sheet:"TutorName"}]

function onOpen(e){
  var menuEntries = [];
  menuEntries.push({name: "Update Lists", functionName: "updateLists"});
  SpreadsheetApp.getActiveSpreadsheet().addMenu("List Updater", menuEntries)
}

function updateLists() {
  var form = FormApp.openById(FORMID);
  var items = form.getItems();
  for (var i = 0; i < items.length; i += 1){
    for (var j = 0; j < LIST_DATA.length; j+=1) {
      var item = items[i]
      if (item.getTitle() === LIST_DATA[i].title){
        updateListChoices(item.asListItem(), LIST_DATA[i].sheet);
        break;
      }
    }
  }
}

function updateListChoices(item, sheetName){
  var data = (SpreadsheetApp.getActiveSpreadsheet()
              .getSheetByName(sheetName)
              .getDataRange()
              .getValues());
  var choices = [];
  for (var i = 0; i < data.length; i+=1){
    choices.push(item.createChoice(data[i][0]));
  }
  item.setChoices(choices);
}

Best Answer

I have answered my own question: Just add another item in the "LIST_DATA" array.
Example:

var LIST_DATA = [{title:"Who's your tutor", sheet:"TutorName"},{title:"Select subject:", sheet:"Subject"}];

So the code would read:

var FORMID = "1rfHTqTj06SE9nZdCSupvAIYAkfmwhn9wOLJnrWuE_6I";

var LIST_DATA = [{title:"Select Subject", sheet:"Subject"},{title:"Who's your tutor", sheet:"TutorName"}];

function onOpen(e){
      var menuEntries = [];
      menuEntries.push({name: "Update Lists", functionName: "updateLists"});
      SpreadsheetApp.getActiveSpreadsheet().addMenu("List Updater", menuEntries)
}

function updateLists() {
      var form = FormApp.openById(FORMID);
      var items = form.getItems();
      for (var i = 0; i < items.length; i += 1){
        for (var j = 0; j < LIST_DATA.length; j+=1) {
           var item = items[i]
           if (item.getTitle() === LIST_DATA[i].title){
              updateListChoices(item.asListItem(), LIST_DATA[i].sheet);
              break;
           }
        }
      }
}


function updateListChoices(item, sheetName){
      var data = (SpreadsheetApp.getActiveSpreadsheet()
                  .getSheetByName(sheetName)
                  .getDataRange()
                  .getValues());
      var choices = [];
      for (var i = 0; i < data.length; i+=1){
         choices.push(item.createChoice(data[i][0]));
       }
      item.setChoices(choices);
}
Related Topic