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:

• <a href="http://maps.google.com/maps?q=1005+Gravenstein+Hwy +N.,Sebastopol,CA,95472+(OReilly)&hl=en">O'Reilly & Associates</a>


 

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:

	="

• <a href="">" & A4 & "</a>
• "

  This is sort of ugly because we need to include double quotes (") in the result, but the double quotes are used as the string delimiter. In Excel you can insert a double quote into a string by entering three double quotes. The result is:

  • <a href="http://maps.google.com/maps?hl=en&q= 1005+Gravenstein+Hwy+N.,Sebastopol,CA,95472+(OReilly)">O'Reilly & Associates</a>
  
  
   

  Now we want to get those links into our own HTML page. Most spreadsheet programs provide other tools to manage hyperlinks, but the simplest way is to just copy the column by selecting the whole thing with Edit images/U2192.jpg border=0> Copy (or Ctrl-C) and then pasting it into your HTML file.

  Open that local file in your browser. You can use the power of the browser without exposing this page onto the Internet. If you are extremely paranoid, you need to aware that you are of course exposing your addresses to Google, which could in theory track address searches by your IP address and do something nasty with that information. Given Google's written commitment to avoid being evil, that shouldn't be a real risk.

  You can open a local HTML file by selecting File images/U2192.jpg border=0> Open in Internet Explorer, or File images/U2192.jpg border=0> Open File in Firefox. The result is a page of links like those in Figure 1-23.

  Figure 1-23. A page of links to Google Maps created in Excel

   

  1.9.1. See Also

  • "Inside Google Maps URLs" [Hack #7].