Google Docs goes wild

Posted by: Gill, wildlife enthusiast

We're thrilled to have Gill as a guest blogger. Through the evolution of Google Docs, she has been one of the most courteous and helpful members of the Google Docs Help Group, assisting users with all manner of questions.

I use Google docs mainly to keep track of the records I keep for the local wildlife group. I try and make a note of anything interesting that I see out and about every day (like this ladybird (or ladybug, as you say in the states), found on my front doorstep on 6th April).

In late 2006 I discovered Google Docs, and decided to keep my records online. I have never looked back. Using a Google spreadsheet gives me several advantages over the old system (uploding a Works spreadsheet to a MySQL database accessed via PHP).

I can enter the records from home, from work or from a friend’s computer; this means I don't have to remember what I saw at lunchtime till I get home.
The records are safe on Google’s servers
Friends and family can enter species they have seen, after I invited them to collaborate
Other members of the Society can see the sheet, as viewers
The results can be made available, in real time, on the website, by publishing the sheet (click on the link to Current year’s records, in progress)

In the beginning, I set up a simple one-sheet file with columns for the English Name, Latin Name, Location, Habitat, Grid Ref, Date, Notes, Special and Recorder. This worked very well, but as I used it I soon found I wanted other information recorded, namely the species number such as the BSBI number for plants, and a (hidden) running counter to make life easier when adding the year-end records to the database. So, I added the extra columns.

I then began to think “Wouldn’t it be great if I didn’t have to keep looking up the Latin name? Or having to type in today’s date, or the Recorder when it’s me?”, so the project grew a bit.

I added my initials to the Recorder column, and =today() in the Date column, then filled down. I already had species lists as two- or three-column tables for species number, English and Latin names for the plants and birds. I uploaded these into a second worksheet, and added the information for common mammals, insects and the few reptiles and amphibians living in northern England. So far, so good. But, I'd hit a roadblock, because I couldn't work out what to do next – I knew what I wanted : type in the English name and have the Latin and the species number fill in automatically from the data sheet, but not how to do it. At this point I joined the Help forum and Ahab (the Google Docs Help Group's most prolific poster) came to my rescue – as he has on many occasions since for me and many other spreadsheet users. He provided me with the formulas I needed: =VLOOKUP(C2,'ref lists'!A:I,3,false) for the first data row, =VLOOKUP(C3,'ref lists'!A:I,3,false) for the next and so on.

I started a new spreadsheet for 2008 by the simple expedient of Copy Spreadsheet and then deleting all but the last couple of rows, that were empty apart from Ahab’s functions. I am very happy with the sheet in its current form, but of course I might need more modifications in the future.

This is from the current version, with the title row frozen: