Google-sheets – Script error trying to populate form drop down menu from spreadsheet

google sheetsgoogle-apps-scriptgoogle-forms

We are trying to link a list on a Google Spreadsheet to a drop down menu on a Google Form.

The twist is that this Google Spreadsheet list will be continuously updated and needs to automatically update the form.

I'm borrowing this code we found online:

var FORMID = "1aGrHm7x39kAJ-iJlFvp58saZHyARyB92uCbyvxGqE7M";
var LIST_DATA = [{title:"Which Document Did You Use?", sheet:"Documents"}]

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

function updateLists() {
  var form = FormApp.openById(1aGrHm7x39kAJ-iJlFvp58saZHyARyB92uCbyvxGqE7M);
  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);
}

It worked for the creator but I'm having this error:

Missing ) after argument list. (line 11, file "Code")

Here is the spreadsheet in question: https://docs.google.com/spreadsheet/ccc?key=0AoFVL1CoqhmxdDE2UDRHbnBaNnAzUjVHLVFQcWQxSlE&usp=sharing#gid=0

Best Answer

The line

var form = FormApp.openById(1aGrHm7x39kAJ-iJlFvp58saZHyARyB92uCbyvxGqE7M);

needs quotes around the id parameter:

var form = FormApp.openById("1aGrHm7x39kAJ-iJlFvp58saZHyARyB92uCbyvxGqE7M");

... or, use the FORMID constant you have already defined:

var form = FormApp.openById(FORMID);

Also, add a ; to terminate line 17:

var item = items[i];
Related Topic