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!
Share Improve this question edited Apr 3, 2012 at 4:05 jrue asked Apr 3, 2012 at 0:56 jruejrue 2,5604 gold badges19 silver badges26 bronze badges 4- 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
Reset to default 1The "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 :-)