running script over multiple sheets

Question

My programming knowledge is weak at best. I would like to be able to run the below script over a few of my sheets in google-sheets. The sheets names would be "Liquor Room", "Mezz" "Cooler", "Bar", "Events", "Knots". They all would pull from the "codes" sheet. Thank you to anyone who can help me and explain it to me in simpleton terms.

---

var mainWsName = "Liquor Room";
var optionsWsName = "codes";
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(2,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);
  }


}

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, secondLevelColumn).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);
  }

Answer 1

You can use a regular expression to match multiple sheet names. Change lines 1, 7, 20 and 22 as follows:

const mainWsName = /^(Liquor Room|Mezz|Cooler|Bar|Events|Knots)$/i;
const ws = SpreadsheetApp.getActiveSheet();
  if (wsName.match(mainWsName) && c === firstLevelColumn && r > 1) {
  } else if (wsName.match(mainWsName) && c === secondLevelColumn && r > 1) {

You are using an onEdit(e) trigger which means that the code will run every time any value in the spreadsheet is hand edited. The options global variable is initialized each time with Range.getValues() which hurts your spreadsheet performance. You may want to move the definitions of wsOptions and options to inside of applyFirstLevelValidation() and applySecondLevelValidation().

Some of the best resources for learning Google Apps Script include the Beginner's Guide, the New Apps Script Editor guide, the Fundamentals of Apps Script with Google Sheets codelab, the Extending Google Sheets page, javascript.info, Mozilla Developer Network and Apps Script at Stack Overflow.