CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
McChief
McChief

Reputation: 425

Grouping multiple IF OR statements

Cooper wrote the following onEdit script to sort columns when the column header name is selected from a dropdown list:

//Dropdown List sort Events/Incidents
 function sortByColumnEventsIncidents(e) {
  const sh=e.range.getSheet();
  if(sh.getName()=='Events/Incidents' && e.range.columnStart==5 && e.range.rowStart==1 && e.value) {
    const headerArray=sh.getRange(2,1,1,sh.getLastColumn()).getValues()[0];
    const col={};
    headerArray.forEach(function(h,i){col[h]=i+1;});
    const rg=sh.getRange(3,1,sh.getLastRow()-2,sh.getLastColumn());
    rg.sort({column:col[e.value],ascending:false});
    sh.getRange('A3').activate();
  }
}

This works perfectly but I now want to change the ascending true/false criteria depending on the column header name selected from the dropdown list. I, therefore, modified the script with the intention that when either "Branch", "Reported by" or "Employee" are selected from the dropdown list, the selected column will sort ascending but if any of the other header names are selected I want those to sort descending. This is my modified script but I can't get it to function:

//Dropdown List sort Events/Incidents
 function sortByColumnEventsIncidents(e) {
  const sh=e.range.getSheet();
  if(sh.getName()=='Events/Incidents' && e.range.columnStart==5 && e.range.rowStart==1 && e.value=='Employee' || sh.getName()=='Events/Incidents' && e.range.columnStart==5 && e.range.rowStart==1 && e.value=='Reported by' || sh.getName()=='Events/Incidents' && e.range.columnStart==5 && e.range.rowStart==1 && e.value=='Branch') {
    const headerArray=sh.getRange(2,1,1,sh.getLastColumn()).getValues()[0]; //Location of Header row
    const col={};
    headerArray.forEach(function(h,i){col[h]=i+1;});
    const rg=sh.getRange(3,1,sh.getLastRow()-2,sh.getLastColumn()); //Range to sort
    rg.sort({column:col[e.value],ascending:true});
    sh.getRange('A3').activate();
  }
  if(sh.getName()=='Events/Incidents' && e.range.columnStart==5 && e.range.rowStart==1 && e.value!=='Employee' || sh.getName()=='Events/Incidents' && e.range.columnStart==5 && e.range.rowStart==1 && e.value!=='Reported by' || sh.getName()=='Events/Incidents' && e.range.columnStart==5 && e.range.rowStart==1 && e.value!=='Branch') { //Location of Dropdown = E1
  const headerArray=sh.getRange(2,1,1,sh.getLastColumn()).getValues()[0]; //Location of Header row
    const col={};
    headerArray.forEach(function(h,i){col[h]=i+1;});
    const rg=sh.getRange(3,1,sh.getLastRow()-2,sh.getLastColumn()); //Range to sort
    rg.sort({column:col[e.value],ascending:false});
  }
}

In other sheets, I have been able to modify the code and get it functioning where I have set one criteria e.g. if equals "Branch" then sort ascending and if does not equal "Branch" then sort descending but I cannot get it to work when I set multiple criteria.

I would appreciate some help getting this to work.

Upvotes: 0

Views: 61

Answers (1)

Cooper
Cooper

Reputation: 64062

Try this:

function sortByColumnEventsIncidents(e) {
  const sh=e.range.getSheet();
  const sA=["Employee","Report by","Branch"];
  if(sh.getName()=='Events/Incidents' && e.range.columnStart==5 && e.range.rowStart==1) {
    const headerArray=sh.getRange(2,1,1,sh.getLastColumn()).getValues()[0];
    const col={};
    headerArray.forEach(function(h,i){col[h]=i+1;});
    const rg=sh.getRange(3,1,sh.getLastRow()-2,sh.getLastColumn());
    if(sA.indexOf(e.value)!=-1) {
      rg.sort({column:col[e.value],ascending:true});
      sh.getRange('A3').activate();
    }else{
      rg.sort({column:col[e.value],ascending:false});
    }        
  }
}

Upvotes: 1

Related Questions