Custom sort order in Google Sheets

Question

I am trying to sort a range of data in a Google Sheets spreadsheet based on the values in a single column AND using a custom sort order. I am able to sort the data, but I can't figure out how to set the priority for sorting (other than alphabetically or reverse alphabetically). I would like to sort based on the order indicated below, but obviously what I have now just does it alphabetically. Help much appreciated!

  function onEdit() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var editedCell = sheet.getActiveCell();

  //sort based on priority
    var columnToSortBy = 4;
    var tableRange = "A2:Q1000";
    var SortOrder = [
      {name: "Urgent", value:1},
      {name: "High Priority", value:2},
      {name: "Moderate Priority", value:3},
      {name: "No deadline", value:4},
      {name: "Complete", value:5}
    ];

    if(editedCell.getColumn() == columnToSortBy){   
      var range = sheet.getRange(tableRange);
      range.sort( { column : columnToSortBy } );  //this needs to be sorted based on priority level, not 
    }
  }

Couldn't get either of the proposed solutions to work as needed, so I wound up adding numbers as prefixes to the names and let them sort based on the numbers. — Clare

Anton Dementiev Anton Dementiev · Accepted Answer · 2018-01-30T00:06:43

GAS documentation doesn't seem to reference custom sort order. You can grab the values from the range, sort them by passing the custom comparator function to the 'sort' method of Array.prototype, and, finally, overwrite the range with sorted array.

The example below assumes there's a single column in the first sheet that needs to be sorted in accordance with priorities set by the 'sortBy' object.

var sheet = SpreadsheetApp.getActive().getSheets()[0];

  var range = sheet.getRange(1, 1, sheet.getLastRow(), 1);
  var values = range.getValues();

  var sortBy = {  
    "a": 2,
    "b": 0,
    "c": 4,
    "d": 1,
    "e": 3 
  };

 values.sort(function(a, b){   
   return sortBy[[a][0]] - sortBy[[b][0]];
  });


 range.setValues(values);

This sorts ascending - to reverse the order, change the comparator function to subtract current element's priority from the next one.

 return sortBy[[b][0]] - sortBy[[a][0]];

Custom sort order in Google Sheets

1 Answers