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. This is amazing! It's very very helpful. Thank you.

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

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

    ReplyDelete
  9. Brilliant Melinda. Thanks again.

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

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

    ReplyDelete
  12. 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
  13. Awesome man, you saved me dollars. Great job.

    ReplyDelete
  14. 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
  15. 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
  16. it works! thank you !!!!!!!!!!

    ReplyDelete
  17. 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
  18. OK, that is amazing. I just did this and it's utterly perfect - exactly what I needed. Thanks for documenting this!!!

    ReplyDelete
  19. 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
  20. Any way where we can display values in dropdown after sorting?

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

    ReplyDelete
  22. Thanks Its very helpful for me

    ReplyDelete
  23. 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
  24. 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
  25. 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
  26. Thanks! This was awesome! Totally worked.

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

    ReplyDelete
  28. 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
  29. 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
  30. This blog post was incredibly helpful to me. An excellent substitute for formRanger.

    Your instructions were meticulous and thoughtful.

    Thank you again, it's working great.

    ReplyDelete
  31. All I get is Invalid form ID when I try to run the script. I copied the segment of the url exactly as pictured above, and made sure my triggers had access to my google.

    Also, the debug through a number of items:
    arguments
    form
    namesList - undefined
    ss - undefined
    names - undefined
    namesValues - undefined
    studentNames - undefined
    i - undefined

    Please assist if you can

    ReplyDelete
    Replies
    1. All those will be undefined until your form ID is correct. That is the piece to work on correcting. Make sure you used the form ID and not the spreadsheet ID...

      Delete
  32. Hi, the script works only for 1000 rows. Why?

    ReplyDelete
    Replies
    1. As in - you are limited to 1000 items in your drop-down list? If so, this is a limitations set by Google.

      Delete
  33. Hi Melinda, The title of this caught my eye and based on all of the positive feedback it looks like it must work. Before I try it I'd like to see if the idea can be extended and (full disclosure) while I have some experience as a developer and would say I'm intermediate skill level with Excel, I am brand new to Google Forms, Docs and Sheets. So here goes, I'd like to show a form with a drop down as described, but then drive the rest of the contents on the form from related columns in the sheet where the list is stored. So if my form pick list comes from column A, I want to show data from cols B, C, etc., elsewhere on the form. Bridge too far or is there a better Google solution? Thanks in advance!

    ReplyDelete
    Replies
    1. Unfortunately (according to my limited knowledge of how Google Forms works), you cannot do this. Once the form is loaded, you cannot do a call out with a script to change the form (that's as simplified as I can make the answer I got). You can within Google Forms have it go to a new section based on the answer of a multiple choice question. But you have to have each instance of that set up within the form.

      Delete
  34. Thank you so much for this script. Very helpful. I'm trying to populate my form dropdown with times. I've entered the times in my source spreadsheet but the dropdown is populating with a massive string including a default date and then the time followed by the GMT offset. Anyway to make sure the displayed dropdown options is just the time in 12hr format?

    e.g. I have a spreadsheet entry of 6:30 pm but the dropdown displays Sat Dec 30 1899 18:30:00 GMT+0000 (Greenwich Mean Time)

    ReplyDelete
    Replies
    1. (AndyH - I moderate the comments on this, so that is why the delay in your comment showing up...)

      But it is worth the wait, because I have an answer for you :-)
      There are 2 possible work-arounds for this
      If you are hand-typing the possible entries into the cells, if you add an apostrophe in the front (ie for your example type '6:30pm ) it will enter into the cell just as you type it. I tested it, and this properly pulls over into the form also.

      If you prefer to use a formula, you can use =text(6:30 PM,"hh:MM AM/PM")
      This will actually return 06:30 PM

      If you are collecting that information from a form entry it is not as clean. If that is the case, you will not be able to pull the information directly off the sheet where the form entries are, but will need to create a secondary sheet to use. In one column - let's say A - you can use a query function to pull the data in the appropriate column from the form responses with the time. Then in column B you can use this formula and fill down (as far as you would like to keep it working on future form entries) -
      =if(A2<>$C$1, text(A2,"hh:MM AM/PM"),"")
      I use C1 in this example - it just needs to be any cell that will stay empty. I also started with A2 assuming a header row. Viola! You will have the correct format for your time.

      Delete
    2. First, thanks so much for your post, Melinda! It's put me well on my way to the solution I've been trying to create. :D

      In the formula just above, I'm curious if there's a reason you use $C$1, rather than "" as a means of checking for a non-empty cell.

      Cheers!

      Delete
  35. Hello,

    I keep getting the error below when trying to create a trigger or debug. It tells me Update Dropdown needs my permission to access data, and then I get the error when I choose which account to use.

    "Sign in with Google temporarily disabled for this app
    This app has not been verified yet by Google in order to use Google Sign In."

    ReplyDelete
  36. Hi Melinda

    This is great! Thanks for sharing the idea.

    I have a small suggestion: on item 3 it could be mentioned that the script must be created from the spreadsheet (source for the drop down list) and not from the form. The trigger will ask for this connection and "from spreadsheet" is what we look for; when making the script for the form, the only option for the trigger is "from form". Naturally I'm suggesting this cause I took a time to figure it out haha.

    Cheers

    Gustavo

    ReplyDelete
    Replies
    1. Hi.. Could you please elaborate on this? This may be the issue I am having. What changes did you make to the script to achieve this?

      Delete
  37. This can be helpful in may ways, I will be working on this today.

    ReplyDelete
  38. Hi Melinda,

    Thanks for posting this! I used the script to load in a list of 78 cities and it worked like a charm!

    For some reason when I tried to do it a second time for a different item in the same form (to load in a list of about 130 zip codes) I get a "Cannot read property 'getRange'" error. I'm not sure why this is, but some googling tells me it may actually be be related to a problem with the previous line where it isn't able to find the listed spreadsheet.

    I would assume there isn't a max of how many scripts can be executed for a given form, correct?

    Any thoughts?

    ReplyDelete
  39. This is super helpful. I was wondering if you have another code that can submit multiple rows of data into different section of the google form?

    ReplyDelete
  40. This is very helpful. Thanks so much! :D

    ReplyDelete
  41. Thank you. The script works great the first time I run it. But when the list in the spreadsheet that must be used to populate the form is updated, and I run the script again, it does not update the list. Any ideas how to fix this? Anyone that can help me?

    ReplyDelete
  42. Great melinda, thankyou. i'm using this with a nice unique sort to make a list so we dont get duplicates in our data.

    ReplyDelete
  43. what to do when page breaks e.g. if question 1 is in 1st section and second question is in 2nd section. the error is coming that page break.

    ReplyDelete
  44. Can anyone help? I have completed the (expert) instructions however my form will not send the project trigger to be recorded on the sheet 1! Does anyone know how to solve this?

    ReplyDelete

Post a Comment

Popular posts from this blog

Google Sheets - Flipping Your Rows & Columns

Google Forms - Copy Summary Graph/Diagram to Use Elsewhere

Google Script - Create Calendar Events from Spreadsheet Data

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