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?
Serge insas :
The array.sort method can have a function argument to choose on what part you want to sort. Code goes like this :\n array.sort(function(x,y){\n var xp = x[3];\n var yp = y[3];\n// in this example I used the 4th column... \n return xp == yp ? 0 : xp < yp ? -1 : 1;\n });\n\n\nEDIT\nFollowing your comment, here is a small demo function that should help to understand how this works.\nInstead of using short form if/else condition I used the traditional form and splitted it in 3 lines to make it easier to understand.\nfunction demo(){\n // using a full sheet as array source\n var array = SpreadsheetApp.getActive().getActiveSheet().getDataRange().getValues();\n Logger.log('Unsorted array = '+array);\n array.sort(function(x,y){\n// in this example I used the 4th column... \n var compareArgumentA = x[3];\n var compareArgumentB = y[3];\n // 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) \n // another example x[0].toLowerCase() and y[0].toLowerCase() would do the comparison without taking care of letterCase...\n Logger.log('compareArgumentA = '+compareArgumentA+' and compareArgumentB = '+compareArgumentB);\n var result = 0;// initialize return value and then do the comparison : 3 cases\n if(compareArgumentA == compareArgumentB ){return result }; // if equal return 0\n if(compareArgumentA < compareArgumentB ){result = -1 ; return result }; // if A<B return -1 (you can change this of course and invert the sort order)\n if(compareArgumentA > compareArgumentB ){result = 1 ; return result }; // if a>B return 1\n }\n );\n Logger.log('\\n\\n\\nSorted array = '+array);\n}\n\nI added a couple of Logger.log to check starting, intermediate and final values. Try this in a spreadsheet.\nHoping this will help.",
2014-10-10T16:28:50
Anubhav Yadav :
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.\n\nvar ss = SpreadsheetApp.getActiveSpreadsheet();\nvar sheet = ss.getSheets()[0];\nvar range = sheet.getRange(\"A1:C7\");\n\n// Sorts by the values in the first column (A)\nrange.sort(1);\n\n// Sorts by the values in the second column (B)\nrange.sort(2);\n\n// Sorts descending by column B\nrange.sort({column: 2, ascending: false});\n\n\nI found this in Google Documentation",
2019-04-22T20:51:46