ScriptdrillSort a range
range.sort(2) sorts the range by its second column ascending; range.sort({column: 2, ascending: false}) sorts descending; pass an array of specs for several keys. The column number is the sheet column, not the position in the range. Exclude the header row from the range or it gets sorted in.
Syntax
range.sort(column) | range.sort({column, ascending}) | range.sort([{column: 1}, {column: 3, ascending: false}])| Part | What it is |
|---|---|
column | Absolute sheet column index (A = 1), even if the range starts further right |
ascending | true (default) or false |
sheet.sort(column, ascending) | Sorts the whole sheet except frozen rows |
Examples
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 4);
data.sort(3);Result: Rows 2 to last sorted by column C ascending; row 1 untouched
data.sort({column: 3, ascending: false});Result: Same rows sorted by column C from highest to lowest
data.sort([{column: 2, ascending: true}, {column: 3, ascending: false}]);Result: Sorted by column B, then by column C descending within each B group
const rows = data.getValues();
rows.sort((a, b) => a[0].localeCompare(b[0]));
data.setValues(rows);Result: Rows sorted by column A with locale-aware text comparison, then written back
Useful when you need custom order, such as numbers stored as text.
Common mistakes
- The header ends up in the middle of the data: your range started at row 1; start at row 2 or freeze row 1 and use sheet.sort.
- Sort appears to ignore the column you asked for: the column index is relative to the sheet, so a range C2:F50 sorted by its first column needs column 3.
- Numbers sort as 1, 10, 2: those cells are text; convert with Number() and rewrite, then sort.
- Blank rows at the top after a descending sort: empty cells sort first; size the range with getLastRow so no blank rows are included.
Practice Sort a range
These drills check your answer on live data, free, no account needed.
- Sort by several keysReturn a sorted copy of `items`. `keys` lists field names in priority order; a leading minus sign, like `"-salary"`, sorts that field from high to low. Numbers compare as numbers and strings with `<` and `>`. Items that tie on every key keep their original order. Do not change `items`.
- Top N with tiesReturn the `n` items with the highest `item[key]`, highest first. If more items tie with the value of the `n`th one, include them all. Items with equal values keep their original order. `n` can be 0 or larger than the list. Do not change `items`.
Questions
Does range.sort move formulas and formatting with the rows?
Yes, whole rows within the range move together, including formulas and cell formatting.
How do I sort by date?
Real date cells sort chronologically with range.sort. If dates are text, convert them to Date objects first or sort the array with new Date(a) - new Date(b).
Related
- Remove duplicatesRemove duplicate rows in Google Sheets with Apps Script: range.removeDuplicates for whole rows or chosen columns, or a JavaScript Set filter on one column.
- Clear and format cellsClear and format Google Sheets cells with Apps Script: clear content or formats, set background and font color, bold, number and date formats, borders.
- getRange and getValuesRead Google Sheets data with Apps Script: getRange by A1 notation or row and column numbers, then getValue for one cell or getValues for a 2D array of rows.
- map, filter and reduceUse map, filter and reduce on Google Sheets data in Apps Script: transform rows, keep rows that match a condition, sum a column, chain them, write back.