I originally had an idea to create this article many moons ago, but never got around to it because during my interaction with Google Spreadsheets led to much frustration. However, it appears that a very smart fellow by the name of Martin Angelov wrote an article for Tutorialzine called “Dynamic FAQ Section w/ jQuery, YQL, and Google Docs” which cut through a lot of the complexity I was inadvertently adding to my version. I highly recommend you take a moment and read that article before proceeding, because frankly I’m lazy and I’ll assume you did!
My version of this code changes very little except that we will use YUI – and dispense with the quote removal aspects, because YUI takes care of that for you.
Start out by making a spreadsheet like this one.
You can try out the YQL query yourself with this link.
Now publish it as a web page like the linked article describes and get the CVS share url.
Check out the code below and replace the spreadsheet url that you have created with the default one of mine:
Hit run and you should see your spreadsheet data showing up on the fiddle!
This is very powerful and way moreso than the linked article suggests. First of all you can also link a FORM to a spreadsheet. You might do this to allow certain users to input data into your spreadsheet in a way that insulates them from actually seeing the spreadsheet, or accidently making destructive changes. You might also create a very simple blog this way by creating a spreadsheet form that includes the title, content, etc. When you submit the form it would update your spreadsheet. You then have a website that grabs the output of that spreadsheet and displays either say the list of titles (a certain YQL query) or say the page details. You could get pretty elaborate with this.
Another use-case might be say a data-drive web game. You have all the monsters’ images, hit points, colors, locations, etc defined in your spreadsheet. Then you could consume that information on the fly with say a canvas-based web game.
The key idea I want you to get here is that you can now easily do: Form->Spreadsheet->YUI+YQL->JSON->Website with basically no complicated server database. Huge idea!
