Automatically sort Google Sheet by most recently updated row-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

This could work with an on form submit but not like you think

function onFormSubmit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == 'Sheet name' && e.range.columnStart != 2 && e.range.rowStart > 3) {
    e.range.offset(0, 2 - e.range.columnStart).setValue(Utilities.formatDate(new Date(), "GMT", "yyyy/MM/dd HH:mm:ss"));
    sh.getRange(3, 1, sh.getLastRow() - 2, 13).sort({ column: 2, ascending: false });
  }
}

columStart will always be one so that will work

row start will work after you get past row 3

and the sheet will always have to be the linked sheet name and resorting the linked sheet seems kind of weird to me it's already sorted by time stamp