Sort Google Spreadsheet With Multiple Criteria Using Script

Question

I have a spreadsheet that I update on a regular basis. I also have to re-sort the spreadsheet when finished because of the changes made. I need to sort with multiple criteria like the below settings. I have searched for examples but my Google search skills have failed me.

Sort range from A1:E59
[x] Data has header rows
sort by "Priority" A > Z
then by "Open" Z > A
then by "Project" A > Z

Answer 1

Mogsdad's answer works fine if none of your cells have values automatically calculated via a formula. If you do use formulas, though, then that solution will erase all of them and replace them with static values. And even so, it is more complicated than it needs to be, as there's now a built-in method for sorting based on multiple columns. Try this instead:

function onEdit(e) {
  var priorityCol = 1;
  var openCol = 2;
  var projectCol = 3;

  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  dataRange.sort([
    {column: priorityCol, ascending: true},
    {column: openCol, ascending: false},
    {column: projectCol, ascending: true}
  ]);
}

Instead of making a separate function, you can use the built-in onEdit() function, and your data will automatically sort itself when you change any of the values. The sort() function accepts an array of criteria, which it applies one after the other, in order.

Note that with this solution, the first column in your spreadsheet is column 1, whereas if you're doing direct array accesses like in Mogsdad's answer, the first column is column 0. So your numbers will be different.