google sheets - automatically sort multiple columns using apps script

google sheets - automatically sort multiple columns using apps script


Let’s take this sample data below,

FirstName LastName Score
Suresh Kumar 78
Alex 85
Shri Ram 88
Murali Manohar 78
Yuvaraj 90

We’re going to sort the score by descending order then first name and last name in ascending order so that we will get highest score first and names sorted in alphabetical order. The expected output is below,

FirstName LastName Score
Yuvaraj 90
Shri Ram 88
Alex 85
Murali Manohar 78
Suresh Kumar 78


Google sheets provides the ability to sort data of two or more columns in a particular order. We can acheive the same using the sort range option provided in the UI. First we need to select columns then select Data –> Sort range. Now we can select columns accordingly and apply the sorting.

The problem with using the option provided in the UI is that when we keep adding more data to the sheets we have to select the columns again and sort them manually. To avoid this manual effort we can use Google Apps Script that will sort the columns in a specific order everytime new data is added.

Choose Tools –> Script Editor and paste the below script and save.

Replace the SHEET_NAME, SORT_DATA_RANGE and SORT_ORDER variables with corresponding values.


SHEET_NAME = "Sheet1";
SORT_DATA_RANGE = "A1:C999";
SORT_ORDER = [
{column: 3, ascending: false},  // 3 = column number, sorting by descending order
{column: 1, ascending: true}, // 1 = column number, sort by ascending order 
{column: 2, ascending: true}
];

function onEdit(e){
  multiSortColumns();
}
function multiSortColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  var range = sheet.getRange(SORT_DATA_RANGE);
  range.sort(SORT_ORDER);
  ss.toast('Sort complete.');
}