Monkey Raptor

Tuesday, May 27, 2014

The Structure of Google Spreadsheets JSON (List and Cells)

Update for January 21, 2015:
The new Google Drive has new URL pattern for the objects (spreadsheet, especially), I think. And I haven't tinkered that.
You might want to read the Google Developers documentation about the new Drive features and probably discuss the API techniques on Stack Overflow.
Let's get started
Before we look at the structure, this is the public spreadsheet I created earlier :
https://docs.google.com/spreadsheets/d/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/pubhtml


The List
It can be fetched using this URL pattern :
https://spreadsheets.google.com/feeds/list/[your_key_here]/od6/public/values?alt=json

The [your_key_here] is the key characters within the URL of the public published spreadsheet.

Formatted JSON (list) of that public spreadsheet I created :

First method to access the values
The value within the "feed""entry""gsx$...""$t" is the value of each cell.
The "problem" using this is, if you have different value on the header cell, then the gsx$... thingy will be followed by different keyword.

Second method to access the values
You can also get the "feed""entry""content""$t".
Of course, it will be longer to re-organize those on the front-end.

Anyway, as you can see above, using list, the cell header value is within the "content". Because I'm using the first method, accessing the value within the "gsx$...", therefore, in my earlier example, I "injected" my own table header to substitute itlol?.

In conclusion, if using the "gsx$...", then we'll have only the "body section" values of the table we made on the spreadsheet.

The "trial and success" earlier example using jQuery, complete with independent page to see the script and all is RIGHT THERE


The Cells
It can be fetched using this URL :
https://spreadsheets.google.com/feeds/cells/[your_key_here]/od6/public/values?alt=json

Formatted JSON (cells):

The JSON format above consists of all the values within the spreadsheet with "easier to read" array element notation.

You can get the value of each cell using : "feed""entry""gs$cell""$t"

And the cell coordinate using :
  • Row : "feed""entry""gs$cell""row"
  • Column : "feed""entry""gs$cell""col"

The "trial and success" using this neat JSON format is at Port Raptor

About the ASCII table at Port Raptor :
The table on that ASCII spreadsheet is not symmetrical. So I had to tinker the arrays a little further within the JavaScript to inject those onto another asymmetrical shape.
What do you know? It works!


This is all front-end tinkering. List of the implemented things there :
  1. CSS (styling) for making the "neat" look-able list.
  2. JavaScript with jQuery library :
    • $.get method, or the longer complete $.ajax method.
    • Creating arrays, then using native JavaScript for and jQuery each() method to iterate over the arrays elements.
    • append() and html() methods for appending/clearing values.


You can always put the callback function within the JSON url to pass the objects.

That is all. Thanks for visiting.


Reference :
  • Simple example of retrieving JSON feeds from Spreadsheets Data API at Google Developers
  • JSON (JavaScript Object Notation) format : www.json.org

Guides on working with Google Sheets JSON properly developers.google.com/apps-script/guides/sheets
The Structure of Google Spreadsheets JSON (List and Cells)
https://monkeyraptor.johanpaul.net/2014/05/the-structure-of-google-spreadsheets.html

No comments

Post a Comment

Tell me what you think...