[GAS Beginner Series #20] Automate Google Sheets Data Sorting: How to Use Range.sort()

When managing customer lists, sales figures, or daily tasks in Google Sheets, you probably find yourself wanting to sort things every day—whether it is viewing records by highest sales, sorting dates from newest to oldest, or organizing names alphabetically.

While you can manually apply filters and sort from the top menu, repeating those clicks every time new data arrives takes up an unexpected amount of valuable time.

That is why in this tutorial, we will explore Range.sort()—an essential Google Apps Script (GAS) method that automatically reorders your specified data range in a flash!

This 20th lesson marks the grand finale of Phase 2: “Multiple Cells, Array Processing, and Table Manipulation.” Even if you are brand new to programming, don’t worry! We will break down everything step-by-step: how to remember ascending vs. descending order, practical multi-column sorting, and how to avoid the classic beginner mistake where header rows get sent flying to the bottom.

1. What Is Range.sort()?

Range.sort() is a method that automatically sorts data within a specified cell range (Range) in ascending (lowest to highest) or descending (highest to lowest) order based on a specific column.

Think of Sorting a Hand of Playing Cards

Imagine holding a shuffled hand of playing cards and neatly organizing them by number or suit. Range.sort() does that exact same reordering task automatically for your Google Sheets tables.

Basic Syntax

// targetRange.sort({column: columnNumber, ascending: trueOrFalse});
range.sort({column: 3, ascending: false});

Inside the curly braces { }, you provide two key parameters:

  1. column (The target column to sort by)
    • Specify the column number as an integer.
    • Column A is 1, Column B is 2, and Column C is 3. (Note: You cannot pass letters like “C”!).
  2. ascending (Sort order direction)
    • true (Ascending): Numbers or values increase (e.g., 1, 2, 3… / A, B, C… / oldest date to newest).
    • false (Descending): Numbers or values decrease (e.g., 10, 9, 8… / Z, Y, X… / newest date to oldest).

💡 Easy Tip to Remember Ascending (true) vs. Descending (false)

The word ascending literally means “going up” or “climbing.”

  • Ask yourself: “Is it ascending (getting bigger)?” If Yes (true), it starts small and goes up (1 → 10).
  • If No (false), it starts large and goes down (10 → 1). Remembering it this way will save you from second-guessing your code!

2. STEP 1: Preparation! Create a “Hokkaido Mountain Elevation” Table

Let’s practice by running this on an actual spreadsheet!

For this exercise, we will use elevation data for prominent mountains in Hokkaido—perfect for planning weekend hiking trips.

Spreadsheet Setup Steps

  1. Create a new Google Spreadsheet and name the file “Hokkaido Mountain Data”.
  2. Rename the sheet tab at the bottom to “Elevation Data”.
  3. Enter the following table into cells A1:C6.

▼ Sample Data (The order can remain unsorted!)

A (Mountain)B (Area)C (Elevation)
1MountainAreaElevation (m)
2Mt. YoteiShiribeshi1898
3Mt. AsahiKamikawa2291
4Mt. RishiriSoya1721
5Mt. TokachiKamikawa2077
6Mt. MoiwaIshikari531

Notice how everything is unordered—from Mt. Moiwa (531m), a favorite urban getaway for Sapporo locals, to Mt. Asahi (2,291m), the highest peak in Hokkaido.

3. STEP 2: [Hands-on] Automatically Sort by Elevation in Descending Order

Let’s use GAS to reorder this list from highest to lowest elevation (Column C in descending order) with a single click!

1. Enter the Code

Open the script editor by going to Extensions > Apps Script from the top menu, and paste in the following code:

function sortMountainsByHeight() {
  // 1. Get the "House (Spreadsheet)" and "Room (Sheet)"
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  // 2. Get the last row containing data (this returns 6)
  var lastRow = sheet.getLastRow();
  
  // 3. Target ONLY the data rows (excluding the header!)
  // getRange(startRow, startColumn, numRows, numColumns)
  // Start at Row 2, Col 1, span 5 rows down (lastRow - 1), and 3 columns wide
  var dataRange = sheet.getRange(2, 1, lastRow - 1, 3);
  
  // 4. Sort by elevation (Column 3) descending (false = highest first)
  dataRange.sort({column: 3, ascending: false});
  
  // 5. Log completion
  Logger.log("Sorted by elevation in descending order successfully!");
}

2. Key Concept: Why Use lastRow - 1?

One line where many beginners pause is sheet.getRange(2, 1, lastRow - 1, 3).

Why do we subtract 1 from lastRow?

  • In our sheet, the last populated row (lastRow) is Row 6.
  • The data we actually want to sort starts after the header (Row 1), covering Rows 2 through 6.
  • From Row 2 to Row 6, there are 5 rows of data in total.
  • Remember the rule from Lesson #4: the third parameter of getRange() is not the end row index, but the number of rows to include (height).
  • Therefore, to calculate 5 rows, we take the total row count (6) and subtract the single header row (1), which gives us lastRow - 1!

3. Save, Run, and Verify

Click “Save” at the top of the editor, then click “Run”.

When you switch back to your spreadsheet, your data will instantly look like this:

▼ Spreadsheet After Execution

A (Mountain)B (Area)C (Elevation)
1MountainAreaElevation (m)
2Mt. AsahiKamikawa2291
3Mt. TokachiKamikawa2077
4Mt. YoteiShiribeshi1898
5Mt. RishiriSoya1721
6Mt. MoiwaIshikari531

The tallest peak, Mt. Asahi, moves straight to the top, while Mt. Moiwa sits at the bottom—perfectly organized!

4. STEP 3: Essential for Real Work: Multi-Condition Sorting

In workplace workflows, you frequently need multi-level sorting—for instance, “group by department first, and within each department, sort by sales from highest to lowest.”

In Google Apps Script, you can achieve this by simply passing an array [ ] containing multiple criteria objects!

function sortMountainsByMultipleConditions() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var dataRange = sheet.getRange(2, 1, lastRow - 1, 3);
  
  // Priority 1: Area (Col 2) alphabetical (ascending: true)
  // Priority 2: Elevation (Col 3) highest first (descending: false)
  // Wrap multiple criteria objects in [ ] separated by commas!
  dataRange.sort([
    {column: 2, ascending: true}, 
    {column: 3, ascending: false}
  ]);
  
  Logger.log("Multi-condition sort (Area > Elevation) completed successfully!");
}

