Sorting Google Sheet DisplayValues

If you use getDisplayValues to retrieve data from sheets, you can hit a problem with sorting. Perhaps numbers have been converted to strings, or dates are in an unsortable format for example

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

This post is about handySort which takes care of all that by referencing the original values as well as the displayValues. This can run both server or client side.

Implementation

I've implemented this as part of the Fiddler (see  A functional approach to fiddling with sheet data), but it can be run standalone too, as in the first examples following.   To implement as standalone, just copy the code later in this article from github, or if you are using the cUseful library anyway, like this. Later in the post I'll show you how this is integrated into Fiddler for an even simpler usage. 

var handySort = new cUseful.Fiddler().handySort;

The general call is
var result = handySort (displayValues [,options]);

cUseful is at
1X-tyDMF_iILp3cQ4MMCY0GQwrOPHl8ocKtWhqVuw1u5PG5wMytL6mjOP

or you can get the source from github.

Examples

Let's start with a simple sort. 
This behaves the same way as Array.sort() does, except you get a new copy of the array (.sort sorts in place)
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. 
This means that if you were getting the data from a spreadsheet you would use both getValues() and getDisplayValues()
  handySort(["21", "2", "11", "1"], {
    values: [21, 2, 11, 1]
  });

Now the string values given are in the correct order
Values sort 1,2,11,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. 
In this first case we have a table of spreadsheet data, but a single column representing the actual values for the column being sorted on 
  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
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
Format dates Sat Dec 12 2015,Sun Dec 14 2014

So dates formatted like this will need guide values. 
handySort(formatDates,{values:dates});

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

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];
    });
    
  }






For more like this, see Google Apps Scripts snippets. Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.
Comments