Use Google Apps Script To Add Dependent Drop Down Options
Вставка
- Опубліковано 26 лип 2024
- Adding Dependent/Dynamic Drop Downs to your spreadsheet can give it a HUGE boost in data quality and make it much easier to manage.
🎉Are you interested in a Bootstrapping Tools Community? Let me know! forms.gle/4tzvffHiRqS7pVZY6 🎉
Get the Source Code here: products.bootstrapping.tools/...
▶︎Video Chapters◀︎
0:00 - Intro
0:20 - How dependent drop downs work
0:40 - Why INDIRECT() doesn't work here
0:57 - The 5 components to building our automation
1:25 - Pulling data from a Named Range
1:57 - Filtering by Level 2 data
2:24 - Using the "e" parameter for a triggered event
3:08 - Building the new Data Validation rule programmatically
3:40 - Creating the triggered event listener
4:12 - Applying the same concept to our Level 3 drop down options
4:56 - UX Improvements
5:38 - Demo
🎬 Other Videos Mentioned In This Video:
Programmatically generate a ToC: • Programmatically Gener...
🔔 Subscribe for more tips just like this: ua-cam.com/users/Bootstrappi...
======= KEEP LEARNING WITH Bootstrapping Tools =======
🎬 MOST RECENT VIDEOS: / bootstrappingtools
🤖 Automate with Google Apps Script: bit.ly/BootstrappingTools-Goo...
💻 Build-Through Videos: bit.ly/BootstrappingTools-Let...
🚑 Community Support Videos: bit.ly/BootstrappingTools-Com...
🚀 Check out some of our written content: bootstrapping.tools
======================================================
👥 Looking for something specific? feedback@bootstrapping.tools
======================================================
💝 Support our channel 💝
Buy us a coffee: ko-fi.com/bootstrappingtools
Or by tipping: koji.to/k/6wBv
======================================================
🔗 Other useful content that we like and will help you on your scripting journey
Javascript for Beginners: amzn.to/3SbGzCm
======================================================
#Bootstrapped #GoogleAppsScript #DependentDropDowns
![Supercharge Google Sheets with Dependent Dropdowns [Google Apps Script]](http://i.ytimg.com/vi/5Yysv-QouTQ/mqdefault.jpg)
🎉Are you interested in a Bootstrapping Tools Community? Let me know! forms.gle/4tzvffHiRqS7pVZY6 🎉
Get the Source Code from this video here: products.bootstrapping.tools/products/dependent-drop-downs-in-sheets-a
The videos are great man - keep up the good work!
Thanks, Matthew! I appreciate you!
Great video! Helped a lot on adapting to my script, thanks!!
Thank you so much for your new updated code. It helped me so much with a project. It was so easy and saved a ton of time. The problem I am having is if the values are the same at level 3 for 2 different level one options. For example, one row may be, IT department, Rachel's team, salary, Rachel's salary (and other team members). Another row might be, Finance department, Sam's team, salary, Sam's salary (and other team members). When I get to that last drop-down, it is giving me options for the IT department and the Finance department because I choose "salary" in the third level.
a great Insight tutorial and very specific and to the point solution .. Thanks a lot
Great video and loved the ending!
Thanks so much!
Link to the demo spreadsheet - docs.google.com/spreadsheets/d/1CyVY5A_tO2L9fEkkLGJ0cjzPxPb5pGuLt9nuzafDzn0/edit?usp=sharing
Link to get the source code: products.bootstrapping.tools/products/dependent-drop-downs-in-sheets-a
Thanks! Would it be possible for you to share the entire script as well? I tried from your video to put it all together, but i'm getting an unexpected error when running the script
What kind of error are you getting when running the script?
Lemme clean up the code a bit so it's easier for y'all to use it as a plug-and-play. Sit tight.
Here's the source code - I made some tweaks so it's easier for y'all to use it like a plug-n'-play.
products.bootstrapping.tools/products/dependent-drop-downs-in-sheets-a
Thank you
Thanks a lot. Great solution!
Glad it helped!
Thank you, this is outstanding. My only issue is that it is slow to update the subsequent columns, but I guess there's no way around that, and I've just adjusted my workflow. Thank you - an amazing piece of script
Ah yes, it is a bit slow. I've been thinking of ways to speed it up... maybe leveraging script properties could help reduce the amount of calls it needs. I'll have to experiment a bit to see if that works 👨🏻🔬
That's awesome man, I've find it for some time and finally found your video. Thank you very much.
Unfortunately, I've write all of your code into my spreadsheet but only get blank drop down list.
It would be very helpful if you can check then
It sounds like maybe the ranges you're referencing aren't correct. Are you able to share?
I took a quick glance and noticed that you weren't passing parameters through the filterTeams() function. You'll wanna do something more like: filterTeams(department, data) so that you can dynamically set the type and data.
I updated it in the spreadsheet you shared. Use that as a reference for when you implement it for the Roles column as well.
Good luck!
Note for anyone having trouble getting the on edit trigger to be from spreadsheets. The app script project has to either be bound to the spreadsheet or have an add-on installed. Easiest way to is to make sure the scripts project is bound to the spreadsheet. From the spreadsheet go to Extensions > Apps Script and then create a project there. When creating a trigger you should be able to see the source of "From spreadsheet" and be able to pick the trigger being "On Edit".
Great Tutorial and thanks! If I want to apply the script to two or more sheets, how should I set the targetSheetName?
Do have the script only apply to certain sheets, you'll want to add in a check within the if statement to make sure the event was triggered on the sheets you want the code to run on.
For example:
if (e.range.getSheet().getName() == 'your_sheet_name' && e.range.getColumn() == 'your_trigger_column') {
// do stuff
}
Thank you for sharing your knowledge, I would like to ask if you have video tutorial to get specific value from drop down list and transfer that value to separate sheet base on column assignment. For Example A1 Dropdown (Dog, Cat, Mouse) then A2 Cell Value (Interger Value Input by User) then transfer that A2 value to Sheet2 Column Dog if the drop down choose is Dog... basically there is a Column specifically assign in Sheet to for Dog, Cat, Mouse.... I hope you can help me on this, thank u so much
I don't have something that specifically covers that scenario - but it's pretty straight forward to move data around different sheets.
One question though -- what triggers the data transfer from Sheet1 to the other sheets? Is it just the change in value of the integer? Or would there need to be some sort of submit button?
Great tutorial. Do you have demo workbook available? I expected to find it in the description but it doesn't appear to be there?
Left a pinned comment for ya!
@@BootstrappingTools where? is it still available?
I just came back to this and realised that I'd dropped a comment. I still don't see a pinned comment like you stated in your comment - what am I missing?
Great video! Curious if it is possible to do this with verticle data instead of grid data? For Example, the level 1 dropdown box would be A1:B1 (Colors, Shapes), and the level 2 drop-down box would be A2:A4 (Red, Blue, Green), B2:B4 (Circle, Square, Triangle). Thanks!
See the comment on Keith Hester for the code that I wrote for solve this use case.
Thank you :) This is wonderful. There's just one problem. How do I update the data validation if I add more rows in Settings sheet?
Gooddddd question - You'd have to rerun the script that sets up the initial set of drop downs. BUTTTTTTT I've been working on this newer version of the tool that'll also do that automatically for you whenever you update the mappings/settings.
New video that talks about the new version of this: ua-cam.com/video/FNZab235eQU/v-deo.html
This is super helpful and exactly what I was looking for. Do you have a copy of the specific script you used in the video that I can utilise? Thank you
Hi Gina!
Yup, here's a link to where you can download the code: bootstrappingtools.gumroad.com/l/dependent-drop-downs-in-google-sheets
@@BootstrappingTools I paused the entire video and transcribed / modified as it went along. I wish I would've read comments sooner
@@patricknadeau8171 On the bright side, you probably learned more about how to build it than people who skipped the video and just downloaded the source code. I call that an absolute win 😉
Hey, thanks for the vid! Can I edit this so at each stage I can add multiple options? I need to be able to use what you have, except select multiple options, not just one at a time!
When you say "select multiple options", do you mean multiple selections in one drop down field?
Hey! Thank you for this tutorial. I am looking for a solution that auto fills an answer based on a previous question
You'll have to edit the code a bit, but this tutorial will basically do what you're aiming to.
Code works great! My dataset is in a different format though. Is there app script written for columnar data where a selection is matched against the header row of the named range, finds the matching header cell, and then the rows underneath are pulled into the dataset to display?
This seems to be the same question as @Dillon Mears.
This is the code that I wrote to mirror your code example. Any recommendations welcome.
function loadListbyColumn(department, data) {
let list = []
let headerrow = data[0];
for (var i = 0; i < headerrow.length; i++) {
if (headerrow[i] == department) {
for (var j=1; ;j++) {
if (data[j][i] == "") {break}
list.push(data[j][i])
}
break
}
}
list = [...new Set(list)]
return list
}
What would you say is the ceiling on this as far as quantity goes for the dropdown menus? I ran into an 'Argument too large' exception when going to apply it to the desired worksheets.. for reference im working with some pretty large/diverse datasets. (300+ level 1 column options >> 35K+ level 2 column options, each with 2/3 level 3 options.. around 400K total rows). So my question is - were I to attempt to modify/chunk the initial build-script, do you think the rest would work okay, or do you have any ideas how to go about this within the GAS environment?
300 options in a single dropdown? PHEWWWW
I tested a 400 option drop down in the new version of this tool, and it seemed to work okay... Not sure about 35K+ options, but maybe!
New video on the new version of the tool: ua-cam.com/video/FNZab235eQU/v-deo.html
I have google form that have 7 dropmenus and I am trying remove chices form other dropmenuns and I write the code on the google app script but it not work on the submit form
Awesome video!!! Its so helpful.. :) . I have a very stupid question, What if the drop Down result is not in A2 but rather in D2:D100 - and E2:E100? I tried changing the rows and columns but its not working for me :( . Thank you!
Hi Amy!
Without seeing what you're trying to do... my best guess would be that you need to change the getRange() values to match with your D and E columns.
For example, instead of "getRange(row,1,1,1)" which is referencing Column A - you would want to do "getRange(row,4,1,1)" so that it references Column D - or getRange(row,5,1,1) so that it references Column E
Lemme know if you're still having trouble with it.
@@BootstrappingTools thank you very much for your reply. I was playing around and I did change these numbers you mentioned under get Range, but it did not work for me:(
Hmm. I'll have to see your code to help you out here. Would you mind pasting a snippet of it to me? Also the error you received.
How many dependent drop-down columns we can do using this code?!
I need about 5 or 6 dependents but things get complicated with each new level 😢?
Yeah.. things will get pretty complex with every level you add. It's no impossible though, you'd just have to expand the IF statement in each subsequent filtering function to make sure you're making the right matches. Not impossible, but definitely more complex.
Hi! I'm getting the "This app is blocked" error when trying to run this script. I've read all over the web and can't find any easy solution. Any help? Thanks
I want to automatically set the value in the 3rd column if there is only one value in the list. How can i do it?? please help me
Hmmmmmmm so sorta like a default value?
What you could do is have a check for the options and do an IF statement so that if the length of options is 1, then you auto set the value
So using the code from this video as the example, you'd want to put the IF statement before your final setCellState call.
For example
...
const range = filterRoles(department, team, getStructureData())
const rule = SpreadsheetApp.newDataValidation().requireValueInList(range, true).build();
targetCell.setDataValidation(rule);
if ( range.length > 1) {
setCellState(targetCell, 'Done')
} else {
targetCell.setValue(range[0]).setBackground('#fff')
}
...
^ Haven't tested that, but that's basically what you'll want to do. A cleaner way would be to also update the setCellState() function to also handle specific values instead of just clearing out the field and setting the background color
hi, this really helped me do a 3 level categorisation! however, i have trouble when using copy paste through dragging a cell downwards for the first category, the second category doesn't update :S does anybody have a fix for this?
Hi Paula!
Ah yes, this code was made for a single cell at a time. I have an updated version of this code coming out this month that'll let you do multiple cells and also do an infinite number of levels :)
Please stay tuned!
@@BootstrappingTools is this out yet? where will it be published? Looking forward to this newest video.
I'm still working on the video for this, BUT... the tool is up and ready for usage. Feel free to give it a try here: bootstrapping.tools/tools/dependent-drop-downs/
Hey!
Very useful ,can you provide coding
Try the code generation tool for this :D
bootstrapping.tools/tools/dependent-drop-downs/
Great! But with lots of data, a single, mixed table is not practical. What is used is a different (hidden) worksheet for each choice in the first dropdown. That is the functionality I need. Love the GAS approach, though!
Hi, at 5:38 when you changed the first dropdown from Finance to Marketing, only the content in the column B was cleared. How can it be scripted to clear the content in B4, C4 and so on if any?
Give the newer version a try -- it should help with the clearing issue you're experiencing
bootstrapping.tools/tools/dependent-drop-downs/
Hi. Is this also possible in google forms?
you mean like dynamically update the dropdown options on the google form while the user is making entries? I know you can update them before the user loads up the google form, but I'm not sure that you can update the options after the page is already loaded up.
Can please share example drives or script?
Hi Mahesh! Thanks for reaching out. Check out the pinned comment for the links you're looking for.
@@BootstrappingTools thanks for reply but i m unable to get the coding for dynamic coding
so can please share the code
@@maheshve4844 Here's the link to where you can download the source code for free: bootstrappingtools.gumroad.com/l/dependent-drop-downs-in-google-sheets
how do I contact you via email or social?
Hi Stanley!
You can reach us at social@bootstrapping.tools -- always happy to receive messages from y'all, but please keep in mind that I don't do freelance coding work - I'm here to teach y'all how to do it yourself :)
Ah, saw your email. Just responded!
This video is posted on my 8th birthday!
Happy belated!
Never seen this before :D But I tried it anyway, I think I am supposed to put something in getValues() - no idea what :D
Haha, trying is the first step to success!
when you call .getValues() - you don't have to put anything into it. It'll return an Array of data back that you can use for whatever you want.