My programming knowledge is weak at best. I would like to be able to run the below script over a few of my sheets in google-sheets. The sheets names would be "Liquor Room", "Mezz" "Cooler", "Bar", "Events", "Knots". They all would pull from the "codes" sheet. Thank you to anyone who can help me and explain it to me in simpleton terms.
var mainWsName = "Liquor Room";
var optionsWsName = "codes";
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(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(wsName === mainWsName && c === firstLevelColumn && r > 1){
applyFirstLevelValidation(val,r);
} else if(wsName === mainWsName && c === secondLevelColumn && r > 1){
applySecondLevelValidation(val,r);
}
}
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);
}
}
function applySecondLevelValidation(val,r){
if(val === ""){
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, secondLevelColumn).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);
}
}
function applyValidationToCell(list,cell){
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule);
}