Auto-sort a sheet whenever data changes

Northwind’s Projects sheet is the team’s single view of what is due and when — but only when it is sorted. New projects get added at the bottom, due dates get nudged around, and within a day the sheet is in entry order rather than deadline order. Someone notices, sorts it by hand, and the cycle starts again.

This script removes the chore entirely. An onEdit trigger fires every time a cell on the Projects tab changes and re-sorts the rows by their dueDate column. The most urgent project is always at the top, with no one having to remember to sort anything.

What you’ll need

• A Projects tab with a header row in row 1, including a column titled exactly dueDate.
• Dates stored as real dates in that column (not text), so they sort chronologically rather than alphabetically.
• Edit access to the spreadsheet, and permission to add an installable trigger.

The script

// The tab to keep sorted, and the header that holds the dates.
const SORTED_TAB = 'Projects';
const DUE_HEADER = 'dueDate';

/**
 * Runs on every edit. If the edit was on the Projects tab, re-sorts the
 * data rows by their dueDate column, soonest first.
 *
 * @param {Object} e The edit event passed in by the trigger.
 */
function onEdit(e) {
  // 1. Bail out if there is no event object (e.g. run manually).
  if (!e || !e.range) return;

  // 2. Only act on edits to the Projects tab.
  const sheet = e.range.getSheet();
  if (sheet.getName() !== SORTED_TAB) return;

  // 3. Find the dueDate column by its header text.
  const headers = sheet
    .getRange(1, 1, 1, sheet.getLastColumn())
    .getValues()[0];
  const dueCol = headers.indexOf(DUE_HEADER) + 1;
  if (dueCol === 0) return; // header not found — nothing to sort on

  // 4. Skip if there is only a header row, or no rows at all.
  const lastRow = sheet.getLastRow();
  if (lastRow < 3) return;

  // 5. Sort the data rows (everything below the header) by due date, ascending.
  const range = sheet.getRange(
    2,
    1,
    lastRow - 1,
    sheet.getLastColumn()
  );
  range.sort({ column: dueCol, ascending: true });
}

How it works

1. onEdit first checks it actually received an event object. Running the function manually passes no e, so the guard stops it from throwing.
2. It reads which tab the edit landed on and returns immediately unless that tab is Projects — every other sheet is left untouched.
3. It reads the header row and finds the position of the dueDate column. indexOf returns a zero-based index, so adding 1 gives the 1-based column number Sheets expects. If the header is missing, it stops.
4. It checks there is more than a header row to sort — with fewer than two data rows there is nothing to do.
5. It selects every data row (row 2 down to the last row, all columns) and calls sort on the dueDate column, ascending, so the soonest deadline rises to the top.

Example run

Before — a new project has just been typed into the last row:

project	owner	dueDate
Acme rebrand	Priya	2026-06-12
Beta launch	Sam	2026-05-30
Catalogue refresh	Lee	2026-06-02

The moment that third row is finished, onEdit fires and the sheet re-sorts:

project	owner	dueDate
Beta launch	Sam	2026-05-30
Catalogue refresh	Lee	2026-06-02
Acme rebrand	Priya	2026-06-12

The nearest deadline is always row 2.

Wire it up

A simple onEdit function works, but an installable trigger is more robust — it survives across editors and has a longer runtime allowance:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose onEdit, event source From spreadsheet, event type On edit.
4. Save and approve the authorisation prompt.

You only need one — do not keep both the simple and installable trigger, or the sort will run twice per edit.

Watch out for

• Sorting on every keystroke is noisy. If you only want a sort when the deadline itself changes, add if (e.range.getColumn() !== dueCol) return; after step 3 so edits to other columns are ignored.
• Sorting moves rows, so the cell a user just edited may jump elsewhere. This is harmless but can feel jarring mid-entry — restricting the trigger to the dueDate column (above) makes it far less disruptive.
• Dates stored as text sort alphabetically: 01/06 lands before 30/05. Format the column as a date and enter real dates so the sort is chronological.
• onEdit triggers have a short runtime budget. On a very large sheet a full re-sort on every edit can feel sluggish — for thousands of rows, consider sorting on a time-driven trigger instead.
• The sort assumes all data rows are contiguous from row 2 down. Blank rows in the middle, or notes below the data, will be swept into the sort.