Make a Searchable Online Database -or- I Love Google Spreadsheets

So you've got a spreadsheet of useful stuff that you want to be able to easily update and turn into an online searchable resource, but you don't want to have to set up a database. Well, Google have the tools for that. Google provide a lot of info, but I found this overwhelming when I got started so am hoping this offers a simple way to get you going.

You'll need a Google account and a place you can edit php webpages (or something like a blog/Blackboard you can enter code).

We're going to create a Google Spreadsheet and then set up a webpage with some special 'visualisation' html to make it searchable, sortable and openly available online! Here's how...

First, set up a new Google Spreadsheet from your Google Docs account.

Fill the spreadsheet with your resources, list or whatever. You'll note I've left the top row blank - I've had problems getting Google to recognise my column headers so tend to leave it blank and we'll label it up later.

Once you've got some data we need to 'publish' the sheet so we can talk to it from our web page.

On the publish screen, click the 'start publishing' button which will make a long web address appear, you need to copy this into notepad or somewhere handy as we'll need the 'key' from this shortly (the bit from after "key=" up until "&output-html").

You also need to change the 'Sharing Settings' to Public, otherwise users need to be logged into Google to make this work - you could use sharing settings to only allow individuals, but in this case, just make it public.

Here is the magic, you need to copy this code into your favourite webpage editor. The code is here. To complete this demo and build a page that searches columns A and B and displays all three then the only edit you need to do is to swap the key from my version to the one you made a note of in the step above. Paste it in, save it and upload it to your web server.

If you're feeling imaginative, this is the line that does the magic you might want to explore:

'SELECT A, B, C where upper(A) like upper("%%") or upper(B) like upper("%%") order by A asc label A "Type", B "Title", C "Date added"'

What this does is says:

Show me columns A, B and C...

...where the thing you searched for matches (in upper case to make sure case doesn't matter) anything in column A or column B...

...order the results by column A is ascending alphabetical order...

...and label the columns like this.

 You can find out many more options of what you can do with this query on the Google pages.

The one I made now looks like this (see it for real here). 

A couple of real-life examples of this.

1. Searching a spreadsheet of music details pulled out of an iTunes library (NowLookHear)

2. Virtual Research Unit Funding Opportunities (University of Brighton). This uses some extra category fields on the spreadsheet and then a new query is built depending on what the user clicked on allowing filtering as well as searching.

For those of you without 'proper' access to webpages, you'll be please to hear you can embed this in something like Blackboard. The search box bit doesn't work but you can display the spreadsheet and let the user view it and sort it. 

In Blackboard you'll need to enter a simple version of the code (simple version available here - like above but without the search box stuff) into an item when the visual editor is turned off. 

This is what it looks like in Blackboard.

Good luck - and if this works for you, please let me know in the comments, would love to see what you do with it!