Parsing JSON from Google Spreadsheet

Question

EDIT: entry.content.$t is not a entry.content.$t field for accessing individual cells. entry.gsx $ [cell column header] is the correct method. Apologies and thanks for the help in resolving this issue.

The original question:

I am trying to parse JSON data from a Google spreadsheet . The problem is that the record field returns a row that is an entire row of the table but looks like a distorted object. How do other people analyze this data? Here's what the contents of node look like:

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

Look carefully, the $t field returns a whole row, which is a row in a Google spreadsheet. So entry.content.$t returns the string: location: 780 Valencia St San Francisco, CA 94110, phonenumber: [phone]...

A further exacerbation of this problem is that some cells in the spreadsheet have commas (for example, addresses) that are not escaped or quoted. Sort of

 jQuery.parseJSON(entry.content.$t) 

or

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

causes an error. I believe regex is an option, but I hope others may have solved it more elegantly. Thanks for the help!

Answer 1

Have you found How to access Google Sheet spreadsheets using Javascript only? ? There, one answer pointed to https://github.com/mikeymckay/google-spreadsheet-javascript .

Also, https://developers.google.com/apps-script/service_spreadsheet seems promising.

Answer 2

The "text" inside the $t attribute is not JSON. According to the documentation, text nodes are converted to $t attributes, so you cannot rely on anything if JSON is formatted correctly.

I would suggest using a regular expression instead, although I will warn you that some fancy stuff will be required to parse this output. As a result, you will use the statement, since you cannot separate by commas - you will need to look for (\w+): but in order to find the next element, you will have to take everything to another match (\w+): but also don't gobble it up. It can be done.

Answer 3

More recently, I had the same problem.

To parse the contents in $ t, you can use this RegEx:

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

it will return key value pairs.

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); } 

This should help: -)