JSONP for Google spreadsheets 20


Nikolai said yesterday “Is Google spreadsheet becoming the new Microsoft Access?”. You know the visual database and form designer. Well, for the web and pure mashup apps, that are mainly or completely based on the web’s infrastructure, so to say cloud services, one could answer “Yes, it can cover at least the database part”. It may be poor in features, but a nice start.
Well all this buzzy stuff aside, read on to see how to use JSONP with a Google spreadsheet, even though it’s not exactly provided.

For me as a front end engineer and JavaScript lover I always think twice before I set up a server system with a database and some Python or PHP code in between which then provides me with the data I need. This is painful, actually too much work for a lazy front-end dev like me, I would also have to do the permission handling and let alone the maintenance. Of course there are projects where it is not possible to store everything in the cloud, as what would be the solution I would favor over the before mentioned server infrastructure. (Let me just get this straight: by storing in the cloud I mean storing my data on mashable online services, which I can access via JavaScript, you will see in a second.)
Ideally JSONP is the solution of choice for getting the data, but sometimes there is not this choice and you have to look around for other ways.

Why JSONP?

The easiest and probably most relevant reason why to use JSONP (read here how it started), is that it is native JavaScript. There is no mapping from one format to another required, so you get natural performance. Second it is just a simple technique for cross-domain communication. And you don’t have to setup no complicated event handling for getting informed about when the data are loaded, it’s all handled.

Google spreadsheet

Let’s take this tiny spreadsheet, it contains the data I want to request, e.g. for showing on the uxebu.com website.

This spreadsheet is really easy to maintain, no geek knowledge is required for that, and this is the great thing about it. If that can serve as the data source for a web service or widget it just makes maintenance so much easier, and you can also tell your customer “just change the data as you like”. It’s not only, that changing is easy, also the permission handling is just a couple of clicks away. The data are hosted on one of the best working networks and you can be pretty sure that if you have no high-traffic site, that those data will just always be served at awesome speed. Enough reasons? I think so.

How to get the data

The google docs unfortunately show nothing really nice about JSONP. Yes you can make the ATOM feed of the spreadsheet be served as JSONP, but look at the data format (better don’t :-)), it really is not what you expect when working with JSON. The data are linear in there, so you have to apply the logic that actually the spreadsheet has in it again to reformat the data, and may be convert and take them apart again. Just believe me it’s not what one expects. If you like the pain, try it out.
And there is the CSV export of the data, that I always loved for it’s simplicity. Just add &output=csv to the URL and you get your spreadsheet as CSV. Very nice. Unfortunately trying to load those data using dojo.io.script.get of course fails:

CSV loading fails

CSV loading fails

Well, trying doesn’t cost a dime. Of course, I tried this first, though it was clear that it wouldn’t work.
I didn’t want to give up on this nice format, it has all the goodies, the lines, columns of the spreadsheet, no overhead, easily human-readable (which is always nice for finding errors in the parsing process) and it just maps so well to JSON. That’s the clue, it maps to JSON, now I just need to get that done. And since I didn’t want to add the overhead of passing the data through a Yahoo! Pipe just for getting a JSONP format I thought, why not make the CSV look like JSONP. The two formats are close enough.

JSONPed CSV

After trying a little bit and seeing the result that google spit out and fixing it again I had the solution (the spreadsheet below), pretty easy actually.

The CSV format is already comma separated, that is kind of half the way to JSON. At least the comma will make sure that we can distinguish the columns from one another. I ended up adding the callback name to the first cell on the left like this __callbackFunctionName__(' the opening parentheses and the apostrophe starts the function call that will be made inside the script tag you load it into. Now you can already guess that either every cell is surrounded by apostrophes, but that would just be too much hazzle and too error-prone, so I decided to just add a column before and one behind the data, when parsing the CSV those will just be dismissed, since they won’t contain a headline, therefore have no key to map the data to.
The last line is a little special, it contains the extra cell containing 0); which is closing the function, this is actually only there to create an extra column, where each line has just a space in it, so we get the comma at the end of every line. So that in the end our JSONPed CSV (the human-readable version here) looks just like this:

__cfn__(',name,url,status,irc, ',  
 ',dojango,http://dojango.org,released,http://mibbit.com/chat/?server=irc.freenode.net&channel=%23dojango, ',  
 ',dojodocs,http://dojodocs.uxebu.com,preview,, ',  
 ',dools,http://code.google.com/p/dools,in progress,, ',  
 ',xray,http://code.google.com/p/xray-project/,alpha,http://mibbit.com/chat/?server=irc.freenode.net&channel=%23xray, ',  
 ',d.js,http://code.google.com/p/ddotjs/,unreleased,, ',0);

