Google Spreadsheet Script to Auto Sort Range after modifying

Question:

( by Herman F )

Hello,

Is it possible to auto-sort a complete row, after modifying a certain cell? Example:

modify a	go with A1	go with A1	go with A1	go with A1
modify c	go with C1	go with C1	go with C1	go with C1
modify b	go with B1	go with B1	go with B1	go with B1

If A2 is modified (wich is modify c in this case), whole row 2 should descend one row below, so you get 'modify a/modify b/modify c' as in row 1/2/3/ accordingly.

Cheers for any help,

Herman.

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: iGoogleDrive.blogspot.com

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
var range = sheet.getRange("A1:E3");
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) {
  sortrange();
};

function sortrange() {  
  var activeSheet = ss.getActiveSheet();
  var activeRange = activeSheet.getActiveRange();
  var sortedValues;
  if( sheet.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);
          }
};

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.

Now, whenever you will edit in the range "A1:E3" of sheet "Sheet1" the above script will automatically execute, and it will give you sorted (ascending by column no. 1) results.


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