Auto update Google Form dropdown from spreadsheet

Learn how to automatically update a Google Form dropdown when you add a new item into a spreadsheet.

Script in pinned comment.

0:00 - Intro
0:42 - Sort dropdown information
1:22 - Create dropdown within form
1:40 - Open scripts in sheets
1:52 - Paste in script
2:12 - Form ID change in script
2:34 - Dropdown list ID change in script
3:32 - Name of sheet change in script
3:48 - Debug
4:16 - Triggers

[automatemybusiness]

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


}

[inhidly]

Champion! liked and subscribed. exactly what I was looking for clean explanation.

[aaroncrossin]

This was just what i've been looking for - thank you!

[azfarjahangir]

Uff, I have been looking for something like this for a while and man this was Awesome !!! Got it done and it will help me out tremendously. Thank you for the amazing video man really appreciate it. Keep up the great work.

[_TheRealtyResource]

Quick question, if you want to use the second column of the same sheet, what do you need to change?

[luisc]

Mentioning from seeing the frist few seconds: only sound for my right ear from your voice. Maybe good to know.
Also, when typing things on the sheet, would be nice to have some zoom to see what is being typed :P

[mariaponcardas]

I subbed! I hope the screenshare is zoomed in. It is very difficult to see it in my 27" monitor.

[waterfields]

Thanks for the video. If I wanted a second question with a dropdown that took it's list of answers from a column on a different tab of the same sheet, how would I do that?

[fletchae]

Thank you for this video! It works perfectly up until the triggers. In the selection for Event Source on triggers, I only get "Time driven" and "from calendar." There is no "spreadsheet" option.

In the code, I had to change "SpreadsheetApp.getActive()" to "SpreadsheetApp.openById("ID inserted here")" because I kept getting an error that the spreadsheet was null. Once I made that change it worked great. Is that change now causing the "spreadsheet" option to disappear on the triggers?

Do you have any other ideas for how to update the trigger so it responds to edits in the spreadsheet? Thank you!

[blepage]

Hi. It's a pity because I have here the answer to my problem and I can't get it as it is wrtten to small for me and I can see exactly the items to customize the code

[MeikeBlock-jcux]

Great. Is it possible to feed similar dropdowns in multiple gsheets in one step? To call various forms in one script?

[EkoPrasetyoNugroho-kw]

how to make 2 dropdown, like first dropdown choose "program" and second dropdown is list dropdown from "program"

[PetrocellIT]

Can it be done for short answers and auto-populate the values of the last entry?

[carloslara]

Hi there Ed! I dont have programming knowledge, but let's say that I want to set 3 different dropdown that auto updates, as a if/them path.. is there any way to do this?

[Aniruddha]

Can we make two level dependent dropdown in google form

[JaimieCherkaoui]

Hello,
Do you know what I need to do if I get this error in the execution log: "Exception: Questions cannot have duplicate choice values.
updateForm @ NewInput.gs:25".
I am not the greatest at this so I have no clue what to do...

[AshnitaButtt]

hello, thanks so much for this. Can you help me with this one issue please -

My dropdown options are in a separate google sheet. Like you did, i added in the name of the tab here ss.getSgeetByName(" "). Im facing the below error :

TypeError: Cannot read properties of null (reading 'getSheetByName')
updateForm @ Code.gs:12