1,2,3,101
would get sorted to 1,101,2,3
Sat 1 Jan, Mon 3 Jan
would get sorted to Mon 3 Jan, Sat 1 Jan
Implementation
var handySort = new cUseful.Fiddler().handySort;
var handySort = new cUseful.Fiddler().handySort;
1X-tyDMF_iILp3cQ4MMCY0GQwrOPHl8ocKtWhqVuw1u5PG5wMytL6mjOP
Examples
handySort(["21", "2", "11", "1"]);
This gives the (seemingly) wrong sort order of
Simple sort 1,11,2,21
To sort in the right order, we provide the original values on which the display values were based.
handySort(["21", "2", "11", "1"], { values: [21, 2, 11, 1] });
Now the string values given are in the correct order
Simple sort 1,11,2,21
You can use the descending option in any sort as required.
handySort(["21", "2", "11", "1"], { values: [21, 2, 11, 1], descending: true });
result
Descending Values sort 21,11,2,1
But of course you are normally sorting rows of data, not single values.
handySort([ ["a", "b", "11"], ["x", "y", "101"], ["u", "v", "2"] ], { values: [11, 101, 2] });
result
Simple row sort [["u","v","2"],["a","b","11"],["x","y","101"]]
And you probably don’t have a handy column like that.
Instead you are more likely to have 2 tables. One with display values, and another with actual values. In this case you have to signal which column is actually being sorted on. This is done by providing an extract function in the options.
handySort([ ["a", "b", "11"], ["x", "y", "101"], ["u", "v", "2"] ], { values: [ ["a", "b", 11], ["x", "y", 101], ["u", "v", 2] ], extractFunction: function(values, a) { return values[a][2]; } });
result
Whole row sort [["u","v","2"],["a","b","11"],["x","y","101"]]
You can remake the values from the display values.
If you don’t have the original values, but know what type the values are, you can simply remake them in the extract function
handySort(["21", "2", "11", "1"], { extractFunction: function(values, a) { return parseInt(values[a], 10); }
result
Remake values 1,2,11,21
Let’s say that instead of tabular data, you have a row of objects that need to be sorted.
Again the extract function shows how to get the data that is to be sorted on, in this case by referencing the property ‘value’.
handySort([{ name: "a", value: 102 }, { name: "b", value: 2 }, { name: "c", value: 101 }], { extractFunction: function(values, a) { return values[a].value; } });
result
Simple property sort [{"name":"b","value":2},{"name":"c","value":101},{"name":"a","value":102}]
Just like the tabular data, your array of objects may not be in sortable format.
If they’ve been created from DisplayValues they’ll need a guide set of values.
handySort([{ name: "a", value: "102" }, { name: "b", value: "2" }, { name: "c", value: "101" }], { values: [102, 2, 101] }) );
result
result Simple values property sort [{"name":"b","value":"2"},{"name":"c","value":"101"},{"name":"a","value":"102"}]
And just like in the tabular version, you may not have a handy array of ‘real’ values.
You may instead have a different version of the object array containing the real values.
handySort([{ name: "a", value: "102" }, { name: "b", value: "2" }, { name: "c", value: "101" }], { values: [{ name: "a", value: 102 }, { name: "b", value: 2 }, { name: "c", value: 101 }], extractFunction: function(values, a) { return values[a].value; } });
result
Values property sort [{"name":"b","value":"2"},{"name":"c","value":"101"},{"name":"a","value":"102"}]
Dates can cause a lot of headaches for sorting.
Especially since you can’t even transfer them successfully to an add-on without transformation (see Transform dates for add-on transfer). With handySort, dates are no different than other values that have been formatted. As an example, here’s an array with a couple of dates, both raw and formatted.
var dates = [new Date(2015, 11, 12), new Date(2014, 11, 14)]; var formatDates = dates.map(function(d) { return d.toString().slice(0,15); });
Sorting the raw dates works fine
handySort(dates);
result
Simple dates Sun Dec 14 2014 00:00:00 GMT+0000 (GMT),Sat Dec 12 2015 00:00:00 GMT+0000 (GMT)
But depending on how the dates have been formatted, you may get a ‘wrong’ result
handySort(formatDates);
result
Values format dates Sun Dec 14 2014,Sat Dec 12 2015
So dates formatted like this will need guide values.
handySort(formatDates,{ values:formatDates.map(function (d){ return new Date(d); })});
result
Values format dates Sun Dec 14 2014,Sat Dec 12 2015
This is fine if you have both the original and the display format.
But if you don’t have the originals, you can still recreate them even though you don’t know which format the dates are actually in!
handySort(formatDates,{ values:formatDates.map(function (d){ return new Date(d); })});
result
Hack format dates Sun Dec 14 2014,Sat Dec 12 2015
Using Fiddler
All this is even easier if you are using Fiddler to manipulate tabular data.
Let’s create some tabular data containing dates
of the format you might get back from getDisplayValues() and getValues() of a Range.
var data = [ ["date","name","value"], [new Date(2015, 11, 12), "a", 1], [new Date(2014, 11, 14), "b", 101], [new Date(2013, 9, 11), "c", 2] ]; var formattedData = data.map (function (d,i) { return i ? [d[0].toString().slice(0,15),d[1],d[2].toString()] : d; });
Now create a fiddler for each of the formatted and unformatted data.
var dataFiddler = new Fiddler().setValues (data); var formatFiddler = new Fiddler().setValues (formattedData);
A simple sort on the date column of the data will of course give the correct order.
dataFiddler.sort("date"));
Result
[{"date":"2013-10-10T23:00:00.000Z","name":"c","value":2},{"date":"2014-12-14T00:00:00.000Z","name":"b","value":101},{"date":"2015-12-12T00:00:00.000Z","name":"a","value":1}]
But sorting the formatted fiddler will give the ‘wrong’ answer
formatFiddler.sort("date"));
result
[{"date":"Fri Oct 11 2013","name":"c","value":"2"},{"date":"Sat Dec 12 2015","name":"a","value":"1"},{"date":"Sun Dec 14 2014","name":"b","value":"101"}]
Fiddler can use a second fiddler as a guide
to sort the formatted data (the second argument specifies descending order)
formatFiddler.sort("date",false,dataFiddler));
result
[{"date":"Fri Oct 11 2013","name":"c","value":"2"},{"date":"Sun Dec 14 2014","name":"b","value":"101"},{"date":"Sat Dec 12 2015","name":"a","value":"1"}]
Converting back to tabular form
The data returned is in the right format to be used as input to a Fiddler, which you can then use to convert to tabular format, like this
new Fiddler() .setData(formatFiddler.sort("date",false,dataFiddler)) .createValues();
result
Back to values [["date","name","value"],["Fri Oct 11 2013","c","2"],["Sun Dec 14 2014","b","101"],["Sat Dec 12 2015","a","1"]]
Motivation
In my Dicers add-on, I need to show the displayValues for filtering in the Dicer visualization (to be able to work to the same level of accuracy as the spreadsheet format is set to), but I also need the Dicer to show the selectable values in a sensible order. I can’t know the type of the original values as it could be any spreadsheet, so using this technique, just one extra line of code in my app, works well.
Before | After |
The code
You’ll find the Fiddler code in the cUseful library, or on github.
cUseful is at
1X-tyDMF_iILp3cQ4MMCY0GQwrOPHl8ocKtWhqVuw1u5PG5wMytL6mjOP
More on this topic available here
- A functional approach to fiddling with sheet data
- Unique values with data fiddler
- More sheet data fiddling
- Fiddling with text fields that look like dates
- A functional approach to updating master sheet
- Populating sheets with API data using a Fiddler
- Header formatting with fiddler
- Formatting sheet column data with fiddler
- Styling Gmail html tables
- Sorting Google Sheet DisplayValues
or you can get the source from github.
The handySort code is as follows.
function handySort(displayValues, options) { // default comparitor & extractor options = options || {}; var descending = options.descending || false; var defaultExtract = function(values, a) { return values[a]; }; var extractFunc = options.extractFunction || defaultExtract; var compareFunc = options.compareFunc || function(a, b) { return a > b ? 1 : (a === b ? 0 : -1); }; // allow regular sorting too var values = options.values || displayValues; if (displayValues.length !== values.length) { throw 'value arrays need to be same length'; } return displayValues.map(function(d, i) { // make an array of indices return i; }) .sort(function(a, b) { // sort the according to values the point to return compareFunc( extractFunc(values, descending ? b : a), extractFunc(values, descending ? a : b) ); }) .map(function(d) { // reorder the tartget array according to index on the values return displayValues[d]; }); }