Intro

I admit it: I was inspired by this article by Codeinfront.com. The idea of creating a snippet of code that anyone can share, update, and learn from is just extremely compelling and gratifying. So, I offer to you something that you may have never thought of. You can use Google Spreadsheets as a JSON data source. YUI3′s JSONP module talks very nicely to this JSON data source and allows you to essentially create sites that can be data driven and yet never require you to have a complicated PHP MyAdmin setup or MYSQL database. You can do it all via client-side technologies and some help from Google Spreadsheets. If you combine this with the Forms ability in G Spreadsheets, you could even create a cool report page based on some survey you create. Let’s get down to code.

The Code

1. Create a Google Spreadsheet

Fire up your trusty browser and create a Google Spreadsheet. We are making a BlogRoll widget here, so I created two columns – title and url.
spreadsheet example

2. Publish it “to the web”

Go over to the “share” button and pull down to the option “publish as a web page”.
publish as a web page
You need to do this first so that the ID of the spreadsheet is generated properly. While keeping this window open (the share one) go down and change the output to “ATOM”. We don’t really care about this format for this demo, but it has the nice side effect of generating the URL that we will cut and paste into our code below.
atom

3. Copy the share url and paste it into the code below

Copy that URL. This is the first part of a two part string that will tell Google you want to get that spreadsheet as a JSON data source. The second part you see in the javascript below called “secondpart” has two elements. The string itself tells Google about our JSON need, but it also has this “{callback}” element. We use this to give YUI3 a hint about where to place the name of the callback function we need it to execute after it gets the JSON back.

4. Update the jsFiddle and try it out!

Try out your changes with the embedded code below! jsFiddle is awesome sauce.

Links

I hope you enjoy this demo. You could really do many awesome things with this, as you are essentially getting a JSON data feed of a database which has a great GUI that happens to be Google Spreadsheets. I read that the original Diablo was moddable via an Excel spreadsheet. Imagine creating a game where you could tweak the settings just by poking around with a Google Spreadsheet. A major hat tip to YUI for making this something that was incredibly easy and straightforward, as handling all the JSON stuff yourself would be a MAJOR pain. Please spread this article around and comment if you liked it!