Applying google script to all sheets except specific-CodePudding

I am very new to google script trying to resolve key issues in business flow. Through search have come up with the below script for multi-level data validation for sheet Template.

How can I edit this script to run for all Sheets except specific ones (ex. Sheet1, Sheet 2)

I have found the option of excluding sheets with the code below, but not sure how to use it and where to input it

var excludes = [];
  // if (excludes.indexOf(s.getName()) != -1) return;

Thanks you in advance

var mainwsMaster = "Template";
var MenuWSname = "Master Menu"
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainwsMaster);
var wsMenu = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(MenuWSname);
var Menu = wsMenu.getRange(2,1,wsMenu.getLastRow()-1,3).getValues();
var firstColumn = 8;
var secondColumn = 9;
var thirdColumn = 10;

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 === mainwsMaster && c === firstColumn && r > 6){ 
    applyFirstLevelValidation(val,r)
  } else if(wsName === mainwsMaster && c === secondColumn && r > 6){
    applySecondLevelValidation(val,r);
  }
}//end onEdit


function applyFirstLevelValidation(val,r){
    if(val === ""){
      ws.getRange(r,secondColumn).clearContent();
      ws.getRange(r,secondColumn).clearDataValidations();
      ws.getRange(r,thirdColumn).clearContent();
      ws.getRange(r,thirdColumn).clearDataValidations();
    }  else {
      ws.getRange(r,secondColumn).clearContent();
      ws.getRange(r,secondColumn).clearDataValidations();
      ws.getRange(r,thirdColumn).clearContent();
      ws.getRange(r,thirdColumn).clearDataValidations();
      var filteredOptions = Menu.filter(function(o){return o[0] === val });
      var filteredpiata = filteredOptions.map(function(o){return o[1] } );
      var cell = ws.getRange(r,secondColumn);
      applyValidationToCell(filteredpiata,cell);
    }
}


function applySecondLevelValidation(val,r){

    if(val === ""){
      ws.getRange(r,thirdColumn).clearContent();
      ws.getRange(r,thirdColumn).clearDataValidations();
    }  else {
      ws.getRange(r,thirdColumn).clearContent();
      var firstLevelColValue = ws.getRange(r, firstColumn).getValue();
      var filteredOptions = Menu.filter(function(o){return o[0] === firstLevelColValue && o[1] === val });
      var filteredpiata = filteredOptions.map(function(o){return o[2] } );
      var cell = ws.getRange(r,thirdColumn);
      applyValidationToCell(filteredpiata,cell);
    }
}


function applyValidationToCell(list,cell){
  
  var rule = SpreadsheetApp
  .newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build();

  cell.setDataValidation(rule);
}

CodePudding user response：

You mentioned this code:

var excludes = [];
  // if (excludes.indexOf(s.getName()) != -1) return;

for your code it would look like this instead:

var excludes = ["Sheet1","Sheet2","OtherSheetToExclude","ETC"];
if (excludes.indexOf(wsName) != -1) {return};

You would place those two lines just after the wsName variable is declared in your onEdit(e) script.

It's basically saying. "Take the name of the sheet you've just edited and look for it in this list i just made called "Excludes". If you find it in there, STOP running this function."

CodePudding user response：

Try

var excludes = ['Sheet1','Sheet2'];

and then, change as following

if( excludes.indexOf(wsName) == -1 && c === firstColumn && r > 6)