Multilevel Dependent Dropdown in Google Sheets (With Google Apps Script)

If you are having trouble when inserting lines replace the conditional witht his line

if(activeSheet.getName()=="Dropdown" && activeRow>1 && activeCol>=3 && activeCol <=4 && activeValue!="")

Thank you for the comments that made me realize it

Were good statement doesn't appear on my end huhuhu

Thanks so much! This is exactly what I needed. You bring joy to the world with your nerdiness. :)

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.

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?

Is there a way to have this work on multiple sheets (but not all)?

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

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 !

Thank you very much!!

Hi . Your Patreon link doesn’t work ..

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.

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])

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.

Thanks

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? 🥲🥲

Not Working properly

Thank you so much!

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

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.

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..

Great video, thanks! Is it possible to let this script work for multiple tabs in one Google sheet?

Huge help, accurate explanation, and fixed my issue! Liked and subscribed! ty again

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?

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.

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.

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 <= lastRow; i++) {
try {
var dropValue = discovery.getRange(i,10).getValue();
if(dropValue != "") {
var dropsSheet = ss.getSheetByName("SOURCES");
var data = dropsSheet.getDataRange().getValues();
var list = data.filter(row=>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;}
}
}

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.

I've just tried your tutorial but it says that the register result is too big): I can't get past it

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'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.

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!

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 <=7){
var worksheet=SpreadsheetApp.getActiveSpreadsheet().toast("Pulling Staff Lists...");
var spreadsheet=worksheet.getSheetByName("Org Leader Config.")
var data=spreadsheet.getDataRange().getValues();
var list=data.filter(row=>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!

Thanks for this video

Amazing lesson. Tried if for my fault codes dropdown list in engineering department work orders system, everything is working perfectly! Thank you very much!!!!

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 && activeCol<=3){
var worksheet=SpreadsheetApp.getActiveSpreadsheet();
var spreadsheet=worksheet.getSheetByName("goc")
var data=spreadsheet.getDataRange().getValue();
var list=data.filter(row=>row[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.

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?

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?

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!!

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)

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?

May I know how you color an entire row based on the selected dropdown list?

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?

How can I apply this to multiple worksheets?

THANK YOU! Been looking for SO long for a solution!!!

This worked perfectly and much less code than others I looked at. Just had to change my offset for my use. Thank you!

Hi, Its says data.filter is not a function , can you pls help.

Thanks for your help

Please also help in to clear content when data is delete or modify at every level.

Please

I've been searching for this my whole life! You're a life saver!