Google Sheets - Dependent Drop Down List for Entire Column, Multiple Levels
HTML-код
- Опубликовано: 22 авг 2024
- Learn how to create multiple level dependent drop down validation list in Google Sheets that works for the entire column. We'll be using JavaScript and Apps Script in this tutorial.
#googlesheets #dropdown #list
I've literally watched about 8 different tutorials but yours is the only one that had exactly what I wanted and explained in an easy way, thank you!!!!
Great to hear!
Great. I 55 and have discovered the wonderful world of coding. I'm learning and your videos are greatly helping. Thanks a lot and keep up the great work
Perfect video. First time ever that I wrote an Apps Script and had to learn a lot in order to achieve a three-level dependent doplist. But you did a fantastic job in explaining all commands step by step. I am still no expert and can hardly describe the purpose of all the code I've written. But most of it makes sense to me and I have a general understanding of what every line is for. That shows how great this video was! It made me learn a very complex and fully new thing in less than three hours. You are my go-to expert for any questions related to Sheets :))) Keep it cocming! - Moritz
Will be using this to create a personal daily report card. (Woke up at 6a, meditated for at least 15min, green day trading, 30min walk, bed by 11p, etc, etc) then get a "grade" at the end of the day. Thank you for the solid instructions.
Thank you so much for doing this video! You don't know how long I looked for this, because we needed it for our sales spreadsheet. I don't have a background in coding nor programing, but your step-by-step explanation was crystal clear. The only problems I ran into were typos or me not handling commands correctly. After following it to the end, it worked like a charm.
Again, thank you very much!
Thank you so much for uploading these videos! I've been trying different solutions to the dynamic dependent dropdown list problem for my homebrew system. Found several ways how to make it work from the internet. Currently yours and Jason Jurotich's is the most comprehensive even though IMHO yours is the more dynamic and scalable. Always looking forward for your next uploads. keep up the good work!
Gee, that was exactly what I was looking for! After watching your video like 4-5 times I managed to modify it for my needs and it is perfect! I cannot thank you enough! The only downside of using Scripts I think is that they're bit slow, but gets the job done nonetheless! Thank you once again, really informative and awesome explanation! Keep it up, bro! Respect
Difficult to deny that coding is a great skill for most jobs right now
Took a bit of tweaking for what I needed but was a massive help, thank you for taking the time post it and step through the process.
YOU ARE THE BEST! Solved my problem 100%
For those who would like to make ranges also autoupdateble try using function:
=UNIQUE('master'!A2:A999) on sheet "options" in cell A2
=UNIQUE('master'!B2:B999) on sheet "options" in cell B2
=UNIQUE('master'!C2:C999) on sheet "options" in cell C2
The only thing that I don`t know how to get rid of is error massage from data validation when you enter new items directly into the master`s sheet.
Let me know if you have an answer.
I have extremely NO experience or knowledge about programming. But this video helped a LOT! Thank you kind sir.
😀
I have enjoyed watching and following along while working in an actual sheet and typing in the script in by hand. A few days ago I just tried something else and nothing worked so today I followed the video exactly and now it works perfectly. I have watched many of your videos and learned a lot. Some folks have ask for a sample sheet or that you provide the code but I think it is better to learn GAS by typing in the code instead of having the code given to you.
Excellent videos and look forward to doing some of the others.
James/mreighties :)
Do you know how to apply this to hundreds of rows at once, instead of running the script row by row?
This tutorial has been immensely helpful! I would definitely categorize my coding capability as amateur, but I found this very easy to follow. I have one question that I haven't seen in the comments yet. I've built a workbook that has repeated sheets for each month. I want to perform this function on each. How do I alter the script so on January, February, March,...etc it performs this multi-level data validation to the same array of data?
Again - very impressed with how well you convey information.
I'm really not a specialist, but in my mind, you need to copy the onEdit function, add a new set of variables that match the new Sheet name and do some tweaks in the onEdit function, like replace the "mainWsName" with the new Sheet.
Great video tutorials. Hat's Off to you guys for useful contents. Wish there was some way to copy paste the code, either from the website or from the youtube description.
I wish too hahahah, but I guess It's better to do it at the same time to learn more.
My notes - hope it helps. also how I made mistakes.
//23:40 ruclips.net/video/s-I8Z4nTDak/видео.html
//24:00 Making sure it grabs the right List, the dependent dropdown.
//global script
// 40:31 making the SHEET a global variable
var mainWsName = "APPSCRIPT";
var optionsWsName = "SECTION";
//
var firstLevelColumn = 1;
var secondLevelColumn = 2;
var thirdLevelColumn = 3;
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName)
//I need to make an Array where all the Sections is mapped to all the Options of that Array.
//creating the array is in 23:31. row and column start.
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);
var options = wsOptions.getRange(2,1,wsOptions.getLastRow()-1,3).getValues();
//31:05 stuck here but fixed. I was missing the applyValidationito Cell visible at 12:59
//now stuck at 32:11 - it doesnt clear
// 36:28 we are going to make it that the Main column can easily be changed without changing the code.
// function myFunction() {
// var list = ["a", "b", "c"];
// var cell = ws.getRange("C2");
// applyValidationToCell(list,cell);
// } //removed at 40:57
//it limits the edits to Column 1 and Row >5
// this way only this column works.
// unforunately this means this script only works with
// this sheet.
function onEdit(e){
var activeCell = e.range;
var val = activeCell.getValue();
var r = activeCell.getRow();
var c = activeCell.getColumn();
var wsName = activeCell.getSheet().getName();
//37:01 Chaging this to firstLevelColumn
if(wsName === mainWsName && c === firstLevelColumn && r > 5) {
applyFirstLevelValidation(val,r);
} else if (wsName === mainWsName && c === secondLevelColumn && r > 5) {
applySecondLevelValidation(val,r);
}
} // end of onEdit
//38:18 new function
function applyFirstLevelValidation(val,r) {
if(val === ""){
ws.getRange(r,secondLevelColumn).clearContent();
ws.getRange(r,secondLevelColumn).clearDataValidations();
// 48:15 clear validations
ws.getRange(r,thirdLevelColumn).clearContent();
ws.getRange(r,thirdLevelColumn).clearDataValidations();
//will try this in 33:16
} else {
ws.getRange(r,secondLevelColumn).clearContent();
ws.getRange(r,thirdLevelColumn).clearContent();
ws.getRange(r,secondLevelColumn).clearDataValidations();
ws.getRange(r,thirdLevelColumn).clearDataValidations();
var fileredOptions = options.filter(function(o){return o[0] === val });
var listToApply = fileredOptions.map(function (o){ return o [1]})
//console.log(listToApply);
//removed console log in 37:40
var cell = ws.getRange(r,secondLevelColumn);
applyValidationToCell(listToApply,cell);
} //else
} // applyFirstLevelValidation
function applySecondLevelValidation(val,r) {
if(val === ""){
ws.getRange(r,thirdLevelColumn).clearContent();
ws.getRange(r,thirdLevelColumn).clearDataValidations();
//will try this in 33:16
} else {
ws.getRange(r,thirdLevelColumn).clearContent();
//44:25
var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
var fileredOptions = options.filter(function(o){return o[0] === firstLevelColValue && o[1] === val });
//46:10
var listToApply = fileredOptions.map(function (o){ return o [2]})
var cell = ws.getRange(r,thirdLevelColumn);
applyValidationToCell(listToApply,cell);
} //else
} // applySecondLevelValidation
// visible at 12:59 this was the other mistake. I forgot to encode this part.
function applyValidationToCell(list,cell){
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule);
}
does it work? thank you :)
@@ysajoya3676 i can only say it worked for me.
I'm having trouble on the e.range, here is the error message: TypeError: Cannot read property 'range' of undefined
Great video, just what I needed! Very clear, quick and nicely presented. Learnt so much doing it, especially having to closely follow your script and also type it in. Thanks.
Used some techinques from this + the dynamic drop down video to build what I needed to. Thank you!
This is great!! Your walk through was so professional. Well done. AND, the script works so well.
Seriously, though, thank you for such a thorough walk-through of the code.
:)
Thank you so much! This is the best channel for learning google sheets with appscript! Keep going!
You're legend making everything so easy, feels like I can make google sheets work like any software. Thank you so much for making these wonderful videos so damn easily accessible to everyone!
the best video that i could find. thank you!
Legend, followed it step by step and it works! Couldn't make two functions out of it, so created another else if so my second function works as well. Brilliant!
Thank you a lot for the video. I just couldn't figure out how I would apply it to two or more sheets.
Your videos are just brilliant. It helps to make Google Sheets work like ERP - thereby offering a pocket friendly turnaround on small businesses. Request to create a library of the Appscripts from where the codes can be copied & implemented easily.
This was super useful - just the right level and speed - learnt much more than I was looking for. Thank you Sir.
After two days of watching on this lol .... I was able to create drop downs for 4 columns with no previous Javascript experience thanks so much!!! For everyone else who is watching with the same background some tips:
Case matters, kept getting an error on the range but it was because I had getrange instead of getRange.
Also if you end up adding more functions for more levels remember you have to update the array on top.
I am wondering does anyone know how to clear a different validation that wouldn't be from the underlying structure? In this example let's say there was a fourth column was title (junior or senior) and you created the data validation on the master sheet just by referencing that range instead of creating rows for record. In the script is there a way to create a rule when you clear the region it clear this other validation?
have to tell you man, been watching ur videos here and there and you are absolutly didactic, really thanks for your effort and congratz for all of this! regards from brazil, tnx again
Hi Team,
I am using this filter 5 dependent columns with more than 5000 rows of data in the background. I never thought that load would be an issue, before I started working on this. Have you faced a similar problem? If yes, please let me know a possible solution.
Thanks for everything you do on this channel!
you are an amazing tutor and helped me so much over the last 2 years, i cant thank you enough man, i really appreciate you!
Thank you for the great informative Vedios .You need to update this method because i managed to create multi levels drop-down list using query and filter with transpose function it was very easy
This tutorial is awesome! Even though I was not using AppScirpt ever before, I was able to apply it to my own needs. I just have one question: is there a possibility that we can automatically set formatting for the dependent drop-down list? For instance, having a "Chip" style instead "Arrow" style and adding colours for different options on the list?
If I love you? What is love anyway, but it was most certainly very helpful. Thank you!
You're going so fast in this video! -your previous was slow and steady and ive been able to catch up. This one i need to pause, LOL. Good job by the way! love your videos!!
Any chance you can post a pic or something of the entire final version of the script? i followed your video step by step but im getting an error and also my play/run arrow is grey and i cant make the script run. thought maybe i missed something.
Amazing Video! I built this exactly how you demonstrated in the video. I am hoping someone out there can help me apply this script to other tabs within the same file.
This is what i've been looking for , thanks man , keep up the good work !
Thank you so much for teaching all of this and so patiently!
perfectly narrated video. It helps me a lot in understanding data validation through scripting. but one flaw is there that this code does not delete the corresponding values when we delete bulk cells. any idea?
I am continuously watching videos from this channel specially for googlesheets....
@Learn Google Spreadsheets I was sucessful following your script, more I did 3 level dependent drop down list for 6 sheets in one workbokk. Thank you very much ! But I found the better way to do without script any more. It's very easy for every one !
How?
I would absolutely love to see a video where this is optimized to work with any number of columns in the "options" sheet
Learn Google Spreadsheets, first of all, thank you for making this tutorial.
Please could you tell us know how we:
- Run this script on multiple sheets(tabs)
my understanding is .getSheetByName doesn't allow additional sheets but what would be your workaround?
I've been struggling to create a dependent drop down list.
The most common method I find with google search is using "list from range" to create the dependent menu. But it works by using a formula that displays a list in a cell somewhere depending on what is selected in menu A. Then menu B is set to use the list displayed by that formula.
Pretty straightforward, however it's not usable for me. The sheet I'm creating will be using data that is often going to be copy pasted in several lines at a time. Meaning that the cells in Column A would be filled simultaneously, which is where this method fails. Because it can't handle more than one input at a time.
I haven't found a viable alternative yet. I think this is crazy, because I want something so simple. It's disheartening that the most common method is essentially so convoluted.
You should be able to use onEdit with range offset to handle those. I have a video that covers onEdit here ruclips.net/video/frAL7bJkU54/видео.html and combined with this video it should work. That being said, if you find this method convoluted then it will probably be too complicated to do it.
If it was possible to double my like, I would have. Thank you because it is been a while I was looking for this
Thats your "on fire mode" (just feels like that) in this third ddvl video is a plus point Sir :)
Too helpful.. 👍The way you explain step by step make my work much easier to learn and implement at the same time
Glad to hear that
Thank you so much! The video is so well explained and amazingly useful! Great job!
Love how you go through the thinking/logic process...but it is really limiting not to have the script text. Sure, typing it out myself was informative...but not having a text to refer to means I'm really hampered in my efforts to troubleshoot. Pretty much ready to give up.
I didn't give up. Finally got it to work by starting over and recreating step by step along with the video. Here's the code in case it's helpful to someone:
//Code from ruclips.net/video/s-I8Z4nTDak/видео.html
var mainWsName = "master";
var optionsWsName = "options";
var firstLevelColumn = 1;
var secondLevelColumn = 2;
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);
var options = wsOptions.getRange(2,1,wsOptions.getLastRow()-1,3).getValues();
function myFunction() {
}
function onEdit(e){
var activeCell = e.range;
var val = activeCell.getValue();
var r = activeCell.getRow();
var c = activeCell.getColumn();
var wsName = activeCell.getSheet().getName();
if(wsName === mainWsName && c === firstLevelColumn && r > 1) {
applyFirstLevelValidation(val,r)
}
} //end OnEdit
function applyFirstLevelValidation(val,r){
if(val === "") {
ws.getRange(r,secondLevelColumn).clearContent();
ws.getRange(r,secondLevelColumn).clearDataValidations();
} else {
ws.getRange(r,secondLevelColumn).clearContent();
var filteredOptions = options.filter(function(o){ return o[0] === val });
var listToApply = filteredOptions.map(function(o){ return o[1] });
var cell = ws.getRange(r,secondLevelColumn)
applyValidationToCell(listToApply,cell);
}
}
function applyValidationToCell(list,cell){
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule);
}
Amazing! Helped a lot! Thank you very much!
Thanks a lot. Had to modify some stuff to fit my purpose, but you are so clear. You are excellent!
How can I apply same app script for multiple sub sheets in one sheet?
First of all thank you for the excellent videos. Do you have a video explaining how to give all users access to an App Script? I finally got the dependent drop down to work, but other users (with edit access to the sheet) cannot run the script. Documentation on StackOverflow is not helping me although I can see that others have also experienced this. Thanks!
Thanks so much for this! However, I simply get a "False" in the second column when I make a selection in column 1. I cannot work out why....is there something obvious I may be doing wrong?
Great content, did fix my problem after a few adjustments from the example in the video, good job!
Suppprrbbbb... Completed my database management system because of you thankyou soo much ❤️❤️❤️❤️❤️❤️
can you please paste code in the comments section so that noobs like me can just use it!!
These videos are very helpful but the lack of a link to a website or GitHub or sample sheet makes applying the lessons tiresome. It would be very helpful and super appreciated if they were included.
Hello Rajesh, I was able to find a solution with this code
function onEdit(){
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data")
var activeCell = ss.getActiveCell();
//in my "data" Tab the first drop-down column is column 1 and starts at row 2
if(activeCell.getColumn() == 1 && activeCell.getRow() > 1){
activeCell.offset(0, 1).clearContent().clearDataValidations();
var makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1;
if(makeIndex != 0) {
var validationRange = datass.getRange(2, makeIndex, datass.getLastRow());
var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
activeCell.offset(0, 1).setDataValidation(validationRule);
}
}
}
Here is my sheet docs.google.com/spreadsheets/d/1ad7yNf0sUbDutQ_ptJeVrpBS_jEtHB83SnpISY_ahu8/edit#gid=1629349362 Hoping this helps you.
I've got this successfully on a project! However, I need the script to run in multiple sheets and not just in one as in the video. I've got no idea on how to do this (nor coding lol), does anyone know how to do so? So much appreciated 🙏🏻
Sending a big appreciation!
works great
One question, I have multiple sheets in the same Workbook and I would like to apply same function to the multiple sheets
is this a possibility??
thank you in advance
Yes. You'll need to move it to a separate function and call it inside onEdit. Check out my onEdit timestamp video for more details.
@@ExcelGoogleSheets Could you please link it? There are a few. thanks!!
@@ExcelGoogleSheets I want to do the same where i want to run the script on different sheets in same workbook but cannot understand how to do it from your onEdit timestamp video.
Greatly appreciate your videos.
//var mainWsNames = "main2"; // no longer necessary
var optionWsName = "list2"
var firstLevelColmn = 1;
var secondLevelColmn = 2;
var only = ["main2","test","Copy of main2","Copy of test"];// store the sheets we want to include/exclude see comments below
var ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();// we will have to modify this so it get the active sheet nothe sheet by name
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionWsName);
var options = wsOptions.getRange(2,1, wsOptions.getLastRow()-1,3).getValues();
function onEdit(e){
var activeCell = e.range;
var val = activeCell.getValue();
var r = activeCell.getRow();
var c = activeCell.getColumn();
var wsName = activeCell.getSheet().getName();
if (!(only.includes(e.range.getSheet().getName()))) { //if we only want to exclude (not use) the drop down on certain pages
// if (only.includes(e.range.getSheet().getName())) {// if we only want to include (use) the drop down on certain pages
return;
}else{
if(c===firstLevelColmn && r>1){
applyFirstlevelValidation(val,r);
};
};
};
function applyFirstlevelValidation(val,r){
if(val === ""){
ws.getRange(r,secondLevelColmn).clearContent();
ws.getRange(r, secondLevelColmn).clearDataValidations();
}else{
ws.getRange(r,secondLevelColmn).clearContent();
var filterOptions = options.filter(function(o) {return o[0] === val });
var listToApply = filterOptions. map(function (o){return o[1]});
var cell = ws.getRange(r, secondLevelColmn);
applyValidationToCell(listToApply,cell);
};
};
function applyValidationToCell(list,cell) {
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule);
};
It only limits upto 500 rows on the list, more than that it has an error. How can I add more than a 500 rows on the list?
"Execution failed: The data validation rule has more items than the limit of 500. Use the "List from a range" criteria instead."
have you resolved this?
You are amazing. thank you for the video. saved my life
Thank you so much for detailed instructions. Further Question: I’d like to know whether it is possible to select several options(multi-select) in one cell on the column of “Sales_Rep” based on this script? Thank you:))
Perfect video, just have a question. What if the dropdown data and dropdown menu sheet are from a different google sheet? Can we use openById().get SheetByName() or name a range, then getRangeByName() to exact the dropdown data?
Thank you so much for this video. If possible, can you make the same video (dependent drop down list) using the updated Apps Script? I followed all the steps but there's always an error and I think it's because of the updated apps script. God bless you.
same concern here :(
Hello, I am a newb and I made this work. However, I don't know how to add two of these functions to one script. I only want two columns to talk to one another but I want to do it on two different areas, ie; Columns (G,H) and then Columns (J,K) AND I want column K to be multiple selections without repetitions.
Should I not even bother? This one thing took me 6 hours today!!
Very well done. This was very thorough and helpful.
Нашла то, что искала! Наконец-то! Большое спасибо!
Man, you're the fucking master.
Super lesson, thank you, I will try to do this 👍🙏
Great!
just exactly what i need factions improve in my Project! Thanks Bro!
Thank you for all the video tutorials, extremely helpful and easy to understand. I followed this one and managed to apply it to my data, I was hoping to have the script apply to ‘open as app’ so I could use it as a front end to take product orders but it did not work. Any advice on how this dependent drop down can be applied in a more user friendly front end? Thanks
Thank you very much for your great video! Helped me a lot!!!
Thank you for the tutorial
What can I do if I want the dropdown to auto-complete when only one option is available?
This REALLY helped me out! Thanks so much!
Fantastic tutorial!!! How would you change the script to paste the 3rd column of data automatically? I only have one item to paste in the third column so it doesn't make sense to have to select it. I would like it to automatically paste the third column of data from the 1st two selected.
Thank you for the awesome tutorial! I am running into some speed issues with the script's calls. I have a large file with many options and dropdowns. Do you have any advice on how to consolidate the calls to speed up my dropdown fields? Thanks.
Hi! Thank you very much for the tutorial. Is it possible to make the script run for several similar tabs? Let it be "master", "main" and "check"? What changes are needed inside the script? Thank you in advance!
Another useful video! Thanks @Learn Google Spreadsheets!
What if I want to use the code for many sheets?
This was an AMAZING tutorial and just what I was looking for, THANK YOU!
I was wondering if you can do a quick tutorial on how to make the script more dynamic (dry - don’t repeat yourself) for using similar drop downs across multiple sheets (selection independent of each other ... and maybe also, dependent across the sheets, if you want to go crazy advance).
For example, let's say you have one "Options" sheet, but multiple “master” sheets where you need to have similar multi-level drop-downs across multiple sheets (same names/source values/etc). So master_1 would have Region, State, and Sales Rep drop downs, master_2 would have Region, State, and Sales Rep drop downs, master_3 would have Region and State drop downs, master_4 would have State and Sales Rep drop downs, and so on.
A tutorial on how to make this script 'dry" would be wonderful - currently, I am declaring each sheet vars and doing if/else within functions which results in a lot of repeated code. Not sure how to dynamically use the sheet object (e.g. pass in the current sheet object (master_1WS or master_2WS, and so on) as an argument in applyFirstLevelValidation function and dynamically write “master_1WS.getRange(row, secondLevelColumn).clearContent();” vs “master_2WS.getRange(row, secondLevelColumn).clearContent();” just once). Thank you again for this video though.
Maybe at some point. I'm really tired of dependent drop-downs :)
@@ExcelGoogleSheets haha, I understand, sounds good. thank you
Great video, think I want to learn more now.
@ 46:26 the "var activeCell = e.range;" no longer makes sense
I am getting the error for the onEdit(e) function "Cannot read property 'range' of undefined"... any suggests on how to fix?
did you find the solution for this error, i am also getting the same
I watch your videos all the time and you have helped me learn so much information. I am curious as to how you would write the code so that you can use the varying dropdown in multiple sheets in a workbook. I have a book that I am building that each sheet is a different event and need to be able to have each one reference my Template page of different equipment and vendors but currently can only make it happen on one sheet. Any ideas?
Did you find an answer to it?
Hi, i got this all right... Thanks so much... Just stuck on one thing...
What if I need to make another sheet which will have the same validation... Like say another Masters sheet like you have in here, and it should also work the same...
great tutorial, i make it with 4 columns.. you save my life... keep like that!
Could you please share how did you add the fourth column? I tried, but did not work
Hello, can I please ask how you did this for 4 columns, as I have replicated the script for a forth column and the data validation will appear but with no information that corresponds with column three.
@@TiptonSamurai
var mainWsName = "main";
var optionsWsName = "options";
var firstLevelColumn = 1;
var secondLevelColumn = 2;
var thirdLevelColumn = 3;
var fourthLevelColumn = 4;
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);
var options = wsOptions.getRange(2, 1,wsOptions.getLastRow()-1,4).getValues();
function onEdit(e){
var activeCell = e.range;
var val = activeCell.getValue();
var r = activeCell.getRow();
var c = activeCell.getColumn();
var wsName = activeCell.getSheet().getName();
if(wsName === mainWsName && c === firstLevelColumn && r > 1){
applyFirstLevelValidation(val,r);
} else if(wsName === mainWsName && c === secondLevelColumn && r > 1){
applySecondLevelValidation(val,r);
} else if (wsName === mainWsName && c === thirdLevelColumn && r > 1){
applyThirdLevelValidation(val,r);
}
}//end onEdit
function applyFirstLevelValidation(val,r){
if(val === ""){
ws.getRange(r, secondLevelColumn).clearContent();
ws.getRange(r, secondLevelColumn).clearDataValidations();
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
ws.getRange(r, fourthLevelColumn).clearContent();
ws.getRange(r, fourthLevelColumn).clearDataValidations();
}
else{
ws.getRange(r, secondLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, fourthLevelColumn).clearContent();
var filteredOptions = options.filter(function(o){ return o[0] === val });
var listToApply = filteredOptions.map(function (o){ return o[1] });
var cell = ws.getRange(r, secondLevelColumn);
applyValidationToCell(listToApply,cell);
}
}
function applySecondLevelValidation(val,r){
if(val === ""){
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
ws.getRange(r, fourthLevelColumn).clearContent();
ws.getRange(r, fourthLevelColumn).clearDataValidations();
} else{
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, fourthLevelColumn).clearContent();
var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
var filteredOptions = options.filter(function(o){ return o[0] === firstLevelColValue && o[1] === val });
var listToApply = filteredOptions.map(function (o){ return o[2] });
var cell = ws.getRange(r, thirdLevelColumn);
applyValidationToCell(listToApply,cell);
}
}
function applyThirdLevelValidation(val,r){
if(val === ""){
ws.getRange(r, fourthLevelColumn).clearContent();
ws.getRange(r, fourthLevelColumn).clearDataValidations();
} else{
ws.getRange(r, fourthLevelColumn).clearContent();
var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
var secondLevelColValue = ws.getRange(r, secondLevelColumn).getValue();
var filteredOptions = options.filter(function(o){ return o[0] === firstLevelColValue && o[1] === secondLevelColValue && o[2] === val });
var listToApply = filteredOptions.map(function (o){ return o[3] });
var cell = ws.getRange(r, fourthLevelColumn);
applyValidationToCell(listToApply,cell);
}
}
function applyValidationToCell(list,cell){
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)
.setAllowInvalid(false)
.build()
cell.setDataValidation(rule);
}
@@mohsinvlog4903 hi its not working..
@@kesavaram1161 Hi, could you please share your doc file? I could help.
This was so helpful. I was wondering if it is possible to create a dependent dropdown list from different tabs in the same WS and possibly from different WS? So that when you select a category in Column A, the list in Column B comes from a tab that matches the selection? I have 7 different tabs that can be pulled from that match the categories in Column A.
I wanna know too!
Thanks for this!
I love the video and how you explain everything but it didn't work with mine. Everything is like yours but I got an error for e.range and for .requireValueInList(list) (it says it cannot be null). Besides that, the list on second column doesn't take the options from the options sheet. Instead it gets a, b, g, which is applied only to the first cell, not to the others. I really don't know what's wrong.
Ya the e.range is messed up for me too
Same here
same here. Any solution u guys got for the e.range???
i have the same problem : TypeError: Cannot read properties of undefined (reading 'range')
Excellent, and thank you for these great classes.
I am anxiously looking after a solution for "Dependent" Dropdown list in Google Forms from a Google Sheets range data.
If you have any of these great tips about It, I'd apreciate, and hopefuly it could help others as well.
Me too. Any update on this topic pls?
Any ideas of how to get drop downs updated either manually or automatically when adding something to data sheet…without deleting data in a cells and entering again..i mean maybe to add a button refresh or update for convinience. Just guessing it would need to act like….remembering data first, then doing cleancontent() then adding remembered data? However there is a downside that google sheets doesnt have an access to a clipboard. So any ideas to this? Thanks
Hi,
Thanks for this great tutorial! I managed to get 7 columns working.
But now that I'm using it, it's a little laggy. Is there a way to optimize the script to be faster filling the columns? Or there's another method for creating the script?
Thanks for the help.
Regards.
hi i couldn't prepare more than 3 columns, its been not showing up, can you ple mail me your script.. Thank you
can you share the script? hi i couldn't prepare more than 3 columns
Hi I am also stuck at 3 columns. please share the script for additional columns. thankyou
@@protailreports4085 I think we just have to make some modification. Not to hard to do it with that's given script.
Thank you so much for videos!
You saved me, thanks!!!
This whole sheet can apply to Google form? Coz I need more than 2 column depending
Hi @sathia Narayanan. Did you find a similar script for Google Form pls?
@@skiglu hi coudl you find it?
could you find it out? i need to do the same thing
Codes pretty much the same. Just add a secondlevelcolvalue and so on and so forth
var secondLevelColValue = ws.getRange(r, secondLevelColumn).getValue();
var filteredOptions = options.filter(function(o){return o[0] === firstLevelColValue && o[1] === secondLevelColValue && o[2] === val});
var listToApply = filteredOptions.map(function(o){ return o[3]});
Don't forget to increase the column size of your options as well:
var options = wsOptions.getRange(2,1, wsOptions.getLastRow()-1, 6).getValues();
First of all, thank you very much, your lessons are great!
But, I have one question. I did everything the same as in your video, and dependent drop down list work correctly, except one cell (C6). I don't know why, but when I'm trying to chose one of the variants in third column (C) in six row there is an error. And it doesn't depend on what variants I choose in first (A) and second (B) column. I can't find any mistakes in code and that's why I'm asking you to help. If it's needed I could share my sheet.
Here is my code:
var mainWsName = "List";
var optionsWsName = "DB2";
var firstLevelColumn = 1;
var secondLevelColumn = 2;
var thirdLevelColumn = 3;
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);
var options = wsOptions.getRange(1, 1, wsOptions.getLastRow(),3).getValues();
function onEdit(e) {
var activeCell = e.range;
var val = activeCell.getValue();
var r = activeCell.getRow();
var c = activeCell.getColumn();
var wsName = activeCell.getSheet().getName();
if (wsName == mainWsName && c === firstLevelColumn && r > 1) {
applyFirstLevelValidation(val,r );
} else if (wsName === mainWsName && c === secondLevelColumn && r > 1) {
applySecondLevelValidation(val,r );
}
} //end onEdit
function applyFirstLevelValidation(val,r ) {
if (val === "") {
ws.getRange(r, secondLevelColumn).clearContent();
ws.getRange(r, secondLevelColumn).clearDataValidations();
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
} else {
ws.getRange(r, secondLevelColumn).clearContent();
ws.getRange(r, secondLevelColumn).clearDataValidations();
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
var filteredOptions = options.filter(function(o) { return o[0] === val });
var listToApply = filteredOptions.map(function(o) { return o[1] });
var cell = ws.getRange(r, secondLevelColumn);
applyValidationToCell(listToApply, cell);
}
} //end FirstLevel
function applySecondLevelValidation(val,r ) {
if (val === "") {
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
} else {
ws.getRange(r, thirdLevelColumn).clearContent();
var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
var filteredOptions = options.filter(function(o) { return o[0] === firstLevelColValue && o[1] === val });
var listToApply = filteredOptions.map(function(o) { return o[2] });
var cell = ws.getRange(r, thirdLevelColumn);
applyValidationToCell(listToApply, cell);
}
} //end SecondLevel
function applyValidationToCell(list, cell) {
var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).setAllowInvalid(false).build();
cell.setDataValidation(rule);
}
No clue. Maybe you have another script running on that cell?
Hi! this video, along with your other videos is so useful! Was able to use it for our inventory. But how can I apply the script to 2 tabs in the same workbook? I was able to apply it in the tab where I input all the "ins" of my inventory, but just wanna know how to also apply it to the "outs" tab of my inventory?
This is great! Do you also know if there is a way to create a project list where you enter in a start date or series of dates and it fills in another cell with color in a calendar view?
Nice tricks. I am interested with how to lock previous drop-down (let's name it Level 2) if the next dependent drop-down (Level 3) isn't blank. You can only change the Level 2 IF Level 3 is equal to blank. This will ensure one to accidentally change the Level 2 before clearing the next Level 3.
hi
Thank you so much This topic has helped me a lot in the field of work
If I have several identical entries in the first column in the master sheet and then copy the values down, the app script no longer works because I am no longer editing one active cell but rather several at once. How can I work around the problem? What would be the alternative here?
Many thanks
Great job with the presentation