Search code examples
jsonserializationgoogle-visualizationsimplejson

Proper way to deal with variations in JSON serialization

I have a web service that uses Python's SimpleJSON to serialize JSON, and a javascript/ client that uses Google's Visualization API. When I try to read in the JSON response using Google Data Table's Query method, I am getting a "invalid label" error.

I noticed that Google spreadsheet outputs JSON without quotes around the object keys. I tried reading in JSON without the quotes and that works. I was wondering what was the best way to get SimpleJSON output to be read into Google datable using

query = new google.visualization.Query("http://www.myuri.com/api/").

I could use a regex to remove the quotes, but that seems sloppy. The javascript JSON parsing libraries I've tried won't read in JSON syntax without quotes around the object keys.

Here's some good background reading re: quotes around object keys:

http://simonwillison.net/2006/Oct/11/json/.

Solution

  • Are you certain the Google API is expecting JSON? In my experience Google's APIs tend not to be massively broken in a manner you're describing -- it could be that they're actually expecting a different format that merely resembles JSON.

    Further poking around reveals instructions for retrieving data in the format Google expects:

    For example, to get the dataSourceUrl from a Google Spreadsheet, do the following:

    1. In your spreadsheet, select the range of cells.
    2. Select 'Insert' and then 'Gadget' from the menu.
    3. Open the gadget's menu by clicking on the top-right selector.
    4. Select menu option 'Get data source URL'.

    I did this and opened the URL in my browser. The data it was returning was certainly not JSON:

    google.visualization.Query.setResponse(
{requestId:'0',status:'ok',signature:'1464883469881501252',
table:{cols: [{id:'A',label:'',type:'t',pattern:''},
{id:'B',label:'',type:'t',pattern:''}],
rows: [[{v:'a'},{v:'h'}],[{v:'b'},{v:'i'}],[{v:'c'},{v:'j'}],[{v:'d'},{v:'k'}],[{v:'e'},{v:'l'}],[{v:'f'},{v:'m'}],[{v:'g'},{v:'n'}]]}});

    It looks like the result is intended to be directly executed by the browser. Try modifying your code to do something like this:

    # old
return simplejson.dumps ({"requestId": 1, "status": "ok", ...})

# new
json = simplejson.dumps ({"requestId": 1, "status": "ok", ...})
return "google.visualization.Query.setResponse(%r);" % json