After creating this entry and the images, it was realized that another parameter, "visualization", was needed to make the solution generic. The updated Excel file reflects this but the screenshot does not.

Several customers in the past have asked if it is possible to place thematic maps in Excel and PowerPoint. And in the past, my response to these queries has been "probably, but it sounds difficult". Recently a new customer asked this questions, and, having recently blogged about Google's new map gadgets, I decided to have a go at getting maps into Excel.

In the recent map gadgets article, I showed examples of the Geomap gadget used in both region mode and in marker mode. Both modes are used by Google in the Google Analytics web interface. Maps are one of the most powerful data visualization approaches one can use, and maps are well suited for GA data. I can readily understand why customers would like them in their Excel reports.

What are the different technical options for getting a data-driven map into Excel? I was able to think of these three approaches. The first approach I considered was to use a Windows Meta File (wmf) with named shapes (i.e. for each state) which could be effected in VBA to set the fill color base on a metric value. I have used this approach myself in the past and I found several web articles and discussions suggesting this approach. But I was concerned about copyright issues with the drawing files and also with the logistics of locating and importing the files.

A Generic Gadget Connector

Another approach I then pursued was the use of the Google Geomap gadget. Excel supports adding interactive controls such as buttons and lists. With Excel for Windows (and possibly for Mac), you can in addition add any ActiveX control to your workbook. The WebBrowser Control is just what the name implies - a web browser you can place onto a worksheet. I surmised that this control could render the Google Geomap gadget, and it was a simple enough experiment to verify this. I just added the control and then used its "Navigate" method to point it at one of our blog samples. It worked, including the mouseover tooltips.

To make this a relatively simple task for ShufflePoint users, I was going to have to eliminate the need for them to create a special-purpose web page for each map they wished to have in a report. With ShufflePoint's generic web query (IQY) data connector, users place GA queries into Excel rather than use separate web query files for each query. The goal I set for myself was to do the same with the Google visualization gadgets. That this could be done was actually hinted at during the creation of the map blog. I realized that the different web pages which wrapped the gadget only had three core settings: a) the gadget package, b)the visualization, c) the AQL query, and d) the gadget options. Adding in the ShufflePoint access key makes four. Everything else in the wrapper is boilerplate HTML and JavaScript. A hour of development verified this to be sound. Consider the benefits:

1. ShufflePoint can host the wrapper
2. The wrapper will work with nearly any Google visualization gadget - not just maps
3. The five parameters can be placed into Excel worksheet cells

The screenshot below shows the results of my initial test. I replicated the three map views (using the same settings and queries) from my geomap blog article. I think it is a pretty slick addition to ones toolbox. There are a couple issues which detract from this solution. First, the user has to add WebBrowser controls and then for each one add a couple lines of VBA code to call their Navigate method. The other issue is that I'm not sure if this will work on Macs. In Goggling I found one article with code which hinted at it perhaps being possible, but the Windows version I created did not work on my Mac which is running Excel 2011.

The Generic gadget wrapper script "GoogleGadget.aspx" has now been deployed to the ShufflePoint application servers, so feel free to give it a try. Download the example with the link below to see how it is used.

The third approach I am experimenting with is to use Google's "Map Charts" image chart in Excel. Work is preliminary, so I am going to leave that for a near future posting. I'll also add some more examples of the connector presented here being used with some of the other cool Google visualization gadgets.

Screenshot of maps in Excel

Downloads

GoogleGadget.xls

References

Visualization: Geomap

http://code.google.com/apis/visualization/documentation/gallery/geomap.html