Google Forms - Drop Down List from Spreadsheet Using Apps Script
Вставка
- Опубліковано 30 вер 2024
- Learn how to update Google Forms dropdown lists from Google Sheets spreadsheet values using JavaScript & Apps Script.
#googleforms #dropdown #Spreadsheet
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
Why still GAS community soo small? And VBA still VERY HUGE!!!
toString doesn't work on my end - it gives me "Native Code". any workaround? Thanks!
Been my long time question's journey.. finally someone has the answer..very helpful..many thanks..
Hello, can someone help, the wsData.getRange is not working for me..
This is really my favorite channel. Much appreciated.
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.
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.
Great teaching skills means a great person! Awesome tutorials! Thank you
They have an add-on called Form Ranger which can do this now.
I am really thankful to you for making this video though.
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
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!
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.
Can you make this script available publicly? I'm having a little trouble following along. Great video, I'm just so new to 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 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...
Thanks for tutorial, very helpful,,
I've one question, I've simple form with 2 Questions : AreaName and BranchName.
How to populate choice of question "BranchName" based on previos choice question on "AreaName"?
how to ask a specified amount of questions from a bunch of questions. let's take an example of 50 questions if the person answers 20 questions from 50 and he has got 17 answers correct then he wants to go to the next level of another 30 questions where the difficulty increases even in this there are some conditions too for the level 2 as in the level 1. In level 1 the client should at least answer 15 out of 20 or else he can Retry and play the quiz again and he can be able to answer 20 out 50 again(random 20 from 50).so this prosses should be added. please help me, sir!!! THANKING YOU...
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.
Thank you!
Everything worked fine. In the final stage I get this error
Exception: Questions "Labels undefined".
Great video.
How would you build in dependency such that if a form user selects "bananas" the next question shows a different set of options then if she selected "berries"?
I would LOVE this capability, but from my short amount of research, it doesn't look possible. The form response is not complete until the user submits the entire form. So a response isn't available to .get in order to route to an array of options until after the full response is submitted.
Maybe you could try this: if you have “groups” (fruits, vegetables, etc.) within the form you can set a question to take you to a specific section (fruits section) and then each column within google sheets would be the answers for the question (betties, bananas, etc.). But if what you want is a mix between this video and the one from the dependent dropdown list... I find it hard, but you could try!
yupp..,
i think it will awesome if we can handle "Dependent Drop Down" on google form + google sheets,
since afaik in html/php its will be handle by ajax or something
great job everyone
I watched 3 tutorial videos before this.... this one helped me a lot.... thanks!
Hi there,
I want to Google Forms responses as graphic in my Wordpress site. But I can't. have you any video that you telling how to doing that?
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
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.
How do you increase the limit on how many values get imported. I have a list of about 1500.
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
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 tut deserves a " subscribed"!
We have thousands of dropdown choices. Looking for type to search, vs. click and scroll How would you do this with a web app?
Thank you so much. Im gonna use this for my students
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 ... 😀
I got this message
"labels.forEach is not a function (line 10, file "Code")"
Just wondering if there's any particular reason why did you put question in hardcoded even though we can store the ID itself?
The lists that you have show up after you hit "." don't appear on my script editor. Anyone know why this might be and how to fix it? New to all this stuff.
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.
I love your easy teaching style! Very easy to follow. The step by step troubleshooting is amazingly informative. Thank you!
It's running well if all your field types are drop-down, how there is a custom field.
Really good teaching style, casually explaining your logic as you type makes it easy to follow.
What web app would you use for something like this?
Thanks for this tutorial! It helped a lot!
Question: Is there a way to use a kind of form event to automatically pull the data from the spreadsheet?
Did you ever figure this out? I'm guessing you could write a script for "When spreadsheet updates, run script"
I need to do this as well as my list may grow and grow and grow as I am adding from yet another form
@@Axelander09 In the Script go to "Edit" and then to "Current project triggers". Then you can make it so it runs with a time trigger or with every submit
@Corey Wicks if you want to run every time when sheets containing the data updates, you can also write your "populate form" codes inside the function onEdit within the sheets' script.
function onEdit(e) {
// put the main codes in this youTube video here;
main();
}
@@lockshore1
Hey, thanks for the reply. I never saw these messages till now but that is what I ended up finding. The "triggers". One was "on form submit".
So even though I have two forms entering data and I only need it to run after one of them, it works just fine 🤷♂️
@@quartats6758
Hey, I thought about an on edit but I also edit the spreadsheet manually sometimes and I didn't want it to lock up everytime I changed an entry. Thanks for the suggestion though!
I don't think this works if you have multiple sections
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.
Great video! Excellent class! Thank you!
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
Very instructed work. Is it possible to get an search bar if there are a lot of elements in dropdown list.
Wow cool did not think that possible
Hi your video is very informative, can you make a video on how to prevent entering of duplicate data in google form
Hey, this is amazing thank you. It's made it easy for me to automate a form. I'm having an issue where i need some of the responses to "Go to section based on answer" however when i run the script it overwrites these changes i've manually made. Could you assist with this at all?
i also meet this issue.
This channel should be promoted by Google
sir when im select date pull data into option it's possible in google forms please give me the tutorial or any doc
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
How would you Replicate a Layout (which is Auto-Expandable BTW) in Sheets?
It was really helpful. You Made My Day !!!!!
How can I set an Image for a ListItem?, please...
What web app would you normally use instead?
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.
Anyone try to update the form with more than 50 options?
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...
Thank you so much for detailed guidance.
Thank you very much for the series of videos. I have learned a lot from your course materials.
30 goddamn minutes. are you serious?
Thank you. You light me up.
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
Can the script code for this be provided?
Your tutorial style is impressive it can help both beginners and seniors.
how to add search bar in google form dropdown
I have problem with the getRange. whenever I run the script, it said "TypeError: Cannot read property 'getRange' of null (line 8, file "Code")". HELP!!!
I'm getting trouble when I use the script to find the ID in a forms that has Images on it.
Exception: Invalid conversion for item type: IMAGE.
Any idea how to solve?
Can you share the line where you get the error?
@@ExcelGoogleSheets Line 42 (item.asListItem().setChoiceValues(values);
The Error says: Exception: Invalid conversion for item type: IMAGE. (line 42, file "Code")
Forget about it. I've Found.
There was an blank column between my data... and the image in the forms didn't have title (was blank too).
I just deleted the blank column and everything went good. Thank you!
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)
Hi, I have Centers.. In each center i Have Partners.. For every partner there are Executives. When i select a Center from drop down.. Next tab for selection i need only Partners of the selected cENTER. iN NEXT tAB, I NEED eXECUTIVES OF THE SELECTED PARTNER. How to do in google form or any code.
I am running into another problem. I was trying to create a workaround for the other issue I was having. I am trying to create a script that will run when I edit the sheet. The function I found starts with..."function onEdit(event)". Everything works perfectly with this function except when I have those global variables you set. If the below lines are set as global variables (at the very top above the "main" function), the "function onEdit(event)" will not work. I have tried removing the global variables and the other script will run, but it will not work if those global variables are there. Can anyone help me understand why the other script will not run if these global variables are set. They are listed below.
var ssID = "....."
;
var formID = "....";
var wsData = SpreadsheetApp.openById(ssID).getSheetByName("Employee Data");
var form = FormApp.openById(formID);
thank you so much bro
Thank you very much! It looks pretty powerful!! It is pretty close to what I would like to do: I have a database of emails with names, phones and postal address. I would like the first question to be to enter the email address and then to display the name, phone and postal address of the people so that they can check this info is up to date.
If it is not, then they can send me the new info
Do you know how I could do that? Would you have other tuto close to this issue? I have watched a lot of them but haven't found the answer :( Thanks a lot for your help!!!
Have you found the solution?
It is not possible in google forms as there is no trigger on question level, I tried it a lot and got to this conclusion after hours of research. You can try custom user forms for this ,this channel has tutorials of the same
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
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.
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?
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
hi, thanks for your awesome video! is anyone know how many maximum options to use this script? I have 2000 options, and it said that "Exception: Failed to set choices. You exceeded the maximum number of choices." I changed it into 3 options and the script worked perfectly. or do you have video about server side script? thanks
Hi guys, I just uploaded the code for building five levels to my GitHub. Hope it's helpful
github.com/spenafajuri/shared-bits
This is great thank you. Question: Is there a way that I can add a column in my Data worksheet that I can either put Active or Inactive, that I can manage from the Google Sheet as Admin side and not necessarily for the user to modify the entry. So like in the example, once I have a list in Data worksheet and I don't want Bananas to show up on the Form drop down, but I want to keep it in my list of Favorite Fruits question, that way I can still use it as data for Graphs or Dashboards that I'm creating from all the data that Forms provide. Is there a way? I'm a newbie on this, so this video really help out a lot already. Thank you in advanced and hope if someone can give me some guidance.
Thank you!
Everything worked fine. In the final stage I get this error
Exception: Questions cannot have duplicate choice values.
I am trying to populate a value in three fields automatically on the basis of data entered by user in first field.
1. User will enter Roll Number.
2. System should automatically pick Name and Mobile Number from google sheet and place these two values in two relevant read only fields.
Here almost everything is working but I don't know why I'm unable to find any method or function to set Text Value of Text field. Please anyone help, I am badly stuck. :(
Thanks for the Video - I have a question -
In the sheet i was working, there are some duplicate values - so if i use above type of script, it was showing as - "Error - Exception: Questions cannot have duplicate choice values". can you tell me how to resolve this.
Note - I don't have knowledge on script writing, i am just using the script mentioned in above video. please help me to edit and what script i should add there.
This video was very helpful. I followed it step by step and replicated the results. I have a question about multiplechoice: Is there an automatic search solution without scrolling the list and selecting the answer in case there are thousands of possible answers? Example by writing a solution? Thanks for the answer...
I am getting problem in the form, when I give update the scrip all the configurations "Go to section based to answer" restart. Can anyone help me?
Thanks you too much for this video.
NVM. I solved the problem.
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
Exception: Failed to set choices. You exceeded the maximum number of choices. (line 45, file "Code")
Please help with this error
Excuse me, i wanna ask something sir. I have followed your instruction even i dont have error about my code to update the dropdown list, but i have an issue in execution time. How can i fix that Sir? Thank you
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.
Good job bro. I really need help for creating a dynamic drop down list. I mean for example, for a address, I need someone to choose a state, then the choices of the next drop down updated according what he chosen before. Sorry my english is not good but hopefully you can get it :)
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
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.
I really love this. But I have a question I hope you could give me time to answer. How to get the Email item no, when you select in the form settings to collect email addresses. I'd like to populate also the Email addresses to control respondents with recognized email addresses.
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?
how to set the drop-down option go to section
very good tutorial
Thank you for this script it has helped me with populating my form. When equipment is signed out, it updates this availability list which is updated on the Google Form. Is it possible to modify the script it to trigger onEdit? Thank you.... humm. instead of onEdit, I think it should be a onChange function. any ideas on triggering it this way?