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.
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!
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...
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...)
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
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!
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
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 !
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.
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.
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.
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.
@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 🤔
So, i wrote the same exact code as yours but when i'm doing a test run of the code it gaves me an error. To be specific the error occurs when i callthe updateDropdownUsingTitle in the main function as you do at minute 28:16. The error is: Cannot read properties of undefined (reding ''getId). I already checked if i missed something and i did not
at 28:30 you run the script and it populates your form. When I run it using the identical code, I receive an error stating, Error TypeError: Cannot read properties of undefined (reading 'getId') updateDropdownUsingTitle @ Code.gs:33 (anonymous) @ Code.gs:17 main @ Code.gs:12 When I log it prior to that everything seems fine. It is only when I delete the logger and at the updateDropDownUsingTitle that it gives me the error. Any idea what is causing this? I am at a loss.
@@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 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.
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
Time stamp: 12:41 conclui a função que determina as opções da lista suspensa (próxima função para retornar os id dos itens 19:13 conclui a segunda função que retorna os ids dos itens (a próxima vai gerar as opções e títulos retirando de uma planilha) 29:45 terceira e última função concluída. Porém, ainda haverá um tratamento de erro na sequência
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.
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.
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.
Is there a way to go the opposite direction? I need to create a template that's filled in from a Google Form. More specifically, I need help with options and checkboxes. For example, on the Google Form I have a Checkbox question with about 15 options, I would like the template (Sheets or Docs, preferably Sheets) to show those options by those checkboxes appropriately checked, not in a mass clump/paragraph. Is this possible?
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.
I am not a techie...I am trying to fetch data from google sheet in google form...I tried above..but I am getting this error , Can you help me? [20-10-03 20:55:29:541 IST] TypeError: SpreadsheetApp.openByID is not a function at [unknown function](Code:4:29)
Thank you for sharing this video. I wonder if it is also possible to do a data validation with a script, in which the input data of google form could be checked by a google spreadsheet, then it gives us a short message back to google form. Like i need to borrow a book, type the book name into the google form and by submitting the script can search the data bank of the spreadsheet and make a response if the book is borrowed or not.
Thanks for sharing this amazing video! Is there any way so that I may call this Google Spreadsheet function from the Google Form onload event ? so everything the data in the google form is automatically updated.
can we make a dependant drown in this form i mean to say when i select first column and pick one value so another dropdown showing corresponding values realted of selected cell
What if you have several dropdowns in the same form that refer to the same list? Should you define each title question or create a function for each dropdown question?
I want to create dynamic dropdown in google forms. For Example, if you select Fruits in first drop down menu then in second drop down it should ask which sub category (like apple, banana etc.) but if I select Vegetable in First drop down, in second drop down it should ask sub category (like cauliflower, broccoli, etc)... Hope you understood my question.... Thanks for the video
If I'm going to do that, I'm going to build it with Sections. First Question (Fruit or Veggie), if Fruit go to section Fruit Dropdown else go to section Veggie Dropdown.
As much as this gives a solution to my problem, watching this is like looking inside the brain of someone with ADHD. Yes, it's somewhat disorganized, jumping from one thought to another, typing this and then having to delete it after. Man, that was a battle. And I have ADHD so yeah.
Can we have dependent Dropdowns in google forms ? Based on the selection of question 1, display specific choices for question 2. I know we can do this in google sheets. I need to know if we can do the same in google forms. (And no, I don’t want to use the goto section in google forms)
Learn Google Spreadsheets noted thanks 🙂 did not think it was possible for a dynamic lookup and subsequent dynamic field dataset generation all real time in google forms.
Interesting but why doesn't google sheets allow you to lay out a form on a separate tab where you would also define the type of form object and whether it's mandatory. Then you can use simple sheet entries to make a form for anything in seconds. Am I following this correctly? Does Google have a template like that?
This looks very useful but I'm trying to figure out how I can build a google form that will allow multiple selections because there's a lot of items I have to try to list. I see theres a grid list but I cannot figure out how to name those properly since it only shows up as a radio and I want the name above each radio button or something like that. Any advice on this?
Thank you for the effort. i have a question. how to get a value from what we choose in the form? and i want to filter data in dropdown using that data choose?
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 :’)
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 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?
I created a form and I need it to be limited to 1 response. problem is you have to logged into your google account to be able to fill it out. is there a script that works around that limitation ?
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!
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!
Really good teaching style, casually explaining your logic as you type makes it easy to follow.
Been my long time question's journey.. finally someone has the answer..very helpful..many thanks..
I watched 3 tutorial videos before this.... this one helped me a lot.... thanks!
This is really my favorite channel. Much appreciated.
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...
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!
Great teaching skills means a great person! Awesome tutorials! Thank you
This tut deserves a " subscribed"!
They have an add-on called Form Ranger which can do this now.
I am really thankful to you for making this video though.
Your tutorial style is impressive it can help both beginners and seniors.
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
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!
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?
Brilliant. The simplest way to explain the sheets-forms relationship.
I have watched a several of your videos. They are very good! Thank You!
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!
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.
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.
Thanks for your video. I found the clue to my problem during your video.
Tqvm sharing👍
Thank you very much for the series of videos. I have learned a lot from your course materials.
that was awesome! i really learned a lot just looking at how you got to the end.
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.
Thank you so much. Im gonna use this for my students
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
Thanks for sharing your knowledge! best internet video on this subject
Great video! Excellent class! Thank you!
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.
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
This is an outstanding tutorial!!! Thank you
What an informative and intuitive tutorials. Thank you very much Bro, it's very helpful for me to maintain my GForm,. :)
thanks for excellent tutorial
This video was incredibly helpful! Thank you!
It was really helpful. You Made My Day !!!!!
This is an awsome video, I appreciate your detail.
So, i wrote the same exact code as yours but when i'm doing a test run of the code it gaves me an error. To be specific the error occurs when i callthe updateDropdownUsingTitle in the main function as you do at minute 28:16. The error is: Cannot read properties of undefined (reding ''getId). I already checked if i missed something and i did not
at 28:30 you run the script and it populates your form. When I run it using the identical code, I receive an error stating,
Error
TypeError: Cannot read properties of undefined (reading 'getId')
updateDropdownUsingTitle @ Code.gs:33
(anonymous) @ Code.gs:17
main @ Code.gs:12
When I log it prior to that everything seems fine. It is only when I delete the logger and at the updateDropDownUsingTitle that it gives me the error. Any idea what is causing this? I am at a loss.
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 ... 😀
Cómo siempre profesor, admirable su trabajo, saludos cordiales desde Los Mochis Sinaloa México.
Excellent, very useful, very informative, Thank you very much!
toString doesn't work on my end - it gives me "Native Code". any workaround? Thanks!
Can you make this script available publicly? I'm having a little trouble following along. Great video, I'm just so new to this.
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 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.
Wow cool did not think that possible
Thank you so much for this helpful tutorial. Mega explanation. Easy to understand. Spot on!
VERY EXCELLENT WALK THROUGH!
Thank you!
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
Thank you for this very useful tutorial.
Time stamp:
12:41 conclui a função que determina as opções da lista suspensa (próxima função para retornar os id dos itens
19:13 conclui a segunda função que retorna os ids dos itens (a próxima vai gerar as opções e títulos retirando de uma planilha)
29:45 terceira e última função concluída. Porém, ainda haverá um tratamento de erro na sequência
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.
very good tutorial
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.
:)
Just wondering if there's any particular reason why did you put question in hardcoded even though we can store the ID itself?
Very instructed work. Is it possible to get an search bar if there are a lot of elements in dropdown list.
Thank you so much for detailed guidance.
Great teamwork
Thanks for the explanation, just curious, you said you would use google form for this project, can you explain what would you use instead, please
Thank you!
Everything worked fine. In the final stage I get this error
Exception: Questions "Labels undefined".
I am really thankful, this is an amazing explanation and very useful information.
Thank you so much
Thank you!
Everything worked fine. In the final stage I get this error
Exception: Questions cannot have duplicate choice values.
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.
Thanks a lot for this, "mini class"! Its was very useful.. :)
Is there a way to go the opposite direction? I need to create a template that's filled in from a Google Form. More specifically, I need help with options and checkboxes. For example, on the Google Form I have a Checkbox question with about 15 options, I would like the template (Sheets or Docs, preferably Sheets) to show those options by those checkboxes appropriately checked, not in a mass clump/paragraph. Is this possible?
This channel should be promoted by Google
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 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?
Nice tutorial 👍
Thank you. You light me up.
I am not a techie...I am trying to fetch data from google sheet in google form...I tried above..but I am getting this error , Can you help me?
[20-10-03 20:55:29:541 IST] TypeError: SpreadsheetApp.openByID is not a function
at [unknown function](Code:4:29)
How would you Replicate a Layout (which is Auto-Expandable BTW) in Sheets?
Thank you for sharing this video. I wonder if it is also possible to do a data validation with a script, in which the input data of google form could be checked by a google spreadsheet, then it gives us a short message back to google form. Like i need to borrow a book, type the book name into the google form and by submitting the script can search the data bank of the spreadsheet and make a response if the book is borrowed or not.
Is there a way to get the type of the item to find out if it is a dropdown or a checkbox etc. then make an update based on the item type?
YOU ARE THE BEST!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thanks for this! Just what I was looking for!
Thanks for sharing this amazing video! Is there any way so that I may call this Google Spreadsheet function from the Google Form onload event ? so everything the data in the google form is automatically updated.
Thank you for this tutorial! It woks perfectly for me!
What web app would you use for something like this?
21:36. Why I can't run getRange()? It's notification gettRange of null. Help me,pls!
can we make a dependant drown in this form i mean to say when i select first column and pick one value so another dropdown showing corresponding values realted of selected cell
Hi your video is very informative, can you make a video on how to prevent entering of duplicate data in google form
What if you have several dropdowns in the same form that refer to the same list? Should you define each title question or create a function for each dropdown question?
I want to create dynamic dropdown in google forms. For Example, if you select Fruits in first drop down menu then in second drop down it should ask which sub category (like apple, banana etc.) but if I select Vegetable in First drop down, in second drop down it should ask sub category (like cauliflower, broccoli, etc)... Hope you understood my question.... Thanks for the video
If I'm going to do that, I'm going to build it with Sections. First Question (Fruit or Veggie), if Fruit go to section Fruit Dropdown else go to section Veggie Dropdown.
Thanks for this tutorial!
As much as this gives a solution to my problem, watching this is like looking inside the brain of someone with ADHD. Yes, it's somewhat disorganized, jumping from one thought to another, typing this and then having to delete it after. Man, that was a battle. And I have ADHD so yeah.
Can we have dependent Dropdowns in google forms ? Based on the selection of question 1, display specific choices for question 2.
I know we can do this in google sheets. I need to know if we can do the same in google forms. (And no, I don’t want to use the goto section in google forms)
Im looking forward too..i still having problem with this.
I don't think so. You can use Web Apps instead but it's not easy.
Learn Google Spreadsheets noted thanks 🙂 did not think it was possible for a dynamic lookup and subsequent dynamic field dataset generation all real time in google forms.
Thanks for this tutorial! It helped a lot!
Awesome!
love this video very much. thanks
Interesting but why doesn't google sheets allow you to lay out a form on a separate tab where you would also define the type of form object and whether it's mandatory. Then you can use simple sheet entries to make a form for anything in seconds. Am I following this correctly? Does Google have a template like that?
Thank you for sharing this,
Can we create quick search for dropdown list in google form (in case we have too many options in dropdown list)?
Thank you
This looks very useful but I'm trying to figure out how I can build a google form that will allow multiple selections because there's a lot of items I have to try to list. I see theres a grid list but I cannot figure out how to name those properly since it only shows up as a radio and I want the name above each radio button or something like that. Any advice on this?
Thank you for the effort.
i have a question.
how to get a value from what we choose in the form?
and i want to filter data in dropdown using that data choose?
this is awesome but it will be good if google add this to be featured in google forms in the future.
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 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?
do you have the script available from the google sheet?
I created a form and I need it to be limited to 1 response. problem is you have to logged into your google account to be able to fill it out. is there a script that works around that limitation ?