OUseful.Info, the blog…

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

Making it a Little Easier to Use Google Spreadsheets as a Database (Hopefully!)

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…

Written by Tony Hirst

May 20, 2009 at 11:08 am

Posted in Data, Visualisation

Tagged with

8 Responses

Subscribe to comments with RSS.

  1. 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.

    SimonD

    May 20, 2009 at 11:22 am

  2. [...] Search « Making it a Little Easier to Use Google Spreadsheets as a Database (Hopefully!) [...]

  3. [...] my original post on Making it a Little Easier to Use Google Spreadsheets as a Database (Hopefully!), I sketched out a simple form for helping create calls to a Google spreadsheet using the Google [...]

  4. 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.

    paula

    November 5, 2009 at 10:02 pm

  5. 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.

    Mat

    May 18, 2010 at 10:28 pm

  6. Can this be converted into a Google gadget? Then, I can embed on a page in my Google site.

    Alan Gandy (@alansgandy)

    October 7, 2011 at 5:26 am

  7. [...] look to see which elections returned a Commissioner based on second preference votes. If I use my Datastore Explorer tool to treat the spreadsheet as a database, and run a query looking for rows where the winner’s [...]


Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 725 other followers

%d bloggers like this: