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: ,
               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 mas (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!

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.,
               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 mas (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!

• javascript
• json
• google-api
• google-sheets

• 1 What is the request you use to get the JSON data? Do you use type cells or type list? (see code.google./apis/gdata/samples/spreadsheet_sample.html) – HBP Commented Apr 3, 2012 at 1:14
• 1 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 bination of objects and arrays in your data to denote rows and columns. – Eli Sand Commented Apr 3, 2012 at 3: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 Commented Apr 3, 2012 at 4:00
• 2 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ı Commented Apr 6, 2014 at 0:09

2 Answers 2

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 mas - 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.

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