How To Use Google Doc Serve JSON From Excel Spreadsheet

Google Doc has many neat hidden feature you might not know about, for instance, you can use Google Doc to monitor website up time, use Google doc to do large file conversion and recovery. Recently, I discovered you can use Google Doc’s spreadsheet to serve JSON static data. This means, for some simple web application that needs server to serve some form of static data will no longer require you as a developer to write any server component code. It also provided a very user friendly medium for you to update the data inside Google doc. If you know the ramification of this, you’d appreciate the shortcut.

Here is how it can be done

First go create a Google Doc spreadsheet, you may do so by create a new Spreadsheet or upload any existing spreadsheet from excel.

Google Spreadsheet

Google Spreadsheet

In this case I have an example spreadsheet with two columns

Google Spreadsheet Publish To Web

Google Spreadsheet Publish To Web

Go to File > Publish to the web …  an option of Publish to the web will appear. Here you can specific which row or column you’d like to publish. Make sure you click “Start Publishing

Google Spreadsheet Publish Options

Google Spreadsheet Publish Options

Once you have published the spreadsheet, under “Get a link to the published data” you will extract the key from the URL.

Google Spreadsheet URL

Google Spreadsheet URL

Only copy and past the key from the popup and replace the key to the following URL below.

Google Spreadsheet Get Doc Key

Google Spreadsheet Get Doc Key

The final constructed URL will be as fellow. By leverage Google spreadsheet API

https://spreadsheets.google.com/feeds/list/0Aqglj65pqAwmdEh4a1otT3lmYnN0TGV1Q2JkdndVUnc/od6/public/basic?hl=en_US&alt=json

Note the key in bold, can be replaced to your OWN Google Doc key. The last parameter alt=json can be replace to XML format.

Google Spreadsheet JSON Result

Google Spreadsheet JSON Result

The end result will be a JSON you can use in your application, pretty neat. As long as you include the URL in your application, you can access the data. The downside is you need to publish the spreadsheet as public data. But the advantage of leverage this out weight by the downside by a big margin. You can update the data in the spreadsheet any time you want and your application uses this data will be reflected the data instantly. (Update: Google documentation on this feature)

Jonathan Hu

Programming by day, Web Development, Canucks & Movies for spare time!
Co-founder of Next of Windows and a cool geek 🙂

Last updated: 08/04/2014

Posted in: How to , IT Pro
Discover more: , , , ,