Sort and randomize data in Google Sheets with Apps Script

Sort data in google sheets using Google Apps Script

You might want to sort some Google Sheet data based on a condition in your Google Apps Script or simply sort by selected columns and rows after inserting a new row.

If you are always building sample data for tutorials like me, you might also be interested in randomising your data programmatically.

Fortunately, Google Apps Script’s SpreadsheetApp Class has built-in methods to handle just that for you. Plus it’s super easy to implement.

Check out the video tutorial below or grab one of the code snippets to add into your own project.

The Video

The example data

Things are much easier to understand with an example. Take a look at our sample data below:

Randomized data created using Google Apps Script

Here we have 4 columns of data containing:

  1. ID
  2. Date
  3. Time
  4. Location

The Sort Google Apps Script Code

Sorting Google Sheet data in Apps Script requires a single line of code using the sort() method of the SpreadsheetApp range class. However, there are a number of variations to how you can apply the sort method. Let’s go through them now.

Set up the function

Let’s set up a basic function to grab our example data. We do this by first grabbing the active spreadsheetLine 7

Next, we can select the sheet our data is on with the getSheetByName() method. Here we have added our spreadsheet name, “Sheet1”,  as our argument.

Finally, we need to select the range of our data. We know it is going to be between A2 and D24 and in our example this range won’t change. So we use the getRange() method to select this range. This method allows us to use the range class and this class contains the sort method.

Note that we exclude the header row here in our range. We don’t want to sort that.

Sort range by a single column – ascending

By default, the sort method will sort a range in ascending order. Let’s say we want to order our data by the ID column our script would look like this.

In this example, you can see that the sort method takes the first column in the range of its argument. Keep in mind that if our range was, say, C2:F24 then column C would be 1.

Our script would now look like this:

Example data for Google Apps Script sort tutorial

Likewise, if we were to sort by date, then our sort method would look like this:

range.sort(2) // Date

And our sheet would look like this:

Sort by the second column in a range in Google Sheets with Apps script

Sort range by multiple columns – ascending

We can sort ranges by multiple columns in ascending order by providing the sort() method with an array containing the columns we wish to sort.

Let’s sort by the ‘Location’ and ‘Date’ rows. Our method will now look like this:

range.sort([4, 2]) // Location and then date 
So now our data looks like this:
Sort by column 4 and 2 ascending with Google Apps Script
It is important to note that whatever comes first in the sort is sorted first. So our range is now sorted by groups of locations followed by the date from latest to most recent for each group.
If we were to swap our sort values around we would sort by ‘date’ first like this:
range.sort([2, 4]) // Location and then date 
Sort by column 2 and 4 ascending with Google Appps Script

Of course, you can also add more than 2 sort items to your sort.

Sort by a single column in descending order

To sort by a column in descending order in Google Apps Script we need to swap out the single column number and replace it with an object. Objects contain key-value pairs of data inside curly braces.

For the sort method, the key-value pairs are:

  • column: (number) – The column number you wish to sort by.
  • ascending: (boolean) – Set to false for descending.

Let’s sort the ‘Time’ column from the latest to the earliest time or in descending order. Our sort method  will now look like this:

range.sort({column: 3, ascending:false}) // Time descending
Which would look like this:
Sort a single row descending apps script

 

Sort by multiple columns in ascending and descending order

To sort multiple columns in our Google Sheet range in descending order, we need to bring back our array. We can then insert the object for each column to sort in this array.

Why don’t we sort the location and date again, but this time in descending order for both columns:

range.sort([{column:4, ascending: false}, {column:2, ascending: false}]) // Location desc, Date desc

 

Giving us this result:

Sort by column 4 and 2 descending with Google Apps Script

Alternatively, if we want to mix columns in different ascending and descending order we can do so. Let’s keep our location column in ascending order and keep those dates descending.

range.sort([{column:4, ascending: true}, {column:2, ascending: false}]) // Location asc, Date desc

Sort by column 4 ascending and 2 descending with Google Apps Script

We can also shorthand our ascending order items by simply providing a number. So our sort method above could also look like this:

range.sort([4, {column:2, ascending: false}]) // Location asc, Date desc

Let’s do one last sort. Here we will sort by the location column in descending order followed by both the date and the time in ascending order.

range.sort([{column:4, ascending: false}, 2, 3]) // Location asc, Date desc

Resulting in this:

Sort by column 4 descending 2 ascending 3 ascending with Google Apps Script

Randomise a range of data in Google Sheets with Apps Script

The range class also has a neat randomize() method, which is great for building sample data.

Just like in our sort method, to randomise our range we need to get the range to randomise and then apply the randomize() method to it. This method doesn’t take any arguments so it is really easy to apply.

Check out the randomise function below:

Give it a try!

Bonus! Create  A Sort Macro in Google Sheets and Review the Script

You can use the Google Sheets macro tool to record actions in Google Sheets. This means that we don’t have to code our sort function from scratch if we don’t want to.

To do this, select the range (e.g. A2:D24) and then go to Extensions > Record Macro.

Create a sort macro in Google Sheets

Your actions in Google Sheets will now be recorded.

Go to Data > Sort range > Advanced range sorting options.

Sorting the range while the macro is in record mode

A dialogue window will pop up.

Select the Sort by as Column BClick Add another sort column.

Select Column C for then by and check the Z-A radio button.

Select Sort.

advanced sorting in Google Sheets

Next, select Save in the Macro recorder.

Save the Google Sheets Macro action

Another dialogue will pop up. Here you can name the macro and provide a keyboard shortcut for it.

Name and create a shortcut for the macro

You’ll now find the macro in Extensions > Macros. 

Macro is now available

The macro conveniently generates a Google Apps Script function that you can review in the Apps Script IDE under the Macro.gs file.

macro saved in Google Apps Script

Conclusion

That’s all there is to sort data in Google Sheets with Apps Script. Conveniently easy stuff.

I’m always interested in hearing how folk apply different scripts to their own projects. Feel free to share how you would integrate this into your own script in the description below. You might just inspire someone with your use case.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Leave a Reply

%d bloggers like this: