I am trying to make the below code available for all worksheet in Google sheet, but can not find a way: (I want to have multiplework sheet for May,june july and so on..)

var mainwsname = "APRIL 21";

var optionwsname = "DataBase1";

var firstlevelcoloumn = 4;
var secondlevelcoloumn = 5;

var thirdlevelcoloumn = 6;



var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainwsname);

var wsDatabase = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionwsname);

var options = wsDatabase.getRange(2,1,wsDatabase.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===firstlevelcoloumn && r>20){ 
    applyfirstlevelvalidation(val,r);
  } 
  else if(wsName === mainwsname && c===secondlevelcoloumn && r>20) {
      applysecondlevelvalidation(val,r);
  }



}// close onedit function

function applyfirstlevelvalidation(val,r){
  if(val === ""){
     ws.getRange(r,secondlevelcoloumn).clearContent();
     ws.getRange(r,secondlevelcoloumn).clearDataValidations();
   } else{
      ws.getRange(r,secondlevelcoloumn).clearContent();
      var filteredoptions = options.filter(function(o){return o[0]===val});
      var listtoapply = filteredoptions.map(function(o){return o[1]});
      var cell = ws.getRange(r,secondlevelcoloumn);
      applyvalidationtocell(listtoapply,cell);
   }
} 

function applysecondlevelvalidation(val,r){
  if(val === ""){
     ws.getRange(r,thirdlevelcoloumn).clearContent();
     ws.getRange(r,thirdlevelcoloumn).clearDataValidations();
   } else{
      ws.getRange(r,thirdlevelcoloumn).clearContent();
      var firstlevelcolvalue = ws.getRange(r,firstlevelcoloumn).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,thirdlevelcoloumn);
      applyvalidationtocell(listtoapply,cell);
   }
} 

function applysecondlevelvalidation(val,r){
  if(val == ""){

    ws.getRange(r,thirdlevelcoloumn).clearContent();
    ws.getRange(r,thirdlevelcoloumn).clearDataValidations();
  } else {
    ws.getRange(r,thirdlevelcoloumn).clearContent();
    var firstLevelColValue = ws.getRange(r,firstlevelcoloumn).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,thirdlevelcoloumn);
    applyvalidationtocell(listToApply,cell);
  }
}



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


 cell.setDataValidation(rule);

}

I have declared the current sheet name as APRIL 21 as the Main worksheet variable. Please help me to declare multiple worksheets (such as MAY, JUNE, and so on). I may include a later month in the future.

CodePudding user response：

Try this:

var optionwsname = "DataBase1";
var firstlevelcoloumn = 4;
var secondlevelcoloumn = 5;
var thirdlevelcoloumn = 6;

var ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var wsDatabase = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionwsname);
var options = wsDatabase.getRange(2,1,wsDatabase.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 ( c===firstlevelcoloumn && r>20){ 
    applyfirstlevelvalidation(val,r);
  } 
  else if( c===secondlevelcoloumn && r>20) {
      applysecondlevelvalidation(val,r);
  }

}// close onedit function

function applyfirstlevelvalidation(val,r){
  if(val === ""){
     ws.getRange(r,secondlevelcoloumn).clearContent();
     ws.getRange(r,secondlevelcoloumn).clearDataValidations();
   } else{
      ws.getRange(r,secondlevelcoloumn).clearContent();
      var filteredoptions = options.filter(function(o){return o[0]===val});
      var listtoapply = filteredoptions.map(function(o){return o[1]});
      var cell = ws.getRange(r,secondlevelcoloumn);
      applyvalidationtocell(listtoapply,cell);
   }
} 

function applysecondlevelvalidation(val,r){
  if(val === ""){
     ws.getRange(r,thirdlevelcoloumn).clearContent();
     ws.getRange(r,thirdlevelcoloumn).clearDataValidations();
   } else{
      ws.getRange(r,thirdlevelcoloumn).clearContent();
      var firstlevelcolvalue = ws.getRange(r,firstlevelcoloumn).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,thirdlevelcoloumn);
      applyvalidationtocell(listtoapply,cell);
   }
} 

function applysecondlevelvalidation(val,r){
  if(val == ""){

    ws.getRange(r,thirdlevelcoloumn).clearContent();
    ws.getRange(r,thirdlevelcoloumn).clearDataValidations();
  } else {
    ws.getRange(r,thirdlevelcoloumn).clearContent();
    var firstLevelColValue = ws.getRange(r,firstlevelcoloumn).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,thirdlevelcoloumn);
    applyvalidationtocell(listToApply,cell);
  }
}

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


 cell.setDataValidation(rule);

}

UPDATES:

1. Removed var mainwsname = "APRIL 21"; and changed var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainwsname); to var ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); since getActiveSheet(); reference the current sheet that you are working on so that the function runs on all sheets.

2. Removed wsName === mainwsname on your if-else statements since we won't be needing this condition anymore to run the script.