I'm new to coding and need help with a GS script. I have a 5k row multiple-level dependent drop-down list.
I'm running into an error when I run the below script. It says TypeError: Cannot read property 'range' of undefined. My research says this is because the onEdit(e) object is triggered when the cell is edited, but why can't I run the code so that I can execute the trigger?
If anyone can help here, I'd be extremely grateful.
var mainWsName = "Input: Transactions";
var options2WsName = "Options2";
var fifthLevelColumn = 5;
var sixthLevelColumn = 6;
var seventhLevelColumn = 7;
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsOptions2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(options2WsName);
var options = wsOptions2.getRange(2,1,wsOptions2.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 === fifthLevelColumn && r > 7){
applyFirstLevelValidation(val,r)
} else if(wsName == mainWsName && c === sixthLevelColumn && r > 7){
applySecondLevelValidation(val,r)
}
} // end onEdit
function applyFirstLevelValidation(val,r){
if(val === ""){
ws.getRange(r, sixthLevelColumn).clearContent;
ws.getRange(r, sixthLevelColumn).clearDataValidations;
ws.getRange(r, seventhLevelColumn).clearContent;
ws.getRange(r, seventhLevelColumn).clearDataValidations
} else {
ws.getRange(r, sixthLevelColumn).clearContent
ws.getRange(r, sixthLevelColumn).clearDataValidations;
ws.getRange(r, seventhLevelColumn).clearContent;
ws.getRange(r, seventhLevelColumn).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, sixthLevelColumn);
applyValidationToCell(listToApply,cell)
}
}
function applySecondLevelValidation(val,r){
if(val === ""){
ws.getRange(r, seventhLevelColumn).clearContent;
ws.getRange(r, seventhLevelColumn).clearDataValidations;
} else {
ws.getRange(r, seventhLevelColumn).clearContent
var firstLevelColValue = ws.getRange(r, fifthLevelColumn).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, seventhLevelColumn);
applyValidationToCell(listToApply,cell)
}
}
function applyValidationToCell(list, cell){
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule);
}