CODEWITHSUNDEEP
javascriptgoogle-apps-script
Marcino123
Marcino123

Reputation: 11

Combine two onEdit() functions in google app script

I have a two simple scripts two make a dynamic dependent drop-down lists based on two different data sources in google sheet. In my sheet value in column B depends on the A, and value in column I depends on H. If I run one script, second doesn't work. I know I have to combine them into one function but I am totally begginer in JavaScript so thats why I ask you. 

function onEdit(){
  var tabLists = "source1";
  var tabValidation = "main";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);
  
  var activeCell = ss.getActiveCell();
  
  if(activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){
    
    activeCell.offset(0, 1).clearContent().clearDataValidations();
    
    var makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
    
    var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1;
    
    if(makeIndex != 0){
    
        var validationRange = datass.getRange(3, makeIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
  
     }  
      
  }
 
}

function onEdit(){
  var tabLists = "source2";
  var tabValidation = "MAINTENANCE";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);
  
  var activeCell = ss.getActiveCell();
  
  if(activeCell.getColumn() == 8 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){
    
    activeCell.offset(0, 1).clearContent().clearDataValidations();
    
    var makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
    
    var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1;
    
    if(makeIndex != 0){
    
        var validationRange = datass.getRange(3, makeIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
  
     }  
      
  }
  
}

I have tried to combine them into one If statement with more variables like "tablist1", "tablists2", "datass1", "datass2" etc. Im so consufed so I would be grateful If you help me.

Regards

Upvotes: 0

Views: 54

Answers (1)

Cooper
Cooper

Reputation: 64032

Try this:

function onEdit(e){
  var sh=e.range.getSheet();
  if(e.range.columnStart==1 && e.range.rowStart>1 && sh.getName()=='main'){
    var srcsh=e.source.getSheetByName('source1');
    e.range.offset(0,1).clearContent().clearDataValidations();
    var makes=srcsh.getRange(1, 1, 1, srcsh.getLastColumn()).getValues();
    var makeIndex=makes[0].indexOf(e.value)+1;
    if(makeIndex!=0){
      var validationRange=srcsh.getRange(3, makeIndex,srcsh.getLastRow());
      var validationRule=SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
      e.range.offset(0,1).setDataValidation(validationRule);
    }  
  }
  if(e.range.columnStart==8 && e.range.rowstart>1 && sh.getName()=='MAINTENANCE'){
    var srcsh=e.source.getSheetByName('source2');
    e.range.offset(0,1).clearContent().clearDataValidations();
    var makes=srcsh.getRange(1,1,1,srcsh.getLastColumn()).getValues();
    var makeIndex=makes[0].indexOf(e.value()) + 1;
    if(makeIndex!=0){
      var validationRange=srcsh.getRange(3,makeIndex,srcsh.getLastRow());
      var validationRule=SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
      e.range.offset(0, 1).setDataValidation(validationRule);
    }  
  }
}

I'm not sure a simple trigger will work here because you are changing user data. So you my require an installable trigger, in which case you won't want to name it onEdit any longer because they will get triggered twice. Once by the simple trigger and once by the installable trigger.

Upvotes: 1

Related Questions