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:
-
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:
-
Click on the "Insert" menu and then select "Gadget...":
-
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:
-
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:
- 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 :).
-
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.
-
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.
-
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.
- 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. :)
