7

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?

Improve this question

3 Answers 3

Reset to default
18

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.

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

6 Comments

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);
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?
The sort method you refer to is a range method, 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...
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.
Yes, it's worth studying that... I learned it some time ago and now I use it all the time ;-) - thanks for accepting (also)
|
1

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

Improve this answer

1 Comment

While this doesn't answer the OP question, it is a great add to the question.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.