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!

Never Collate Emails Again -or- I Love Google Forms

My presentation at the Brighton Library Teach Meet (#btnlibtm) was on how to quickly gather feedback using Google Forms. As I kind of rushed through it (there was only 5 minutes) I thought I'd blog it for anyone who'd like a reminder at a slower pace...

Log into your Google Docs account (it's free and if you've got GoogleMail then just click on the 'Documents' link at the top) and click on 'Create new' -> Form

This is what a blank form looks like - ready for you to add a title, description and your first question.

Here we go, first question 'Your name' and you'll see I've ticked the box to make it 'required' ie they have to complete it to submit the form.

Now we're going to add a multiple choice question, but note there are seven different question types to choose from. After adding question you can drag them to re-order, edit them, delete them until you're happy

Here's our form ready to launch - note at the bottom there is the URL to the 'live form' - thats the link you want to email or tweet to your audience.

This is what the live form looks like (you can see a real one here). 

Now the magic happens. By creating a form, Google Docs automatically created an accompanying spreadsheet. As your audience complete the form, their responses are automatically logged under the question headings.

Here are the results from the live demo I did tonight - very impressive watching the responses appear on screen.

And once the results are in you can click on 'show summary of responses' to get an at-a-glance overview of responses.

So, if you are ever thinking of sending an email and then collating the responses into a spreadsheet - then stop! The small effort involved in setting up the form is instantly rewarded by the fact you have nothing to collate - your spreadsheet os ready for you to sort, annotate and do with as you will.

This is just the basic stuff - you can create quite complex, branched surveys using this tool. All free, and unlimited responses allowed.

A word of warning, I wouldn't recommend using this for secure or personal data - Google doesn't comply with UK Data Protection regulations at this time.

A Game for Teaching Boolean Searching

At tonight's Brighton Library Teach Meet (#btnlibtm) one of my favourite sessions was Siobhan Duvigneau from the Institute of Development Studies demonstrating the way she teaches boolean using an experiential learning technique...or I would say, using a game.

Siobhan noted that students don't find boolean searching a very interesting topic and making it engaging and relevant can be hard. So she tries to make her demonstration very visual for her students by making it physical - "stand up if..." and then stating the search criteria.

Here's the first round: Stand up if...you are Female and a Librarian:

Then we were encouraged to suggest some NOT criteria. I offered: "stand up if...you are male or female and NOT from the University of Brighton". Actually this confused people, but they worked out whether they should or shouldn't be standing!

I spoke to Siobhan after to see how she extends this when she's got more time than the 5 minutes we had tonight. She says she sets the students targets to achieve, like: "come up with a query that leaves only one student standing". This, for me, really adds the game elements, a competition to quickly find a way of achieving the target she has set using the rules of the game. You could have a lot of fun getting the students to come up with their own increasingly complex targets and enjoying the challenge of finding the search criteria to give those results.

The beauty is the visual nature of the results. I don't believe you could model this online in anywhere near the same way - having individuals figure out of they should or shouldn't be standing and seeing the results is a very powerful demonstration.

Augmented Reality - having a play with Aurasma

Augmented Reality: the technology of combining real word images, video, etc. with computer-generated information and/or imagerydefinition from http://www.macmillandictionary.com/

I read a good post this week on Steve Boneham's blog called "Simple Augmented Reality with Aurasma". He was writing about a new (free) iPhone app called Aurasma which allows you to create your own personal image-based Augmented Reality "anywheres" - basically linking images in the world to images on your phone. I'll explain...

So, I set up Aurasma on my iPhone and showed it the University of Brighton logo:

Then I chose an image (could have been a video) on my phone which I wanted it to display any time the app saw the logo out in the world. This is known as an "Anywhere". For step by step instructions, I refer you back to Steve's post rather than repeating! This was the image I chose (a google maps screengrab with the Uni contact details, web address etc):

So now, when I use the app to look at things which happen to contain the logo, say the sponsors page of the Brighton Fringe programme for example:

Then this is what happens - it sees the logo and overlays the media I have chosen:

Now the cool bit, is that you can try this out yourself - if you have an iPhone! Get yourself back to this blog post on your iDevice and click on this link which shares my uni "Anywhere":

http://share.aurasma.com/YXVyYXNtYTovLz9jaGFubmVsaWQ9MDQ1ZGQ3ZjBjYjE3ZDkzOTk2YTU3ZmZiYzhiZDQ3N2QmYWN0aW9uPXN1YnNjcmliZQ==

It should take you to download the app if you don't have it, and if you do it automatically loads my Uni "Anywhere" onto your app. You can test it by using your phone to look at this post on another device as I handily put the logo in near the top...

So this is a "taster" app, but certainly one I'd like to see developed into a more fully featured app where there are global "Anywheres".

Note: some of you are probably thinking QR codes, which is a similar technology, but this allows you to use any image, not just a code and it overlays rather than links.