Parse JSON from Google Spreadsheet
Solution 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.
Solution 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 :-)
jrue
Updated on August 06, 2022Comments
-
jrue over 1 year
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. Soentry.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!