How To Use Google Doc Serve JSON From Excel Spreadsheet

9

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.

2013 10 12 1101 450x312 - How To Use Google Doc Serve JSON From Excel Spreadsheet
Google Spreadsheet

In this case I have an example spreadsheet with two columns

2013 10 12 1101 001 450x395 - How To Use Google Doc Serve JSON From Excel Spreadsheet
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

2013 10 12 1101 002 450x414 - How To Use Google Doc Serve JSON From Excel Spreadsheet
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.

2013 10 12 1102 450x417 - How To Use Google Doc Serve JSON From Excel Spreadsheet
Google Spreadsheet URL

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

2013 10 12 1102 001 450x238 - How To Use Google Doc Serve JSON From Excel Spreadsheet
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.

2013 10 12 1125 450x614 - How To Use Google Doc Serve JSON From Excel Spreadsheet
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)

9 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here