Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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);
}
share|improve this question
2  
What is "Tom Horwood's code"? –  Al E. Dec 16 '13 at 22:11
    
Right now you have two different unregistered accounts going. If you register an account, you can use webapps.stackexchange.com/contact to merge all of your accounts together. –  jonsca Dec 18 '13 at 18:16
    
Okay, you've posted some code, but what's the source? –  Al E. Dec 18 '13 at 19:01

1 Answer 1

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);
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.