Formatting and filtering a Google Spreadsheet using Google Visualization API

Previously posted on

Quick blog post from the iPad where I have extended the functionality of the spreadsheet I created earlier. I found it very easy to complete all the functions on the iPad **except for the spreadsheet formatting function** (how do you reduce the number of decimal places for a number column on the iPad?). I used [Textastic]( to edit the HTML, [GDrive]( to upload the file (the Google Drive app crashed a lot) and the full desktop view of Google Drive within Chrome to get the necessary URL for the image file.

My first aim was to get the URL to become a hyperlink. I found a [blog post]( which quickly helped solve this issue.

Next I wanted to turn the hyperlink into a button which opened the URL in a new window. The CONCATENATE function in Google Sheets helped here.

Then I wanted to hide the preURL column from the view. This took a little while because I forgot I was dealing with a spreadsheet and used the column headers as field names!

Finally I build a simple filter which allowed me to limit the results displayed to the user.

I’m now ready to design and populate a single spreadsheet containing all the training material – YouTube videos, webinars, iTunesU courses, articles, blog posts and files – which will be automatically limited depending on which page the user visits (beginner, intermediate or advanced). This should reduce the time it currently takes to manually edit the HTML in Google Sites.

Does anyone else use Google Sites within their educational establishment to help deliver staff training or CPD? I’d love to hear from you.

Querying a Google Spreadsheet with Google Visualization API

Previously posted on

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]( 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]( if you are interested) where I removed the query and [encoded it into the URL]( instead.

I then found that, if I saved the working HTML to Google Drive, I could [publish a folder as a website]( So I made the Google Drive folder public on the web and grabbed the 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!!