Saturday, August 10, 2013

Google Spreadsheet Auto Sort Range on Edit

Question:

( by Chance Carnahan )


I have a spreadsheet that is basically a leaderboard for a game my school is playing. The teams are sorted from 1st to last by the number of points they have. When I change the number of points that one team has; however, it doesn't re-sort the teams to put them in the new correct order. Is there any way that I can make the spreadsheet do this automatically? I don't want to have to highlight everything and click re-sort each time a team scores a point.

Solution:

Have a look at the following animated screenshot:




Have a look at the following code:

///////////////////////////////////////

// This script has been developed by Kishan.
// For more visit http://iGoogleDrive.blogspot.com

var sortCol=0; // 0 for first column and 1 for second column and so on...
var asc=true; // set variable asc to false for descending sort

function onEdit(e) {
  //Use any one or both of the following two:
  //sortNamedRange("myRange");
  sortRange("Sheet1","B5:E15");
};

function sortRange(sheetName,rangeName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sortSheet = ss.getSheetByName(sheetName);
  var range = sortSheet.getRange(rangeName);
  var activeSheet = ss.getActiveSheet();
  var activeRange = activeSheet.getActiveRange();
  var sortedValues;
  if( sortSheet.getName() == activeSheet.getName() &&
      activeRange.getLastRow() >= range.getRow() && 
      activeRange.getRow() <= range.getLastRow() &&
      activeRange.getLastColumn() >= range.getColumn() && 
      activeRange.getColumn() <= range.getLastColumn() )
      { 
        sortedValues=range.getValues().sort(mySortFunction);
        range.setValues(sortedValues);
      }
};

function sortNamedRange(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var namedRange = ss.getRangeByName(name);
  var namedSheet = namedRange.getSheet();
  var activeSheet = ss.getActiveSheet();
  var activeRange = activeSheet.getActiveRange();
  var sortedValues;
  if( namedSheet.getName() == activeSheet.getName() &&
      activeRange.getLastRow() >= namedRange.getRow() && 
      activeRange.getRow() <= namedRange.getLastRow() &&
      activeRange.getLastColumn() >= namedRange.getColumn() && 
      activeRange.getColumn() <= namedRange.getLastColumn() )
      { 
        sortedValues=namedRange.getValues().sort(mySortFunction);
        namedRange.setValues(sortedValues);
      }
};

var mySortFunction = function(a,b) {
  try{x=a[sortCol].toLowerCase();
      y=b[sortCol].toLowerCase();}
  catch(e){x=a[sortCol];y=b[sortCol];}
  return (x>y)?(asc?1:-1):(x<y)?(asc?-1:1):0

};

///////////////////////////////////////

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet.


Set the sorting order either true (for ascending) or false (for descending) order.
Provide the Sheet name and Range (I have marked it with green color, so that you can quickly edit it).
You can also use sortNamedRange function if you have given a name to that range that you need to sort.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

10 comments:

  1. Hy, it's working but if I have sum formula in column I wish to sort by, script removes the formula from cell.
    How to avoid this?

    Thanks in advance,
    Luko

    ReplyDelete
    Replies
    1. Did you ever get a response to this? Im looking for the same answer

      Delete
    2. I need answer for this too

      Delete
    3. Still wondering how to implement this sort script and not have the built in formula SUM removed. May need a function created to replace SUM but how would that be implemented?

      Delete
  2. This is great Kishan... .I want the spreadsheet to have blank rows at the bottom so that I can add additional data over time and have it auto-sort. But the script sorts all of those blank rows to the top. How would I change it so that the blank rows stay at the bottom?

    ReplyDelete
    Replies
    1. Did you ever figure this out?

      Delete
    2. mate in the fourth row change
      var asc=true
      to
      var asc=false

      Delete
  3. Thanks for this, you saved a lot of time for me

    ReplyDelete