How to sort 2 different ways from a data validation cell onEdit

Question

I am trying to create an onEdit trigger to sort a list 1 of 2 ways. The way to sort the list is decided by a data validation. One sort option (Name Only) would be column 3 Ascending. The second option (Name and TH) would be sort column 4 descending then column 3 Ascending.

I currently have the following code which triggers if the data validation cell is changed. This works just fine but doesn't allow me to choose another way to sort the data.

function sortRoster() {

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var roster = activeSheet.getRange("B8:D77");
  var sorter = activeSheet.getRange("G4");

  roster.sort( [{ column : 4, ascending: false },{ column : 3, ascending: true }] );
  var clear = activeSheet.getRange("G4").clearContent().setValue("Do you want to Sort?");
}

function onEdit(e) {
  // test if G4 was modified
  if (e.source.getActiveSheet() && e.range.rowStart === 4 && e.range.columnStart === 7) {
    sortRoster(); 

  }
}

I have tried first adding another function ( calling it sortName() ) which would run the sort on column 3 only.

function sortName() {

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var roster = activeSheet.getRange("B8:D77");

  roster.sort( [{ column : 3, ascending: true }] );
  var clear = activeSheet.getRange("G4").clearContent().setValue("Do you want to Sort?");

Then I tried changing the if to

if (e.source.getActiveSheet() && activeCell == "Name Only") {
  sortName();
  sorter.clearContent().setValue("Choose how to Sort?");
} else if (e.source.getActiveSheet() && activeCell == "Name and TH") {
  sortRoster();
  sorter.clearContent().setValue("Choose how to Sort?");
}

This idea does not work and I don't understand why.
I feel I don't fully understand how the event trigger works even after my research online. I've seen several pages like this that are similar but not that seem to address the same issue I have.

Thank you in advance for your assistance and guidance.

Answer 1

Sorting onEdit() via a couple of Checkboxes

This needs a couple of checkboxes in A4 and A5. It's a part something else. You also need to create an installable onEdit trigger for onCheckOpenSideBar(e) or your can use the function provided.

function onCheckOpenSideBar(e) {
  if(e.range.getSheet().getName()!='Sheet1')return;
  if(e.range.rowStart==4 && e.range.columnStart==1) {
    if(e.value=='TRUE') {
      sortByCol(3);
      e.range.getSheet().getRange(e.range.rowStart,e.range.columnStart).setValue("FALSE");
    }
  }
  if(e.range.rowStart==5 && e.range.columnStart==1) {
    if(e.value=='TRUE') {
      sortByCol(4,5);
      e.range.getSheet().getRange(e.range.rowStart,e.range.columnStart).setValue("FALSE");
    }
  }
}

function sortByCol(col,col2) {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var rg=sh.getRange(1,3,getColumnHeight(col,sh,ss),3);
  if(col2) {
    rg.sort([{column: col,ascending:true},{column: col2,ascending:true}]);
  }else{
    rg.sort({column: col, ascending:true});
  }
}

function createOnEditTrigger() {
  ScriptApp.newTrigger('onCheckOpenSideBar').forSpreadsheet('Spreadsheet ID').onEdit().create();
}

This is what my spreadsheet looks like before sorting.

The checkboxes in A2 and A3 were used for opening a sidebar and a dialog.

Adding the use of a Validation DropDown

function onCheckOpenSideBar(e) {
  if(e.range.getSheet().getName()!='Sheet1')return;
  if(e.range.rowStart==2 && e.range.columnStart==1) {
    if(e.value=='TRUE') {
      openTheSidebar();
      e.range.getSheet().getRange(e.range.rowStart,e.range.columnStart).setValue("FALSE");
    }
  }
  if(e.range.rowStart==3 && e.range.columnStart==1) {
    if(e.value=='TRUE') {
      openTheDialog();
      e.range.getSheet().getRange(e.range.rowStart,e.range.columnStart).setValue("FALSE");
    }
  }
  if(e.range.rowStart==4 && e.range.columnStart==1) {
    if(e.value=='TRUE') {
      sortByCol(3);
      e.range.getSheet().getRange(e.range.rowStart,e.range.columnStart).setValue("FALSE");
    }
  }
  if(e.range.rowStart==5 && e.range.columnStart==1) {
    if(e.value=='TRUE') {
      sortByCol(4,5);
      e.range.getSheet().getRange(e.range.rowStart,e.range.columnStart).setValue("FALSE");
    }
  }
  if(e.range.rowStart==6 && e.range.columnStart==1) {
    if(e.value=='C') {
      sortByCol(3);
    }
  }
  if(e.range.rowStart==6 && e.range.columnStart==1) {
    if(e.value=='D,E') {
      sortByCol(4,5);
    }
  }
}

Now it Looks Like this:

Or This:

Data Validation Dialog: