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. 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
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!
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
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!
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.
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
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 :)
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.
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, 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.
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!
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.
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.
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?
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.
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
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!
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:))
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.
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.
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?
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!
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.
Neat. I'm already taking it literally to next level of five columns. Is there any way to optimize the filtering? Should I build a combination table and cache it? Is this the way?
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!!
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
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 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.
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
@@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();
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?
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?
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!
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! Thank you so much for taking the time to make this video. I'm having a little bit of trouble at 31:15 where your filters apply into master. The main difference between your master and mine is that your drop down options are located in the rows(2+) and columns(1-3) as options. For me, my options are in the columns(1-3) but for my master, I want the drop downs to appear from E4 to G13. I cant seem to figure out how to get it to that location. I'm not sure if my explanation makes sense, if you need clarification, please ask. Thanks For example, yours says var cell = ws.getRange("C4"); but mine says var cell = ws.getRange("G4"); and yours says if(wsName == "master" && c === 1 && r >1) mine says if(wsName == "master" && c === 5 && r > 3)
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 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?
Hi, this is just great! but I have a doubt! Is this sheet usefull to be linked to a Google Form too? so that you can use dependent dropdown list en forms?
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();
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.
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?
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.
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?
Hello, do you have any advice how could I set the second dropdown list to be Distinct values? In other words, how can I remove a used item from the drop-down list if the item was already selected in the column? Thank you
How can I run this script on 2 seperate sheets? trying to have the same dependent drop down data validations for IN's/OUT's tabs/sheets for inventory management..
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 th-cam.com/video/frAL7bJkU54/w-d-xo.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.
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 !
Thank you very much for this tutorial. helped me a lot and was very well presented! Would there be an easy way to pass text to the last column vs a drop down? For the way I'm trying to use it, I only have one option after selecting columns 1 and 2, the third column is a number which allows me to sum a list. Thanks for any help you can provide.
My code is the same as what you presented, yet I'm wondering if for the applySecondLevelValidation there could be another function it points to, to pass a numeric value off to the third column vs a drop down list. I can send screenshots of my data if that would help yet I see no way to do that here in youtube. But overall, I pick an option in column 1, which narrows the selection to only relevant options in column 2, which gives me a number in column 3 which represents "lead time" for that job. I'll have multiple lines of selections for various processes that allows me to sum the total lead time for a job.
Ok is there a way to get the 2nd row with the dropdown validations to show up and change if my 1st column is auto populating based on a filtered list from another sheet instead of from a dropdown in the 1st column? Originally I had issues because my 1st row was populating based on HLOOKUP formulas in the cells, but the script acted as if the cell never changed so the script wasn't working correctly ( I assume because the script could "see" the formula in the cell and it never changed). Now I noticed that the script only recognizes a change is a row where the "paste" started and all the rows below it are not recognized by the script.
Awesome Video. Is there a way to have a default option in columns 2 and 3? i.e The most common option(s) will appear in the dropdown without having to select.
Great video and explained in detail. I still have question about one of the thing if that is possible using google sheet or not. Lets say there is Western region dropdown and you have selected it for 3 times. If you want to select it for 4th time, it won't be available for selection. Can we hide dropdown list value after selected for specific number of time?
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
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()
Thank you very much. This type of dropdown, but in location form, is what I'm looking for. In the Philippines, there are numerous barangays, cities, and provinces. Do you have a trick that will connect this to this country? I'd like to create a project for this as a registration form for my clans.
Great video - very helpful. Thank you @LearnGoogleSpreadsheets. I constantly get an e.range error - is there a reason for it? Also is it possible to use this for multiple output sheets with different names, connected to the same database sheet?
@@ExcelGoogleSheets Worked - I scrolled down the comments and saw your reply on not to run the function in the editor window. A quick question - how would you write this script for multiple master sheets (eg. master 1 (Category A), master 2 (Category B)..)? I have currently pasted the same script multiple times and changed the sheet name, however dropdowns don't show up.
Great video! Thanks a lot, worked like a charm! However I wanted to ask for additional help/tips. In the master sheet, my first column (Region) won't be inputted manually but rather by a formula. When I try this it only shows the data validation options to the first cell where the formula was inputted. Is there a way to have the validation not associated with the active cell but with all values within that column? Thanks a lot for your help!!
@@ExcelGoogleSheets Apologies, not sure I understood (total newbie). In my case I have a couple thousand lines and the information in master sheet, (column 1 & 2) might already be known for some of those cells so it would be a waste of time to manually input them all. This is the issue I'm trying to address. From what I understood I would need to assign the OnEdit to a certain event that would trigger the formulas (if function in this case) to update. Is there a specific type of event that would let me do it? Would I have to create like a button or something? Again, apologies for the lack of knowledge. It's just that I'm applying this for a couple of countries and a couple of thousand lines per each county and it would be a more than one off exercise so all help is welcome! Once again, thanks so much!
@@ExcelGoogleSheets Thanks for the help, it works to do it manually. Not to push my luck here but is there any easy way to apply this to more than one sheet in the same spreadsheet? I'm having a hard time finding this out by looking online
![[Part 1] ทารกน้อยถูกทิ้ง👶 หญิงชราเก็บขวดขายเลี้ยงดูเขาจนโต #โลกการละคร](http://i.ytimg.com/vi/nPTDvFCExks/mqdefault.jpg)
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
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!
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
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!
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.
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
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.
I have extremely NO experience or knowledge about programming. But this video helped a LOT! Thank you kind sir.
😀
Difficult to deny that coding is a great skill for most jobs right now
the best video that i could find. thank you!
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?
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.
Used some techinques from this + the dynamic drop down video to build what I needed to. Thank you!
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, 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.
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!
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.
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.
Thank you so much! This is the best channel for learning google sheets with appscript! Keep going!
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?
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.
:)
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
Thank you so much! The video is so well explained and amazingly useful! Great job!
Thank you so much for teaching all of this and so patiently!
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!
This is what i've been looking for , thanks man , keep up the good work !
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:))
This was super useful - just the right level and speed - learnt much more than I was looking for. Thank you Sir.
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!
If I love you? What is love anyway, but it was most certainly very helpful. Thank you!
Amazing! Helped a lot! Thank you very much!
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.
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.
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?
Suppprrbbbb... Completed my database management system because of you thankyou soo much ❤️❤️❤️❤️❤️❤️
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!
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.
Thanks a lot. Had to modify some stuff to fit my purpose, but you are so clear. You are excellent!
Great content, did fix my problem after a few adjustments from the example in the video, good job!
Another useful video! Thanks @Learn Google Spreadsheets!
What if I want to use the code for many sheets?
If it was possible to double my like, I would have. Thank you because it is been a while I was looking for this
Thank you a lot for the video. I just couldn't figure out how I would apply it to two or more sheets.
Neat. I'm already taking it literally to next level of five columns. Is there any way to optimize the filtering? Should I build a combination table and cache it? Is this the way?
Not sure, but sounds interesting. Maybe it's worth trying caching service and see if that improves the speed, because it's usually annoyingly slow.
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!!
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
Very well done. This was very thorough and helpful.
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
just exactly what i need factions improve in my Project! Thanks Bro!
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 :(
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);
};
Thank you very much for your great video! Helped me a lot!!!
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!
You are amazing. thank you for the video. saved my life
Man, you're the fucking master.
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?
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
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?
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!
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.
Hi! Thank you so much for taking the time to make this video. I'm having a little bit of trouble at 31:15 where your filters apply into master. The main difference between your master and mine is that your drop down options are located in the rows(2+) and columns(1-3) as options. For me, my options are in the columns(1-3) but for my master, I want the drop downs to appear from E4 to G13. I cant seem to figure out how to get it to that location. I'm not sure if my explanation makes sense, if you need clarification, please ask. Thanks
For example, yours says var cell = ws.getRange("C4");
but mine says var cell = ws.getRange("G4");
and yours says if(wsName == "master" && c === 1 && r >1)
mine says if(wsName == "master" && c === 5 && r > 3)
Thats your "on fire mode" (just feels like that) in this third ddvl video is a plus point Sir :)
This REALLY helped me out! Thanks so much!
Нашла то, что искала! Наконец-то! Большое спасибо!
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.
Super lesson, thank you, I will try to do this 👍🙏
Great!
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?
Hi, this is just great! but I have a doubt! Is this sheet usefull to be linked to a Google Form too? so that you can use dependent dropdown list en forms?
Thanks for your effort done, I have a question, how can add column for 4th DDL
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();
Very well demonstrated video! Thanks!
Is there a way to use the vlookup formula with this?
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!
I would absolutely love to see a video where this is optimized to work with any number of columns in the "options" sheet
Thank you for the tutorial
What can I do if I want the dropdown to auto-complete when only one option is available?
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?
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?
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?
Hello sir...
Great video 👍👍🙏🙏
Hello, do you have any advice how could I set the second dropdown list to be Distinct values? In other words, how can I remove a used item from the drop-down list if the item was already selected in the column?
Thank you
How can I run this script on 2 seperate sheets? trying to have the same dependent drop down data validations for IN's/OUT's tabs/sheets for inventory management..
I have the same concern.... Did you manage to get around?
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 th-cam.com/video/frAL7bJkU54/w-d-xo.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.
Do you have an updated script on this using a trigger? Thank you :)
Sir...would you be Agreeable to Update your Code to Work with the Chrome V8 Engine?
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?
Thank you very much for this tutorial. helped me a lot and was very well presented! Would there be an easy way to pass text to the last column vs a drop down? For the way I'm trying to use it, I only have one option after selecting columns 1 and 2, the third column is a number which allows me to sum a list. Thanks for any help you can provide.
Sure. What's your code?
My code is the same as what you presented, yet I'm wondering if for the applySecondLevelValidation there could be another function it points to, to pass a numeric value off to the third column vs a drop down list. I can send screenshots of my data if that would help yet I see no way to do that here in youtube. But overall, I pick an option in column 1, which narrows the selection to only relevant options in column 2, which gives me a number in column 3 which represents "lead time" for that job. I'll have multiple lines of selections for various processes that allows me to sum the total lead time for a job.
Ok is there a way to get the 2nd row with the dropdown validations to show up and change if my 1st column is auto populating based on a filtered list from another sheet instead of from a dropdown in the 1st column?
Originally I had issues because my 1st row was populating based on HLOOKUP formulas in the cells, but the script acted as if the cell never changed so the script wasn't working correctly ( I assume because the script could "see" the formula in the cell and it never changed).
Now I noticed that the script only recognizes a change is a row where the "paste" started and all the rows below it are not recognized by the script.
Great video, think I want to learn more now.
Awesome Video.
Is there a way to have a default option in columns 2 and 3?
i.e The most common option(s) will appear in the dropdown without having to select.
I am also looking for this default option.
Would it be possible to duplicate Column (A, B, C) dependent drop down list to Column (D, E, F) and so on with the same dependent drop down list?
Great video and explained in detail. I still have question about one of the thing if that is possible using google sheet or not. Lets say there is Western region dropdown and you have selected it for 3 times. If you want to select it for 4th time, it won't be available for selection. Can we hide dropdown list value after selected for specific number of time?
The short answer is yes, the long answer is, well, very long :)
@@ExcelGoogleSheets did you already covered it in any other video? Or can you help me how can I get that?
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
looks great ...do you have the code to download available?
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
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.
Such a great Video!
Thank you very much. This type of dropdown, but in location form, is what I'm looking for. In the Philippines, there are numerous barangays, cities, and provinces. Do you have a trick that will connect this to this country? I'd like to create a project for this as a registration form for my clans.
Great video - very helpful. Thank you @LearnGoogleSpreadsheets. I constantly get an e.range error - is there a reason for it? Also is it possible to use this for multiple output sheets with different names, connected to the same database sheet?
Well, are you running the function from the script editor window? If the answer is yes, then you should not be running it that way.
@@ExcelGoogleSheets Worked - I scrolled down the comments and saw your reply on not to run the function in the editor window. A quick question - how would you write this script for multiple master sheets (eg. master 1 (Category A), master 2 (Category B)..)? I have currently pasted the same script multiple times and changed the sheet name, however dropdowns don't show up.
@@jaydalal6806 hey do u find out the solution bro ? I have the same issue here
Hello and Good Day, How to run this scripts on multiple tabs or sheets with the same functions on both tabs or sheets.
Thanks.
did you figure it out?
i got it! thanks though
@@williamhuff100 How did you get it? I am trying to figure out the same.
Great video! Thanks a lot, worked like a charm!
However I wanted to ask for additional help/tips.
In the master sheet, my first column (Region) won't be inputted manually but rather by a formula. When I try this it only shows the data validation options to the first cell where the formula was inputted. Is there a way to have the validation not associated with the active cell but with all values within that column?
Thanks a lot for your help!!
onEdit will not get triggered by formulas. You'll need to assign it to cells that trigger those formulas to update.
@@ExcelGoogleSheets Apologies, not sure I understood (total newbie).
In my case I have a couple thousand lines and the information in master sheet, (column 1 & 2) might already be known for some of those cells so it would be a waste of time to manually input them all. This is the issue I'm trying to address.
From what I understood I would need to assign the OnEdit to a certain event that would trigger the formulas (if function in this case) to update. Is there a specific type of event that would let me do it? Would I have to create like a button or something?
Again, apologies for the lack of knowledge. It's just that I'm applying this for a couple of countries and a couple of thousand lines per each county and it would be a more than one off exercise so all help is welcome!
Once again, thanks so much!
There is no such event. Yea, maybe just add a button and run it manually.
@@ExcelGoogleSheets Thanks for the help, it works to do it manually.
Not to push my luck here but is there any easy way to apply this to more than one sheet in the same spreadsheet?
I'm having a hard time finding this out by looking online
@@fnbrites Yes, just modify the if statement that checks for the sheet name and column row positions and add some javascript or statements.