Google Form: Create a custom drop down menu based upon values in a Google Spreadsheet

How to create a custom drop down menu in a google form based upon values in a Google Spreadsheet.

1)  Create a Google Form WITH a list item and set the form to automatically post the results to a spreadsheet.  For this example code will be based upon this tutorial on how to Create a Basic Google Form.  Purely for this example the list item name is going to be called "Names of People."

2)  Here is a quick screen shot of the form.  Note that the choices of A,B,C,D and E were items that were manually entered in when creating the form in google drive.

3) Now create a NEW spreadsheet and in that sheet create a list of names like this...

4)  Create a NAMED RANGE by doing the following...

5)  Create a new script and paste in the following code. (tip: If you are unsure on how to create a script see steps 3-5 on This Blog Post on How to Send a Custom Email from a Google Script)

function editForm() {

  

  //you ONLY need to change (at MOST) the next 3 lines.. 

  var formID = '0123456789ABCDEFGHIJKLMNOP' //this is the ID of the form you are using

  var NameInForm = 'Names of People' //This is the Name from the form (steps 1 & 2)

  var NamedRange = 'CustomNames' //This is the NamedRange (step 4: item #4)

  

  // open the form and then get all items from inside the form.   

  var form = FormApp.openById(formID);

  AllFormItems = form.getItems();

  // loop through all of the items in the form

  for (i=0; i < AllFormItems.length ; ++i) {

    FormTitle = AllFormItems[i].getTitle();

    

    // Compare the title to that of each item in your form

    if (FormTitle == NameInForm) {

      FormItemId = AllFormItems[i].getId();

    }

  }

  

  // If the item was not found then exit

  if (FormItemId == 0) {

   return; 

  } 

  

  // Casting item to listItem because the form is using a drop down. 

  FormItemList = form.getItemById(FormItemId).asListItem();

  

  // Getting object of current spreadsheet from which we need

  // to get values for drop down list.

  ss = SpreadsheetApp.getActive();

  // Getting the items in your named range from the spreadsheet

  SheetItemRange = ss.getRangeByName(NamedRange);

  SheetItems = SheetItemRange.getValues();

  

  // Create the array of choices and loop through each of them

  var SheetItemChoices = []

  for (i=0; i < SheetItems.length; ++i) {

    

      SingleSheetItem = SheetItems[i][0].toString().trim();

      if (!(SingleSheetItem=='')) {

        SheetItemChoices.push(FormItemList.createChoice(SingleSheetItem));

      }

    }

   

  // Setting the choice array to drop down item.

  FormItemList.setChoices(SheetItemChoices);

}

6)  Create the following trigger

7)  Manually update your spreadsheet and then go look back at the form.  It will have automatically updated itself and look like this...