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.

In this case I have an example spreadsheet with two columns

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”

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

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

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.

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)