Monkey Raptor: 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 :

{
  "version": "1.0",
  "encoding": "UTF-8",
  "feed": {
    "xmlns": "http://www.w3.org/2005/Atom",
    "xmlns$openSearch": "http://a9.com/-/spec/opensearchrss/1.0/",
    "xmlns$gsx": "http://schemas.google.com/spreadsheets/2006/extended",
    "id": {
      "$t": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values"
    },
    "updated": {
      "$t": "2014-05-26T04:50:28.596Z"
    },
    "category": [{
      "scheme": "http://schemas.google.com/spreadsheets/2006",
      "term": "http://schemas.google.com/spreadsheets/2006#list"
    }],
    "title": {
      "type": "text",
      "$t": "U.S States Example"
    },
    "link": [{
      "rel": "alternate",
      "type": "application/atom+xml",
      "href": "https://docs.google.com/spreadsheets/d/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/pubhtml"
    }, {
      "rel": "http://schemas.google.com/g/2005#feed",
      "type": "application/atom+xml",
      "href": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values"
    }, {
      "rel": "http://schemas.google.com/g/2005#post",
      "type": "application/atom+xml",
      "href": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values"
    }, {
      "rel": "self",
      "type": "application/atom+xml",
      "href": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values?alt\u003djson"
    }],
    "author": [{
      "name": {
        "$t": "[edited_value]"
      },
      "email": {
        "$t": "[edited_value]"
      }
    }],
    "openSearch$totalResults": {
      "$t": "50"
    },
    "openSearch$startIndex": {
      "$t": "1"
    },
    "entry": [{
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/cokwr"
        },
        "updated": {
          "$t": "2014-05-26T04:50:28.596Z"
        },
        "category": [{
          "scheme": "http://schemas.google.com/spreadsheets/2006",
          "term": "http://schemas.google.com/spreadsheets/2006#list"
        }],
        "title": {
          "type": "text",
          "$t": "1"
        },
        "content": {
          "type": "text",
          "$t": "u.s.state: Alabama, statecapital: Montgomery, stateabbreviation: AL, statemotto: Audemus jura nostra defendere\n(We dare defend our rights)"
        },
        "link": [{
          "rel": "self",
          "type": "application/atom+xml",
          "href": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/cokwr"
        }],
        "gsx$no.": {
          "$t": "1"
        },
        "gsx$u.s.state": {
          "$t": "Alabama"
        },
        "gsx$statecapital": {
          "$t": "Montgomery"
        },
        "gsx$stateabbreviation": {
          "$t": "AL"
        },
        "gsx$statemotto": {
          "$t": "Audemus jura nostra defendere\n(We dare defend our rights)"
        }
      }, {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/cpzh4"
        },
        "updated": {
          "$t": "2014-05-26T04:50:28.596Z"
        },
        "category": [{
          "scheme": "http://schemas.google.com/spreadsheets/2006",
          "term": "http://schemas.google.com/spreadsheets/2006#list"
        }],
        "title": {
          "type": "text",
          "$t": "2"
        },
        "content": {
          "type": "text",
          "$t": "u.s.state: Alaska, statecapital: Juneau, stateabbreviation: AK, statemotto: North to the future"
        },
        "link": [{
          "rel": "self",
          "type": "application/atom+xml",
          "href": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/cpzh4"
        }],
        "gsx$no.": {
          "$t": "2"
        },
        "gsx$u.s.state": {
          "$t": "Alaska"
        },
        "gsx$statecapital": {
          "$t": "Juneau"
        },
        "gsx$stateabbreviation": {
          "$t": "AK"
        },
        "gsx$statemotto": {
          "$t": "North to the future"
        }
      }, {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/cre1l"
        },
        "updated": {
          "$t": "2014-05-26T04:50:28.596Z"
        },
        "category": [{
          "scheme": "http://schemas.google.com/spreadsheets/2006",
          "term": "http://schemas.google.com/spreadsheets/2006#list"
        }],
        "title": {
          "type": "text",
          "$t": "3"
        },
        "content": {
          "type": "text",
          "$t": "u.s.state: Arizona, statecapital: Phoenix, stateabbreviation: AZ, statemotto: Ditat Deus\n(God enriches)"
        },
        "link": [{
          "rel": "self",
          "type": "application/atom+xml",
          "href": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/cre1l"
        }],
        "gsx$no.": {
          "$t": "3"
        },
        "gsx$u.s.state": {
          "$t": "Arizona"
        },
        "gsx$statecapital": {
          "$t": "Phoenix"
        },
        "gsx$stateabbreviation": {
          "$t": "AZ"
        },
        "gsx$statemotto": {
          "$t": "Ditat Deus\n(God enriches)"
        }
      }, {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/chk2m"
        },
        "updated": {
          "$t": "2014-05-26T04:50:28.596Z"
        },
        "category": [{
          "scheme": "http://schemas.google.com/spreadsheets/2006",
          "term": "http://schemas.google.com/spreadsheets/2006#list"
        }],
        "title": {
          "type": "text",
          "$t": "4"
        },
        "content": {
          "type": "text",
          "$t": "u.s.state: Arkansas, statecapital: Little Rock, stateabbreviation: AR, statemotto: Regnat populus\n(The people rule)"
        },
        "link": [{
          "rel": "self",
          "type": "application/atom+xml",
          "href": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/chk2m"
        }],
        "gsx$no.": {
          "$t": "4"
        },
        "gsx$u.s.state": {
          "$t": "Arkansas"
        },
        "gsx$statecapital": {
          "$t": "Little Rock"
        },
        "gsx$stateabbreviation": {
          "$t": "AR"
        },
        "gsx$statemotto": {
          "$t": "Regnat populus\n(The people rule)"
        }
      }, {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/ciyn3"
        },
        "updated": {
          "$t": "2014-05-26T04:50:28.596Z"
        },
        "category": [{
          "scheme": "http://schemas.google.com/spreadsheets/2006",
          "term": "http://schemas.google.com/spreadsheets/2006#list"
        }],
        "title": {
          "type": "text",
          "$t": "5"
        },
        "content": {
          "type": "text",
          "$t": "u.s.state: California, statecapital: Sacramento, stateabbreviation: CA, statemotto: Eureka\n(I have found it)"
        },
        "link": [{
          "rel": "self",
          "type": "application/atom+xml",
          "href": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/ciyn3"
        }],
        "gsx$no.": {
          "$t": "5"
        },
        "gsx$u.s.state": {
          "$t": "California"
        },
        "gsx$statecapital": {
          "$t": "Sacramento"
        },
        "gsx$stateabbreviation": {
          "$t": "CA"
        },
        "gsx$statemotto": {
          "$t": "Eureka\n(I have found it)"
        }
      }, {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/ckd7g"
        },
        "updated": {
          "$t": "2014-05-26T04:50:28.596Z"
        },
        "category": [{
          "scheme": "http://schemas.google.com/spreadsheets/2006",
          "term": "http://schemas.google.com/spreadsheets/2006#list"
        }],
        "title": {
          "type": "text",
          "$t": "6"
        },
        "content": {
          "type": "text",
          "$t": "u.s.state: Colorado, statecapital: Denver, stateabbreviation: CO, statemotto: Nil sine numine\n(Nothing without the Deity)"
        },
        "link": [{
            "rel": "self",
            "type": "application/atom+xml",
            "href": "https://spreadsheets.go
.............
etc....

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 it^lol?.

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

{
  "version": "1.0",
  "encoding": "UTF-8",
  "feed": {
    "xmlns": "http://www.w3.org/2005/Atom",
    "xmlns$openSearch": "http://a9.com/-/spec/opensearchrss/1.0/",
    "xmlns$gs": "http://schemas.google.com/spreadsheets/2006",
    "xmlns$batch": "http://schemas.google.com/gdata/batch",
    "id": {
      "$t": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values"
    },
    "updated": {
      "$t": "2014-05-26T04:50:28.596Z"
    },
    "category": [{
      "scheme": "http://schemas.google.com/spreadsheets/2006",
      "term": "http://schemas.google.com/spreadsheets/2006#cell"
    }],
    "title": {
      "type": "text",
      "$t": "U.S States Example"
    },
    "link": [{
      "rel": "alternate",
      "type": "application/atom+xml",
      "href": "https://docs.google.com/spreadsheets/d/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/pubhtml"
    }, {
      "rel": "http://schemas.google.com/g/2005#feed",
      "type": "application/atom+xml",
      "href": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values"
    }, {
      "rel": "http://schemas.google.com/g/2005#post",
      "type": "application/atom+xml",
      "href": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values"
    }, {
      "rel": "http://schemas.google.com/g/2005#batch",
      "type": "application/atom+xml",
      "href": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/batch"
    }, {
      "rel": "self",
      "type": "application/atom+xml",
      "href": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values?alt\u003djson"
    }],
    "author": [{
      "name": {
        "$t": "[edited_value]"
      },
      "email": {
        "$t": "[edited_value]"
      }
    }],
    "openSearch$totalResults": {
      "$t": "255"
    },
    "openSearch$startIndex": {
      "$t": "1"
    },
    "gs$rowCount": {
      "$t": "999"
    },
    "gs$colCount": {
      "$t": "26"
    },
    "entry": [{
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/R1C1"
        },
        "updated": {
          "$t": "2014-05-26T04:50:28.596Z"
        },
        "category": [{
          "scheme": "http://schemas.google.com/spreadsheets/2006",
          "term": "http://schemas.google.com/spreadsheets/2006#cell"
        }],
        "title": {
          "type": "text",
          "$t": "A1"
        },
        "content": {
          "type": "text",
          "$t": "No."
        },
        "link": [{
          "rel": "self",
          "type": "application/atom+xml",
          "href": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/R1C1"
        }],
        "gs$cell": {
          "row": "1",
          "col": "1",
          "$t": "No."
        }
      }, {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/R1C2"
        },
        "updated": {
          "$t": "2014-05-26T04:50:28.596Z"
        },
        "category": [{
          "scheme": "http://schemas.google.com/spreadsheets/2006",
          "term": "http://schemas.google.com/spreadsheets/2006#cell"
        }],
        "title": {
          "type": "text",
          "$t": "B1"
        },
        "content": {
          "type": "text",
          "$t": "U.S. State"
        },
        "link": [{
          "rel": "self",
          "type": "application/atom+xml",
          "href": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/R1C2"
        }],
        "gs$cell": {
          "row": "1",
          "col": "2",
          "$t": "U.S. State"
        }
      }, {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/R1C3"
        },
        "updated": {
          "$t": "2014-05-26T04:50:28.596Z"
        },
        "category": [{
          "scheme": "http://schemas.google.com/spreadsheets/2006",
          "term": "http://schemas.google.com/spreadsheets/2006#cell"
        }],
        "title": {
          "type": "text",
          "$t": "C1"
        },
        "content": {
          "type": "text",
          "$t": "State Capital"
        },
        "link": [{
          "rel": "self",
          "type": "application/atom+xml",
          "href": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/R1C3"
        }],
        "gs$cell": {
          "row": "1",
          "col": "3",
          "$t": "State Capital"
        }
      }, {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/R1C4"
        },
        "updated": {
          "$t": "2014-05-26T04:50:28.596Z"
        },
        "category": [{
          "scheme": "http://schemas.google.com/spreadsheets/2006",
          "term": "http://schemas.google.com/spreadsheets/2006#cell"
        }],
        "title": {
          "type": "text",
          "$t": "D1"
        },
        "content": {
          "type": "text",
          "$t": "State Abbreviation"
        },
        "link": [{
          "rel": "self",
          "type": "application/atom+xml",
          "href": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/R1C4"
        }],
        "gs$cell": {
          "row": "1",
          "col": "4",
          "$t": "State Abbreviation"
        }
      }, {
        "id": {
          "$t": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/R1C5"
        },
        "updated": {
          "$t": "2014-05-26T04:50:28.596Z"
        },
        "category": [{
          "scheme": "http://schemas.google.com/spreadsheets/2006",
          "term": "http://schemas.google.com/spreadsheets/2006#cell"
        }],
        "title": {
          "type": "text",
          "$t": "E1"
        },
        "content": {
          "type": "text",
          "$t": "State Motto"
        },
        "link": [{
          "rel": "self",
          "type": "application/atom+xml",
          "href": "https://spreadsheets.google.com/feeds/cells/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values/R1C5"
        }],
        "gs$cell": {
          "row": "1",
          "col": "5",
          "$t": "State Motto"
        }
      }, {        
.............
etc....

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 :

CSS (styling) for making the "neat" look-able list.
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

Monkey Raptor

Tuesday, May 27, 2014

The Structure of Google Spreadsheets JSON (List and Cells)

No comments:

Post a Comment