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.