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!!


Replit Uncategorized

Cost comparison of hosting vs using Replit cycles

Are you looking for a way to cut hosting costs for your website? Look no further! I recently faced a similar challenge with my csteach.uk site and I’m excited to share my findings with you. As many of us know, the cost-of-living crisis in the UK has made it important to reduce expenses wherever we […]

Read More
Student Attainment Teacher Training + Support Uncategorized

Using Replit to Deliver 21st Century Assessment #replitedfest2022

Below are the slides from my Replit EdFest 2022 session on 21st July 2022

Read More
Teacher Training + Support

Mail merge and splitting files Google Docs

We are in prelim season and just about at the stage where we analyse student marks individually and as a cohort. I wanted to use my Google Sheet prelim breakdown showing how students performed in each question of the exam as source for a mail merge so that I could generate individual reflection sheets for […]

Read More