Sort Google Spreadsheet With Multiple Criteria Using Script

Sort Google Spreadsheet With Multiple Criteria Using Script

Learn how to programmatically sort data in Google Sheets based on multiple columns using Google Apps Script, providing advanced control beyond standard sorting features.

Google Sheets offers powerful built-in sorting capabilities, but sometimes you need more dynamic or complex sorting logic that isn't directly available through the UI. This is where Google Apps Script comes in handy. By writing a custom script, you can define precise sorting rules based on multiple columns, in any order (ascending or descending), and even incorporate conditional logic. This article will guide you through creating a Google Apps Script to sort your spreadsheet data with multiple criteria.

Understanding Google Apps Script for Sorting

Google Apps Script is a JavaScript-based language that lets you extend the functionality of Google Workspace applications like Google Sheets. For sorting, the Range object provides a sort() method. This method is highly versatile and can accept an object or an array of objects to define sorting criteria. Each object in the array specifies a column index and a boolean indicating the sort order (true for ascending, false for descending).

flowchart TD
    A[Start: User Triggers Sort] --> B{Get Active Spreadsheet & Sheet}
    B --> C{Define Data Range to Sort}
    C --> D{Define Sorting Criteria}
    D --> E[Apply Sort Method to Range]
    E --> F[End: Data Sorted]

Flowchart of the Google Apps Script sorting process

Basic Multi-Criteria Sorting Script

Let's start with a basic example. Imagine you have a list of products and you want to sort them first by 'Category' (ascending) and then by 'Price' (descending) within each category. Your data might look like this:

To achieve this, we'll write a script that targets the relevant columns. Remember that column indices are 1-based in Google Apps Script.

function sortDataByMultipleCriteria() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  
  // Assuming your data starts in A1 and has headers
  // Get the data range, excluding the header row
  var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
  
  // Define sorting criteria:
  // 1. Sort by 'Category' (column 2) in ascending order
  // 2. Then by 'Price' (column 4) in descending order
  range.sort([
    {column: 2, ascending: true},  // Category (Column B)
    {column: 4, ascending: false} // Price (Column D)
  ]);
  
  SpreadsheetApp.flush(); // Ensure all pending changes are applied
  Logger.log('Data sorted successfully!');
}

Adding a Custom Menu to Trigger the Sort

To make your script easily accessible, you can add a custom menu item to your Google Sheet. This allows users to run the sort function with a single click, without needing to open the script editor.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Sort')
      .addItem('Sort Products', 'sortDataByMultipleCriteria')
      .addToUi();
}

Advanced Sorting Considerations

You can extend this concept further:

• More Criteria: Simply add more objects to the array passed to range.sort(). The order in the array determines the priority of the sort.
• Dynamic Ranges: Instead of hardcoding sheet.getRange(2, 1, ...), you could use sheet.getDataRange() and then adjust it if you have headers, or even prompt the user for a range.
• Conditional Sorting: For very complex scenarios, you might need to fetch the data into an array, sort the array using JavaScript's Array.prototype.sort() with a custom comparison function, and then write the sorted data back to the sheet. This offers the most flexibility but is also more resource-intensive for large datasets.

1. Open Google Apps Script Editor

In your Google Sheet, go to Extensions > Apps Script.

2. Paste the Code

Delete any existing code (Code.gs file) and paste the sortDataByMultipleCriteria() and onOpen() functions into the script editor.

3. Save the Script

Click the floppy disk icon (Save project) or File > Save.

4. Refresh Your Spreadsheet

Close and reopen your Google Sheet. You should now see a new menu item called 'Custom Sort' in your spreadsheet menu bar.

5. Run the Sort

Click Custom Sort > Sort Products to execute your multi-criteria sort.