Thank you! I followed every step of the way, and for someone with no coding background, my code worked! and feeling not as lost as when i started your vid. THANK YOU!
Thank you so much you video is excellently presented and so well explained. I avoided having to manually enter 104 items in a dropdown list. Well done!
This is exactly what I need! I am having an issue... my worksheet question is "Select incident", I copied your code exactly but didn't change anything to match the question. I am getting an error at line 22 "ReferenceError: interviewer_question is not defined".
Thanks for reaching out! When you say "dependence drop downs" - do you mean where the drop down options change depending on what was chosen in the question before it?
@@heraayesha1308 I don't think that's actually possible using Google Forms since you can't change the options after the page has finished loading. However, you can do it through a custom web app that you can build and host via google apps script. It's quite involved though... Another alternative is to set up your Google Form to be multi-paged where the option selected from the first drop down question routes the responder to a different version of the next question which would each have different options. That's not as involved as a web app, but will also take a bunch of time to map out.
Nice working bro but... Every time I searched for this title but in return these app scripts are helpful but I'm fedup because I'm not technically sound for app scripts, so what is the easy alternative? Thanks
@@BootstrappingTools Thanks so much for this incredibly helpful video! When I go in to set up an onEdit trigger, I don't get that option. I only get Time-Driven and From calendar. Am I doing it the wrong way? Thank you!
THanks for this!! But I have a problem. I'm using 2 Form questions that pull from the same list of choices in column A. I simply "duplicated" question 1 to create question 2. I don't know why, but changes to the list only show up for question 1, not question 2. Is there something I need to add to have it update both question lists?? THanks!
Hi Jeff! Yeah, that could happen in this case b/c the Question's text is the same. A way around this is with using the question IDs, but you'd have to go through a few steps to get those. Fortunately, you only have to do that one. The other option is to slightly alter the question text so that they're unique between the two questions.
Thank you very much for the tutorial. My dropdown menu has more items that setChoiceValues can incorporate. How can I add the rest of the values to the list?
I don't have a salesforce account to test their api on... but based on their docs, it looks like a regular rest api and some of the endpoints use grahpql. To hit external apis in apps script - use UrlFetch.fetch(url, options)
Yup, as long as you have the Google Form IDs, you can have a spreadsheet update the options across all of them. The tedious part will be grabbing the unique question ID from each form so it uploads correctly.
Great video! Thank you for the help, I ran this code with numbers for a raffle. When the number is chosen, it is removed automatically in the Sheets and replaced with a blank spot. The code doesn't work for me since it states that "Exception: Questions cannot have duplicate choice values." since there are multiple blanks. Any way to fix this or that the code removes the blanks if there are any? Thanks in advance!!
Hi! Amazing video thank you! So my code is up and running but I need to add a trigger whenever the items on the spreadsheet are modified. But I can't seem to find a video for this code! Can you please let me know if you have made a video for this, I found one but it was onEdit for emails not on edit for a google sheet to update the answers on a google form.
For a multiple choice question, you'll want to use asMultipleChoiceItem() instead of asListItem(). Then it's a similar process to update the values -- setChoiceValues([]some_array_of_choices)
I have a problem with a Google form shared with a team of 50 operators. If we submit responses together, the Google sheet fails to record all the data. I would like to know how to solve this problem.
teria como enquanto um usuário responde um formulário (o formulário não foi concluído) eu obter uma responda de um item especifico? Colocar a resposta do item em uma variável para uso posterior? Pergunto isso porque quero fazer um formulário em que as opções de item como lista suspensa ou múltipla escolha fossem atualizados conforme a resposta de um item anterior.
hi ! I'm looking for a way to get my forms item IDs pushed into a sheets formula. Is there a way I can push the console.lof((items[i].getTitle() + '-' + items[i].getId()) part into a sheet ? Thanks for your help
Hi, is there a way for the scrips to also be run automatically? for example if I'm using forms for students entering a class but it has a maximum amount of students that can enter, can it automatically remove itself from the forms with this script? or how would I be able to do it
how can i add answer key automatically from google sheet , like i have 100 question and i have all the answer key in google sheet then how can i automatically set those answer in my form? what will be the code for appscript?
Hi there, Thanks for helping me with such a great video. I just typed all your code by watching this video and successful. Question: I added a button (picture) on my google sheet and adding function name on that button. So I can update google form after I update my sheet. But when I click it gives this error "Script function Populate could not be found". I don't have any other script (this is the only 1). further Info: whenever I click on Appscript through that sheet, it always open a new script. I don't know if its relevant. can you please help. Thanks
Hmm... it sounds like maybe the script you created isn't container-bound to your spreadsheet. To fix this - you can open up apps script from the sheet and then copy in the code. That way, you're definitely using an apps script project that is bound to that specific sheet.
HI i want to add daily auto update access code to my google form. I have created as a short answer text, which is working. but i have to update the code daily. how i write a script to link a cell from google sheet (the cell i can make to change daily)
To all others to use it, they'd have to authorize the app. You can create a custom menu option that'll let them do that. The alternative is to deploy a private add-on to your Google Workspace account.
Good Day Bro! Could you help me about this one everytime I debug the script it only shows like this Execution log 7:53:21 AM Notice Execution started 7:51:28 AM Info RegionDropDown - function () { [native code] } It shows [native code] instead of number showing. Big help is much appreciated.
hello, need your help to create a dependent dropdown in *google form* using data or dependent dropdown menus from a google sheet. please guide me or if possible make a video on it. P.S : Don't want to use logic based section shuffle or *form ranger* or *cascade formatting*. thanks in advance
Since a Google Form renders everything at once, doing dynamic field options isn't really possible without using the section based directions. If you want to keep your users on a single page and change the options of a field based on a previous field's options, I would suggest creating a web app form which you can do by creating an apps script web app. You'd leverage the doGet(e) simple trigger to present the page, some javascript to alter the field options based on what the user selects via onchange="", and then the doPost(e) simple trigger to handle the form submit.
Great Tutorial Man !!! Btw I had another problem with my google forms. I have a different spreadsheet which contains all the names of our clients in a table. I copied that data onto the drop-down list in the google form. I wanted it so that when anyone adds another name to the table in the spreadsheet, it dynamically got updated in my google forms. Can you please HELP ME ????
Hi Sanmay, thanks for reaching out! That sounds very similar to what we did in the video. If you use getRange().getValues() against the sheet that you're storing the client names, you should be able to send it up to your google form using the asListItem().setChoiceValues() functions. Give that a try. If you run into any issues, feel free to reach out and I'll be happy to help you debug your code. Good luck!
Hi google sheets legend. I have a question that you might be able to answer. Is it possible to add entrys in the linked spreadsheet and have them be updated in the form view? At the moment if my form has 5 responses and i add some in the spreadsheet it still only shows as 5 responses in the form. I dont know how forms distingushes what rows were added by froms and then leaves out rows added in sheets.
Hi Jake! That is a very interesting use case you have and it's 100% possible to import responses into your google form and also have it recognized as legit responses by the Google Form. It's a bit tricky since you can't just add it to the Google Sheet... you actually have to create the response and submit it through the form. Assuming that you have quite a bit (enough to not want to do that manually), you could write a script to do that. This is a bit too complicated to explain in a comment thoroughly... but the overall summary of what you need to do is iterate through your sheet data row by row, and create an ItemResponse object that you apply into a new Form Response. So you'd do form.createResponse() to create a new form response object (let's call it newResponse) -> Then newResponse.withItemResponse(item.as___type().createResponse(your_sheet_data_cell)) ----> once you go through each question/column in the row. Then you'll do newResponse.submit()
Hello. It sounds exactly like what I want to do. However, being a dumb user, I tried to follow "letter by letter" what you showed, and I bump into an answer "items is not a function". Please help.
Haha, I can't count how many times I made little typos that I spent hours trying to debug and fix. Happens to all of us - usually we can laugh about it later on :)
@@BootstrappingTools Another question: I understand that the form will be updated when someone runs the script. Is it possible to have the form updated each time it is opened? So that people using the form will always have the updated list. I hope my question is clear.
@@pascalrobert2934 Unfortunately, the onOpen trigger is a bit restricted in what it can do - it's auth mode is LIMITED based on Google's rules. There's a couple of ways around this though: 1. Have a script that updates it whenever someone updates the options in a google sheet via the onEdit trigger 2. Create a time-based trigger that constantly updates the form options on a set schedule (e.g. every day, hour, 30min, etc)
@@BootstrappingTools N°2 sounds like an excellent idea. Thank-you. A daily update would be great. Any idea on how to do that? As mentioned earlier, I am a dumb user, having no skill in google programming. I don't even know what a trigger is (even though I understand the meaning). Thank-you.
Great video! I have no idea why I'm getting the error code (ReferenceError: organization is not defined setOrganizationDropdown @ Code.gs:18). I reviewed the code several times for typos, but nothing worked. Can you offer any suggestions? Thank you!
🎉Are you interested in a Bootstrapping Tools Community? Let me know! forms.gle/4tzvffHiRqS7pVZY6 🎉
Thank you! I followed every step of the way, and for someone with no coding background, my code worked! and feeling not as lost as when i started your vid. THANK YOU!
I've been looking for a something like this for ages! Thanks it's so useful
Glad it was helpful!
Thank you so much you video is excellently presented and so well explained. I avoided having to manually enter 104 items in a dropdown list. Well done!
Glad it helped
This is exactly what I needed thank you so much for taking the time to make this video
This is exactly what I need! I am having an issue... my worksheet question is "Select incident", I copied your code exactly but didn't change anything to match the question. I am getting an error at line 22 "ReferenceError: interviewer_question is not defined".
Great video man!
Appreciate it!
This has been super helpful. Thank you
Glad you found it valuable!
Thanks, man. This was super helpful!
Awesome sauceeeee!
Really clear explanation. Thanks.
Glad it was clear! Sometimes I feel like I just ramble....
Bruh you are amazing. Thank you so much!
Happy to help!
Great video, sample explanation and usedfull
Glad you enjoyed it!
Great video! Would you have another video or script for dependent dropdowns in GForms? Thanks and more power!
Thanks for reaching out!
When you say "dependence drop downs" - do you mean where the drop down options change depending on what was chosen in the question before it?
@@BootstrappingTools this would be great!
@@heraayesha1308 I don't think that's actually possible using Google Forms since you can't change the options after the page has finished loading. However, you can do it through a custom web app that you can build and host via google apps script. It's quite involved though...
Another alternative is to set up your Google Form to be multi-paged where the option selected from the first drop down question routes the responder to a different version of the next question which would each have different options. That's not as involved as a web app, but will also take a bunch of time to map out.
Nice working bro but...
Every time I searched for this title but in return these app scripts are helpful but I'm fedup because I'm not technically sound for app scripts, so what is the easy alternative?
Thanks
Hi Raza!
Yeah... it can be a bit daunting at first. Which part are you getting stuck at?
If i had a way to triple like this video I would! Thank you!
😁
Thanks for the great video!!
How can I get the form to update automatically whenever a change occurs to the reference sheet?
Yup! To do that, you could set up an onEdit trigger that watches for changes made to the reference sheet/range.
@@BootstrappingTools Thanks so much for this incredibly helpful video! When I go in to set up an onEdit trigger, I don't get that option. I only get Time-Driven and From calendar. Am I doing it the wrong way? Thank you!
Thanks!
Aw thanks! I appreciate your support!
THanks for this!! But I have a problem. I'm using 2 Form questions that pull from the same list of choices in column A. I simply "duplicated" question 1 to create question 2. I don't know why, but changes to the list only show up for question 1, not question 2. Is there something I need to add to have it update both question lists?? THanks!
Hi Jeff!
Yeah, that could happen in this case b/c the Question's text is the same. A way around this is with using the question IDs, but you'd have to go through a few steps to get those. Fortunately, you only have to do that one. The other option is to slightly alter the question text so that they're unique between the two questions.
How can I set an Image for a ListItem?, please...
Thank you very much for the tutorial.
My dropdown menu has more items that setChoiceValues can incorporate. How can I add the rest of the values to the list?
Hi, It was great and useful. Could you please explain the google form integration with Salesforce?
I don't have a salesforce account to test their api on... but based on their docs, it looks like a regular rest api and some of the endpoints use grahpql.
To hit external apis in apps script - use UrlFetch.fetch(url, options)
Thanks for this video, can I do this in multiple google forms?
Yup, as long as you have the Google Form IDs, you can have a spreadsheet update the options across all of them. The tedious part will be grabbing the unique question ID from each form so it uploads correctly.
Great video! Thank you for the help, I ran this code with numbers for a raffle. When the number is chosen, it is removed automatically in the Sheets and replaced with a blank spot. The code doesn't work for me since it states that "Exception: Questions cannot have duplicate choice values." since there are multiple blanks. Any way to fix this or that the code removes the blanks if there are any? Thanks in advance!!
Hi! Amazing video thank you! So my code is up and running but I need to add a trigger whenever the items on the spreadsheet are modified. But I can't seem to find a video for this code! Can you please let me know if you have made a video for this, I found one but it was onEdit for emails not on edit for a google sheet to update the answers on a google form.
감사합니다
😅?
Hi what if its a multiple choice not drop down?
For a multiple choice question, you'll want to use asMultipleChoiceItem() instead of asListItem(). Then it's a similar process to update the values -- setChoiceValues([]some_array_of_choices)
I have a problem with a Google form shared with a team of 50 operators. If we submit responses together, the Google sheet fails to record all the data. I would like to know how to solve this problem.
teria como enquanto um usuário responde um formulário (o formulário não foi concluído) eu obter uma responda de um item especifico? Colocar a resposta do item em uma variável para uso posterior?
Pergunto isso porque quero fazer um formulário em que as opções de item como lista suspensa ou múltipla escolha fossem atualizados conforme a resposta de um item anterior.
hi ! I'm looking for a way to get my forms item IDs pushed into a sheets formula. Is there a way I can push the console.lof((items[i].getTitle() + '-' + items[i].getId()) part into a sheet ? Thanks for your help
Hi, is there a way for the scrips to also be run automatically? for example if I'm using forms for students entering a class but it has a maximum amount of students that can enter, can it automatically remove itself from the forms with this script? or how would I be able to do it
how can i add answer key automatically from google sheet , like i have 100 question and i have all the answer key in google sheet then how can i automatically set those answer in my form? what will be the code for appscript?
Hi there,
Thanks for helping me with such a great video.
I just typed all your code by watching this video and successful.
Question: I added a button (picture) on my google sheet and adding function name on that button. So I can update google form after I update my sheet.
But when I click it gives this error "Script function Populate could not be found".
I don't have any other script (this is the only 1).
further Info: whenever I click on Appscript through that sheet, it always open a new script. I don't know if its relevant.
can you please help.
Thanks
Hmm... it sounds like maybe the script you created isn't container-bound to your spreadsheet. To fix this - you can open up apps script from the sheet and then copy in the code. That way, you're definitely using an apps script project that is bound to that specific sheet.
HI
i want to add daily auto update access code to my google form. I have created as a short answer text, which is working. but i have to update the code daily. how i write a script to link a cell from google sheet (the cell i can make to change daily)
Good Day bro, My script is working now how would I share it to my co worker? Thank you answering.
To all others to use it, they'd have to authorize the app. You can create a custom menu option that'll let them do that.
The alternative is to deploy a private add-on to your Google Workspace account.
hi , it was great and useful, can you share source code please ?
This one's pretty old - I'll make a new video on this.
Could you please provide the script. Not so techy here but I'm following your video.
Good Day Bro!
Could you help me about this one everytime I debug the script it only shows like this
Execution log
7:53:21 AM Notice Execution started
7:51:28 AM Info RegionDropDown - function () { [native code] }
It shows [native code] instead of number showing.
Big help is much appreciated.
Looks like you might be missing the () at the end of your function call.
hello,
need your help to create a dependent dropdown in *google form* using data or dependent dropdown menus from a google sheet. please guide me or if possible make a video on it.
P.S : Don't want to use logic based section shuffle or *form ranger* or *cascade formatting*.
thanks in advance
Since a Google Form renders everything at once, doing dynamic field options isn't really possible without using the section based directions.
If you want to keep your users on a single page and change the options of a field based on a previous field's options, I would suggest creating a web app form which you can do by creating an apps script web app. You'd leverage the doGet(e) simple trigger to present the page, some javascript to alter the field options based on what the user selects via onchange="", and then the doPost(e) simple trigger to handle the form submit.
Hello. Thank you.
Help me!
Google forms SectionHeaderTitle automatic update at the google sheets. Have its Google apps script code?
Hi Mustafa,
I'm... not sure I understand your question. 😅
What are you trying to do?
Problemi çözdüm. Teşekkür ederim 🙏
Great Tutorial Man !!! Btw I had another problem with my google forms. I have a different spreadsheet which contains all the names of our clients in a table. I copied that data onto the drop-down list in the google form. I wanted it so that when anyone adds another name to the table in the spreadsheet, it dynamically got updated in my google forms. Can you please HELP ME ????
Hi Sanmay, thanks for reaching out!
That sounds very similar to what we did in the video. If you use getRange().getValues() against the sheet that you're storing the client names, you should be able to send it up to your google form using the asListItem().setChoiceValues() functions.
Give that a try. If you run into any issues, feel free to reach out and I'll be happy to help you debug your code. Good luck!
Add an on change trigger to the spreadsheet to execute the sync function
Hi google sheets legend. I have a question that you might be able to answer.
Is it possible to add entrys in the linked spreadsheet and have them be updated in the form view?
At the moment if my form has 5 responses and i add some in the spreadsheet it still only shows as 5 responses in the form. I dont know how forms distingushes what rows were added by froms and then leaves out rows added in sheets.
Hi Jake!
That is a very interesting use case you have and it's 100% possible to import responses into your google form and also have it recognized as legit responses by the Google Form.
It's a bit tricky since you can't just add it to the Google Sheet... you actually have to create the response and submit it through the form. Assuming that you have quite a bit (enough to not want to do that manually), you could write a script to do that.
This is a bit too complicated to explain in a comment thoroughly... but the overall summary of what you need to do is iterate through your sheet data row by row, and create an ItemResponse object that you apply into a new Form Response.
So you'd do form.createResponse() to create a new form response object (let's call it newResponse)
-> Then newResponse.withItemResponse(item.as___type().createResponse(your_sheet_data_cell))
----> once you go through each question/column in the row. Then you'll do newResponse.submit()
@@BootstrappingTools and that is why you are the Legend 🥇
I did this and it worked 👍👍
Thanks for the info
Hey Dude Can you help Me I need your Help ?
What's up?
Hello.
It sounds exactly like what I want to do.
However, being a dumb user, I tried to follow "letter by letter" what you showed, and I bump into an answer "items is not a function".
Please help.
Forget my question, I typed ( instead of [. Thank-you.
Haha, I can't count how many times I made little typos that I spent hours trying to debug and fix. Happens to all of us - usually we can laugh about it later on :)
@@BootstrappingTools Another question: I understand that the form will be updated when someone runs the script. Is it possible to have the form updated each time it is opened? So that people using the form will always have the updated list.
I hope my question is clear.
@@pascalrobert2934 Unfortunately, the onOpen trigger is a bit restricted in what it can do - it's auth mode is LIMITED based on Google's rules.
There's a couple of ways around this though:
1. Have a script that updates it whenever someone updates the options in a google sheet via the onEdit trigger
2. Create a time-based trigger that constantly updates the form options on a set schedule (e.g. every day, hour, 30min, etc)
@@BootstrappingTools N°2 sounds like an excellent idea. Thank-you. A daily update would be great. Any idea on how to do that? As mentioned earlier, I am a dumb user, having no skill in google programming. I don't even know what a trigger is (even though I understand the meaning). Thank-you.
Great video! I have no idea why I'm getting the error code (ReferenceError: organization is not defined setOrganizationDropdown @ Code.gs:18). I reviewed the code several times for typos, but nothing worked. Can you offer any suggestions? Thank you!