Using Google Spreadsheets as a database (no, it really is very interesting, honest)

This post by Tony Hirst should be recommended reading for every journalist interested in the potential of computers for reporting.

Why? Because it shows you how you can use Google spreadsheets to interrogate data as if it was a database; and because it demonstrates the importance of news organisations releasing data to their users.

Put aside any intimidation you might feel at the mention of APIs and query languages. What it boils down to is this: you can alter the web address of a Google spreadsheet to filter the data and find the story.

Simple as that. 

Hirst uses the example of the spreadsheet of MPs expenses recently released by The Guardian (they’ve also published Lords expenses). By altering the URLs this is what he generates (I’m quoting his bullet points):

OK, you need to know the words to use (and if you have a link to an easy reference for these let me know*), but this is still a lot easier than using programming languages and databases.

As I say, this also illustrates the importance of publishing raw data so users can interrogate it in their own ways, which is precisely what The Guardian’s Data Store has been doing, meaning that people like Tony can create interfaces like this.

Wonderful.

*Tony has very generously created this page which helps you formulate your search – and generates the URL. If you were working on a different spreadsheet you could just replace the spreadsheet URL and change any column references accordingly.

UPDATE: Tony also has a version which allows you to pick from Guardian datasets.

About these ads

23 thoughts on “Using Google Spreadsheets as a database (no, it really is very interesting, honest)

  1. Pingback: Making it a Little Easier to Use Google Spreadsheets as a Database (Hopefully!) « OUseful.Info, the blog…

  2. Pingback: The golden age of computer-assisted reporting is at hand » Nieman Journalism Lab

  3. Pingback: The golden age of data journalism?

  4. Pingback: links for 2009-05-21 «

  5. Pingback: links for 2009-05-22 « Sarah Hartley

  6. James Goffin

    The mention of Google Spreadsheets here is a little misleading – you could equally do the same sort of analysis using an offline tool like Microsoft Excel or Calc (the free OpenOffice equivalent).
    The point remains though that most journalists could do with help in getting to grips with this; I’ve had colleagues asking this week precisely about how they can use spreadsheets to cut through the swathes of data around expenses.

    Reply
  7. Pingback: links for 2009-05-25 « David Black

  8. Calli

    But spreadsheets are just databases. Its just another label?

    Whether you use google, microsoft, Zoho or any other out there.

    And of course you can sort the fields to generate the ‘useful’ data. This should not be ‘news’.

    Reply
  9. paulbradshaw

    James, Calli – the ‘news’ here it seems to me is that you can alter the URL in ways that filter information in more sophisticated ways than just using ‘sort’. It’s that that makes this more like a database you can interrogate using combinations of factors than a simple spreadsheet

    Reply
  10. Pingback: Every news organisation should have a Datastore | Online Journalism Blog

  11. James

    This is interesting indeed.

    I tried with my own spreadsheet,
    “http://spreadsheets.google.com/tq?tqx=out%3Ahtml&tq=select+count%28A%29+where+A%3DPHP%3Fkey%3D0AipNn919hx-OdHNhTDl1RExJVlFMUXVwOGUtNjdzanc&hl=en”

    but I always got this message:
    “Oops, an error occured.
    Status: error
    Reason: Access denied
    Description: Access denied”

    Anyone can view and edit the spreadsheet via the key I used in this example, how come I still get access denied ?

    Reply
  12. Nicolas

    The problem is that don’t work with private spreadsheet only public. Any idea to work with provate spreadsheet and make queries like this. Will be great !

    Reply
  13. Jaydeep

    @James Goffin :

    The author here means that Google Spreadsheets can be used as Databases in the cloud. You just update the data and create some dynamic app that receives the updated data and displays it on the users screen, be it a mobile, tablet or desktop PC.

    Reply
  14. Tefhanne

    Hi,

    Just wanna ask on how to insert row or data to my Google spreadsheet using JavaScript and HTML.

    Reply
  15. text seduction

    Wriote moгe, thats all I hаve to saу. Literally, it seemns аs thоugh you relied οn the video tо make youг point.
    You сlearly know what youre talkinjg abοut, whyy throw аωay уour intelligence оn just powting videos tо your weblog ωhen уou cοuld be giviing uus sokething
    informative tο read?

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s