I love using Google Form for collecting data and surveys. At times, I wish a feature, where inputs for Drop Down List, Checkbox or Multiple choice could be feed from excel sheet. This will help us when form itself is dependent on values from some other source.
We can achieve it with a simple google script. The script is executed from spreadsheet. It updates the Google form with a trigger set 'on change' on spreadsheet.
In below script, I take an existing google form and modify one of the drop down item in it. The values for this drop down item is fed from a named range in current active sheet.
Glad to see you back to technical blogging. Best of luck...
ReplyDeleteThanks Max. Hope I continue.
ReplyDeleteNitin - Thank you for this post. I have been wondering about this for some time. I have successfully implemented your script for one name range and one drop down list question. However I have run into trouble when I try to do Check Boxes and run into an error that it cannot find the .ascheckbox in object. I am not well versed in programming.... I am not sure if I have to choose a whole new set of variable names for each question that I wish to dynamically populate ? Please advise.
ReplyDeleteSorry @PropertyTech I didn't understand your problem. Possibly, it might be due to mismatch in kind of item you are manipulating. Probably, you are trying to modify an item which is not really a check box. Can you please share your form / script with me tachniki@gmail.com?
ReplyDeletethats amazing, exactly what im looking for.
ReplyDeletethanks man
Thanks Mohammed for response. I hope it helped. :)
DeleteHey Nitin, I'm facing a problem while executing your code in my form. at time of saving the script an error ""Syntax error. (line 64, file "Code")"" is showing up. I think this is because of newer version of Google services. Can you please suggest any new method for the same.
ReplyDeleteLine 64 is last line. Is it due to some missing parenthesis.
DeleteHi Nitin
DeleteHope you can assist as this is exactly what I am looking for. Like Kapil I am also getting the Syntax error (line 64, file "Code") however as am not fluent in code am unable to pinpoint where I am missing parenthesis. I have tried many and asked around but no luck
Would it be possible for me to share with you to you to have a look at for me? It would be much appreciated
Thanks
Hi Lane,
DeleteAre you exactly using same code? Can you please drop me a mail at tachniki @ Gmail and share whatever you have written? I will look at it.
There was a bug in above code. Line number 60 has a parenthesis that was causing issue. Updated it.
DeleteThanks a ton
ReplyDeleteplease can you help me ? I need help to update the google form when user select answer of question 1 the options of question 2 will change "because the options of question 2 dependent on answer of question 1 "
ReplyDeletefor example
Q1-buildings:
- building 1
- building 2
Q2- rooms:
if the user select building 1
the options will be
room 202
room 205
if the user select building 2
the options will be
room 304
room 309
do you know how can do this ?
Hi Salha,
DeleteOne simple way to do this will be using condition in google form and taking user to different page. But I don't think that is most elegant solution as the values will be stored in different columns.
I have not got much time to find a programmatic solution for this. Let me see if I can do some research in next weekend. :)
thanks, Nitin Jain
DeleteI used this way, but this way take more time from users.
:)
I agree it takes more time. I hope we had better solution.
DeleteHi guys, Do someone find a solution for Salha problem?
DeleteI don't think that is possible. Easiest solution will be to use "Go to section based on this question". That will work only if there is not too many options of options.
DeleteThis comment has been removed by the author.
ReplyDeleteExcellent, and well commented within the code. Many Thanks
ReplyDeleteGlad that it helped. :)
DeleteI was hoping this was the answer to my challenge, but not quite. Is it possible to capture the value chosen in say DropDown1, and use that value to determine which list of values are selected on the spreadsheet to go into Dropdown2. e.g. Dropdown1 contains "Fruit" "Vegetables" "Meat". If I chose Fruit in Dropdown1, then DropDown2 shows "Apple" Orange" "Banana", if I choose Meat then DropDown2 shows "Lamb" Beef" "Chicken". I guess it is about being able to capture that live value?
ReplyDeleteForgot to add, I know I can use conditional branching on the form, but this adds far too many columns in the spreadsheet.
DeleteYou are correct, this cannot solve required problem. That will need some enhancement at javascript level. I don't think google forms has that yet.
DeleteThanks, further research has shown this to be the case for now. The missing link for google forms in my opinion. Thanks for your work.
ReplyDeletecan you give example what problem actually your code solves?
ReplyDeleteI am looking for a way such that in one dropdown I select an unique ID, and in next dropdown the name is autoselected.
Currently I don't think we have any way in which you can change dropdown options using javascript / ajax etc.
DeleteDear Nitin . how to get the ID of yr Google Form
ReplyDeleteHi,
DeleteWhen you are creating a google form, you will see url like- https://docs.google.com/forms/d/abcdefghijklXT_Nkfl_9Lno392I6Ue4pu4tToaPkO-21E/edit. Form Id for this form is abcdefghijklXT_Nkfl_9Lno392I6Ue4pu4tToaPkO-21E.
Sorry for bad formatting of text above. :)
Delete