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

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)

This gives the (seemingly) wrong sort order of

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()

Now the string values given are in the correct order

You can use the descending option in any sort as required.

result

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

result

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.

result

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

result

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’.

result

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.

result

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.

result

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.

Sorting the raw dates works fine

result

But depending on how the dates have been formatted, you may get a ‘wrong’ result

result

So dates formatted like this will need guide values. 

result

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!

result

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.

Now create a fiddler for each of the formatted and unformatted data.

A simple sort on the date column of the data will of course give the correct order.

Result

But sorting the formatted fiddler will give the ‘wrong’  answer

result

Fiddler can use a second fiddler as a guide

to sort the formatted data (the second argument specifies descending order)

result

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

result

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

or you can get the source from github.

The handySort code is as follows.

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.