The client

Every line is now a parameter for our callback function. Simply use dojo’s dojo.io.script.get() hook onto the callback function and we got our spreadsheet data in any site within JavaScript. A little after-parsing and done.
The dojo source code may look like this:

dojo.require("dojo.io.script");

dojo.addOnLoad(function(){
    var url = "http://spreadsheets.google.com/pub?key=rmlQvp80ksEJg6r49fJhjuw&output=csv";
    dojo.io.script.get({url:url});
    dojo.connect("__cfn__", function(){
        var args = dojo._toArray(arguments),
            colNames = args[0].split(",").slice(1, -1),
            data = [];
        for (var i=1, l=args.length-1, d; i<l; i++){
            // Convert the data using the colNames to an object.
            // (Might be an esoteric step, but correct this way.)
            d = {};
            dojo.forEach(args[i].split(",").slice(1, -1), function(item, index){
                // Do some CSV conversion.
                item = item.replace(/""/g, '"');
                if (item.charAt(0)=='"' && item.charAt(item.length-1)=='"'){
                    item = item.substr(1, item.length-2);
                }
                d[colNames[index]] = item;
            });
            data.push(d);
        }
        dojo.query(".theProjects")[0].innerHTML = dojo.toJson(data, true);
    });
});

You can download and try out a test page here.

Of course, there are a couple of drawbacks.

  1. You have to make the spreadsheet adhere to a certain style, the first column, the last column, etc. and one error in there may break it entirely. So there is not much fault tolerance. Additionally you are not supposed to use new lines in the content of the spreadsheet!
  2. The callback name is fix, though if you are using this sheet in one app this should be ok.

But see the advantages, you get permission handling for editors for free, you can let your customer edit the data without your involvement and so on. As always, when the use case requires this solution you can pull it out of your sleeve.
Oh, the source code above is also still not handling the escaped commas I guess, so there are still places where this can be improved.

Further thinking

Another way I though about was simply only surrounding the CSV by /* and */ which would relieve us from any JavaScript parsing error, since everything is a comment. But my quick test using innerHTML didn’t work. And innerHTML is not that relieable anyway so it might be better I didn’t waste more time on it. May be loading this into CSS tag enables us reading the data back … I don’t know, those are just random thoughts.
Another thought Nikolai had was “A spreadsheet-based CMS”. Of course, why not. You can basically store any kind of data in the spreadsheet, even HTML if you really would want to.

This can spin of more discussions and interesting ideas, let’s see what the future will bring.
The web is becoming more fun every day!


About Wolfram Kriesing

