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

Comments

  1. Brilliant blog... nice...
    I have a requirement to populate only the required values in the dropdown which I own. Do we have the way to do it.
    The sheet used by the google form consists of columns "Country" and "UserID". If the userID matches with my login, then only the country which I own should get populated in the dropdown.
    Thank you and let me know if you have idea over here.

    ReplyDelete
    Replies
    1. Sorry - I don't have time within my job to do modifications with the scripts unless it is for people within my organization. I share out what I have done so hopefully someone else can spend less time on it than I did to build it.

      Delete
    2. How should I change the code to have multiple questions with the drop-down list?
      Thank you for this work, I found it very useful!

      Delete
    3. Priceless persistence there "Unknown"!

      Author writes helpful blog
      You follow up with what's effectively "can you do my work for me?"
      Author politely explains no
      You ask again, just using different words 👍

      Delete
    4. (I'll give them the benefit of the doubt that it is a different "Unknown" ;-) )

      But, regardless, this ask is beyond what I am able to do at this time.

      Delete
  2. Hi,
    Can I have an update for my previous comment?
    Thanks

    ReplyDelete
  3. Thank you very much for this, Melinda!

    ReplyDelete
  4. Very Nice. Thank you.
    However I was unable to find the correct drop down list ID - and I tried passing the ID as a string "123" and also as an integer 123 (ID is type integer...) and instead just did this

    var namesListItemArray = form.getItems(FormApp.ItemType.LIST);
    var namesList =namesListItemArray[0].asListItem(); //its the first list i want

    ReplyDelete
    Replies
    1. Brilliant solution for finding it another way! Finding the ID is what I seem to spend the most time on...

      Delete
  5. Thank you so much for this Melinda!
    super easy to follow and works perfectly - even for me who's never coded!

    ReplyDelete
  6. You saved lot of time.
    Thanks

    ReplyDelete
  7. This is amazing! It's very very helpful. Thank you.

    ReplyDelete
  8. Just used the script and it works a treat. Thank you!

    ReplyDelete
  9. Thanks Melinda. It works. This has great potential !!!

    ReplyDelete
  10. Brilliant Melinda. Thanks again.

    ReplyDelete
  11. So helpful! This worked flawlessly for me. Thank you so much for your clear and thorough instructions!

    ReplyDelete
  12. Hi Linda,
    I've been looking for a way to do this. This looks very doable. :)

    ReplyDelete
  13. This is a brilliant post. Thank you very much.
    I have a question I am hoping you can answer: I update the spreadsheet using another form. I added a "on form submit" trigger to the spreadsheet script and the following function:

    function onFormSubmit(e) {
    MailApp.sendEmail('my email address, 'updateForm is running', 'triggered
    automatically');
    updateForm();
    }
    My trigger looks like this (I cannot find a way to attach a screen shot):
    Me | - | Head | From spreadsheet - on form submit | onFormSubmit

    It is not triggering when I submit an update from my form. Is there a reason you can think of?

    ReplyDelete
    Replies
    1. Not sure - I've sometimes turned triggers off, and then put them back on and they have worked?

      Delete
  14. Awesome man, you saved me dollars. Great job.

    ReplyDelete
  15. I want only distinct values not duplicate..how to do that.?

    ReplyDelete
    Replies
    1. Use "unique" in a formula on the entries in your spreadsheet - this will have to be in a different column, so make sure to match to the correct column in the script then. (Line with namesValues - change the 1 to the corresponding column)

      Delete
  16. Hi how about getting rid of duplicate entries? THanks

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Use "unique" in a formula on the entries in your spreadsheet - this will have to be in a different column, so make sure to match to the correct column in the script then. (Line with namesValues - change the 1 to the corresponding column)

      Delete
    3. Can you provide a sample on the "unique" case?

      Delete
    4. Here's an example on how I used it to find the unique entries and sort the data, before pulling on it in the script - this was simply on the spreadsheet in another column.

      =unique(sort(C2:C))

      I then had to reference the correct column in this line of the script (change the 2,1 to 2, the correct column reference):

      var namesValues = names.getRange(2, 1, names.getMaxRows() - 1).getValues();

      Delete
    5. thank you so much Melinda :D

      Delete
  17. it works! thank you !!!!!!!!!!

    ReplyDelete
  18. This is great, thanks so much. Please let me know what I would need to change to have it work with a Multiple Choice selection instead of a Dropdown? Thanks in advance.

    ReplyDelete
    Replies
    1. Multiple Choice - replace asListItem() with asMultipleChoiceItem() in the script.

      Happy scripting!

      Delete
  19. OK, that is amazing. I just did this and it's utterly perfect - exactly what I needed. Thanks for documenting this!!!

    ReplyDelete
  20. Melinda, this is a brilliant article. Thank you so much for it. I do have a question though.

    Is it possible to have dependent responses using this code and if so how would you do it?

    for instance if you were to choose a car type first and the second question would be color where the color of the vehicle would be depending on the car chosen

    i.e. Auto 1 comes in Blue and Black, Auto 2 comes in Blue and Green, Auto 3 comes in Black and Green

    Would you have to have every combination of car and color in your google sheet?

    ReplyDelete
    Replies
    1. I'm not sure - this isn't something I have explored. If you figure it out please be sure to link a solution back to here :-)

      Delete
  21. Any way where we can display values in dropdown after sorting?

    ReplyDelete
  22. Aloha. This worked perfectly. Thank you so much for sharing!

    ReplyDelete
  23. Thanks Its very helpful for me

    ReplyDelete
  24. This is awesome, thank you so much for sharing this!

    Is there a way to auto-populate a dropdown on the form based on the selection on a previous drop down?

    e.g. Column A on the sheet is 'Names' and Column B is 'Name ID', if a user selects a Name from the drop down then it will aut-populate the next drop-down with the associated ID.

    Thanks!

    ReplyDelete
  25. Thank you very much. To get this to work pulling from a pr-existing workbook i edited line 8 to this:

    var ss = SpreadsheetApp.openById("Workbook ID");

    ReplyDelete
  26. I can't find the data-item-ID. I highlighted the question title and right clicked for inspect. I am looking under the "Element" tab but the highlight area doesn't have "data-item-ID". I also ran a search and received several returns under the "Sources" tab but they look like "data-item-id="'+_.X(f.id)+'"':"" "

    What am I missing?

    ReplyDelete
    Replies
    1. Sometimes finding it depends on the browser I have found - you might try what PaulC (comment why up above) did in calling all items and matching to the correct one ?

      Delete
  27. Thanks! This was awesome! Totally worked.

    ReplyDelete
  28. Thanks so much for sharing - generous people like you make the Internet such a powerful useful too. :)

    ReplyDelete
  29. I'm having an issue with this that I hope someone can shed some light on.

    When I initially wrote this script, my form populated perfectly with the options I made available in the linked spreadsheet. However, it is not updating as I add to the linked column.

    Ex: One question is what product are we making today? The options at the time I wrote the script were, let's say:
    Staples
    Paper clips
    Pens

    As a test, I added Pencils to the list after the script was written and it's not populating in the form no matter how many times I refresh either document, save or debug the code, or open or close either document. I've also changed the triggers to update on open, change, and edit with no success. Time-based triggers don't seem to help either.

    I'm sure there is an easy solution here that I'm simply overlooking. Any help from the group would be super helpful. Thanks in advance and thanks Melinda for sharing this; I'm sure it will change my life after this hurdle!

    ReplyDelete
    Replies
    1. Double check that a) you are connecting to the correct sheet on your spreadsheet and b) you are referencing the correct column of data

      Those are the two mistakes I made when I had the same issue...

      Delete
  30. Omg thank you. I had over 50 names I needed to put in the drop down so this was so good!

    ReplyDelete
    Replies
    1. Just an FYI - if it is a static list, you can copy a list in a document or spreadsheet paste it into a multiple choice question (first option). It will recognize the breaks and put 1 item per line. Of course, that doesn't work so well if the list is constantly changing... :-)

      Delete

Post a Comment

Popular posts from this blog

Google Forms - Copy Summary Graph/Diagram to Use Elsewhere

Google Sheets - Flipping Your Rows & Columns

Google Script - Create Calendar Events from Spreadsheet Data

Google - Reset a Form for Reuse with the new School Year