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
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!!!!
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 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!
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!
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
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 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 :)
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.
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, 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.
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!
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
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.
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 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?
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!!
My notes - hope it helps. also how I made mistakes. //23:40 ua-cam.com/video/s-I8Z4nTDak/v-deo.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); }
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)
Hi! Following your guidelines but encounter an issue. Please guide me why in my AppScrip am I unable to add second "/" to close the IF function (//close IF) like you did it at 37:57. (if I write this part somewhere else and copy it in, the text becomes light gray and I am not able to make any other add any other symbols to the whole scrip.
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
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!
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?
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?
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.
Thank you 🙌 for this tutorial however I am stuck at 22:16 whenever I put the if statement under the onEdit function, it does not work anymore, any help will greatly appreciated. 😁😁
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.
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?
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
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:))
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!
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.
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.
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 ua-cam.com/video/frAL7bJkU54/v-deo.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.
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!
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....
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!!
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.
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.
Is there a way to create this script to mulitple sheets in one workbook. This script has been working great for one and now I am having to add additonal sheets for other staff to utilize in the same workbook. Can you update me with the write Script. Thanks
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?
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
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.
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()
Hi , i have a problem . My third Column Drop-down is of dates. But it is showing the dates in a different format like this "Tue Oct 13 2020 00:00:00 GMT+0530 (India Standard Time)". I would like it as just "13/10/2020". Can you please help.
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."
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?
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); }
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 🙏🏻
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, In my First level validation my drop down is a list of dates. but dates are showing in this format (Mon Apr 27 2020 00:00:00 GMT+0530 (India Standard Time)) is there any way to show this in just this (27/04/2020) format. since it is showing in the above mentioned format my Second level validation is not getting a match. Please help!
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
Hello, trying your script but at 11:16 mark, I am getting an error. It says myFunction @ Code.gs.6. Says parameters don't match the method signature. this is so confusing
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
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?
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
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!
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 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!
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!
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 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
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 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?
the best video that i could find. 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!
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!
This was super useful - just the right level and speed - learnt much more than I was looking for. Thank you Sir.
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.
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!
@ 46:26 the "var activeCell = e.range;" no longer makes sense
Thank you so much! The video is so well explained and amazingly useful! Great job!
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
Used some techinques from this + the dynamic drop down video to build what I needed to. Thank you!
Suppprrbbbb... Completed my database management system because of you thankyou soo much ❤️❤️❤️❤️❤️❤️
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.
Thank you so much for teaching all of this and so patiently!
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 is what i've been looking for , thanks man , keep up the good work !
Amazing! Helped a lot! Thank you very much!
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?
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!
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!!
My notes - hope it helps. also how I made mistakes.
//23:40 ua-cam.com/video/s-I8Z4nTDak/v-deo.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.
Нашла то, что искала! Наконец-то! Большое спасибо!
If I love you? What is love anyway, but it was most certainly very helpful. Thank you!
Very well done. This was very thorough and helpful.
If it was possible to double my like, I would have. Thank you because it is been a while I was looking for this
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)
Great content, did fix my problem after a few adjustments from the example in the video, good job!
Hi! Following your guidelines but encounter an issue.
Please guide me why in my AppScrip am I unable to add second "/" to close the IF function (//close IF) like you did it at 37:57. (if I write this part somewhere else and copy it in, the text becomes light gray and I am not able to make any other add any other symbols to the whole scrip.
// means comments. anything that follows it are simply notes for yourself, so you can skip those.
} is what will close the if function.
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 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
Thanks a lot. Had to modify some stuff to fit my purpose, but you are so clear. You are excellent!
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!
I'm having trouble on the e.range, here is the error message: TypeError: Cannot read property 'range' of undefined
Thank you a lot for the video. I just couldn't figure out how I would apply it to two or more sheets.
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?
You are amazing. thank you for the video. saved my life
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?
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.
Hello sir...
Great video 👍👍🙏🙏
Super lesson, thank you, I will try to do this 👍🙏
Great!
Thank you 🙌 for this tutorial however I am stuck at 22:16 whenever I put the if statement under the onEdit function, it does not work anymore, any help will greatly appreciated. 😁😁
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.
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?
just exactly what i need factions improve in my Project! Thanks Bro!
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
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:))
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!
Thank you very much for your great video! Helped me a lot!!!
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 :(
Thank you for the tutorial
What can I do if I want the dropdown to auto-complete when only one option is available?
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.
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 ua-cam.com/video/frAL7bJkU54/v-deo.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.
Man, you're the fucking master.
Thats your "on fire mode" (just feels like that) in this third ddvl video is a plus point Sir :)
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!
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....
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!!
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();
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 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
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.
Great video, think I want to learn more now.
Great job with the presentation
Is there a way to create this script to mulitple sheets in one workbook. This script has been working great for one and now I am having to add additonal sheets for other staff to utilize in the same workbook. Can you update me with the write Script. Thanks
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?
Such a great Video!
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
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')
Another useful video! Thanks @Learn Google Spreadsheets!
What if I want to use the code for many sheets?
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.
Hi , i have a problem . My third Column Drop-down is of dates. But it is showing the dates in a different format like this "Tue Oct 13 2020 00:00:00 GMT+0530 (India Standard Time)". I would like it as just "13/10/2020". Can you please help.
Do you have an updated script on this using a trigger? Thank you :)
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?
I would absolutely love to see a video where this is optimized to work with any number of columns in the "options" sheet
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?
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?
You saved me, thanks!!!
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 🙏🏻
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, In my First level validation my drop down is a list of dates. but dates are showing in this format (Mon Apr 27 2020 00:00:00 GMT+0530 (India Standard Time)) is there any way to show this in just this (27/04/2020) format. since it is showing in the above mentioned format my Second level validation is not getting a match. Please help!
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
This REALLY helped me out! Thanks so much!
You're a wizard !
:)
Hello, trying your script but at 11:16 mark, I am getting an error. It says myFunction @ Code.gs.6. Says parameters don't match the method signature. this is so confusing
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
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?