Sort and randomize data in Google Sheets with 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:

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 spreadsheet. Line 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:

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 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:

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:

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:

Giving us this result:

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

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:

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.

Your actions in Google Sheets will now be recorded.

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

A dialogue window will pop up.

Select the Sort by as Column B. Click Add another sort column.

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

Select Sort.

Next, select Save in the Macro recorder.

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

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

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

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.