I received an interesting question from Greg Papuga the other day, on a long ago post for creating a dynamic table off of a Google spreadsheet (wow, that code is messy. eek.) When receiving the spreadsheet data as JSON, Greg wonders if he can sort this incoming JSON by date. That is, dates were entered in the spreadsheet in a random order, but it would make more sense to apply a sort for how it’s actually displayed in his web page.

And of course you can! This is a fun trick I end up using quite a lot, and have been meaning to write up.

First of all, I can’t remember if I previously mentioned a wonderful JavaScript library called Underscore, which you should get well acquainted with. Lots of little utilities that you wish existed in JS, but you know, don’t. We like it enough that it’s automatically imported into all of our AP Interactive projects.

Now, Underscore has a smart little method called sortBy. Pure JavaScript sort is designed to be used on a list. [‘banana’, ‘apple’, ‘grape’] –> [‘apple’, ‘banana’, ‘grape’] or [4,1,3] –> [1,3,4]

But when we receive a JavaScript object, such as the JSON returned from Google spreadsheets, we receive a more complex object, like: [{fruit: ‘banana’}, {fruit: ‘apple’}] etc. So we need a way to sort, but tell the computer which key (or label) of each object we want to sort by.

First, you need to import the Underscore library. Download the code, place it in the same folder as the rest of your webapp, refer to it in the head of your HTML like so:

Any references to JSON after that line will be to the sorted version of JSON.

Let’s parse out what that line of code means.

First, we assign this function call to the variable of json, so it overwrites our current data. You could also save to a new variable, to maintain both unsorted and sorted data.

We pass two arguments to _.sortBy. The first is the data that we want to sort. The second is a function.

That inner function takes one argument, which is each object (or “row) in your data. We call it “item”.

Then, in that function, we return a result that we will sort by. Here, we access the fruit member (or “column”) of a given row, which is the attribute we want to sort by. There will be a similar date member in your Google spreadsheet, if you created such a column.

Hopefully, that clears things up for Greg, and anyone else seeking to understand this fundamental and powerful data manipulation technique. Not just related to spreadsheets, but a general strategy for manipulating data structures.

Share this post:

Underscore is a fine library (indispensable in fact!), but did you know that you can pass a comparator function to the native .sort() method? The syntax is slightly harder to understand at first than _.sortBy(), but you have more control and it’s faster (underscore uses .sort() internally, but only after it’s done a load of other work, which you can see in the annotated source code – http://underscorejs.org/docs/underscore.html. Which is well worth reading by the way – I’ve learned a lot from studying it).

That's quite a cumbersome example because we're comparing non-numeric properties – you can sort numbers and dates by doing e.g. this instead:

records.sort( function ( a, b ) { return a.date – b.date; });

If you want to clone the array to sort it without mutating the original, you can do

clone = original.concat();

MDN has lots more info on .sort() and other array prototype methods – it turns out that browsers can natively do a lot you'd expect to need underscore for. And it's always faster! (Though you won't generally notice unless you've got a HUGE amount of data, so it's often up to which way is easier to code, of course.)

Whose ramblings are these?

Hi! I'm Michelle, a passionate journalist-programmer on an ongoing quest to combine data, technology and writing in the service of telling stories about our world.

My current playground for experimenting is the Associated Press' Washington, DC bureau, where I work as an Interactive Producer, focusing on the data journalism I live, breathe and love.

I've worked as a Data Producer at PBS and a data developer/journo intern at the LA Times' Data Desk. I'm also a proud alum of Medill, Northwestern's journalism school, and originally from the Chicago area.