A backlog that's supposed to be sorted by priority only stays that way until someone changes a priority value and forgets to re-sort, which is exactly what onEditAutoSortByPriority fixes by re-sorting automatically the moment priority or due date changes.
Manual sorting also has a nasty failure mode: selecting the wrong range before hitting Data > Sort can scramble rows relative to each other, silently corrupting a backlog that people rely on for planning. A script-driven sort always operates on the same, correctly bounded range.
This example watches exactly two columns - Priority and Due Date - and re-sorts the full data range by both whenever either one is touched, using a two-level sort so ties in priority fall back to the earlier due date.
By the end you'll have a Backlog tab that reorders itself instantly after any relevant edit, plus a toast notification confirming the sort ran.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Column | Field | Purpose |
|---|---|---|
| A | Title | Task name, unaffected by the sort logic but moves with its row |
| B | Owner | Assignee, also moves with the row during sort |
| C | Priority | Primary sort key - watched column that triggers a re-sort |
| D | Due Date | Secondary sort key - also a watched column |
What it does
Whenever Priority or Due Date changes on the Backlog sheet, the function re-sorts every data row (excluding the header) first by Priority ascending, then by Due Date ascending for any rows sharing the same priority. A toast confirms the re-sort so the edit doesn't feel invisible.
- Only two columns are watched: Priority (C) and Due Date (D)
- Sort touches the full row width, so Title and Owner move together with their Priority
- A toast notification gives visible feedback that something happened
Prerequisites
The sort assumes row 1 is a header and that Priority values are directly comparable (numbers or a consistent text scale like 'P0', 'P1', 'P2') - mixed types in the same column will sort inconsistently.
- A sheet named 'Backlog' with a single header row
- Priority column using a consistent, sortable value format
- Due Date column formatted as actual dates, not text
- An installed onEdit trigger
Walkthrough
dataRange is built dynamically from headerRows + 1 down to lastRow, and across every column up to lastColumn, so adding new columns to the right of Due Date is automatically included in future sorts without code changes.
Range.sort accepts an array of sort specs, and passing both priorityColumn and dueDateColumn in one call performs a stable two-level sort in a single operation rather than two separate sort calls that could otherwise undo each other.
Edge cases
Editing Priority and Due Date in the same paste operation still fires onEdit only once for the whole pasted range, and the guard only needs one of the two columns to be included to trigger a re-sort.
- Blank Priority cells sort before or after populated ones depending on Sheets' default text-vs-number ordering
- Sorting while a filter view is active can behave unexpectedly since sort operates on the underlying range, not the filtered view
- Adding a brand new row without a Priority value will jump to one end of the list on the next sort
Testing
Change a Priority value on a row buried in the middle of the Backlog and confirm it jumps to its correctly sorted position, then edit only a Due Date on a row sharing the same priority as another to confirm the secondary sort breaks the tie correctly.
- Edit the Title or Owner column and confirm no sort occurs
- Add a new row with a duplicate priority and check tie-breaking by due date
- Verify the toast appears exactly once per qualifying edit, not once per cell in a multi-cell paste
Hardening
A user with a text-formatted Priority column (like 'High', 'Medium', 'Low') will get alphabetical rather than severity-based ordering, since Range.sort has no concept of custom priority order.
- Convert Priority to numeric values (1, 2, 3) internally or via a helper column so sort order matches intended severity
- Add a check that skips the sort while a filter or protected range is active on the sheet
- Debounce rapid successive edits with a short lock using LockService to avoid overlapping sort calls
Variations
The same two-column sort pattern extends naturally to three or more sort keys - for example adding Owner as a tertiary key so tasks assigned to the same person cluster together within a priority tier.
- Add Owner as a third sort key for grouping within the same priority
- Sort descending on Priority instead if higher numbers mean higher urgency in your scale
- Trigger the same sort on a schedule instead of on edit, for sheets updated by an external integration rather than manual typing
Full code: onEditAutoSortByPriority()
The function rebuilds the sort range from the sheet's current dimensions on every run, so it stays correct even as rows are added or removed over time.
function onEditAutoSortByPriority(e) {
var sheet = e.range.getSheet();
if (sheet.getName() !== 'Backlog') return;
var headerRows = 1;
var lastRow = sheet.getLastRow();
if (lastRow <= headerRows) return;
var editedColumn = e.range.getColumn();
var priorityColumn = 3;
var dueDateColumn = 4;
if (editedColumn !== priorityColumn && editedColumn !== dueDateColumn) return;
var lastColumn = sheet.getLastColumn();
var dataRange = sheet.getRange(headerRows + 1, 1, lastRow - headerRows, lastColumn);
dataRange.sort([
{ column: priorityColumn, ascending: true },
{ column: dueDateColumn, ascending: true }
]);
SpreadsheetApp.getActiveSpreadsheet().toast('Backlog re-sorted by priority and due date');
}- Line 3: Restricts the trigger to the Backlog sheet so unrelated tabs don't get re-sorted on every edit.
- Line 7: Exits if there's no data below the header row, avoiding a sort call on an empty range.
- Line 12: Only continues if the edited column is Priority or Due Date, ignoring edits to Title or Owner.
- Line 15: Builds the sortable range dynamically from the current last row and last column.
- Line 17: Calls Range.sort with a two-level spec so Due Date breaks ties within the same Priority.
- Line 22: Shows a toast so the person editing sees visible confirmation that the backlog was reordered.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before turning on auto-sort
- 1Priority column uses a consistent, sortable value type
- 2Due Date column is formatted as real dates
- 3Header row excluded from the sort range
- 4Tested a tie-breaking scenario on Due Date
- 5Confirmed sort doesn't fight with an active filter view
- 6Trigger installed as onEdit on the Backlog sheet specifically
Frequently asked questions
A button requires someone to remember to click it, which defeats the point of keeping the backlog reliably ordered; an onEdit trigger guarantees the sort happens the instant a relevant value changes, with zero extra clicks.
Range.sort operates on the sheet's underlying data regardless of any filter view layered on top, so the data itself gets reordered - anyone with a filter view open will see their filtered results reflect the new order automatically.
Yes, Range.sort accepts an array of any length, so add a third object like column and ascending for Owner to the array to add it as a tertiary key.
Range.sort orders text alphabetically, so 'High' would sort before 'Low' regardless of actual urgency; convert to a numeric priority scale or a helper column mapping text to numbers for correct ordering.
Yes - formulas using absolute row references like fixed cell links will point at the wrong row after a sort, since the values move but the reference doesn't follow; use structured references or array formulas that aren't row-position dependent instead.
Temporarily remove the installed trigger before running the import, or add a script property flag that the onEdit function checks and skips sorting while set, then re-enable both afterward.