Creating Dynamic Client-side Maps Mashups with Google Spreadsheets

Friday, March 30, 2007 at 9:34:00 AM

In the past, only those privileged few developers with access to server-side scripting and a database like MySQL were able to create easily updated dynamic maps mashups. With the introduction of the Google Spreadsheets Data API, all of that has changed. Like the other GData APIs, the spreadsheets API has an optional JSON output. If you don't know about JSON, it's a lightweight interchange format that allows for hierarchical nesting as well as various data types. But more importantly, JSON output is native Javascript code itself, and with a callback parameter that wraps the JSON output text in parentheses and a function name of your choosing, it allows you to get around some of the cross-domain security issues you might encounter in typical client side JavaScript.

So if you want the data from a published spreadsheet feed in your page, there's no need to worry about a server-side proxy for cross-domain XMLHttpRequest calls: just create a script tag with the feed in JSON format as the source, and you've got your data. Developers used to writing server-side proxies may not be impressed by this, but I encourage you to give it a whirl - once you go JSON, it's hard to go back.

But let's bring the discussion back to maps. Below is an example of a map based on a JSON feed from a spreadsheet of my favorite Seattle restaurants.

The steps to creating it are as follows:

  • When the page loads, we call a cm_load function that loads in a GMap2 at a default location, and then calls cm_getJSON
  • The cm_getJSON dynamically creates a new script tag and inserts it in the page. The src of the script tag is the (public) spreadsheets feed URL plus additional parameters to specify a function to call when the JSON returns ("&alt=json-in-script&callback=cm_loadMapJSON")
  • In the callback function, cm_loadMapJSON, we iterate through the entries in the JSON feed (the table rows) and create a new marker and sidebar entry for each of them.

Here's the coolest part though - I didn't code this map. My Spreadsheets->Map wizard did. If you've got a spreadsheet of location data and you want a dynamic map based on that spreadsheet, just try out the wizard. It will generate the code that you can embed on your website or upload to your Google Pages account, and if you're an adventurous developer, you can use the code as a starting point.

If you don't want to get a hosting account, check out API developer Esa's quick Spreadsheets mapper. Just put columns named 'lat' & 'lng' in your spreadsheet, put the public key in the URL of his page, and you've got a permalink to your map.

Start playing around, and let us know in the Maps API group or the Spreadsheets API group if you have any questions.