//23:40 https://www.youtube.com/watch?v=s-I8Z4nTDak&t=670s
//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);
}