Wolfram Kriesing has more than fourteen years professional experience in IT. The early involvement in web technologies provides him with deep knowledge and experience for designing and implementing stable and scalable architectures.

  • Pingback: JSON-P hacks | Continuing Intermittent Incoherency

  • Chris

    Why not ask google to add &output=json format, so that the formatting issues aren’t exposed to the spreadsheet end user?

  • http://david-burger.blogspot.com David

    You mentioned that Google Spreadsheets will server up an Atom Feed format of your spreadsheet via JSONP – I gave a simple example on how to do that on my blog here:

    http://david-burger.blogspot.com/2009/03/display-google-docs-spreadsheet-data-on.html

  • http://www.uxebu.com Wolfram Kriesing

    @Chris Having it built right in by Google would of course be the best thing. And the solution above is not ideal, the customer can still screw up the spreadsheet, but it is useable _now_.

  • http://shaneosullivan.wordpress.com Shane

    Is there a reason you didn’t use the CsvStore from Dojo? Or the GoogleFeedStore for Atom?

  • http://www.uxebu.com Wolfram Kriesing

    @Shane I tried the CsvStore it just doesn’t work with cross-domain, even if the google spreadsheet CSV it doesn’t support JSONP for the CSV export (for whatever reason). And to be honest I played around with the ATOM feed for a while (maybe I didn’t find the ideal way) but the format in which the data had been exported in the ATOM feed where linear and a lot of the spreadsheet structure was lost.
    And since my data were so simple I felt that a store adds a lot of overhead, a function call for every value just adds up, so I went the KISS route … Yes, getter+setters are a must for JavaScript :-).
    But in the end, if there had been the JSONP way for the CSV data of the Google Spreadsheet and the store (or I would have extended it) I would have used it and not bothered twisting my and other’s heads this way.

  • http://unclescript.blogspot.com Peter Svensson

    This was very good!! Thanks for posting this.

    Cheers,
    PS

  • Pingback: nathan hammond :: Using Google Spreadsheets To Make Testing Easier

  • http://www.uxebu.com Wolfram Kriesing

    Wow, Nathan Hammond just added a blog post which shows how to automagically generate the JSONfied version of the data, and you don’t need to add the funny first and last columns, but can worry just about the data. This rocks! The formula itself looks likes a giant, but it does the job it seems. Impressive work!
    http://nathanhammond.com/using-google-spreadsheets-to-make-testing-easier

  • jean

    stumbled across this page whilst looking for what Google Spreadsheet offers in terms of script programming (something like vbscript in Excel).

    Some javascript solution seems natural.

    Great job on the formula.

  • Pingback: Uxebu.com - JavaScript addicts » Google Technology User Group Munich

  • http://www.uxebu.com Wolfram Kriesing

    Dada … inspired by Chris Heilmans talk about YQL at the WebMontag in Frankfurt two days ago, I simply tried it with YQL and it is muuuuch easier to use that for converting the spreadsheets output to JSONP.
    The URL (including the YQL query) is the following: http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20csv%20where%20url%3D%27http%3A%2F%2Fspreadsheets.google.com%2Fpub%3Fkey%3Drai4OXpsbS2Bc04lWn575ZA%26output%3Dcsv%27&format=json&callback=cbfunc
    And you can get JSONP out of every spreadsheet :-)
    Great, ha? Though the ideal solution would be google offering JSONP directly, but this workaround just makes it much easier than the solution I described above. Fortunately!
    Have fun …

  • Pingback: Pulling Google Spreadsheets data to Projectrix « Research Log of Web Science Students

  • http://www.D2Surveys.com D2 Items Free

    Our renters moved out a few months ago and left us having a mess to clean up. Not just were there holes within the wall, they also destroyed doors, window frames and carpets. They were incredibly smart and covered up the damage with pictures, posters and curtains so we didn’t realize the extent from the damage until they were gone. Now we’re fixing up the house so we can rent it again. Thankfully, this career is significantly easier as we have a laptop with wireless capabilities. We are capable to Get Free Diablo 2 Items Online. as well as other shows so we are entertained although toiling away. It is excellent that the Online offers issues of this nature.

  • http://www.D2Surveys.com D2 Items Free

    For the past nine days we are already snowed in. This has not been quite much fun as all seven kids can be found trapped inside the house with me for just a good deal with the time. It has been too cold for them to stay out for any length of time. To add to that, our electric keeps heading in and out. Whenever we do have electricity we charge the laptop battery. When the electricity goes out, we either play games, read or Get Free Diablo 2 Items Online. thanks to our generator. I’m glad we discovered the website where we can do this as it keeps everybody occupied and quiet. I imagine I miss the quiet most of all.

  • http://www.domainwebcrawler.com/ Pauletta Condroski

    I’m havin’ some troubles trying 2 load your blog. I have been read it many times before and never gotten something like this, but now when i try 2 load something it just takes a little while (10-15 minutes ) and then just stop. I’ve tried with “www.” or not. Does anyone know what the trouble could be? Ask your support at hosting..And, yes, thanks for your job!

  • Pingback: My Search Engin

  • http://www.local-pc-guy.com Mike B

    I wasn’t able to get your exact solution to work as Google Spreadsheet kept putting a newline at the end of each line of the csv output, causing the script to break. Instead, I created a spreadsheet where the first cell (A1) created an array variable, and then each subsequent row is assigned as a string to the array. A column at the end has then end quote and semicolon, so each line is a standalone JS statement. Then I used $.getScript (jQuery) to include that script into my page, and then was able to use the returned array variable just like I would have used a CSV file.

    The Google JSON didn’t work so well for me either, I just wanted a CSV I could import, and what I ended up with was very close to perfect for my simple needs. Hope that helps someone else struggling to do something like this.

  • http://www.cheapoverheadprojectors.net elmo overhead projectors

    Simply discovered your site on google and i think it’s a shame that you’re not ranked higher as this is a terrific post. To change this i decided to save your website in order to my Rss reader as well as I’ll try to say you in one associated with my personal posts since you genuinely deserv much more readers when publishing content of this high quality.

  • http://gda.ou.edu/forum/profile.php?mode=viewprofile&u=2189 Bob Kissick

    hello,this article is great,I found it on bing and I love it very much,I agree with what you have said, it help me a lot in decision,but I am not follw well with the last part,can you explain it for me ?I will appreciate your answer,and I will keep on watching your blog