Google Script - Create a Drop-Down List from Spreadsheet Column

(Updated on 3/21/2017 - triggers have been moved!)

(I just created a new related post on how to create a multiple choice question on the form that a bit simplified - takes the "other" answers in a multiple choice question on a form, and updates the multiple choice options with those as options.)

I guess it was only a matter of time before I ended up dabbling with Google Scripts.  Today I was asked by a teacher how he could populate a drop-down list on his Google Form from data on a spreadsheet - and have it update as he changed the data.  Not finding an add-on for this (why recreate the wheel? :-)  I explored creating a script.  And...discovered that this task is not too hard!

To do this yourself -
1. Create your form.  
Really important step.  Make sure to create your drop-down item and give it a title, but you do not need to add any options to it (because later you will populate it from the spreadsheet).

2. Create the spreadsheet where your list will reside.
You have two options for this.
A: You can create the spreadsheet for your form responses and add a second sheet to it.  That is what I did here - adding a sheet called Student Names:

B: You can create a new spreadsheet to use.  If you do this you will need to add the spreadsheet ID to line 8 of the script.

3. Open up the Script editor
Go to the Tools Menu and select Script editor

4.  Replace the code
Replace the default code -

with this - (all the text highlighted in gray include the final bracket)
 
function updateForm(){
  // call your form and connect to the drop-down item
  var form = FormApp.openById("Your Form ID");
  
  var namesList = form.getItemById("The Drop-Down List ID").asListItem();




// identify the sheet where the data resides needed to populate the drop-down
  var ss = SpreadsheetApp.getActive();
  var names = ss.getSheetByName("Name of Sheet in Spreadsheet");

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

5. Enter Your Form ID
Replace the text Your Form ID with your actual form id.  You can get this from the URL of your form when you are editing it - it is the part between the backslashes as marked here

6. Enter Your Drop-Down ID 
Probably the hardest task in this - it took me a couple hours to find the item ID.  You need to right-click on the item in your form and select to Inspect Element.

Once looking at the source, it should have a section highlighted that corresponds with that question - look for data-item-ID followed by a number. (I utilized the search for this...)

(Highlighting over the selected section then confirmed I was looking at the right item.)

Replace the text The Drop-Down List ID with the ID you just found.

6. Enter Your Sheet Name
Replace the text Name of Sheet in Spreadsheet with the name you put on your new sheet that will have the data.  (In my example the sheet is Student Names.)

7. Set Your Script Trigger
Go to the Edit Menu and select Current project's triggers.  Set at least one trigger to make your script run automatically (unless you really like opening it up all the time...)

8. Debug (test your accuracy)
If you click on the bug icon, it will debug your script.  If something needs fixing, it will give information at the bottom of the window.  If all is right with the world, it will run and nothing will happen.

That's it!  Go and test it - add names to the first column of your spreadsheet and see what happens on your form.  You can either wait for the trigger to run it, or you can manually run it with the play icon while looking at the script.

Finally - if you want the data from a column OTHER than the first one, just update this bit of code:
names.getRange(2, 1
change the 1 to the column you want (2 for column B, 3 for column C, etc).