Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Let's say I want Google Spreadsheets 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.

share|improve this question

3 Answers 3

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
share|improve this answer
    
Parameters must be separated by semicolon ; e.g. = sort(Sheet1!A:C; 3; TRUE) –  user17634 Feb 25 '12 at 12:20
1  
@Petr It works just fine for me with the commas. –  William Jackson Feb 27 '12 at 2:47
    
works a treat to have a sorted tab, thanks! –  Jeff Atwood Feb 11 '13 at 21:43
    
@user17634: That depends on your locale settings. –  Vidar S. Ramdal Nov 12 '13 at 13:35

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

  • in the menu, go to Tools -> Script Editor...
  • select Create a new project
  • 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 } );
      }
    }
    
  • 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 on 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.

share|improve this answer
    
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 '12 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 '12 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. –  w0lf May 3 '12 at 15:52
    
@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 –  w0lf May 3 '12 at 15:54
    
@w0lf Thank you that worked perfect. now I would like to add to it or have a seperate function that performs the exact same function but on open or refresh. So when I open/refresh the excel sheet it will auto sort 4th column descending the same as when I edit the code in column 4 with your current code. Thanks –  user19738 May 4 '12 at 18:34

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 } );
  }
}
share|improve this answer
    
I found I had to var sheet = SpreadsheetApp.getActiveSpreadsheet(); instead of what's here. –  dldnh Jul 23 '13 at 16:15

protected by Community May 4 '12 at 18:52

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?