Google Sheets Scripting sort by column then sort by column 2

Question

I'm trying to sort a range (3 columns total) by column 2 (Star grade) then by column 3 (level to level) then by column 1 (Bot), here's the current script I'm using to get it to sort by just column 2:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("DarkestSpawn");
var range = sheet.getRange("AD12:AF51");
var sortCol=1; // 0 for first column and 1 for second column and so on...
var asc=false; // 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
};

This is example of a before, then after I want to do:

Before After

Ranges have a sort method.

Robin Gertenbach
– Robin Gertenbach

2017-10-25 17:10:19 +00:00
Commented Oct 25, 2017 at 17:10 — Robin Gertenbach
– Robin Gertenbach, Commented Oct 25, 2017 at 17:10

Ed Nelson · Accepted Answer · 2017-10-27 01:42:47Z

2

Here it is for your specific sheet and range. I also am attaching a new test spreadsheet.

function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("DarkestSpawn");  
var sheet=e.source.getActiveSheet().getName() 
var range=s.getRange("AD12:AF51")
var col =e.range.getColumn() 
var actRow=e.range.getRow() 
var sortFirst=30
var sortSecond=31
var sortThird=32
var sortFirstAsc=true
var sortSecondAsc=false
var sortThirdAsc=false

if(sheet=="DarkestSpawn" && col==30 && actRow >= 12 && actRow <= 51 || sheet=="DarkestSpawn" && col==31 && actRow >= 12 && actRow <= 51 ||sheet=="DarkestSpawn" && col==32 && actRow >= 12 && actRow <= 51){
    range.sort([ {column: sortSecond, ascending: sortSecondAsc},{column: sortThird, ascending: sortThirdAsc},{column: sortFirst, ascending: sortFirstAsc}]);  
}}

Here is my test spreadsheet. https://docs.google.com/spreadsheets/d/1LGdDsmk9_ciZ9fXZ-KjxcOrJspadNGU9dONhrkQytQM/edit?usp=sharing

edited Oct 27, 2017 at 1:42

answered Oct 25, 2017 at 17:46

Ed Nelson

10.4k2 gold badges32 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

DarkestSpawn Over a year ago

I have no idea how to convert that to look at the specific range of AD12:AF51. When I look at what you put, what I'm understanding is you tell it where to stop for row and column then to start from the very first column (Column 1) and move forward. I apologize if I'm misunderstanding your code. I need to only look at the range of AD12:AF51 because there is stuff above and below that I don't want sorted.

DarkestSpawn Over a year ago

Okay so I tried the re-edit you have. Get and error: "TypeError: Cannot read property "source" from undefined. (line 4, file "AutoSortResearch")" So I changed it to:

DarkestSpawn Over a year ago

function onEdit(e) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var s = ss.getSheetByName("DarkestSpawn"); var sheet = s.getRange("AD12:AF51") var col = sheet.getLastColumn() var sortFirst=30 var sortSecond=31 var sortThird=32 var sortFirstAsc=true var sortSecondAsc=false var sortThirdAsc=false

DarkestSpawn Over a year ago

if(sheet=="DarkestSpawn" && col==30 && actRow >= 12 && actRow <= 51 || sheet=="DarkestSpawn" && col==31 && actRow >= 12 && actRow <= 51 ||sheet=="DarkestSpawn" && col==32 && actRow >= 12 && actRow <= 51){ sheet.sort([ {column: sortSecond, ascending: sortSecondAsc},{column: sortThird, ascending: sortThirdAsc},{column: sortFirst, ascending: sortFirstAsc}]); }}

Ed Nelson Over a year ago

@DarkestSpawn Try it now. Fixed both above script and shared spreadsheet. Sorry about that.

DarkestSpawn · Accepted Answer · 2017-10-27 03:06:55Z

2

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("DarkestSpawn");
var range = sheet.getRange("AD12:AF51");

function onEdit(e)  {
  range.sort([{column: 31, ascending: false}, {column: 32, ascending: false}, {column: 30, ascending: true}]);
}

This is what I ended up with that works. After many trials and errors, re-reading the sort reference and the above code I got to this and it finally worked.

THANKS!!!!

answered Oct 27, 2017 at 3:06

DarkestSpawn

411 silver badge5 bronze badges

Collectives™ on Stack Overflow

Google Sheets Scripting sort by column then sort by column 2

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related