auto sorting multiple columns, secondary column triggers resort too

Question

I have a sorting question. I have two columns in my table that I need to sort by (primary=column 1, secondary=column 4). I am using the script below and it works for sorting by column 1, and it works for sorting column 4 if I also make a change in Column 1 but not by itself. I would like to be able to edit a value JUST column 4 value and have it do the secondary sort.

Ex: Column 1: Date, Column 2: LastName, Column 3: DOB, Column 4: Payment I'd like to maintain the spreadsheet in date order (ascending, allowing repeat values), but secondarily organized by payment amount (ascending). That way I can see by date the order of highest to lowest payments. If I change JUST the payment data in a cell, I'd like it to resort by column 1 (date) and then 4 (payment). This script is not accomplishing that last bit. Any advice?

/**
 * Automatically sorts the 1st column (not the header row) Ascending.
 */
function onEdit(event){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 1;
  var tableRange = "A2:T99"; // What to sort.

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( [1, 4] ); // or
    range.sort( [{ column: 1, ascending: true }, 4] ); // or
    range.sort( [{ column: 1, ascending: true }, { column: 4, ascending: true }] );
  }
}

Taifun Taifun · Accepted Answer · 2013-08-06T20:39:46

Probably I don't understand the problem? I suggest to change your if statement from

if(editedCell.getColumn() == columnToSortBy){ 
  ...
}

to

if((editedCell.getColumn() == columnToSortBy) || (editedCell.getColumn() == 4)){   
  ...
}

auto sorting multiple columns, secondary column triggers resort too

1 Answers