Auto-Sorting Your Google Sheet

Sorting your Google Sheet using Apps Script is very simple. Firstly, you'll need to decide how you want to sort your data - either by the entire sheet or by using a specific range.

In this how-to guide, we'll go over sorting the entire sheet. For sorting a specific data range within a sheet, check out the Auto-Sorting a Specific Google Sheet Range how-to guide.

Unlike sorting a specific data range, sorting the entire sheet only allows you to sort by one column at a time. To do a more complex sort that uses multiple columns, you'll need to call the .sort() method against a Range object.

Understanding the .sort() method

Basically put, you'll get your Sheet object and then run the .sort() method against it. When running the .sort() method, you'll be allowed two parameters - one for the column number and the other for the sorting direction.

The column numbers start from 1 where 1 equals "A", 2 equals "B" and so on. The sorting direction takes in a boolean value where true equals ascending order and false equals descending order.

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1') sheet.sort(1, false) // Sorts Sheet1 by Column A in descending order

Making it Easier to Use

It's always good to follow a "DRY" (Don't Repeat Yourself) method when writing code, so here's a function that'll help make it easier for you to make sure of this sorting method throughout your code - regardless of what sheet you're running it against, what column you want to sort by, and it also makes the direction more intuitive by using a regular expression to determine the direction.

function sortSheet(sheet, column, direction) { sheet.sort(column, new RegExp('asc', 'ig').test(direction) ? true : false) }

---

Automatically Sorting Your Range

The tricky part to auto-sorting is the use of an event trigger. There's two ways to create an event trigger, either using a simple trigger (like shown below) or using an installable trigger (Learn more here)

Here's an example of a simple trigger where everything in 'Sheet1' will auto-sort whenever someone makes an edit to it:

function onEdit(e) { const triggeredSheet = e.range.getSheet() const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1') const sortSheet = (sheet, column, direction) => { sheet.sort(column, new RegExp('asc', 'ig').test(direction) ? true : false) } if (triggeredSheet.getName() == sheet.getName()) { sortSheet(sheet, 2, 'Ascending') } }

You might have noticed that I embedded the sortSheet function into the onEdit function. You could just have it outside of the onEdit function if you want, I just did it like this to keep the code clean for this example.

To test or run this code, make sure to do what the user would do since this code utilizes the onEdit Simple Trigger. See our Event Triggers Tutorial Video for step-by-step instructions on how to use event triggers.