April 2, 2007

Map a List of Locations from a Google Spreadsheet

Google Spreadsheets has an interesting advantage over Microsoft Excel and other desktop applications: you can publish a spreadsheet and always have the latest version on the web without manually republishing it. Because Google Spreadsheets has an API, you can use the spreadsheet as an easy-to-update structured file always available on the web, as an input for web applications.

Pamela Fox wrote a post on Google Maps Blog that details how to create a map from a Google Spreadsheet that contains geographical information.

First you need to build a spreadsheet that has a header and a list of locations described by title, latitude, longitude. There's a nice batch geocoder that uses Google Maps API and can help you build your spreadsheet: copy the generated text in a text file and import it in Google Spreadsheets.

Once you have the spreadsheet, you need to create the connection with Google Maps. There are three ways to do that:

  1. The simple way. This page requires to modify the URL and add the key parameter from the spreadsheet. That's all: no customization, the spreadsheet must have fixed column names, no embedding option.

  2. Almost as simple. This wizard lets you customize the map and embed it in your site.

  3. The hard way. Use Google Maps API and Google Spreadsheets API to build your own application.
at

12 comments:

  1. AnonymousApril 3, 2007 at 5:50 PM

    This still seems a little more user friendly to make a map with multiple locations and notes: http://www.batchgeocode.com/

    ReplyDelete
  2. Alex ChituApril 3, 2007 at 10:36 PM

    @Anonymous:

    You didn't get the whole point of this exercise: create a file that's easily updatable and acts as input data for a map. If you use Batchgeocode, you'll generate a new map everytime you need to add or edit some data. In the approach from this post, the spreadsheet is independent from the map and it updates automatically.

    ReplyDelete
  3. AnonymousJune 23, 2007 at 5:19 PM

    Hey, great hint! One question, maybe you can help... do you know how I could add categories (restaurant, bar, hotel) and give the pins different colors?

    Thanks!

    ReplyDelete
  4. Dennis DanielsAugust 16, 2007 at 4:17 AM

    Trying to get this too work... My spreadsheet is fairly simple... but I'm getting NaN) in front of each title element on the right. Furthermore, I've only one lat/long to test but two are showing up. Finally gmaps are complaining that it can't show that I need to zoom out... but with only one lat/long value that doesn't make sense does it? There is a second marker on the map but it's not coming from my data as I triple checked my spreadsheet. I'd appreciate any help here!

    ReplyDelete
  5. Alex ChituAugust 16, 2007 at 4:25 AM

    Here's an example of spreadsheet that works (ignore the "message" column):
    spreadsheets.google.com/pub?key=pHpNHC9GculPZQpuIX07TYQ

    ReplyDelete
  6. graeme.normanSeptember 12, 2007 at 4:50 PM

    I've struggled with various methods of having GoogleMaps load data from other files, with limited success - this method seemed to offer a simple alternative. Have followed all the steps carefully, but my markers are not showing @#$%. Oh well - (lots of head scratching here), back to the tutorial!

    ReplyDelete
  7. sobelitoSeptember 17, 2007 at 10:58 AM

    This comment has been removed by the author.

    ReplyDelete
  8. sobelitoSeptember 17, 2007 at 11:01 AM

    Hey All.

    I've been working on a codeless wizard for converting addresses in google spreadhseets to customizeable maps. Feel free to give it a whirl:

    http://mapalist.com

    Cheers.

    ReplyDelete
  9. AnonymousFebruary 22, 2008 at 4:40 AM

    Hey, I am created a map that displays markers using an XML file and can toggle marker types on and off. e.g bars, toilets.

    If I used the spreadsheet could they still be toggled on and off?

    Thanks, Joe

    ReplyDelete
  10. BradyMarch 28, 2012 at 4:41 AM

    I had this same problem about how to show my Excel data on a map. I finally built my own solution to make it super easy. Just copy and paste your data in, and it will show your map. You can embed your maps and everything. Take a look at http://topo.ly.

    ReplyDelete
  11. theycallmechadJuly 17, 2012 at 10:02 AM

    So I'm wanting to use a spreadsheet with multiple addresses on it and generate a unique map for each showing directions from each address to a predetermined location. Is this at all possible?

    Thank you!

    ReplyDelete
  12. AnonymousSeptember 12, 2012 at 1:01 PM

    Can I change markers to numbers to correspond with locations?

    ReplyDelete

Note: Only a member of this blog may post a comment.