JSONP and Spreadsheets

A year has passed since I wrote about the merits of JSONP, and since then, services have slowly started using it, allowing you to use your data from web pages anywhere in contexts outside of the site of the services in question. Adoption is not very rapid, but every service that adopts it and does it well is a great win for the online community (and, I argue, the services themselves), since they are increasing the leverage of web programmers all over the world to a great extent.

Possibly even more so than javascript libraries and frameworks on offer; at the heart of JSONP lies a core of very lightly wrapped, highly concentrated raw data served in the most easily digestable format possible for a javascript programmer. It's just the matter of pointing a script tag at the data, and the data comes to your code, an HTTP request and a function call later. You just name the callback and get to do whatever it is you want to do with the data. (Yes. It really is that simple.)

The topic of this post will be spreadsheets, as spreadsheets is a very useful and low-barriers-to-entry way to access and maintain organizing structured data, the bread and wine of web applications, widgets, aggregation services and other tools and accessories you live and breathe on the web. Every one processes data of some sort, and most have a need to get it from somewhere. And I think online spreadsheets have lots of merit here, addressing a sore need in a very good manner.

No online spreadsheet that I am aware of supports JSONP natively today, though you can coax at least Google Spreadsheets and EditGrid into "sort of" supporting it. In the Google case you get valid and a letter correct JSONP feed, which is however not JSONP in spirit, whereas with EditGrid you get as good a spiritual match as you care for, but no URL configurable javascript callback name. So neither is really there yet, but I'll discuss how even this is already enough to be useful, after a fashion.

Google Spreadsheets

Let's start with Google, as there is less to say about that. Nowhere in their interface is there an actual JSONP (or JSON) option to pick, but you can ask its ATOM feed formatter to reformat its data as (surface) JSONP and it will happily comply. The question, however, when devising an export format, is always "how would my data best be expressed for its target domain?", which, in the case of JSONP, is javascript. And since javascript has multidimensional arrays, which is what a spreadsheet gives a visual face to, and ATOM does not, the outcome when you reformat the ATOM feed as JSONP is, shall we say, suboptimal.

You reformat a Google Spreadsheets ATOM feed to JSONP by appending the URL query parameter ?alt=json-in-script at the end of its URL, and, making it interactable client-configured JSONP, Google accepts the additional parameter callback, naming our callback, and supplies it with the gobbeldegok that came out of the transformation. The ATOM feed is limited to single sheet output, and given all of these constraints, here is what (reindention for humans by me; the original feed is even less legible -- not to mention the horrible URL) comes out of this example spreadsheet:

/Sheet1\      /Sheet2
A:1 B:1 C:1       b:1    
A:2 B:2 C:2   a:2 b:2 c:2
A:3 B:3 C:3   a:3     c:3

In summary, it chewed off A:1, drenched the place with huge id URLs, links, pointers to schemas and other junk invented out of nowhere, added some useful metadata about the file and munged together cells into not separately addressable entities (an example of which being row 2, which came out as "A:2" and "B:1: B:2, C:1: C:2"). Still, even given this gooey mess of things, fine hackers such as David Huynh manage to craft gems atop it. I again warmly recommend Exhibit. Expect upcoming and more in-depth articles about it here, as I have amassed a large backlog of great hacks with it recently. It is a very hackable visualization engine indeed.

EditGrid

EditGrid does lots of things right, and in great style. They even care about making your URLs look good. Their developer interfaces are paid equal amounts of love and attention as are their user interfaces, which in this case is great praise for both.

The data you commit to an EditGrid spreadsheet not just sits there, but bends to your will and comes out in whichever data format you care enough about to craft or look up a conversion template for -- which is done on the fly by applying XSLT templates server side to the XML format they export to. To finish it off, all URLs are legible, short and converting to a different format using either of the built-in defaults or your own custom formats is a simple matter of changing the extension. Here is our example, again, at http://www.editgrid.com/user/ecmanaut/example. The live URL allows anyone a spreadsheet interface to the documet, and the author and those he chooses to share editing priviges with, the option of editing it. Watch the other permalinks below and feel the warm fuzzies inside:

Format	Output
XML	.xml
HTML	.html
CSV	.csv
PDF	.pdf
Excel	.xls
OpenDocument	.ods
OpenOffice 1.x	.sxc
Gnumeric	.gnumeric
TeX source	.tex

I took some time to hack up a few useful variants on JSON and JSONP formats of the data for myself (and indeed others; take your picks -- and should you feel like extending them further, I recommend working off the last one, which plays best on the strengths of XSLT, .null.cells.json). It's not full-blown JSONP, of course; you can't configure the callback name from the URL the way you should be able to (which becomes useful the instant you import more than one spreadsheet), but this is as close as we get without the interactive step.

I have organized the formats and the XSL templates by their properties; a few are standards compliant JSONP, following the JSON definition, some are even more size conservative and employ the short form used by javascript to define sparse arrays where there are empty cells. Those that are legal and interoperable JSON(P) (not all JSON consumers are javascript!) are tinted greenish in the table, the rest slightly rougeishly blushing.

	Empty cells become ""	Empty cells omitted
Plain JSON	.xsl .json {"workbook":[ {/*...*/}, {"name":"Sheet2","sheet":[ [{},{"type":"string","input":"b:1","value":"b:1","content":"b:1"},{}], /*...*/]} }	.xsl .null.json {"workbook":[ {[/*...*/]}, {"name":"Sheet2","sheet":[ [,{"type":"string","input":"b:1","value":"b:1","content":"b:1"},], /*...*/]} }
JSONP, data only	.xsl .data.jsonp editgridCallback({"workbook":[ {"name":"Sheet1","sheet":[ ["A:1","B:1","C:1"], ["A:2","B:2","C:2"], ["A:3","B:3","C:3"]]}, {"name":"Sheet2","sheet":[ ["","b:1",""], ["a:2","b:2","c:2"], ["a:3","","c:3"]]} })	.xsl .null.data.jsonp editgridCallback({"workbook":[ {"name":"Sheet1","sheet":[ ["A:1","B:1","C:1"], ["A:2","B:2","C:2"], ["A:3","B:3","C:3"]]}, {"name":"Sheet2","sheet":[ [,"b:1",], ["a:2","b:2","c:2"], ["a:3",,"c:3"]]} })
JSONP, all properties	.xsl .cells.jsonp editgridCallback({"workbook":[ {/*...*/}, {"name":"Sheet2","sheet":[ [{"type":"","input":"","value":"","content":""},/*...*/], [{"type":"string","input":"a:2","value":"a:2","content":"a:2"},/*...*/], [/*...*/]]} })	.xsl .null.cells.jsonp editgridCallback({"workbook":[ {/*...*/}, {"name":"Sheet2","sheet":[ [,{"type":"string","input":"b:1","value":"b:1","content":"b:1"},], /*...*/]} })
Exhibit JSONP	.xsl .exhibit.jsonp editgridCallback({"items":[ /*...*/ {"type":"Sheet2","":"a:3","b:1":"","":"c:3"}] })	.xsl .null.exhibit.jsonp editgridCallback({"items":[ /*...*/ {"type":"Sheet2","":"a:3","":"c:3"}] })

I'll get back about how to use those Exhibit JSONP to do great things. This post is mostly for reducing my backlog about things to blog to get things out where people can play with and work their own magic with them.

Categories:

• JSONP
• AJAX
• mashups
• spreadsheets