I found this script that creates dependent drop down lists on a sheet (mainWsName = "Sheet1") based on a reference table that's on another sheet (optionsWsName = "ReferenceData") where firstLevelColumn is the first drop down list on Sheet1, secondLevelColumn drop down list is created when the value is selected on firstLevelColumn on Sheet1, and finally thirdLevelColumn drop down list is created when the value is selected on secondLevelColumn on Sheet1

This script works on one sheet but doesn't work on multiple sheets.

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

var mainWsName = "Sheet1";
var optionsWsName = "ReferenceData";
var firstLevelColumn = 3;
var secondLevelColumn = 4;
var thirdLevelColumn = 5;

var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);
var options = wsOptions.getRange(2, 1,wsOptions.getLastRow()-1,3).getValues();

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

I tried getting rid of mainWsName, moving it to a separate function and calling it inside onEdit but to no avail. I want this function to work on Sheet2, Sheet3, Sheet4, etc. as well.

CodePudding user response：

To make the code only do its thing on Sheet1, Sheet2 and Sheet3, use String.match(), like this:

  if (!wsName.match(/^(Sheet1|Sheet2|Sheet3)$/i)) {
    return;
  }

See the dependentDropDownLists_ script for sample code.

CodePudding user response：

Add this to work on multiple sheets

function onEdit(e) {
  const sh = e.range.getSheet();
  const shts = ["Sheet1","Sheet2","Sheet3"];
  const idx = shts.indexOf(sh.getName());
  if(~idx) {
    //place code for all three sheets here
  }
}