Querying a Google Spreadsheet with Google Visualization API

Previously posted on Postach.io

I’m adding to my school training website and want a quick and easy way of categorising the training materials available. I wanted to create a relational database and query it in some way but, to the best of my knowledge, Google Sites doesn’t allow this. I’m tied to Google Sites because the school are using Google Apps for Education and I actually like the security features it offers in keeping the web content restricted to those with school email addresses only.

Yesterday I found a website that suggested a way that I could [display a Google Spreadsheet via the Google Visualization API](http://acrl.ala.org/techconnect/?p=4001). A few hours later I was able to recreate the examples where dataTables were created within the Javascript however I could not find a decent **complete** example that showed how to link to an existing spreadsheet.

Searching through the forums it became apparent that this method did not work in Google Sites – even when embedded in an HTML box – the problem being that you can’t add the jsapi library in scripts. So I started again, using another domain to ensure that the code worked. It did after a little experimentation (and you can [grab the example here](https://dl.dropboxusercontent.com/u/42287593/GoogleSpreadsheet_Viz_template.html) if you are interested) where I removed the query and [encoded it into the URL](http://meyerweb.com/eric/tools/dencoder/) instead.

I then found that, if I saved the working HTML to Google Drive, I could [publish a folder as a website](https://developers.google.com/drive/web/publish-site). So I made the Google Drive folder public on the web and grabbed the googledrive.com link from the details pane. From there I found the file I wanted (imaginatively titled test2.html!) and embedded it in my Google Site using iFrame.

It worked!!

But so much effort to combine three Google technologies!!