Monday, August 19, 2013

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 ago with A1go with A1go with A1go with A1
modify cgo with C1go with C1go with C1go with C1
modify bgo with B1go with B1go with B1go 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:
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,

7 comments:

  1. Wow Kishan, looks great. I'll let you know how stuff ends in my sheets ;)

    ReplyDelete
  2. How do you increase the range so that the script takes into account for future info, like i need all 1000 rows to be included without all my current data being shifted to the bottom every time i add something in the next row, my current data set goes from A2 to C27 but if i add something in row 28 nothing happens, unless i change the script each time to include the next row. If i make the range from A2 to C1000 and edit something then all my data shifts down to row 973 to 1000. Any help will be greatly appreciated

    ReplyDelete
  3. I am working on a document that I need to have auto sorted every time a new date is entered. Row 1 A-F are the column headers (F being the date column)

    Every time a new date is entered into column F (along with the accompanying information from A-E columns) needs to be sorted according to the most recent date.

    Is there any way you can help me? I have been poking around with the code you posted above and cannot wrap my head around it.

    ReplyDelete
  4. I have basically the same question as the two above posters... I'd like to have my columns sorted without being shifted to the bottom of my anticipated range. Thanks in advance for any help you can give me!

    ReplyDelete
  5. Nevermind, I figured the answer. I used:


    function onEdit(e) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    sheet.sort(7, true);
    }


    where "7" is Column G. I sorted Column B so changed it to "2" My sheet is named "Sheet 1" by default but you would change it to whatever yours is called.

    ReplyDelete
  6. Hi You have the only auto-sort script that has worked for me - thank you for your help! It's working great, only I can't figur eout how to sort for multiple ranges at once. In other words, I'm trying to auto sort four sets of data based on edit. Any ideas how to get that to function correctly using the script editor?

    ReplyDelete