Hack 8. Generate Links to Google Maps in a Spreadsheet

Get a handle on your own data with Google Maps.

What if you could create a private (or public) web page with links to Google Maps for each of the people in your company? Or all the members of your social club or church? You would be able to click on the names of people in your group and pop up a map of their location. Well, you can!

In "Inside Google Maps URLs" [Hack #7], we examined the structure of Google Maps URLs. Now we will use that information to create links to Google Maps that show your own contacts. We'll assume that you've got your contacts in a spreadsheet program, such as Microsoft Excel, Gnumeric, or OpenOffice Calc.

Figure 1-22 shows a sample data set with a name, street address, city, state, and ZIP Code set up in columns. This spreadsheet is available as http://mappinghacks.com/google/sample_data.xls.

Figure 1-22. Name, address, city, state, and ZIP Code in spreadsheet columns

We can go from this format to a full HTML link in Excel. The HTML link for each element will look like this:

 

The li tag will put each line in an HTML bulleted list. You will be able to click on the name and pop up a map centered on the address that goes with that person or company.

The first step is to use the concatenation function to put the name, address, city, state, and ZIP Code together in a new column. You can do this by using either the concatenation function or the shortcut & that does the same thing:

=CONCATENATE(B4,",",C4,",",D4,",",E4,"(",A4,")")

or:

=B4 & "," & C4 & "," & D4 & "," & E4 & "(" & A4 & ")"

This creates an address that looks like this:

1005 Gravenstein Hwy N.,Sebastopol,CA,95472 (O'Reilly)

You'll note that we use the custom location title trick [Hack #7] to associate the locations on the map with the names from our spreadsheet.

Next, we'll replace the spaces with plus signs and ampersands with %26 (so they don't mess up the format of the URL), and then add the q= part of the query. Excel provides the Substitute() function. You give it a string, then the value you want to get rid of, and a new value to replace the old value. In this command I concatenate the q= part of the parameter with the result of replacing the spaces in the combined address with plus signs.

=CONCATENATE("q=", SUBSTITUTE(SUBSTITUTE(F4," ","+"), "&", "%26"))

The result looks like this:

q=1005+Gravenstein+Hwy+N.,Sebastopol,CA,95472+(O'Reilly)

Next concatenate the other parts of the Google URL:

=$G$1 & G4

$G$1 is an absolute reference to a cell containing http://maps.google.com/maps?hl=en& and G4 is the cell with our cleaned up query parameters. Note how the order of the parameters doesn't matter. We can list the q= part first or hl=en first. This gives us the full Google Maps URL of:

http://maps.google.com/maps?hl=en&q= 1005+Gravenstein+Hwy+N.,Sebastopol,CA,95472+(O'Reilly)

We have to make just one little addition in order to get the full HTML that we want:

="