Automatically sort Google Sheet by most recently updated row

Question

I want to have a sheet which automatically sorts the most recently edited row to the top, in order to surface freshly edited results and not let them be buried by outdated entries.

Answer 1

Here's the solution I came up with for a Google Apps Script:

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var dateColumn = 2; // What column should date be written to
  var headerRows = 2;
  var tableRange = "A3:M101"; // What data to sort.
  var sheetName = 'Sheet name' // Name of the sheet to sort

  if( sheet.getName() == sheetName ) { // Checks that we're on the correct sheet
    var editedCell = sheet.getActiveCell();
    var offset = dateColumn - editedCell.getColumn() // Finds the offset needed to move from edited cell to date column
    if( editedCell.getColumn() != dateColumn && editedCell.getRow() > headerRows) { // Don't overwrite manually set dates or the header row(s)
      var dateCell = editedCell.offset(0, offset); // Go to relevant date cell
      var now = new Date();
      now = Utilities.formatDate(now, "GMT", "yyyy/MM/dd HH:mm:ss"); // Format datetime
      dateCell.setValue(now);
    }

    var range = sheet.getRange(tableRange);

    range.sort( { column : dateColumn, ascending: false } ); // Sort range by date column, newest on top
    // range.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP); // Sets text wrapping to wrap if uncommented
  }
}

Plus adding an event trigger on form submission.