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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

Kishan.

iGoogleDrive