Thursday, November 27, 2008

Geocoding with Google Spreadsheets (and Gadgets)

Update: Google is deprecating Google Spreadsheets gadgets, as they announced in this post, so I am no longer updating or supporting them. If you're a developer, you can try Apps Script or the Spreadsheets Data API to see if you can accomplish the same thing. If you're a user, sorry, sometimes Google shuts down little used features.

Recently, quite a few people pointed me out to Tony Hirst's post on "Data Scraping Wikipedia with Google Spreadsheets". This was a really cool post because it demonstrated alot of features about Google Spreadsheets that people don't take advantage of (but should, because they're awesome): importing data from webservices or websites, publishing spreadsheets, creating charts of data, etc. But, I was sad to see that Tony had to go through so much pain to just geocode the data. Most people I know wouldn't be able to create a Yahoo! Pipe to parse the CSV and geocode spreadsheet data.. there should be an easier way. Well, there is, and that's what this post will show.

Google Spreadsheets now lets people embed gadgets, just like they can on iGoogle, Orkut (OpenSocial), Google Maps (Mapplets), and other containers. Those gadgets can live within the spreadsheet, access un-published data from a worksheet, and manipulate that data into some useful or visual output. There are gadgets for creating timelines, motion charts, word clouds, and now, courtesy of me, a gadget for geocoding data. This is my secret weapon for geocoding small sets of data, and can hopefully help other people out there.

Here are the steps for geocoding a spreadsheet with the gadget:

  1. Create a new spreadsheet, and put the addresses into rows. If your address is composed of multiple columns, just concatenate those into one single column using the "&" operator. The image below shows a spreadsheet of Australian beaches:

  2. Click on the "Insert" menu and then select "Gadget...":

  3. This presents you with various categories of gadgets to choose from (similar to the iGoogle directory). My gadget isn't yet in the gallery, so you'll need to select "Custom" and then type in the URL to the gadget:

  4. The gadget will appear embedded in the current worksheet, and it will prompt you to select a range of data to send to the gadget. Select all the rows for the column with your address data, and you should see the Range text field update with the range. If it doesn't work, you can always manually type it in. After doing that, click the Apply button:

  5. Since I always hate having charts or gadgets cluttering my sheets (and I like taking advantage of screen real estate), I then usually select the "Move to own sheet" option from the gadget menu. If you don't opt to do that, atleast resize the gadget to give it a decent amount of space in the worksheet. (Image omitted because I accidentally wrote over it :).
  6. When the gadget loads, it will present instructions, a blank map area, and a "Find Addresses" button. Click the "Find Addresses" button. It will start sending geocoding calls using the Maps API, and filling in the sidebar and map with results. It will stop at 99 results, for both technical and legal reasons (just use the gadget multiple times if you have more data than that). Any addresses that failed to geocode will have a red marker and show up in the bottom left of the map. All the markers are draggable, so you can move them around to tweak the location (this was a really important feature to me- geocodes can often use a nudge or two). In this step, you may notice some of the data didn't geocode at all, and perhaps you'll go back to your spreadsheet, edit the address a bit, and do this step again. For example, I had to add "Australia" to some of the beaches in my spreadsheet for disambiguation with other beaches.

  7. When you're satisfied with the marker locations, then select all in the text field below the map, and copy the text. The text contains the latitude/longitudes in row-order for all the markers, and is formatted so that it's easy to paste into spreadsheet columns.

  8. Finally, create two empty columns in your worksheet, and paste that text into the cells. If you put your cursor in the first row/column, then it should paste perfectly and align with the address data.

  9. You're done! Now you can use the latitude/longitude data in your Google Maps API mashup, perhaps by using the Spreadsheets -> Map Wizard or the Spreadsheets Mapper.

For those of you intrigued by Google Spreadsheets Gadgets, you may also want to check out my Spreadsheets -> JSON/SQL/XML converter, Spreadsheets based Flashcards, and State-Based Cluster Map. And the code is open-source, so you might want to modify and improve them, too. :)

Saturday, November 15, 2008

BarCamp Sydney: Best way to start off a life in Sydney?

So, I just arrived in Sydney 4 days ago, on a fateful Tuesday morning. Since then, I've moved into an apartment with a fellow Googler), learned how to hang laundry out to dry, hung out with the Sys-Ops crew in the office until 3 am after getting kicked out of the Bungalow8 bar, and today, participated in BarCamp Sydney.

BarCamp Sydney started off with a debate about rolling your own CMS versus modifying a pre-existing one, which is really the eternal debate about rolling your own anything versus using pre-existing anythings, so it triggered a lot of discussion and a counter-session later. This was a good indication of the sessions to come: a technical topic with an opinionated audience.

Some of the most heated debates were during the HCI talk about user interfaces (I'm personally convinced that there's no such thing as one good universal interface - there's just a thing as a consistent experience and well trained users), and during the final discussion about CleanFeed, a proposed internet censorship ("filtering") system in Australia. And then, of course, a few of us engaged in the obligatory JQuery versus JavaScript debate in the hallway (fyi: JQuery lost).

The least technical session was also the one that went on the longest - "Mind Hacking." A professional stage hypnotist came to demonstrate how easily people can be suggested things via marketing and advertising, and she attempted to prove it through stage hypnosis. I desperately wanted to be hypnotized, with the hopes of obliviously quacking like a chicken on stage, so I was one of the 6 volunteers in chairs at the front of the room. Unfortunately, it appears that I'm too immature or rebellious to be hypnotized - every time I was meant to fall into a deep sleep, I ended up in a fit of giggles. It certainly didn't help when an enterprising man in the audience yelled "Take it off!"... ;)

I myself gave two different sessions during the day. The first was a code walkthrough of a simple App Engine app, showing off the various features of GAE (and I might have thrown in a few <blink> tags). You can download that code in a ZIP, or just read through it in the "Getting Started" documentation.

The second was a brief description of HTML5 with as many demos as I could fit in 20 minutes (just got through video and forms), and it was based on the talk I gave at FOSSmy last weekend. You can see the slides here, and download the demos here. Both talks seemed to go well, and I got a few compliments on presentation style after. As far as I've figured out, my presentation style is high energy plus demo plus explanation (= keep them awake, wow them, teach them).

After all the talks, we all enjoyed free drinks in a bar just for us, and then a bunch of us went to dinner at a nearby Italian place. There, I experienced the amazingness that is oysters+bacon and oyster+cheese. Omg, I never thought to put oysters plus melty cheese and white sauce together, but it is a cheesy seafood heaven in your mouth. We finished the meal by drawing naughty doodles on the tablemat and surrounding them with HTML tags (my use of <table> tags was finally justified).

I must admit; my motivations in attending BarCamp Sydney were twofold: 1) find an excuse to talk about Google/web technology, and 2) find some friends. Someone at the after-party actually told me (drunkenly) "I'd like to be your friend", and I replied honestly, "well, actually, I don't have any yet, so that'd be great". He was kinda taken aback, but hey, it's the truth. And I actually think maybe I did make a couple friends today, and I'm thrilled about that. I'm hoping that today will be a precursor to much fun + geeky times in Sydney, and I thank all of you who welcomed me here today :)

All pics here were taken by Halans, one of the unorganizers of the conference.