Parse JSON from Google Spreadsheet

Question

EDIT: entry.content.$t is the wrong field to access individual cells. entry.gsx$[cell column header] is the correct method. Apologies and thanks for helping to solve this.

Original question:

I'm trying to parse JSON data from a Google Spreadsheet. The problem is, the entries field returns a string that is an entire row of the spreadsheet—but appears as a malformed object. How are other people parsing this data? Here is what the content node looks like:

"content":
{
    "type"   :"text",
    "$t"     :"location: 780 Valencia St San Francisco, CA 94110,
               phonenumber: (555) 555-5555,
               website: http://www.780cafe.com,
               latitude: 37.760505,
               longitude: -122.421447"
},

Look carefully, the $t field returns an entire string which is a row in the Google spreadsheet. So entry.content.$t returns a string: location: 780 Valencia St San Francisco, CA 94110, phonenumber: (555) 555-5555...

Further exacerbating this issue is that some of the cells in the spreadsheet have commas (like addresses) which aren't escaped or quoted. Something like

jQuery.parseJSON(entry.content.$t)

or

eval('('+ entry.content.$t + ')')

throws an error. I suppose regex is an option, but I'm hoping others may have solved this in a more elegant way. Thanks for the help!

Answer 1

The "text" inside the $t attribute is not JSON. According to the documentation, text nodes are transfomed in to $t attributes, so you cannot rely on anything in there being properly formatted JSON.

I would suggest using a regular expression instead, though I will warn you that to parse that output will require some fancy stuff. You'll end up using an assertion since you can't split on commas - you'll have to search for (\w+): but in order to find the next element, you'll have to take in everything up to another matching (\w+):, but also not gobble it up. It can be done.

Answer 2

Just recently, I had the very same problem.

To parse content of $t, you can use this RegEx:

/(\w+): (.+?(?=(?:, \w+:|$)))/mgi

it will return pairs of key-value.

JavaScript example:

    var currentPropertiesText = jsonEntry['content']['$t'];

    // var propsArray = currentPropertiesText.split(", ");
    var re = /(\w+): (.+?(?=(?:, \w+:|$)))/mgi;
    var propsArray = re.exec(currentPropertiesText)
    var props = {};

    while (propsArray != null) {

        var propName = propsArray[1];
        var propValue = propsArray[2];

        props[propName] = propValue;

        propsArray = re.exec(currentPropertiesText);
    }

That should help :-)