Google Forms - Drop Down List from Spreadsheet Using Apps Script
HTML-код
- Опубликовано: 9 май 2019
- Learn how to update Google Forms dropdown lists from Google Sheets spreadsheet values using JavaScript & Apps Script.
#googleforms #dropdown #Spreadsheet
![XO (Only If You Say Yes) - ENHYPEN エンハイプン 엔하이픈 [Music Bank] | KBS WORLD TV 240712](http://i.ytimg.com/vi/wBT1bKKwPTo/mqdefault.jpg)
Been my long time question's journey.. finally someone has the answer..very helpful..many thanks..
This will have better workflow than having to update both the sheet and the form everytime a dropdown is updated.
With your script, you just update Sheet's options list then run the script and you'll have consistent sheet and form data.
Great job - Thanks!
Great teaching skills means a great person! Awesome tutorials! Thank you
This is really my favorite channel. Much appreciated.
Brilliant. The simplest way to explain the sheets-forms relationship.
Wonderful! You've answered my request!! :))
This has helped me a lot, and I'm very grateful.
Hopefully it helps many others as well.
And as always, excellent explanation.
Thank you! Thank you!!
(...but now I undestood your suggestion to use Web app instead...)
Good do you have sample code
I love your easy teaching style! Very easy to follow. The step by step troubleshooting is amazingly informative. Thank you!
Really good teaching style, casually explaining your logic as you type makes it easy to follow.
Thank you for this clear step by step explanation. I am selftaught myself, but your explanation took away a lot of the guess work when trying to code. Thanks for explaining why you code certain things you do. THis will be very useful for all my other type of coding!
Cómo siempre profesor, admirable su trabajo, saludos cordiales desde Los Mochis Sinaloa México.
This tut deserves a " subscribed"!
Thank you for your tutorials! I've learnt a lot from you these moths and I want to thank you for sharing all your knowledge in these videos.
Very Informative Tutorial, Thanks for Sharing 👍
I request you to please make a tutorial on response through a trigger on form submit with the relevant results in an email.
Hope to see this in your next video.
Regards
Amazing video... Just because I don't know how to do this, I was using Web app as you have mentioned... But for small list populations like my subject lists, this is a cool options now. Thanks a lot for this.
Thanks for this video, I had an issue with repeated data values, because I was using last names. but I used concatenate and merged first and last names and it worked like a charm...
that was awesome! i really learned a lot just looking at how you got to the end.
Your tutorial style is impressive it can help both beginners and seniors.
Thank you very much for the series of videos. I have learned a lot from your course materials.
Thanks for this! Just what I was looking for!
Thank you so much for this helpful tutorial. Mega explanation. Easy to understand. Spot on!
You teach quickly and efficiently, your lessons answer all my questions and allow me to move forward quickly. And in addition it is a real pleasure to watch your videos. Even I like reading articles, learning through your videos is great and fast, to my opinion. Thanks thanks thanks !
Great to hear!
Good video man. Thanks 🙋🏻♂️
I like to use fruit selection in my proof of concept designs as well. Usually durians have a big part to play ... 😀
Thank you for your time! Congrats.
Hi, Thanks for this tutorial. It was really helpful. I have a spreadsheet with thousands of unique ids call them emp IDs. Now I want to put a validation that inputs given by any google form user must be validated from that emp ids in spreadsheet. Currently they are putting it manually. Even if I create a dropdown as explained here, it will be difficult for them to search from thousands of IDs and select correct one. Your help is really appreciated.
Thank you for this tutorial! It woks perfectly for me!
Thank you so much for detailed guidance.
What an informative and intuitive tutorials. Thank you very much Bro, it's very helpful for me to maintain my GForm,. :)
They have an add-on called Form Ranger which can do this now.
I am really thankful to you for making this video though.
I am really thankful, this is an amazing explanation and very useful information.
Thank you so much
I have watched a several of your videos. They are very good! Thank You!
Excellent, very useful, very informative, Thank you very much!
straight to the point. thanks
Great guide - I've adapted this with alert dialogues to push changes to forms. Thank you! The next update will be to allow the user to choose what type of data is in the form, and then update accordingly - Dropdown or Radio Button
Any chance you could share it?
@@so8075 hey - it’s part of a live project, so I’d have to create a new project and set it up to share. Anything I can answer here via text?
Great video! Excellent class! Thank you!
This video was incredibly helpful! Thank you!
Thank you. You light me up.
Thank you, man!!!
Thanks for sharing your knowledge! best internet video on this subject
Thanks a lot for this, "mini class"! Its was very useful.. :)
Tqvm sharing👍
Thank you for this very useful tutorial.
so great! thank you!
YOU ARE THE BEST!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thanks sensei, nice vid! beeen googling for a day or two, found three or four methods to do that but got stuck in implementation for my project, cause I don't know a thing about JS. with your step-by-step testing, I think i'm getting close to it. Best regards
Thanks for your video. I found the clue to my problem during your video.
Great guide. I would like to add however that Google has recently disabled less secure app access so it has become necessary to edit the json manifest file and maybe even create a project to manage authorizations.
Thanks for this tutorial! It helped a lot!
Awesome!
I'm still very new to App Script and learning JS right now to help improve upon what I can do to make things where I work more efficient and automated. I've learned so much from your video's. Just wanted to send a little something to say thanks.
Thank You!
Excellent! Thanks!
It was really helpful. You Made My Day !!!!!
This is an outstanding tutorial!!! Thank you
Done, thank you so much
Thanks for this tutorial!
Thank you very much.
Thank you so much. Im gonna use this for my students
Thank you for the effort. This is a good starting point for me when I'm trying to grab data from a spreadsheet to prepopulate a form. That said, this is a bit too complex for someone who just wants to grab values from a single column in a spreadsheet and put them in a single question in a form. A much simpler and more straight-forward tutorial should be more helpful.
Thank you ,very interesting! Is there a way to upload the questions directly from the sheet? Or a way to work with dropdown, write answer and option button at the same form?
as always, great video bud 👍cheers for your efforts of helping and sharing knowledge.
One thing that Google Forms lacks is dependent or dynamic drop down lists, it would be great if Google would add that function to Forms.
Once again bud, great video. Thanks.
yeah they've been needing to do it for a while, i think the reason they haven't is because they're reliant on 3rd party developers to create add on's to supplement those features. There are currently several add on's for forms that claim to do this "Form ranger" and "Dynamic Fields" although i haven't tried using either of these myself yet. I agree Google does it best when they simply bake these features into their apps though.
@@zr2ee1 actually i have used some of those add-ons and they don't work very well.
@Guillaume Duluc i thought about that, but the problem with that is: will it work will you choose answers from the form? i mean, does it update on real time? form ranger does something like that 🤔
Maybe just use a Web App instead if you want a form with all those bells and whistles?
@@ExcelGoogleSheets cause we're retarded and haven't fully learned how to code yet lol
VERY EXCELLENT WALK THROUGH!
Thank you!
great course thank you
Great teamwork
love this video very much. thanks
Muchas Gracias!
Nice tutorial 👍
This was very helpful and easy to replicate - thank you!
:)
Wow cool did not think that possible
This video was very helpful. I followed it step by step and replicated the results.
I really appreciate the time you took to explain each step you made and reference where you can find similar content.
I did run into one issue. One of the columns I am referencing in my sheet has an array formula within in.
So in the form blanks are still shown. I assume the script recognizes the blank cells as having a value since there is a function tied to them?
Is there a variable I need to add or change in the script editor so the blanks don't appear?
Thanks.
Just import your list from google sheets into another column, again in google sheets itself. Like use query to import from A to C or another sheet and then since query only needs to fill values in one of the cells, you'll be fine.
Thank you!
Everything worked fine. In the final stage I get this error
Exception: Questions cannot have duplicate choice values.
thanks for excellent tutorial
How would you Replicate a Layout (which is Auto-Expandable BTW) in Sheets?
Thanks!!!
Bro, You are Awesome... That's what I was looking for.... Thanks for Uploading it..... Love you Bro.. Keep Posting such videos.... I love you
you are genius !
This is an awsome video, I appreciate your detail.
Thanks so much for doing this. You mentioned that this is not the most efficient way of doing this. What more efficient way were you referring to?
Thanks for your videos. can you please post something on validations on date type question in forms
Hi, thanks for all your videos it helps me a lot.
I'm just curious if this is done in google form side, will it update the form without clicking any button from google sheet?
Awesome! Just what I've been looking for! :D Do you have videos about Webapps together with using Google Sheets?
Thanks a lot!
Great! Thanks a lot!
Hi. I am trying to follow the video and write the code, However when I copy the line "updateDropDownUsingTitl(title,values)" at 28:06 code start to give errors all around. If you have a fully written working code could you share? Thank you. ismail
Very Nice explanation and useful video, thank you ! I am looking transfer Dropdown selected value in Google sheet From Google Form and run Google sheet App script Automatic.
nice tutorial
Thank you so much for the time and effort you put into this! This has been a HUGE help. Just one more question... could you show a way of running this exact script from the Google sheet? So basically “on load” it will run the script.
At this moment Forms can't run other apps on load.
@@ExcelGoogleSheets Thank you for the reply. You mentioned in the video that you can write the script in Google Firms or in Sheets. So I assumed there would be "triggers" inside Forms. I understand why you prefer Web apps 😊 You have been a huge help in so many ways. Thank you!
Wow, your videos are so good, I have managed to use this.
Only issue was for some reason I had to change the function name from "main" to something else because the button kept giving a main function not found error. Thank you though, I always look for your stuff when solving problems.
:)
Thats Great, Thank You
thanks.
Thank you very much. I prefer not to use addons and third party softwares so I like your videos, they are very helpful. Is there a way to indicate the date of last update of a video. I prefer to compare the current updates.
Thanks for the video. Any way you will share your code through Dropbox or other format?
its 2022!! ..and, still it helps me a lot ! !🥰Thank you
👍
@@ExcelGoogleSheets Amazing workaround. Obviously, a pain to do. Do you have something in mind when you say you prefer a web app solution? I've looked at a ton of form writers and can't find "type and find" search capabilities in lookups and dropdowns. 2022 and Excel can't do it, but Google Sheets can.
Thank you!
Everything worked fine. In the final stage I get this error
Exception: Questions "Labels undefined".
Thank you. This was a great help. SuperAWESOME. Tx
Thanks for this! I have been looking for something in which we get the image url which is inserted into a question .
This was great, thank you! How can we allow this to work for multiple question types (such as List, like this video demonstrates, and CheckBox)? I tried duplicating the code, having it refer to a Data2 sheet just for the checkbox questions, and now the dropdown options in Code1 are not working, though I'm not getting any errors. When I delete the checkbox code in Code2, Code1 works perfectly. Any ideas?
thank you so much bro
I really can’t thank you enough! this is just what we need. Btw, Is there a way around forms so I can start typing to search an option in a dropdown? We have like 50 different options sometimes :’)
That 'search option' is what I am looking for as well
Search is automatic in dropdown, just go ahead click the dropdown and type for letter or word you need.
You just have to get a hang of split second timing for next search or more letter for a word to search.
For example, you type X, it'll go to option that starts with X.
If you type quickly xy then it will go to option say Xylophone, bypassing xray option.
You type X, wait a second then type A, it will search for A, not XA.
I hope that helps.
Thank you very much for this video which I was looking for. Is there anyway I can copy paste the code?
Can you make this script available publicly? I'm having a little trouble following along. Great video, I'm just so new to this.
Hey there,
Can we do this with other question types like Checkboxes, or Multiple chice instead of Drop-down?
I use GForms & GSheets for quizzes & attendance management system for my college. Your method could be greatly helpful if it worked on Checkboxes, or Multiple chice question types.
Thanks in advance...
very good tutorial
Good afternoon. I have watched several of your videos and have learned how to automate a lot of work duties. Thank you for step by step instructions on all of these automations. I am having an issue with this one. For some reason, when I run the code, it puts all of my data from column 1 into the 1st option of the dropdown only. I have went back through this video several times, but am not finding where I missed steps to have this populate as Option 1, option 2, etc. Any ideas or suggestions would be appreciated.