I have this script to auto-sort rows of data based on the item selected in the dropdown but it's not working. Can anyone figure out why?

    function onEdit(e) {
      
      const as = e.source.getActiveSheet();

      var ordering = {
      "Approved":1, //move to the top
      "Accept": 2,
      "Hold": 3,
      "Decline": 4, //move to the bottom
      };
    
      var compareIndex = 13;
      var range = as.getRange(4,1,as.getLastRow(),as[0].length);
      var values = range.getValues();
    
      values.sort(function(a,b){
        return ordering[[a][compareIndex]] - ordering[[b][compareIndex]];
      });
      range.setValues(values);
    }

Here is what the sheet looks like:

The goal is to have rows of data move to the appropriate position based on the Status selected from the dropdown (Column M). "Decline" would move to the bottom while "Approved would move to the top - "Accept" and "Hold" would be between those, in the order shown in the script.

To add another layer - I would ideally also want any dropdowns which have nothing selected to stay at the top of the sheet (with any row with a selected value from the dropdown to be sorted below) and I would like each category (i.e., Approved) to be sorted by date (Column A) with the most recent date at the top of each Status group.

Can anyone help with this?

Thanks!

CodePudding user response：

Sorting Rows

function onEdit(e) {
  //e.source.toast("Entry")
  const sh = e.range.getSheet();
  if (sh.getName() == 'SheetName' && e.range.columnStart == 13 && e.range.rowStart > 3) {
    //e.source.toast('Sorting')
    const oA = ["Approve", "Accept", "Hold", "Decline",""];
    vs = sh.getRange(4,1,sh.getLastRow() - 3, sh.getLastColumn()).getValues();
    vs.sort((a,b) => {
      let vA = oA.indexOf(a[12]);
      let vB = oA.indexOf(b[12]);
      return vA - vB;
    });
  }
  sh.getRange(4,1,vs.length,vs[0].length).setValues(vs);
  //e.source.toast('Done');
}

• sort

CodePudding user response：

Modification points:

• When I saw your updated script, it seems that as is const as = e.source.getActiveSheet();. In this case, I think that an error occurs at as[0].length of var range = as.getRange(4,1,as.getLastRow(),as[0].length);.
• If values of var values = range.getValues(); is correct values of your data range, I think that ordering[[a][compareIndex]] - ordering[[b][compareIndex]] shoud be ordering[a[compareIndex - 1]] - ordering[b[compareIndex - 1]]. But, from your sample output situation, if you want to put the empty rows to the above of the data, it should be (ordering[a[compareIndex - 1]] || 0) - (ordering[b[compareIndex - 1]] || 0).

When these points are reflected in your script, it becomes as follows.

Modified script:

function onEdit(e) {
  const sheet = e.range.getSheet();
  var compareIndex = 13;
  // if (sheet.getSheetName() != "Sheet1" || e.range.columnStart != compareIndex || e.range.rowStart < 4) return; // If you want to restrict the edited cells, please use this. At that time, please set the sheet name.
  var ordering = {
    "Approved": 1, //move to the top
    "Accept": 2,
    "Hold": 3,
    "Decline": 4, //move to the bottom
  };
  var range = sheet.getRange(4, 1, sheet.getLastRow() - 3, sheet.getLastColumn());
  var values = range.getValues();
  values.sort(function (a, b) {
    return (ordering[a[compareIndex - 1]] || 0) - (ordering[b[compareIndex - 1]] || 0);
  });
  range.setValues(values);
}

• If you want to put the empty rows to the below of the data, please modify return (ordering[a[compareIndex - 1]] || 0) - (ordering[b[compareIndex - 1]] || 0); to return (ordering[a[compareIndex - 1]] || 5) - (ordering[b[compareIndex - 1]] || 5);.