CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Sanli
Sanli

Reputation: 77

Combine two onEdit functions, each of them working for its own sheets using its own data

I have two codes. Each of them gives me three drop-down lists depending on each other and works for different sheets. Each code uses its separate list of data.

How can I combine these two scripts, each of them working for 2 different pages? (in total there are 4 pages with three drop down lists)

First function:

var mainWsName1 = "PERSONELextra";   
var mainWsName2 = "ISKUR";                                                    
var optionsWsName = "Lists";   

var firstLevelColumn = 12;                               
var secondLevelColumn = 13;                              
var thirdLevelColumn = 14; 

var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);                                

   function onEdit(e){   

  var activeCell = e.range;                               
  var val = activeCell.getValue();                                
  var r = activeCell.getRow();                                
  var c = activeCell.getColumn();                             
  var wsName = activeCell.getSheet().getName();
  // compare the edited sheet name against multiple allowed sheet anmes with ||                               
  if(wsName == mainWsName1 || wsName == mainWsName2 && r > 4){
    if(c == firstLevelColumn){                               
      applyFirstLevelValidation(val,r, wsName);                               
    } else if(c == secondLevelColumn){                               
      applySecondLevelValidation(val,r, wsName);                              
    }  
  }                             
} 

function applyFirstLevelValidation(val,r,wsName){
  //  obtain the sheet name dynamically from the onEdit() function  
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsName);                            
  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 = wsOptions.getDataRange().getValues().filter(function(o){ return o[0] == val });  // <--- Modified        
    var listToApply = filteredOptions.map(function(o){ return o[1] });                              
    var cell = ws.getRange(r, secondLevelColumn);                               
    applyValidationToCell(listToApply,cell);                                
  }                               
}                               

function applySecondLevelValidation(val,r, wsName){   
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsName);                            
  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 = wsOptions.getDataRange().getValues().filter(function(o){ return o[0] === firstLevelColValue && o[1] == val });  // <--- Modified
var listToApply = filteredOptions.map(function(o){ return o[2] });
var cell = ws.getRange(r, thirdLevelColumn);  // <--- Modified
applyValidationToCell(listToApply,cell);
}
}

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

cell.setDataValidation(rule)
}

And here is the second function for the other two sheets (and another data sheet):

var mainWsName1 = "GİDER";                                                       
var optionsWsName = "Lists2";   

var firstLevelColumn = 13;                               
var secondLevelColumn = 14;                              
var thirdLevelColumn = 15; 

var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);                                

function onEdit(e){                             
  var activeCell = e.range;                               
  var val = activeCell.getValue();                                
  var r = activeCell.getRow();                                
  var c = activeCell.getColumn();                             
  var wsName = activeCell.getSheet().getName();
  // compare the edited sheet name against multiple allowed sheet anmes with ||                               
  if(wsName == mainWsName1 || wsName == mainWsName2 && r > 4){
    if(c == firstLevelColumn){                               
      applyFirstLevelValidation(val,r, wsName);                               
    } else if(c == secondLevelColumn){                               
      applySecondLevelValidation(val,r, wsName);                              
    }  
  }                             
} 

function applyFirstLevelValidation(val,r,wsName){
  //  obtain the sheet name dynamically from the onEdit() function  
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsName);                            
  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 = wsOptions.getDataRange().getValues().filter(function(o){ return o[0] == val });  // <--- Modified        
    var listToApply = filteredOptions.map(function(o){ return o[1] });                              
    var cell = ws.getRange(r, secondLevelColumn);                               
    applyValidationToCell(listToApply,cell);                                
  }                               
}                               

function applySecondLevelValidation(val,r, wsName){   
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsName);                            
  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 = wsOptions.getDataRange().getValues().filter(function(o){ return o[0] === firstLevelColValue && o[1] == val });  // <--- Modified
var listToApply = filteredOptions.map(function(o){ return o[2] });
var cell = ws.getRange(r, thirdLevelColumn);  // <--- Modified
applyValidationToCell(listToApply,cell);
}
}

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

cell.setDataValidation(rule)
}

Upvotes: 0

Views: 72

Answers (1)

Marios
Marios

Reputation: 27348

Explanation:

  • Rename the two onEdit functions to onEdit1 and onEdit2. These names are up to you, but you can't have two onEdit functions.

  • Put all the global variables you have outside of the onEdit1 and onEdit2 functions inside each function.

  • You don't need to redefine the helper functions multiple times but you need them only once and they will work for both onEdit1 and onEdit2.

  • Finally, create an onEdit that will execute both onEdit1 and onEdit2.

Solution:

function onEdit(e){ 
  onEdit1(e);
  onEdit2(e);
 }

 function onEdit1(e){   
  var mainWsName1 = "A1";   
  var mainWsName2 = "A2";                                                    
  var optionsWsName = "listforA";   
  var firstLevelColumn = 12;                               
  var secondLevelColumn = 13;                              
  var thirdLevelColumn = 14; 
  var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);           

  var activeCell = e.range;                               
  var val = activeCell.getValue();                                
  var r = activeCell.getRow();                                
  var c = activeCell.getColumn();                             
  var wsName = activeCell.getSheet().getName();
  // compare the edited sheet name against multiple allowed sheet anmes with ||                               
  if(wsName == mainWsName1 || wsName == mainWsName2 && r > 4){
    if(c == firstLevelColumn){                               
      applyFirstLevelValidation(val,r, wsName,secondLevelColumn,thirdLevelColumn,wsOptions)                               
    } else if(c == secondLevelColumn){                               
      applySecondLevelValidation(val,r, wsName,firstLevelColumn,thirdLevelColumn,wsOptions)                              
    }  
  }                             
} 
                          
function onEdit2(e){     
  var mainWsName1 = "B1";
  var mainWsName2 = "B2";                                                        
  var optionsWsName = "listforB";   
  var firstLevelColumn = 13;                               
  var secondLevelColumn = 14;                              
  var thirdLevelColumn = 15; 
  var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);      
  var activeCell = e.range;                               
  var val = activeCell.getValue();                                
  var r = activeCell.getRow();                                
  var c = activeCell.getColumn();                             
  var wsName = activeCell.getSheet().getName();
  // compare the edited sheet name against multiple allowed sheet anmes with ||                               
  if(wsName == mainWsName1 || wsName == mainWsName2 && r > 4){
    if(c == firstLevelColumn){                               
      applyFirstLevelValidation(val,r, wsName,secondLevelColumn,thirdLevelColumn,wsOptions);                               
    } else if(c == secondLevelColumn){                               
      applySecondLevelValidation(val,r, wsName,firstLevelColumn,thirdLevelColumn,wsOptions);                              
    }  
  }                             
} 

function applyFirstLevelValidation(val,r,wsName,secondLevelColumn,thirdLevelColumn,wsOptions){
  //  obtain the sheet name dynamically from the onEdit() function  
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsName);                            
  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 = wsOptions.getDataRange().getValues().filter(function(o){ return o[0] == val });  // <--- Modified        
    var listToApply = filteredOptions.map(function(o){ return o[1] });                              
    var cell = ws.getRange(r, secondLevelColumn);                               
    applyValidationToCell(listToApply,cell);                                
  }                               
}                               

function applySecondLevelValidation(val,r, wsName,firstLevelColumn,thirdLevelColumn,wsOptions){   
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsName);                            
  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 = wsOptions.getDataRange().getValues().filter(function(o){ return o[0] === firstLevelColValue && o[1] == val });  // <--- Modified
var listToApply = filteredOptions.map(function(o){ return o[2] });
var cell = ws.getRange(r, thirdLevelColumn);  // <--- Modified
applyValidationToCell(listToApply,cell);
}
}

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

cell.setDataValidation(rule)
}

Upvotes: 3

Related Questions