CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
DarkestSpawn
DarkestSpawn

Reputation: 41

Google Sheets Scripting sort by column then sort by column 2

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

Upvotes: 2

Views: 3332

Answers (2)

DarkestSpawn
DarkestSpawn

Reputation: 41

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!!!!

Upvotes: 2

Ed Nelson
Ed Nelson

Reputation: 10259

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

Upvotes: 2

Related Questions