OUseful.Info, the blog…

Trying to find useful things to do with emerging technologies in open education

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?

Written by Tony Hirst

May 21, 2009 at 11:18 am

Posted in Data, Tinkering

8 Responses

Subscribe to comments with RSS.

  1. I’ve been hacking around with this – it’s great. One workbook I’m trying to work on has multiple sheets. Only the second sheet is published. I’ve tried to hack the URIs but I can only get them to work on the first sheet; have you got any ideas how to get the URI to work on the second, third, fourth sheets?

    Andy Cotgreave

    May 21, 2009 at 2:13 pm

  2. Okay – for separate sheets, the preview stuff won’t work at the moment and you’ll have to hack the URI directly: add eg &gid=1 on the end of the uri to get the second sheet, &gid=3 to get the fourth sheet, and so on.

    eg http://spreadsheets.google.com/tq?tqx=out:html&tq=select%20*&key=rba_5ctUc2I736qzAw8lxVQ&gid=1

    arrghh – try this: http://bit.ly/19yBl2

    Tony Hirst

    May 21, 2009 at 2:27 pm

  3. @Andy Okay, I just added a handler (hopefully?) for additional sheets. Any probs, let me know…

    Tony Hirst

    May 21, 2009 at 2:50 pm

  4. […] Search « Last Night’s Update to the “Google Spreadsheets as a Database” Demo […]

  5. Hi Tony,

    Thanks a lot for your demo-site – it’s great !

    I managed to set up several queries to my Google-Spreadsheet.
    At the beginning I had problems with the “key” google gave me (there came always this “Ooups”-message with “no sign in”).
    Now I got another “key” and it works better – I usually get the query done and shown on a separate web-page; just sometimes there is still this “Ooups”-message with “no sign in”, and after I opened my google-email-account and left it, then every thing works.
    Why is this (are there any “cookies” necessary) ???

    – thanks for your help, Frank


    October 1, 2009 at 11:15 am

  6. […] a way to access cell data from a user defined spreadsheet. The solution was using Tony Hirst’s Google Spreadsheets as a Database code (just for fun … (more because I wanted to see if I could do it) I wrote some code to […]

  7. […] Fortunately Tony has a lot of experience with using Google Spreadsheets as a database and way back in 2009 started developing an explorer tool to help users built spreadsheet queries. This tool (here’s one of the latest versions) lets you […]

  8. […] See comment by James to streamline this even more.I was recently rediscovering an old Hirst post on Google Spreadsheets as a Database in which he demos an “interface for constructing URIs to query Google spreadsheets using the […]

Comments are closed.


Get every new post delivered to your Inbox.

Join 1,347 other followers

%d bloggers like this: