Google Script - Making a Form Question from Spreadsheet Data

This is totally a revisit of the post I did on made on October 31, 2016 about "Google Script - Create a Drop-Down List from Spreadsheet Column"

I had another use for this today.  I wanted educators completing a form to fill in their district.  Yet the thought of all the different spelling/acronyms I might give was going to make the data analysis crazy.  For people from my organization alone in the first 5 minutes I got Calhoun ISD, Calhoun, Calhoun Intermediate School District, CISD.  Seriously.

I remembered this script I used before to create a drop-down from a spreadsheet, and figured I could revamp it for use with a Multiple Choice item - one where people could input their district if it was not there yet using the 'other' option.

Viola!  Here it is modified for a multiple choice - much is the same as before, but there are some differences.

To do this yourself -
1. Create your form.  
Really important step.  Make sure to create your multiple choice 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). Make sure to add the option of "other" so people can input items onto the list.

2. Create the spreadsheet where your list will reside.
I created the spreadsheet for my form responses and add a second sheet to it, calling the additional sheet Districts:

3. An additional step on this spreadsheet - since I want to constantly update this from the form entries (column E on my responses sheet), in Column C I ran a query 

=query('Form Responses 1'!E1:E) 

This pulled all the entries for the District question from the responses sheet.  I the ran another formula in Column A to sort them and only get the unique entries

=unique(sort(C2:C))

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 multiple choice item
  var form = FormApp.openById("Your Form ID");
   
  var namesList = form.getItemById("The Multiple Choice ID").asMultipleChoiceItem();

// identify the sheet where the data resides needed to populate the multiple choice
  var ss = SpreadsheetApp.getActive();
  var names = ss.getSheetByName("Name of Sheet in Spreadsheet with list");

  // 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 districtNames = [];

  // convert the array ignoring empty cells
  for(var i = 0; i < namesValues.length; i++)    
    if(namesValues[i][0] != "")
      districtNames[i] = namesValues[i][0];

  // populate the drop-down with the array data
  namesList.setChoiceValues(districtNames);
  
}

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.  I selected to View Source, then utilized the search for to look for data-item-ID ...then did a comparison until I found the right question.  This ID will be a random alpha-numeric - mine was a 9 digit number.  It appears multiple places in the source. (One hint from another person was to do a 1-question form at first for this, then add the others later.)

Replace the text The Multiple Choice 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 Districts.)

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...).  Mine runs every time someone submits a form entry.

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).