85

Let's say I want Google Sheets to automagically sort some data, for example column C.

How do I do that?

I know that I can manually sort data by right-clicking and selecting Sort data, but this is not what I'm looking for.

10 Answers 10

69

You can use the sort() function for that, but you have to have your data in one place, and an automatically sorted copy of that data in another place.

For example, say I have Sheet1 with my data:

   | A    | B    | C
=====================
 1 | This | this | 2
 2 | Is   | is   | 1
 3 | Test | test | 3

Then in Sheet2, cell A1, I would put this function:

=sort(Sheet1!A:C, 3, TRUE)

This would show my data, but sorted by column C (the third column), ascending.

   | A    | B    | C
=====================
 1 | Is   | is   | 1
 2 | This | this | 2
 3 | Test | test | 3
4
  • Parameters must be separated by semicolon ; e.g. = sort(Sheet1!A:C; 3; TRUE)
    – user17634
    Feb 25, 2012 at 12:20
  • 3
    @Petr It works just fine for me with the commas. Feb 27, 2012 at 2:47
  • 8
    @user17634: That depends on your locale settings. Nov 12, 2013 at 13:35
  • The problem with this approach is that you cannot move item #3 to item #1 position (it duplicates #3).
    – toobulkeh
    Jan 7, 2021 at 22:18
61

It is also possible to use Google Apps Scripts to achieve automatic in-place sorting of the data.

This may be more difficult to achieve and is more error-prone (I'd still go for William Jackson's solution, +1 BTW), but I thought it was interesting enough to show.

I have a sheet that looks like this:

auto-sorting sheet

I added a new script using these steps:

  1. In the menu, go to Tools -> Script Editor...

  2. Select Create a new project

  3. In the empty code window that appears, paste the following code which will be run automatically whenever a cell is edited:

    function onEdit(event){
      var sheet = event.source.getActiveSheet();
      var editedCell = sheet.getActiveCell();
    
      var columnToSortBy = 4;
      var tableRange = "B3:E9";
    
      if(editedCell.getColumn() == columnToSortBy){   
        var range = sheet.getRange(tableRange);
        range.sort( { column : columnToSortBy } );
      }
    }
    
  4. Go back to the sheet and start playing around with the values to see the table sort automatically each time

Note:

In the above script:

  • The value 4 represents the index of column D (the Value column - the one that's going to be sorted)
  • The value "B3:E9" represents the table range (excluding the header row)

Your table will most likely differ from mine, so these values should be adjusted accordingly.

11
  • I tried yours [edited] function onEdit(event){ var sheet = event.source.getActiveSheet(); var editedCell = sheet.getActiveCell(); var columnToSortBy = 4; var tableRange = "A2:F99"; if(editedCell.getColumn() == columnToSortBy){ var range = sheet.getRange(tableRange); range.sort( { column : columnToSortBy } ); } } but keep getting an error: TypeError: Cannot read property "source" from undefined. (line 2) which is the following code: var sheet = event.source.getActiveSheet(); any ideas? NM, only get the error when running from the script page, but it works properly on the actual spreadshee
    – drizzt09
    May 3, 2012 at 14:00
  • but keep getting an error: TypeError: Cannot read property "source" from undefined. (line 2) which is the following code: var sheet = event.source.getActiveSheet(); any ideas? NM, only get the error when running from the script page, but it works properly on the actual spreadsheet. Now question... its sorting A-Z, how do I change it to auto-sort Z-A? thanks
    – drizzt09
    May 3, 2012 at 14:00
  • @drizzt09 When you call the method you probably specify null for the event parameter, which is otherwise properly populated by the Google Spreadsheet infrastructure when a real event is triggered. May 3, 2012 at 15:52
  • 4
    @drizzt09 to change the sort order, change the range.sort line like this: range.sort( { column : columnToSortBy, ascending: false } ); . For more sorting options see developers.google.com/apps-script/class_range#sort May 3, 2012 at 15:54
  • 1
    If you want a specific sheet instead of applying the sort to every sheet, replace the first function line with this: var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Income");
    – Shygar
    Feb 23, 2020 at 18:05
15

Another option without a script is:

=QUERY(A1:C3,"SELECT * ORDER BY C")  

The range is restricted (A1:C3) because where the order is ascending blank entries will appear first.

2
  • 6
    Could you please let me know where should I put this in?
    – Louis Tran
    Aug 27, 2017 at 5:32
  • 3
    You can use SELECT * WHERE C <> '' ORDER BY C to ignore blank entries, then you can use a large enough range to include all current and future rows.
    – Guss
    Dec 27, 2017 at 11:39
13

Here's a generic script that will autosort based on the 1st column, and assumes a Header row.

To create a script:

  • In the menu, go to Tools -> Script Editor...

In the empty code window, paste the following code, which will run automatically whenever a cell is edited:

 // LinkBack to this script:
 // http://webapps.stackexchange.com/questions/7211/how-can-i-make-some-data-on-a-google-spreadsheet-auto-sorting/43036#43036

 /**
 * Automatically sorts the 1st column (not the header row) Ascending.
 */
function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 1;
  var tableRange = "A2:T99"; // What to sort.

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: true } );
  }
}
3
  • 4
    I found I had to var sheet = SpreadsheetApp.getActiveSpreadsheet(); instead of what's here.
    – dldnh
    Jul 23, 2013 at 16:15
  • 2
    This works great, but how can I edit this so it only sorts on a single sheet in doc?
    – moobot
    May 28, 2015 at 10:49
  • doesn't seem to work Feb 19, 2019 at 2:22
