Last Night’s Update to the “Google Spreadsheets as a Database” Demo

[If you’re looking for my November 2010 Guardian Datastore/Government Spending Data explorer, see the post: Government Spending Data Explorer]

Yesterday I posted about Making it a Little Easier to Use Google Spreadsheets as a Database (Hopefully!), demoing a simple interface for constructing URIs to query Google spreadsheets using the Google query language.

One of the problems the interface presents is reconciling the column headings with the column letters (the first column is, by default, column ‘A’; the second is ‘B’, and so on).

So using the , I made a few tweaks to generate this version…

Firstly, when you preview the spreadsheet column headings at the top of the screen, a ‘headings by column label’ is also produced:

Along with that is a list box containing the column headings. (Note that this headings are pulled down from the spreadsheet whose key you entered. SO if you use another spreadsheet key, you’ll probably get different headings…;-)

As you select headings, two things happen: firstly, the column labels are added to the text box whose contents actually go towards producing the query:

(You’ll notice that the selection box is a multiple selection box).

Secondly, the ‘where’ selection box is populated using columns that have been selected:

(Note that it is quite possible to construct queries of the form select A,B,C where D=42, but I took the gamble that for a simple(?!) interface, it’s reasonable to expect users to be selecting columns that they’re also searching on; because the actual query is constructed based on the contents of the text boxes, the user is free to edit those as they will. The list boxes are just supposed to make things a little easier…)

When choosing items in the where selection box, I add some possible conditional options to the text box for that column:

If you choose multiple where conditions, they are wrapped in brackets and an and/or prompt is used to join the separate statements:

Another couple of minor tweaks:

– single quote marks weren’t being encoded properly in the created CSV and HTML URIs (thanks to an email from Andy Cotgreave for tipping me off to that one with an example query that wasn’t working for him); hopefully this is fixed now (encodeURIComponent doesnlt encode single quotes – but escape() does).

– you can now just past a Google spreadsheet URI into the ‘key’ entry box and it should be parsed out (thanks to Simon Dickson for that suggestion in a comment to the previous post).

And here’s the regular expression I used:
if (/key=([^&]*)/.test(key)) key=/key=([^&]*)/.exec(key)[1];

That’s it for now, so back to work…

PS A couple of gotchas… Firstly, if you own a spreadsheet that isn’t publicly viewable and you use this tool, if you’re logged in to your Google account it will probably work fine, but it won’t work for anyone who doesn’t have permission to see that sheet. Secondly, if you own a spreadsheet, it’s tempting to put ‘metadata’ in rows below the table. That means this metadata appears in data meaningful columns. So I’m starting to think that ‘metadata’ such as where the data was sourced from, possibly ownership and contact details for the data owner etc should be placed in a separate column at the right hand side of the table?