Google Script sort 2D Array by any column

Question

I had a asked an earlier question about retrieving records from a database, here: Retrieving Records from a Google Sheet with Google Script

I'm fairly comfortable with manipulating arrays and creating my own sorting algorithms, but I want to use the existing Array.sort() method to organize the data because of its speed. I'm finding that I can easily use this to sort a 2D array by the first column of data, but I can't find the syntax to sort on a different column of data, other than the first.

The closest that I've found is this: Google Apps Script Additional Sorting Rules. However, these inputs haven't worked for me. Here is what I get for the following code, for my array, tableData:

tableData.sort([{ column: 1}]);

=>TypeError: (class)@4dde8e64 is not a function, it is object. (line 49, file "sortTablebyCol")

tableData.sort([{column: 1, ascending: true}]);

=> TypeError: (class)@4d89c26e is not a function, it is object. (line 50, file "sortTablebyCol")

What is the proper syntax for choosing which column of data to sort on?

score 16 · Accepted Answer · edited Jun 20 '20 at 09:12

The array.sort method can have a function argument to choose on what part you want to sort. Code goes like this :

    array.sort(function(x,y){
      var xp = x[3];
      var yp = y[3];
// in this example I used the 4th column... 
      return xp == yp ? 0 : xp < yp ? -1 : 1;
    });

EDIT

Following your comment, here is a small demo function that should help to understand how this works.

Instead of using short form if/else condition I used the traditional form and splitted it in 3 lines to make it easier to understand.

function demo(){
  // using a full sheet as array source
  var array = SpreadsheetApp.getActive().getActiveSheet().getDataRange().getValues();
  Logger.log('Unsorted array = '+array);
  array.sort(function(x,y){
// in this example I used the 4th column... 
    var compareArgumentA = x[3];
    var compareArgumentB = y[3];
    // eventually do something with these 2 variables, for example Number(x[0]) and Number(y[0]) would do the comparison on numeric values of first column in the array (index0) 
    // another example x[0].toLowerCase() and y[0].toLowerCase() would do the comparison without taking care of letterCase...
    Logger.log('compareArgumentA = '+compareArgumentA+' and compareArgumentB = '+compareArgumentB);
    var result = 0;// initialize return value and then do the comparison : 3 cases
    if(compareArgumentA == compareArgumentB ){return result }; // if equal return 0
    if(compareArgumentA < compareArgumentB ){result = -1 ; return result }; // if A<B return -1 (you can change this of course and invert the sort order)
    if(compareArgumentA > compareArgumentB ){result = 1 ; return result }; // if a>B return 1
    }
            );
  Logger.log('\n\n\nSorted array = '+array);
}

I added a couple of Logger.log to check starting, intermediate and final values. Try this in a spreadsheet.

Hoping this will help.

Thank you for the suggestion, Serge. I looked further into the sheet.getrange(), range.sort() and range.getvalues() documentation as well as the documentation link that you provided. I realized that the difference between what I was doing and what the documentation suggests is shown as below: //What I was originally doing, that gives an error => var tableData = sheet.getDataRange().getValues(); tableData.sort(1); //What actually works => var tableDataRange = sheet.getDataRange(); tableDataRange.sort(1); — nbkincaid, Oct 10 '14 at 18:08
Why would the sort function be able to handle the range and not the array of its values? Do you have any insight to this? — nbkincaid, Oct 10 '14 at 18:14
The sort method you refer to is a [range method](https://developers.google.com/apps-script/reference/spreadsheet/range#sort(Object)), an array is a JavaScript object and has only JavaScript methods... the example I show is pure JavaScript, you can actually do more with it... — Serge insas, Oct 10 '14 at 19:19
Thank you, Serge. That makes sense. I didn't understand that the sort method was a method of the Range class, as you said. Thank you for the code snippet, it works perfectly for me, even though I don't quite understand the syntax of it yet. I will dig into this. — nbkincaid, Oct 10 '14 at 19:43
Yes, it's worth studying that... I learned it some time ago and now I use it all the time ;-) - thanks for accepting (also) — Serge insas, Oct 10 '14 at 19:48
I added a small demo code to show more precisely how it works. — Serge insas, Oct 10 '14 at 23:16

score 1 · Answer 2 · answered Apr 22 '19 at 20:51

It seems if instead of using .getValues , you restrict to .getDataRange then perhaps your original sort code "tableData.sort([{column: 1, ascending: true}]);" can work if you avoid the square bracket.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange("A1:C7");

// Sorts by the values in the first column (A)
range.sort(1);

// Sorts by the values in the second column (B)
range.sort(2);

// Sorts descending by column B
range.sort({column: 2, ascending: false});

I found this in Google Documentation

score 0 · Answer 3 · answered Oct 10 '14 at 18:50

My suggestion is to use a library like underscore.js which has a lot of useful functions to manipulate collections, arrays, map/reduce, sorting etc... Works without a glitch with Google Apps Script. That's the first library I add to any project I start on GAP.

Google Script sort 2D Array by any column

3 Answers3

EDIT

Linked