Google sheets auto sort script (Easy)

Sorting data is a very common task when working with spreadsheets. It is an essential part of data analysis. While sorting manually is straightforward, sorting them automatically requires some level of scripting.

In this tutorial, you will learn about google sheets auto sort script. Also, you will learn how to sort the data in the sheet at the “On change” event.

Table of content

• Auto sort using formula
• Auto sort using a script
• Auto sort multiple columns using a script

Google sheets auto sort using formula

You can use the sort() function to auto sort a range of data in google sheets. 

Syntax:

SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])

Input:

range – The range of data that needs sorting

sort_column – Index of the column containing the values by which to sort. The column can be in the selected range of data or it can be outside. 

is_ascending – TRUE or FALSE indicating whether to sort in ascending order or in descending order.

sort_column2, is_ascending2 … Additional columns and sort order flags beyond the first, in order of precedence (OPTIONAL)

Notes:

• A range is sorted only by the specified columns, other columns are returned in the order they originally appear.
• A range specified as a sort_column must be a single column with the same number of rows as a range.

Here is a demo showing how to auto sort using the sort() function.

We have the below data which we want to sort by shipping time.

To auto sort this data, you have to use the function at a place other than where your data is.

You can use this formula in sheet 2 or an empty area in sheet 1.

I will use sheet 2.

In sheet 2, cell A1, write the formula

=sort(Sheet1!A:C, 3, FALSE)

This will sort the data automatically and create a copy in sheet 2. Here is the result:

Here we are sorting icing column C, so the index parameter is 3. We are sorting the data in descending order, so the is_ascending parameter is set to false.

This is how you will sort data using the auto sort formula. 

Google sheets auto sort script

Let’s see how to auto sort data using a custom script.

First, navigate to Extensions in the menu bar and click on App script. This will bring up the script editing window. 

Copy and paste the below code into your script editor.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");             // SHEET NAME
var range = sheet.getRange("A2:Z");                  // RANGE TO SORT
 
function onEdit(e)  {
 range.sort([{column: 3, ascending: true}]);        // COLUMN NUMBER TO SORT
}

First, let me explain the code.

The first line is to define a variable and set it to the active spreadsheet. Second-line is to get the name of the sheet where your data lies. The third line is to define a range variable and set it to the range of your data.

Next, we will create a function called onEdit which will observe any change in the data set and execute the logic inside the curly brackets. 

Inside the curly bracket, we have the “range.sort” method which works similarly to the SORT() function we just discussed.

In the “range.sort” method, we have passed an index of the column by which we want to sort. The other parameter is to define if we want to sort by ascending or descending.

Now click on the Save button in the script editor menu. This will enable the Run button. Click on the Run button to execute the script.

This might ask for some permissions. Since it’s your private script, go ahead and allow all.

Once the “Execution completed” message appears on the Execution log section, you can go to your sheet and see the data has been sorted by the logic defined in the script.

Go ahead and try changing anything in column C and you will notice the data will sort automatically. 

So this is how Google sheets auto sort script works. 

Google sheets auto sort multiple columns

Let’s see how to auto sort multiple columns in google sheets.

Here is an example data set.

We need to first sort this by Category and then by Revenue.

The final result will have categories listed in alphabetical order and then the Items will be sorted by revenue from high to low.

The final output should look like this:

To do this using script, follow the below script.

// Define the order by which you want to sort
SORT_ORDER = [
{column: 1, ascending: false},  // index 1 for category column
{column: 3, ascending: true} // Index 3 for Revenu column
];
// On sheet edit, apply the function multiSortColumns()
function onEdit(e){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1"); // Get the working sheet name
var range = sheet.getRange("A2:C7"); // Get the range of data
range.sort(SORT_ORDER);
ss.toast('Done sorting.');
}

Here I have explained each line of code within the comments. 

Copy this code and paste it into your google sheets script editor. Save and then Run the script.

FAQ

Here are some of the frequently asked queries about google sheets auto sort script.

Wrapping up

In this tutorial, we learned how the google sheets auto sort script works. Also, you saw how to sort using one column and multiple columns. 

You can extend the script to do even more advanced sorting in google sheets. So go ahead and try google sheets auto sort script in your worksheet.

Appendix

[1] App script in google sheets – Link

Further Reading

New to google sheets ? Start here