Google Apps Script Additional Sorting Rules

Question

I am working on a Google Apps Script spreadsheet application, and one of the abilities I would like the program to have is to automatically sort a series of form responses based on data from 2 different columns. So I would want to sort it by the data in column 16 and then sort by column 1. I can achieve this functionality manually using the method at: https://drive.googleblog.com/2010/06/tips-tricks-advanced-sorting-rules-in.html

Currently I am running the Spreadsheet.sort(column, ascending) function with the first column, but I cannot make it sort so that it will accept the second column as an additional sorting rule. Is there a method in Google Apps Script that I could use to emulate this functionality?

score 12 · Accepted Answer · answered Apr 07 '13 at 13:38

12

See doc: https://developers.google.com/apps-script/reference/spreadsheet/range#sort(Object)

function sortFormResponses() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // change name of sheet to your sheet name
  var s = ss.getSheetByName("Form Responses");
  var lastCol = s.getLastColumn();
  var lastRow = s.getLastRow();

  // assumes headers in row 1
  var r = s.getRange(2, 1, lastRow - 1, lastCol);

  // Note the use of an array
  r.sort([{ column: 1, ascending: true }, { column: 16, ascending: true}]);

}

answered Apr 07 '13 at 13:38

ScampMichael

3,688
2
16
23

That's first class solution ! I didn't know that method. thanks for the info :-) – Serge insas Apr 07 '13 at 13:50
The new Docs have an abundance of new information and code examples and are well worth a review. Thanks Googlers. – ScampMichael Apr 07 '13 at 14:55

Serge insas · Answer 2 · 2013-04-07T13:54:54.413

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).

I don't know if it would be any more efficient, but I think you could also sort the array with a single function: `data.sort(function(x,y){return x[0] == y[0] ? (x[1] == y[1] ? 0 : x[1] < y[1] ? -1 : 1) : x[0] < y[0] ? -1 : 1;});` — AdamL, Apr 07 '13 at 08:18
Hi Adam, interesting ! but I did it like that to handle more clearly the sort order, ie which column first... it appears to be quite tricky to get a logical result... (well "logical" to my pov ;-) - In your approach what is the order ? — Serge insas, Apr 07 '13 at 08:32
My logic might be flawed Serge, but I was trying to achieve the same order as yours; ie "col1" takes precedence over "col2". So comparing "col2" values should only be required when the corresponding "col1" values are equal. Again, there might be hole in my logic... — AdamL, Apr 07 '13 at 08:43
I tried it on a test sheet but I can't get it to do what I want... could you add a second answer with a complete code if you have some time to spend on that ? here is a [shared ss](https://docs.google.com/spreadsheet/ccc?key=0AnqSFd3iikE3dE12RUNzZ0MyMXN2Yl9VdXpNb0phbEE&usp=sharing) to test, my code is in there — Serge insas, Apr 07 '13 at 08:54
Sure mate, I entered a complete alternative function in your ss for your perusal. — AdamL, Apr 07 '13 at 11:58
Thanks Adam, quite neat I must say ... I'll show it in my answer. — Serge insas, Apr 07 '13 at 13:51

Google Apps Script Additional Sorting Rules

2 Answers2

Linked