5

It can be done using Google App Script. The script will automatically sort whenever there is a change in sheet data.

On your Google Sheet:
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.');
}

Source: https://www.idiotinside.com/2018/06/08/sort-multiple-columns-google-sheets-apps-script/

1
  • Very convenient, plug & play script! Work instantly! Aug 24, 2021 at 2:01
1

Using the script solution, but sort on more than one column

I wanted to sort by a dropdown menu column and then by date.

To do so, modify the "range.sort" line of either of Cristian or geekspotz's code snippets like so:

// Sorts descending by edited column, then ascending by column 1 
// Note the use of an array
range.sort([{column: columnToSortBy, ascending: false}, {column: 1, ascending: true}]);

The difference is adding the straight brackets around your whole statement (array) and separating the sorts by commas.

Sort code modification pulled from Serge's Stack Overflow answer here: Automatic Sorting On Sheets

1

Here i created an auto sorting script and spreadsheet(it require a bit of work on the sheet, a copy paste of the code and setting your ranges. Its based on multiple stackexchange answer

https://docs.google.com/spreadsheets/d/1LDohwCuy8HZg4YCQEOuWmgpY_WG2BVh_aH-i4JGM5BE/edit#gid=0

Its record and gets the value of the range you focus on at last edit compare it to value now after actual edit and if different it sort the table.

0

Google Sheets has Filters for this very reason and doesn't require you to make a new sheet in your document.

Documentation: https://support.google.com/docs/answer/3540681

1
  • 1
    To me, it seems like this abbreviates the process, but doesn't count as "auto-sorting". You still need to click the column header to trigger the re-sort after new data entry. Or maybe I'm missing a trick. Feb 17, 2021 at 18:08
0

This function seems to work for me:

function sortRangeByColumn(range, columnNum, direction) {
  range
  .offset(1, 0, range.getNumRows() - 1) // to ignore the header row https://www.reddit.com/r/GoogleAppsScript/comments/di13bl/google_spreadsheet_script_how_to_sort_a_range_of/f3t6c0h/
  .sort([ // https://webapps.stackexchange.com/a/138495/27487
    { 
      column: columnNum,
      ascending: direction.toUpperCase() === 'ASC'
    }
  ]); // https://webapps.stackexchange.com/a/25570/27487
}
1
  • Since the question isn't about Google Apps Script, please explain how this answers the question and how this script should be used.
    – Rubén
    Aug 1, 2021 at 16:17
0

Easily-configurable multi-column sort that automatically re-runs upon edit

Example workbook: Google Sheets Apps Script for automatic saved multi-column sorting (PUBLIC)

function onEdit(e) {
  applySorting("mainTable");
}

function applySorting(rangeName){   
  // See https://webapps.stackexchange.com/a/158165/27487   
  const sortingRulesRange = getRangeByName(rangeName + "_sorting");
  const dataRange = getRangeByName(rangeName);
  const sortingRulesArray = [];
  const numRows = sortingRulesRange.getNumRows();
  for (let r = 1; r <= numRows; r+=1) {
    const colNum = sortingRulesRange.getCell(r, 1).getValue();
    if (colNum) {
      const direction = sortingRulesRange.getCell(r, 2).getValue();
      Logger.log(direction);
      const ascending = direction.toUpperCase() === 'ASC';
      sortingRulesArray.push({column: colNum, ascending});
    }
  }

  Logger.log(sortingRulesArray);
  dataRange.sort(sortingRulesArray);
  toast("Sorted " + rangeName + ".");
}

function getRangeByName(rangeName) {  
  return SpreadsheetApp.getActiveSpreadsheet().getRangeByName(rangeName);
}

function toast(message) {
  const spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.toast(message);
}

I made the example workbook public so that you can clone (make a copy) of it without even requesting permission from me.

1
  • Since the question isn't about Google Apps Script, please explain how this answers the question and how this script should be used.
    – Rubén
    Aug 1, 2021 at 16:17