Running this script sorts the areas alphabetically first (Ishikari, Kamikawa, Shiribeshi, Soya). Within the “Kamikawa” region, Mt. Asahi (2,291m) neatly appears before Mt. Tokachi (2,077m)!

5. Watch Out! 4 Common Beginner Traps and How to Fix Them

While Range.sort() is remarkably powerful, a small configuration oversight can cause unexpected headaches. Here are four common traps beginners run into and how to easily resolve them.

Trap 1: Accidentally Sorting the Header Row

This happens when you try to sort everything using sheet.getDataRange().sort(...) or sheet.getRange("A1:C6").sort(...), including Row 1.

When you include headers, the text label “Elevation (m)” is treated as a regular data row, sending your header flying to the middle or bottom of the table.

[Solution]

Always define your sort range starting from row 2 (skipping headers), using the getRange(2, 1, lastRow - 1, ...) pattern demonstrated in STEP 2!

Trap 2: Passing Letters to the column Parameter

A common mistake is writing column: "C" because you want to sort by Column C.

[Solution]

GAS strictly requires column arguments in sort() to be numbers (1, 2, 3…). Map Column A to 1, Column B to 2, Column C to 3, and so on.

Trap 3: Empty Cells Moving to the Bottom

If your range includes blank cells, Google Sheets will naturally push those blank cells to the very bottom of the range, regardless of whether you pick ascending (true) or descending (false) order.

This is built-in spreadsheet behavior. If you need blanks handled differently, consider filling them with 0 or dummy placeholder values before running the sort.

Trap 4: Forgetting the Outer Array Brackets [ ] for Multiple Conditions

When providing multiple conditions, omitting the outer brackets and writing {column: 2...}, {column: 3...} will trigger an “incorrect signature / parameters do not match” error.

[Solution]

Whenever you pass two or more sorting criteria, GAS expects them inside a single array “container,” so remember to wrap the entire list in square brackets: [ {column: 2...}, {column: 3...} ].

6. Phase 2 Complete! Summary and What’s Next

In this lesson, we took an in-depth look at Range.sort() to sort Google Sheets ranges effortlessly.

  • Use column (numeric index) and ascending (true for ascending, false for descending).
  • Exclude header rows with getRange(2, 1, lastRow - 1, numColumns).
  • Wrap multiple sort rules in square brackets [ ].

With this lesson, you have officially completed all of “Phase 2: Multiple Cells, Array Processing, and Table Manipulation” (Lessons #11 to #20)! Huge congratulations!!

By combining the skills you have mastered so far—batch retrieving values (getValues), filtering (filter), transforming (map), sorting (sort), and writing back to the sheet (setValues)—you can now build fully automated, production-grade data pipelines on your own!

Starting with Lesson #21, we step into “Phase 3: Automation, UI Extensions, and Error Handling”!

We will dive into exciting features like running scripts the moment a spreadsheet opens (onOpen) to elevate your tools into full-fledged business systems. Stay tuned!