An unofficial blog that watches Google's attempts to move your operating system online since 2005. Not affiliated with Google.

Send your tips to gostips@gmail.com.

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.

12 comments:

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

    ReplyDelete
  2. @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. 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. 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. Here's an example of spreadsheet that works (ignore the "message" column):
    spreadsheets.google.com/pub?key=pHpNHC9GculPZQpuIX07TYQ

    ReplyDelete
  6. 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. This comment has been removed by the author.

    ReplyDelete
  8. 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. 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. 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. 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. Can I change markers to numbers to correspond with locations?

    ReplyDelete

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