Multilevel Dependent Dropdown in Google Sheets (With Google Apps Script)
Вставка
- Опубліковано 27 сер 2024
- In this video we will learn how to create a cascade of n dependent dropdowns in Google Sheets. It will work on 2, 3, or how many you want, as long as they are in order, one after the other
➤You can find the template from this video in my patreon page, where you´ll also have priority responses to your questions
practicalsheet...
You can also join my academy practicalsheets.com, where you´ll find, not only the templates for this and all the videos, but also Google Sheets Courses, technical service, and more
➤You can also subscribe to the channel where you´ll find a new Sheets video every week.
Here are some of the places where you can follow and support me:
➤Patreon: practicalsheet...
➤Webpage: practicalsheet...
➤Newsletter: practicalsheet...
➤Telegram: practicalsheet...
➤Facebook: practicalsheet...
➤Twitter: practicalsheet...
Any suggestion, question or insights, feel free to comment below
If you are having trouble when inserting lines replace the conditional witht his line
if(activeSheet.getName()=="Dropdown" && activeRow>1 && activeCol>=3 && activeCol
I've been searching for this my whole life! You're a life saver!
Are you having any issues with Line 3? I keep getting an error
Thanks for the words!
Can you share the error?
@@practicalsheets I emailed you :) Looking forward to your reply! :)
This is really incredible and very informative. Thanks
I've watched several videos on multilevel dependent dropdown lists and this is the first that worked out for me. I just completed this for an application I'm working on. Thanks.
Excellent!
Thank you so much for this video! This is the tutorial I used because it's explained so well. Starting from 18:18-18:55 you noted the problem of duplicate categories, but stated you would make another video about this issue in the future. I would really appreciate it if you could make an updated video addressing this problem (as well as the other problem of deleting). My spreadsheet and others could really use your help. Thank you!
I feel Google should come up with a better and quicker solution for it as this is a very common requirement in spreadsheets !
Thanks for the tutorial , it is good !
You are right
One of the main complaints from users that come from Excel is that doing dependent dropdown requires some work, whilest in Excel is much simpler
Regards!
Excellent and thorough demonstration of how to accomplish this in App Script. Thanks for making the video!
Thank you so much. I have been looking for this in a while and it worked exactly like I wanted. Thanks a lot
Glad it is useful!
Regards,
This worked perfectly and much less code than others I looked at. Just had to change my offset for my use. Thank you!
Thanks for the kind words!
Regards!
Amazing lesson. Tried if for my fault codes dropdown list in engineering department work orders system, everything is working perfectly! Thank you very much!!!!
Excellent!
Thx for the comment!
This is what I was looking for!
Excellent!
Great man 👍🏼. There’s one request I hope you consider, what if I want dependent drop-down goes vertically instead of horizontally.
Thank you.
Excellent suggestion. It shouldn´t be that different. I´ll think about it and come back to you
Regards!
Thanks so much! This is exactly what I needed. You bring joy to the world with your nerdiness. :)
I'm sorry for adding my implementation of the code in the comments. I understand that you want to drive traffic to your patreon.
No probl!
I can't help if you want to share your codes.
In the patreon I have to offer additional things like quick answers and variations over the basic templates
Thanks anyway for the comment!
Kind Regards
THANK YOU! Been looking for SO long for a solution!!!
Thank you for the kind words
Regards!
@@practicalsheets Hi! I got it to work but I noticed that, the dependent dropdown only populates, when I click away into a different cell and then click into it again. Am i doing something wrong?
@@itsgracehui Hello! It should populate when you enter or select the new data. Does it not?
@Shaybel Roquial L. Algones Hello! Could I see your code? In my sheet it is working as soon you select from the main dropdown. Regards!
Thanks for making this, it really has been helpful. Also, I'd really appreciate your guidance on how to clear data in columns D, E, F once the option in column C is changed. Also clear only columns E, F when the value in column D is changed and so on.
Thanks.
Excellent suggestion for part 2. It shouldn´t be complicated
Regards!
This is awesome, thanks so much! I’m having a bit of trouble understanding the arrays bit and how to connect/reference the columns from the data sheet with/in the dropdown sheet.
Thanks for the words!
Yeah, the array part is complicated at first
Anything you need let me know
Kind Regards
Huge help, accurate explanation, and fixed my issue! Liked and subscribed! ty again
Hello. I have a doubt. Mine is working fine till 15:45. But when I do steps from 17:00, dropdown list is blank, why is that?
thank you so much, this is exactly what i need
Great video! :) I have a suggestion for your next video: How to scan QR codes and save the data in it to an spreadsheet. As far as I know Google Sheets can do it. I hope my suggestion help you.
Thanks for the suggestion! I´ll look for it! Regards!
Great video,
I am working on a free to use tool for people to use to create a budget, track spending, and plan savings as well as getting out of debt. I have managed to get my first catagory done although I am strugling to setup a sub catagory would apprecaite a conversation to better understand building this.
This is fantastic! Is there any simple way to prevent users from manually inputing data in columns D, E and F (i.e. not coming from the gradual building of dropdowns starting with column C)?
Very interesting
Maybe you could try by putting an impossible data validation in these columns, say "Text contains "hjkkdsfhjkshdfhsdkf""
Once the dropdown is selected it will remove this data only for that row and create the dropdown (I hope)
Kind Regards
@@practicalsheets Yeah that actually works, awesome!
@@StefanHielscher-gw8oj Excellent!
I thought everything worked fine, but I see now I seem to have 1 issue with the script, not sure how to solve it.
I have 3 columns with dropdown lists. The first has 3 unique values. Then in my second list some values are the same, just as you have in your 3rd list (for example, you have Functions for Google > Sheets, as well as for Microsoft Office > Excel). Somehow when I select a value in my 2nd dropdown list, the dropdown in the last column just gives ALL values, instead of only the ones that need to show. Not sure if it makes sense what I'm explaining..
So I am getting the appropriate dropdown for the second column which is dependent upon the first, but for the 3rd column, it is showing me a dropdown dependent upon the second one only and not first and second one both.
Thank you very much!!
Thanks for the kind message!
Man, I'm trying to apply the array to my sheets, but I haven't figured it out.
My dropdown starts in U (21) Column, a and my Data is just like yours, Suite starts in A.
I'm código the arrange like this
var=list.data.filter(row=>row[activecol-21]==activeValue).map(row=>row[activeCol-22])
This is not working, I hope you can help me
Wow, thanks a ton! This video really helped me out. Do you happen to know if it's possible to format the dropdown as a "chip style" using App Script?
Good question
I´m not sure, given that it is so new
I looked around and I don´t see it
I´ll keep looking
Regards!
I chatted with you in FB about my error, please see it and help me, because I tried many ways but no effected. Thank you for your help!
Hi! Please you can write to practical.sheets@gmail.com instead
Regards!
hey, thank you for the information in this video it is helpful so far. I am running into a bit of a speed.
TypeError: Cannot read properties of null (reading 'getDataRange')
at dropdown(Code:11:26)
at onEdit(Code:20:3)
Normally when this happen you may have a problem with your sheet. Maybe the name was spelled wrong
Kind Regards
Love the script and the video. I know it has been asked before, but any progress or suggestions on vertical vs horizontal. I tried changing the offset from 0,1 to 1,0, and it seems to work for the first dropdown, but then the next is just blank. It seems like it should be easy, but it does not work. Thanks.
Thank you!
What do you mean by vertical vs horizontal
Regards!
@@practicalsheets
You say as long as the sheet with the dropdowns are one next to each other and that the order corresponds to the order in the data table. For instance, you use the following:
DATA Sheet
1 2 3 4
1 Suite Tool Category Subcategory
2
DROPDOWN Sheet
1 2 3 4 5 6
1 Email, Name Suite Tool Category Subcategory
2
So your multiple dropdowns are one next to the other going horizontally.
What I was asking, and some others had asked, was having the dropdown sheet look like this:
1 2 3
1 Email, Name Suite
2 Tool
3 Category
4 Subcategory
But I found the solution. The reason it would not work is that the first dropdown (Suite) looks to the Suite column in Data, so this works fine for horizontal or vertical. (meaning R1C1(Data) corresponds to R1C3(dropdown), so Suite to Suite.
But for the next iteration if going horizontally, now R1C2(Data) corresponds to R1C4(dropdown), R1C3 to R1C5, etc, so Tool to Tool, Category to Category, etc. But if you move down vertically you get R2C2(Data) corresponding to R2C3(dropdown) so Tool to Suite. The row doesn't matter, but there are now no matches because data in looking in the tool column to match the suite column. You need R2C2 to correspond to R2C4. This continues each time you move downward. (R3C3 to R3C5, etc). So after the line:
var data=spreadsheet.getDataRange().getValues()
I added the following:
if (activeRow>=2){ //if on the second iteration onward
activeCol=activeRow+2 //so R2 would be C4, R3 would be C5, etc
}
And you just change the line offset for setDataValidation(validation) to 1,0 instead of 0,1.
I made my own specific sheet when coming up with this, so did not do this with your exact code and data, so I don't know if I missed anything, but if you add this to your code it should work.
Thanks for this, really helpful. However, when I add a new row above or below the formula has an error, is there a way to fix this?
Hello!
Do you mean when you add a row in the dropdown sheet? or in the lists of values?
Regards!
Thanks for your help
Please also help in to clear content when data is delete or modify at every level.
Please
@Practical Sheets
I will work on part 2
Regrds!
@@practicalsheets Need this very much. Will it be availble here soon or sooner in Patreon?
@@stanleypaul1381 Hello!
Ill work on it, and make it available next week in patreon if i can
Regards!
I am trying to have a check box in sheet1!C12:C162 prompt 1 of 2 drop down lists in sheet1!F12:F162. I need the same format for an additional 12 sheets. 1 for every month. Any suggestions?
Hello
The two dropdowns are the same for all 12 months
I recommend using a more simpler code or formula. Here are 2 videos that may help
ua-cam.com/video/uuC24mFV8CY/v-deo.html
ua-cam.com/video/Wb8aMIL0Idw/v-deo.html
Regards!
Thanks for this video
Thank you for commenting!
My Last Column when i filtered but it shows all uniq data values, not only the particular one's, show's not relevant to that one also,please advise
Hello, Practical Sheets! Thank you so much for this video! I am trying to use this script for just one dependent dropdown list. Right now it is working so that the dependent dropdown appears, but it is blank, nothing in the list. Do you have any recommended troubleshooting actions?
I've read over and tinkered with the code a lot and I haven't been able to figure why I'm not getting that second list for the dropdown.
Thank you for your help!
I just figured it out!! I was missing a word!! :) This is such an awesome script, thank you so much!!! Just subscribed to your Patreon :)
Okay, I'm having another problem now. I am using this script in a spreadsheet/workbook with multiple tabs, and it's running in every single tab, instead of just the tab that I refer to in the code. Why is that happening??
figured this one out too. :)
You don´t need me at all ;)
This thread act as a reflection and a testament of how you figure things out! Congrats
PS. Sorry for getting back so late. You can write in the patreon forum
Regards!
Love this one, thanks for teaching and sharing! As for my case when I applied the code to my own data, I could get all of the data range when looking at the Execution log however I do not get the wanted filtered data. It shows nothing inside the bracket (Info [ ] ). Any idea why the filter function did not return any results?
Just figured it out after reading all the previous comments/answers in here so this is the code I wrote "var list= data.filter(row=>[0]==activeValue).map(row=>row[1]) - The code did not work because I missed to write "row" before [0] - So the whole exact code should be then var list= data.filter(row=>row[0]==activeValue).map(row=>row[1]). Been stuck for two days and looking around the internet/google on what might be the error but no satisfying results so came back again to this video and read all the existing comments and found out that we have to pay attention to each single element we write 😁
Thanks man!
i get this message:
Exception: The parameters (number) don't match the method signature for SpreadsheetApp.Range.offset.
at dropdown(Code:14:16)
at onEdit(Code:22:3)
im using a Right-to-Left sheets (its in hebrew).. what am i doing wrong?
Can you show me the offset line?
Regards!
Thank you so much!
Hey, how's it going? Dude please help me. The function: =SUMIF(B:B;">=1.76") will sum only the values greater than or equal to 1.76 from column "B".
But instead of adding I wanted to MULTIPLY. How do I do this?
Hello
Here you can use SUMPRODUCT
I´ll try to do a video soon
Regards!
I have been getting the appropriate dropdown for the second column which is "Manufacture" dependent upon the "Parameter" but for the "Pack Size" it is showing me a dropdown dependent upon the Manufacturer only and not Parameter and Manufacturer both.
I seem to have the same issue. Were you able to solve it?
If I set this script to my template tab, how can I get it to run on other tabs within the same spreadsheet when I make a copy of the template? I'm thinking I could set the script to work on Active Sheet but I'm not sure where to indicate that on the script. Thanks
Hello!
One option would be to remove this part of the code
activeSheet.getName()=="Dropdown"
However you should be careful because this will make it work in all of your sheets
Regards!
@@practicalsheets I think this is the exact problem I'm having! (Disclaimer, I know nothing about App Script and I just copied what you were typing but I was able to figure out how to tweaks to make it work on my spreadsheet.) I need to apply this to several sheets. So I thought I'd just copy and past the same script below the original one and update the name of the sheet accordingly. But only one spreadsheet worked. Now that I see this comment, I removed the line you indicated above and now it works on all the sheets. Is there a way to make it work on some but not all sheets?
@@Angie-lf7h I am working on a video to address this. Stay tuned the next couple of weeks! Regards!
Thanks
Thank you!
I have problems running this now (it kept the 4th column blank) is it because on the data validation criteria, the "list" was replaced with "dropdown (from a range)"? An update on this would be great! Let me know how to solve this it can be an update on line 12 | var list=data.filter(row=>row[activeCol-3]==activeValue).map (row=>row[activeCol-2])
May I know how you color an entire row based on the selected dropdown list?
The easiest way would be to use conditional formatting. I could do a video if you want
Regards!
I just followed your steps, but i found when i add more Rows at the bottom, the dropdown ended from Column F to G, could you please help! Thanks!
Hello!
Do you mean rows in the Data? or in the Dropdown sheet?
Regards!
@@practicalsheets Thanks for the reply! Rows in the dropdown sheet
@@terrysun2683 Hello
I think I don´t fully understand you
You mean that in the new rows added you only have dropdowns up to G?
Regards!
I'm having the same problem when I add rows in the dropdown sheet, kindly help
Hi!! Thanks so much for this video! Im hoping you can help me. I am at the part of the video where you are testing "Toast" and mine doesnt show up :( The error I keep getting is "TypeError: Cannot read property 'getRow' of null dropdown @ Code.gs:3". Any ideas why?
WEre you able to solve?
Regards!
I have more than 500 values in the dependent dropdown. and Data validation has a limit of 500 values . Is there any possible way to make it dependent dropdown.If so please revert ASAP.
Thanks for the great video! Would you happen to have any suggestions on how to get this to work for verticle data? Ex) Level 1 Dropdowns A1:B1 (Google, Microsoft Office), Level 2 Dropdowns A2:A4 (Sheets, Forms, Docs), and B2:B4 (Excel, Teams, Word). I have tried playing around with the code, but have not had any luck. I was able to get your code to work by creating a helper sheet that transposes my data, but I am trying to find a better solution that doesn't require the helper sheet. Thanks in advance! Have a great day.
Hello!
I´m not sure if it can be done
I will leave it as a second video, if I make it, ;)
Regards!
I get the following errro: ReferenceError: row is not defined
at dropdown(main:22:29)
at onEdit(main:35:3)
Can someone please help me?
Great video, thanks! Is it possible to let this script work for multiple tabs in one Google sheet?
I found the answer in one of the comments here, thanks!
Is there a way to have this work on multiple sheets (but not all)?
Yes! I´m working on a tutorial on this subject
Regards!
@@practicalsheets thank you so much!
Were good statement doesn't appear on my end huhuhu
I've just tried your tutorial but it says that the register result is too big): I can't get past it
How many rows do you have?
Regards!
How can I apply this to multiple worksheets?
Hello!
If it´s in the same order in every sheet
Just change this line
if(activeSheet.getName()=="Dropdown" && activeRow>1 && activeCol>=3 && activeCol 1 && activeCol>=3 && activeCol
Hi . Your Patreon link doesn’t work ..
Finally I fixed it!
Thanks for noticing ande letting me know
Regards!
Not Working properly
challenge. do this with no code
I'm loving this script. Thank you. I've edited it to happen onOpen and to view each row for a value that's already set. Then, depending on its predefined value, it sets the dropdown for the respective cell. The only problem there are about 550 lines and it only gets to about 200 before timing out. How would you adjust this code to make it faster?
function onOpen() {
dropdown();
}
function dropdown() {
// var activeValue = "CD";
var ss = SpreadsheetApp.getActiveSpreadsheet();
var discovery = ss.getSheetByName("DISCOVERY");
var lastRow = discovery.getLastRow();
//The Loop
for (var i = 3; i row[0]==dropValue).map(row=>row[1]);
var validation = SpreadsheetApp.newDataValidation().requireValueInList(list).setAllowInvalid(false).build();
var discovery = ss.getSheetByName("DISCOVERY");
discovery.getRange(i,6).setDataValidation(validation);
} else {continue;}
} catch(error) {continue;}
}
}
I keep getting an error and cant figure out what it could be. This is the code I used, my onEdit() is set up separately. The sheet where my dropdowns are located is called 'Active' (column 6-8 and row 3 - row 1 & 2 have header info) and the data sheet is called 'Org Leader Config.' (column 1, row 2):
function DependentDrop() {
var activeCell=SpreadsheetApp.getActiveRange();
var activeRow=activeCell.getRow()
var activeCol=activeCell.getColumn()
var activeValue=activeCell.getValue()
var activeSheet=activeCell.getSheet()
if(activeSheet.getName()=="Active" && activeRow>2 && activeCol>=6 && activeCol row[activeCol-6]==activeValue).map(row=>row[activeCol-5])
var validation=SpreadsheetApp.newDataValidation().requireValueInList(list).setAllowInvalid(false).build()
activeCell.offset(0,1).setDataValidation(validation)
}
}
I am getting the following error:
Error TypeError: Cannot read properties of null (reading 'getSheetByName')
at DependentDrop(DependentDrop:10:31)
at onEdit(Code:9:3)
I'm at a loss on where I messed up. Thanks!
Hello!
Remove the toast part here
var worksheet=SpreadsheetApp.getActiveSpreadsheet().toast("Pulling Staff Lists...");
Regards!
@@practicalsheets I tried that, script now looks like:
function DependentDrop() {
var activeCell=SpreadsheetApp.getActiveRange();
var activeRow=activeCell.getRow()
var activeCol=activeCell.getColumn()
var activeValue=activeCell.getValue()
var activeSheet=activeCell.getSheet()
if(activeSheet.getName()=="Active Reqs and Progress" && activeRow>2 && activeCol>=6 && activeCol row[activeCol-6]==activeValue).map(row=>row[activeCol-5])
var validation=SpreadsheetApp.newDataValidation().requireValueInList(list).setAllowInvalid(false).build()
activeCell.offset(0,1).setDataValidation(validation)
}
}
I'm getting this error now:
Exception: Please select an active sheet first.
DependentDrop @ DependentDrop.gs:2
@@karinab5593 Hello
When you say you "set up separately", is that your code is not within the Sheet
This is where the problem may be
Regards!
@@practicalsheets I have separate sections for each of the scripts I'm running.
This is the Code.gs file
function onEdit()
{ NewCopyRow(); //run 1st
MoveHires(); // run 2nd
MovePauseCancel(); // run 3rd
DependentDrop(); //run 4th
}
Then I have a XX.gs file for each functions above. Not sure if that makes sense
@@karinab5593 You mean a library?
You should separate this part
var activeCell=SpreadsheetApp.getActiveRange();
LEave it in the code of your sheet and then add it as an argument to your DepentDrop function
Regards!
Thank you so much for the very useful video!
But my code doesnt work:
function dropdown() {
var activeCell=SpreadsheetApp.getActiveRange();
var activeRow=activeCell.getRow()
var activeCol=activeCell.getColumn()
var activeValue=activeCell.getValue()
var activeSheet=activeCell.getSheet()
if(activeSheet.getName()=="Jan" && activeRow>4 && activeCol>=1 && activeColrow[activeCol-3]==activeValue).map(row=>row[activeCol-2])
var validation=SpreadsheetApp=newDataValidation().requireValueInList(list).setAllowInvalid(false).build()
activeCell.offset(0,1).setDataValidation(validation)
}
}
function onEdit() {
dropdown()
}
Errors:
TypeError: activeCell.getRow is not a function
at dropdown(Code:3:28)
at onEdit(Code:15:3)
TypeError: data.filter is not a function
at dropdown(Code:12:19)
at onEdit(Code:19:3)
ReferenceError: SpeadsheetApp is not defined
at dropdown(Code:9:5)
at onEdit(Code:14:3)
Could you pls help me point out sth wrong here? Thanks alot.
I found one error
var data=spreadsheet.getDataRange().getValue();
It is getValues()
Regards!
@@practicalsheets Thanks teacher, now it works!
Hi! Thanks a lot for this! For some reason I cannot get it to work, what am I missing? Should I activate this script somewhere? My file is as yours but nothing happens. Maybe I have notifications disabled? This is my code:
function dropdown() {
var activeCell=SpreadsheetApp.getActiveRange();
var activeRow=activeCell.getRow()
var activeCol=activeCell.getColumn()
var activeValue=activeCell.getValue()
var activeSheet=activeCell.getSheet()
if(activeSheet.getName()=="Dropdown" && activeRow>1 && activeCol==3){
SpreadsheetApp.getActiveSpreadsheet().toast("Were good")
}
}
function onEdit(){
dropdown()
}
Thanks!!
Add this
if(activeSheet.getName()=="Dropdown" && activeRow>1 && activeCol==3){
SpreadsheetApp.getActiveSpreadsheet().toast("Were good")
Logger.log("It´s working")
}
And execute from the editor
Regards!
Amazing video! But I can't get my code to work :( I keep getting this error. Any advice on how to fix?
TypeError: ws.getSheetbyName is not a function
at dropdown(Code:10:15)
at onEdit(Code:20:3)
Hello!
Be careful with upper and lower case
getSheetByName
Regards!
@@practicalsheets Yes... after hours of staring, and trying everything, that was the issue hahaha. Thanks so much!
@@MariaMiranda-om2lj Been there. sometimes just a coma costs hours and hours
But it is very gratifying to solve it, even if it is trivial
Finding errors and debugging is a whole science
Regards!
not working even I try to copy your code and sheet but no results at first it show me a error (please select an active sheet first. -code.gs:2) I'm Stuck here I'm very new in this. please anyone can help me? 🥲🥲