1

I have a complex spreadsheet that rolls up data from all over the place into a filtered list. We need to sort this filtered list, along with notes added afterward in columns to the right of the filter. People may add data or choose to change the filter, so the list also needs to automatically re-sort when it's edited.

Various attempts at using the SORT function on this sheet haven't worked. I've read elsewhere that the only way to really do this is a script--I've tried the one below, but it doesn't sort everything in the defined range (it's like the filter overrides everything). Anyone have suggestions on how to get this working? Perhaps a QUERY function instead?

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

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

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: true } );
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.