Google-sheets – How to code 3 series dependent drop down on multiple sheets

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I have a code to run a 3 tier dependent drop down list – it currently works for one sheet in my workbook named "BlankNode" but I need it to work for ~20 different sheets. I tried adding a script for each sheet but only the last one I edit will work. I think I need a loop function for a range of sheets but that doesn't seem to be working. Am I able to get the below code to run on multiple sheets?

var mainWsName = "BlankNode";
var optionsWsName = "options"
var firstLevelColumn = 2;
var secondLevelColumn = 3;
var thirdLevelColumn = 4;


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(activeCell){

  var activeCell = ws.getActiveCell();
  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,thirdLevelColumn).clearContent();
      ws.getRange(r,secondLevelColumn).clearDataValidations();
      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);
}

Best Answer

Try using a regular expression to match sheet names, like this:

const mainWsName = /^(BlankNode|Sheet2|Sheet3)$/i;
// ...
const ws = SpreadsheetApp.getActiveSheet();
// ... in function onEdit(activeCell)
    if (wsName.match(mainWsName) && c === firstLevelColumn && r > 1) {

...or use this script.