michael j. harr, mba
google sheets multiple column sort script
On this page, you'll find three scripts written for Google Sheets that will allow you to sort multiple columns within a defined range. The first creates a menu upon opening the affected spreadsheet, the second sorts a range using three columns, and the third sorts a range using two columns. This script was created to make sorting a personal budget/cash flow forecast easier.

use case - personal cash flow forecast

Without this script, sorting a cash flow forecast takes a good bit of time. Using only Google Sheets' menu, to sort a cash flow forecast like the one to the right, you'd have to:
  1. Click and drag to highlight the range to be sorted
  2. Click 'Data'
  3. Click 'Sort Range'
  4. Check the 'Data has header row' box
  5. Choose sort by 'Date' ascending
  6. Click to add another sort column
  7. Choose sort by 'Cash In' descending
  8. Click to add another sort column
  9. Choose sort by 'Cash Out' descending
  10. Click the 'Sort' button
The result of this is a sort that mimics a bank's transaction sort, but it takes far too many steps. Instead, this script can be installed - going from 10 steps to two.

the script

To get to a much simpler, less time consuming sort while giving your mouse a break, I created a script that would sort a predefined range in two ways and add menu-level navigation to make it dead-simple. As you will see below, 18 lines of code can save a huge amount of time that would otherwise be wasted. Here's the script with an explanation of the elements below.
​function onOpen(e) {
   SpreadsheetApp.getUi()
       .createMenu('Sort')
       .addItem('Bank Sort', 'BankSort')
       .addItem('Payee/Payor Sort', 'PayeeSort')
       .addToUi();
 }

function BankSort() {
  SpreadsheetApp.getActiveSheet().getRange("B5:E1000").sort({column:4,ascending:false});
  SpreadsheetApp.getActiveSheet().getRange("B5:E1000").sort({column:5,ascending:false});
  SpreadsheetApp.getActiveSheet().getRange("B5:E1000").sort({column:2,ascending:true});
}

function PayeeSort() {
  SpreadsheetApp.getActiveSheet().getRange("B5:E1000").sort({column:2,ascending:true});
  SpreadsheetApp.getActiveSheet().getRange("B5:E1000").sort({column:3,ascending:true});
}

function onOpen

This first function creates a menu item called 'Sort' and has two options displayed under the main menu - 'Bank Sort' and 'Payee/Payor Sort'. Line by line, here's what the code does:
  1. Creates the function 'onOpen' that runs when the affected spreadsheet is opened
  2. Calls up the Google Sheet UI (User Interface) 
  3. Creates a top level menu option named 'Sort' which is added to the end of the preset menu
  4. Adds 'BankSort' function to the 'Sort' menu
  5. Adds 'PayeeSort' function to the 'Sort' menu
  6. Displays the menu and functions in the Google Sheets UI
A couple notes here:  You can change the name of the top level menu by editing line three in the single quotation marks. On 'addItem', the display name is first and the function name is second.

function BankSort

This function sorts a preset range of cells within the active sheet. There are three sorts that accomplish the desired result - transactions sorted by ascending date, descending cash in, and descending cash out. This allows you to view future cash flows in the same order your bank will process the transactions. Here's what the code says:
  1. Creates the function 'BankSort' that runs when the menu option is clicked
  2. Using Google Sheets, get the active sheet, select the range B5:E1000, sort the 4th column of the sheet (Cash Out) in descending order
  3. Using Google Sheets, get the active sheet, select the range B5:E1000, sort the 5th column of the sheet (Cash In) in descending order
  4. Using Google Sheets, get the active sheet, select the range B5:E1000, sort the 2nd column of the sheet (Date) in ascending order
Notice that this is the opposite order that you would normally need to click when using the standard Sort Range options in Google Sheets.

function PayeeSort

The third function sorts a preset range of cells within the active sheet. There are two sorts that create a list of transactions sorted by ascending Payee/Payor Name and ascending Date. This sort makes it easier to edit transaction series when payments, deposits, payees, or payors change. Here's the code breakdown:
  1. Creates the function 'Payee Sort' that runs when the menu option is clicked
  2. Using Google Sheets, get the active sheet, select the range B5:E1000, sort the 2nd column of the sheet (Date) in ascending order
  3. Using Google Sheets, get the active sheet, select the range B5:E1000, sort the 3rd column of the sheet (Payee/Payor Description) in ascending order
Again, notice that this is the opposite order that you would normally need to click when using the standard Sort Range options in Google Sheets.

how to install the script to your google sheet

Step 1:  Click 'Tools' from the main menu
Step 2:  Click 'Script editor' from the sub-menu
Step 3:  Delete all preset code
Step 4:  Copy and paste the code above into the code editor
Step 5:  Name your script
Step 6:  Save your script
Step 7:  Test your script

NOTE:  I use the spreadsheet above with my financial planning clients and all of them have the same layout and generally never have data to row 1000. To customize this basic script to your needs, you'll likely need to change the names, pre-defined range, and sort priorities. Regardless, this is a good template for a script that sorts multiple columns in Google Sheets.

comments, questions, suggestions

If you would like to make a comment, ask a question, or add a suggestion, please use the contact form below. I generally respond via email within one business day and any sensible updates will be added to this page, as needed.