You can do the sorting at array level, just get data from the sheet to a matrix and sort the matrix in multiple passes choosing the column you want to sort on.
something like this :
function test(){
sortSheetOnColumn(2,3)
}
function sortSheetOnColumn(col1,col2){
var sh = SpreadsheetApp.getActiveSheet();
var data = sh.getDataRange().getValues();// get all data
var header = data.shift();
data.sort(function(x,y){ // Note: sort method changes the original array
// var xp = Number(x[col2-1]);// use these to sort on numeric values
// var yp = Number(y[col2-1]);
var xp = x[col2-1].toLowerCase();// use these for non-numeric values
var yp = y[col2-1].toLowerCase(); // I used toLowerCase() for my use case but you can remove it or change it to whatever you need
Logger.log(xp+' '+yp); // just to check the sort is OK
return xp == yp ? 0 : xp < yp ? -1 : 1;// sort on column col ascending
});
data.sort(function(x,y){ // Note: sort method changes the original array
// var xp = Number(x[col1-1]);// use these to sort on numeric values
// var yp = Number(y[col1-1]);
var xp = x[col1-1].toLowerCase();// use these for non-numeric values
var yp = y[col1-1].toLowerCase();//
Logger.log(xp+' '+yp); // just to check the sort is OK
return xp == yp ? 0 : xp < yp ? -1 : 1;// sort on column col ascending
});
// and at the end take back the headers
data.unshift(header);
sh.getDataRange().setValues(data);
}
or better, following Adam's comment :
function sortSheetOnColumn2(col1, col2) {
var sh = SpreadsheetApp.getActiveSheet();
var data = sh.getDataRange().getValues();// get all data
var header = data.shift(), x1, y1, x2, y2;
col1--;
col2--;
data.sort(function(x, y) {
x1 = x[col1].toLowerCase();
y1 = y[col1].toLowerCase();
x2 = x[col2].toLowerCase();
y2 = y[col2].toLowerCase();
return x1 == y1 ? (x2 == y2 ? 0 : x2 < y2 ? -1 : 1) : x1 < y1 ? -1 : 1;
});
data.unshift(header);
sh.getDataRange().setValues(data);
}
but Michael's answer if more clever using build in Range.sort method that I was not aware of (at least of its extended possibilities).
