Google Sheet as JSON

Google Sheet as JSON

This is a single tutorial as a part of a larger series, “USING A GOOGLE SHEET AS DATABASE FOR WEB PAGE“. We will be looking at the simplest way to leverage a Google Sheet as a read only database for rendering dynamic content on a web page via a JSON object returned from a Google Sheet.

Create a Google Sheet

Row 1 is the header, the name of the columns you will use to access the data, for this simple example I’m creating a sheet with the following data:

Genus Specific Epithet Cultivar
Smith John David
Quercus rubra Aurea
Salvia greggii Furman’s Red

You can get the Google Sheet ID from the URL while you’re editing the sheet, i.e.

https://docs.google.com/spreadsheets/d/1Vbi3IW5N2OYL9QyHn9p8zoHYitlMybLtxwIyrj9uFHA/edit#gid=0

Publish the sheet

With the sheet you want to use open:

  • File > Publish to the web…
    • Link, Entire Document, Web Page
    • If you want changes to automatically show up when the sheet is modified, make sure the “Automatically republish when changes are made” is checked.
    • Click “Publish”
    • Then you will be given the URL to access the document, same key, different URL (note pubhtml):
      • https://docs.google.com/spreadsheets/d/1Vbi3IW5N2OYL9QyHn9p8zoHYitlMybLtxwIyrj9uFHA/pubhtml

Access Sheet as JSON

There is a google developers article that gives some great information on accessing a published sheet as json (Simple example of retrieving JSON feeds from Spreadsheets Data API)┬áit’s a great resource but slightly opaque so I’ll try to shed some light on how this works.

You can see the google sheet as json by hitting the following URL:

https://spreadsheets.google.com/feeds/feed/key/worksheet/public/values?alt=json-in-script

  • feed – options are list or cell, this changes the structure of the json object that is returned, for this tutorial I am using list
  • key – the key to your google sheet
  • worksheet – which “tab” of the sheet to reference, od6 is the default sheet (the leftmost tab/sheet visible within the interface)

Putting that all together for this simple demonstration, you can view the spreasheet as json with the following URL (replace the key with your key if you are creating your own Google Sheet).

https://spreadsheets.google.com/feeds/list/1Vbi3IW5N2OYL9QyHn9p8zoHYitlMybLtxwIyrj9uFHA/od6/public/values?alt=json-in-script

HTTP access control – CORS (Cross-origin HTTP Request)

As of the writing of this post (Dec 2015), the response from spreadsheets.google.com includes a response header “access-control-allow-origin:*”, so as long as you are using a browser that supports CORS you will be able to retreive that json object from any domain without even utilizaing JSONP, however google does allow you to specify a function name to wrap the respons in a javascript function name if you need/want to use JSONP.

Using Javascript to Populate a Web Page from Google Sheet

jQuery Ajax Example

https://jsfiddle.net/godoloju/59zL64hx/

Fiddle Notes

  • JAVASCRIPT is set to “No wrap – in head”, meaning the js is placed into the head element directly.
  • Click on “Generate Table from Google Sheet” to see it in action.
  • Uncomment “//console.log(theSheet);” to see the json object that is returned (only works in browsers that support console.log, such as Chrome or Firefox)

Working With the Returned JSON

  • The first row is used as the column headings, these are translated into a named key for referencing values in the columns.
  • A collection of the columns is available at theSheet.feed.entry[]
  • The columns are addressible via the column headings as gsx$headingname (headings are converted to lower case)

Putting this all together, you can generate a table from the following Google Sheet table:

Genus Specific Epithet Cultivar
Smith John David
Quercus rubra Aurea
Salvia greggii Furman’s Red

Assuming “theSheet” is the JSON object returned from Google, with a simple forEach loop we can create a table for our web page:

table = "";
theSheet.feed.entry.forEach(
    function(row) {
        tableRow = "<tr>";
        tableRow += "<td>" + row.gsx$cultivar.$t + "</td>";
        tableRow += "<td>" + row.gsx$genus.$t + "</td>";
        tableRow += "<td>" + row.gsx$specificepithet.$t + "</td>";
        tableRow += "<tr>";
        table += tableRow;
    }
)

Hope this is helpful!

I’m not much of a javascript programmer, so I welcome feedback on how I can improve my examples.

Leave a reply

Your email address will not be published. Required fields are marked *