Google docs just turned the corner for me, from being ‘a convenient trimmed down online version’ to being ‘an innovative online version packed with features’. I am quite impressed, and hope this is the first of many enhancements to come that make life (and the world) that much easier.
Backstory: I am having a wedding in the UK, and expecting quite a number of out of town guests to attend. Part of the web site that I have set up to support the wedding includes a list of potential accommodation sites, with links and short descriptions. Exactly the type of thing that begs for an interactive map. I did some research on the best way to set up Google Maps to support this feature, and most of the options required hacking some code together and using the API.
I found an an article on apitricks blog, that clearly lays out a method to geo-code using a list of addresses in a google docs spreadsheet. The process is incredibly simple.
- Make a new Google Docs spreadsheet
- Column A: Include a column that has descriptive information (Name, text description) after the address column. This will be used as the description when you mouse over the point on the map.
- Column B: Load up your addresses – best bet is to include all address components in a single field (street address, city, state, zip, country all together in a single cell). If you have the components in separate cells, concatenate them into a single field.
- Column C: all rows get the same URL “http://maps.google.com/maps/geo?output=csv&q=” – this will be used to build and submit the query to google maps.
- Column D: Call the “ImportData()” function. This will be run as a formula within google spreadsheets, and will return Lat/Long information that can be used to develop the map. The syntax is as follows: “=ImportData(CONCATENATE(C1,B1))” – where “1″ is for the row number (“C2,B2″ for row 2, “C3,B3″ for row 3…)
- Columns E, F, and G will auto-magically be populated by the google spreadsheet as the output of the ImportData function. Columns F and G are the keys to the map (Lat and Long, respectively).
- Column H – for simplicity, I make this column equal to the descriptive text in Column A (or you can pull a number of fields together here). This will be used to populate the meta data for the flags (the mouseover text that appears for each flag).
- Go to the “Insert” menu, and choose “Gadget…”
- Choose “Maps” from the available options, then the type of map you would like to use (I use the 2nd option, “Map”).
- In the gadget settings, choose the “Range” to include the Lat and Long, as well as the final descriptive text columns. (In my example, my range is “Sheet1!F2:H42″. This corresponds to worksheet 1, and columns F, G, and H, rows 2 through 42.
- Click the checkbox for “last column tooltips” (this is the reason we created and included column H).
- Choose map style… I used the hybrid map.
- Click “Apply and close”. A map will appear on your spreadsheet, populated with flags and tooltip descriptions corresponding to the addresses / Lat&Long in your spreadsheet.
- Click the map, and a “Gadget” menu will appear in the top right corner… click it and a dropdown list will appear. Choose “Publish Gadget”, and it will provide a handy bit of script text that you can copy and paste into your web page.
Once your data is populated in the spreadsheet, the whole process takes less than 10 minutes. That absolutely amazes me. One limitation is that Google Docs only allows up to 50 functions to be called on a single sheet – so you are limited to geo-coding 50 addresses. There is nothing that would limit you from loading the addresses 50 at a time, and hard-coding the Lat/Longs as many times as you need to for your list.