It was nice to see a couple of people picking up on my post about using Google Spreadsheets as a database (e.g. Using Google Spreadsheets as a database (no, it really is very interesting, honest) over at the Online Journalism blog), but it struck me that the URL hacking involved might still deter some people.
(Btw, the only list of keywords I’ve found to date for the query language are on the official documentation pages, and even then they aren’t complete…)
So – I spent an hour or so last night putting together a first attempt at a form based interface for writing the queries and constructing the URLs.
The form starts with a slot for the key of the spreadsheet you want to query – clicking on the preview button should display the column headings:
This preview can be used to help you select the columns you want to interrogate or return in your query, counting left-to-right: A, B, C and so on.
Next up are some hints on example queries:
and then there is the query form itself:
I’ve made a start on separating out the different bits of query, but there’s clearly lots more that could be done. For example, an optional “order by” slot could be provided (with a post-qualifying asc or desc selection), or the select entry box could be turned into a multiple selection listbox displaying the column headers, (but I only gave myself an hour, right?;-) [Note to self: lots of handy functions here – Google Visualization API Reference]
Anyway, once you make the query, links to the URIs of the HTML preview and CSV versions of the query are automatically generated, and the HTML table of results is displayed:
The CSV URI can then be used to import the data into a Many Eyes Wikified data page, for example.
Anyway, hopefully this makes it a little easier to get people started with these queries. A good place to start looking for spreadsheets is on the Guardian DataBlog.
Note that this “work” also ties in strongly to the idea of “data journalism (hashtag: #datajourn) which I’d be interested in hearing your thoughts about…
OUseful.Info, the blog... 
This is fantastic Tony. You’re singlehandedly blowing the potential of Google Spreadsheets wide open. :) A bit of polish on this interface, and we’ll be flying.
After literally seconds of reflection, I suggest:
– a bit of input string processing (regex?) to let people paste full URLs (or anything else) into the ‘key’ box
– show the column letter above the heading somehow? or cut out the column letters altogether? Maybe a ‘construct query via dropdowns’ approach might be even easier.
Yes – agreed – but i have other things i am supposed to be doing today:-(
adding column headers etc should be quite easy (also pulling column headings into a selector) via the viz api methods:
http://code.google.com/apis/visualization/documentation/reference.html
Just need more time… or help from the lazyweb? ;-)
thank you!!! your interface was extremely helpful. wouldn’t have been able to make my database otherwise, especially considering google’s documentation isn’t even comprehensive. coool.
Could someone help me. I want to use a google spreadsheet as a database that I can make a mobile enabled website I can access from my phone to tasks for myself or a friend. A collaborative task list using google spreadsheet as the database.
Can this be converted into a Google gadget? Then, I can embed on a page in my Google site.