CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestampcolumnsorting
plex
plex

Reputation: 11

Automatically sort Google Sheet by most recently updated row

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.

Upvotes: 0

Views: 1116

Answers (2)

Cooper
Cooper

Reputation: 64110

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

Upvotes: 0

plex
plex

Reputation: 11

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.

Upvotes: 1

Related Questions