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!

What is the request you use to get the JSON data? Do you use type cells or type list? (see http://code.google.com/apis/gdata/samples/spreadsheet_sample.html) — HBP, Apr 03 '12 at 01:14
Looking at the examples further, I think you're doing something wrong - either in how you're requesting the JSON or in the spreadsheet data itself. From what I can see, you shouldn't receive an entire row as a text field; you should be getting a combination of objects and arrays in your data to denote rows and columns. — Eli Sand, Apr 03 '12 at 03:02
Doh! I figured it out. You got it. I was extracting the wrong node. Apparently, it does return individual objects for each cell. My mistake. THanks so much for catching this. — jrue, Apr 03 '12 at 04:00
I had the same problem. Looks like I was using /public/basic at the end of the URL. It should have been /public/values. — Ufuk Hacıoğulları, Apr 06 '14 at 00:09

score 1 · Answer 1 · answered Jan 06 '16 at 13:23

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 :-)

score 1 · Answer 2 · answered Apr 03 '12 at 01:52

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.

Parse JSON from Google Spreadsheet

2 Answers2

Linked