LaVOZs

The World’s Largest Online Community for Developers

'; google apps script - Trying to make a three tiered dependent dropdown work on multiple sheets using appscript - LavOzs.Com

I have been able to get my sheet to work seamlessly within one sheet but I need the ability to be able to use the script across multiple sheets in the same workbook, the problem I run into is without the isolation that is currently assigned it will edit every page with any changes. Does anyone have any ideas? Here is the code that i am using for the script:

var mainWSName = "Tracker";
var optionsWsName = "Reference";
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(12, 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)
}


}//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);
}

}



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);
}

}

function applyValidationToCell(list,cell) {

 var rule = SpreadsheetApp
 .newDataValidation()
 .requireValueInList(list)
 .setAllowInvalid(false)
 .build();

cell.setDataValidation(rule);
}
Related
Limiting geocoding calls in a Google Spreadsheet
Retrieve row from a table (with a specific value) and insert specific row to a new sheet
Google Sheets Script - Send to email in multiple cells on sheet
Searching a Google Form response (Google Sheets) for row and column numbers
Send an email when a specific cell is changed
Google Sheets Script: Combining onEdit with openById(“OtherSheetsId”)
onEdit() does not trigger if using VLOOKUP