0

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.