Using Spreadsheet Mapper 2.0 with Google Earth & Google Maps

Posted on March 11, 2008. Filed under: Google Earth, Google Maps, Network Links |

Over the next few weeks I’m going to be writing a series of posts on some of the lesser know tools provided by Google for creating data layers in Google Earth and Google Maps.  The Google Earth Outreach program provides a number of these tools.  According to a press release from early last summer, Google Earth Outreach is “designed to help nonprofit organizations around the world leverage the power of Google Earth to illustrate and advocate the important work they do.”  The program includes comprehensive online guides, video tutorials, and case studies about using Google Earth specifically targeted to the needs of nonprofit organizations.  In addition, organizations can also apply for a Google Earth Pro grant ($400 value). 

In this initial post we’ll take a look at the Spreadsheet Mapper 2.0 tool created by the Outreach team for creating Google Earth and Google Maps placemark layers using Google Docs.  Google Docs is a great way to create, share and collaborate on documents, spreadsheets and presentations online.  Spreadsheet Mapper takes advantage of this online, collaborative environment by allowing you to create placemark layers for display in Google Earth and Google Maps through a spreadsheet created in Google Docs.  Because Google Docs is a collaborative tool, members of your team can simultaneously enter data and instantly publish updates to GE and GMaps.  So, let’s take a look at how this is done through a basic example.

In this example we’re going to create a placemark KML file containing existing Starbucks locations. The Starbucks data contains a unique identifer for each location along with the latitude, longitude coordinate pair and a physical address.  This data is contained within the “Starbucks No Linefeeds.csv” file which was last updated in January 2008. This file contains almost 9,000 Starbucks locations.  Since there are so many locations I’m only going to use a sample area for this example. The download also contains a Starbucks.bmp image which we’ll use in our next post in the series which covers Spreadsheet Mapper templates for styling icons and information balloons.  Let’s get started.  By default, Spreadsheet Mapper will use various templates to create some sample data.  We’ll do this first and then replace the sample data with our own Starbucks location data.

  1. Open the starter spreadsheet.  If you haven’t already done so you’ll need to login to your Google account or create one if necessary.
  2. Select File –> Rename and give you spreadsheet a name (“Starbucks Locations”).
  3. Fill in the “Author’s Information” and “About your KML Document” sections.
  4. Optional Parameters
    • Enable “Google Maps Compatibility” if you want the layer to work in Google Maps
    • Access the “Advanced/Optional Settings” by clicking the tab indicated on the left to un-hide rows
  5. Click the Publish tab and select Publish Now.  This step will publish the document to the web at the URL listed.
  6. Copy the publisher URL and paste it into the white cell provided under “Publish spreadsheet”.
  7. Copy the “Network Link KML” cell that you see below, open Google Earth, select My Places, then right-click and paste.
    Network Links capability in Google Earth provides for the delivery of dynamic data to your users.  We are using a Google Docs spreadsheet which can be edited by multiple users simultaneously.  Network Links in Google Earth are a perfect complement to a Google Docs spreadsheet since they can automatically refresh the Google Earth display to reflect updated data from a spreadsheet.  Get more information on Network Links in our “Mastering KML in Google Earth” e-learning course.So, at this point we’ve copied the sample data contained in the template into Google Earth.  You will notice a variety of folders, placemark icons and information balloons which have been created based on parameters found in the templates contained within Spreadsheet Mapper.

    The Spreadsheet Mapper comes with six templates that can be used to control icon and balloon styles.  Click any of the links at the bottom of the spreadsheet to get more information about each of these templates.  We’ll cover detailed information about the templates in a coming slide, but for now you can get an idea of how they are structured.
  8. At this point you’d want to prepare your template values.  However, as I mentioned above I’m going to save the details of altering the template values for another post since it really deserves a thorough explanation. 
  9. Now let’s add in the Starbucks data.  Open the “Starbucks No Linefeeds” Excel spreadsheet which contains the latitude,longitude, identification, and address values for each Starbucks location.  Due to the large size of the file I’m only going to add in Starbucks locations for my city, San Antonio.  Go to the PlacemarkData link at the bottom of the spreadsheet.
    • The Folder Name is optional, but in this case we’re going to define the folder name as “Starbucks” for each placemark instance.  What this will do is group the placemarks under the same Folder in Google Earth.  This would be helpful if you’d like to group Starbucks locations by city (i.e. San Antonio, Dallas, Houston, Austin). 
    • For the Placemark Name column I’m going to add in the unique identifier for each location based on the information pulled from the Excel spreadsheet we downloaded.  You should be able to copy and paste the data from MS Excel to the Google Docs spreadsheet to save time.
    • We’ll also enter the latitude, longitude values for each Starbucks location, also pulled from the Excel spreadsheet we downloaded.  If you don’t have coordinate values for each placemark you can enter an address which can be used to generate the placemark. 
    • Finally, we specify a template (#5 in this case) to define our icon and balloon styles.  The spreadsheet should look something like you see below.
  10.  Click Publish –> Re-publish document in your Google Docs spreadsheet.  Then refresh your network link in Google Earth by right clicking on “Link to – Spreadsheet” in the Places panel and selecting Refresh.  This should refresh the Google Earth display with the new Starbucks locations we entered in the Google Docs spreadsheet.
In our next post we’ll cover the templates provides by Spreadsheet Mapper and also take a look at how you can create your own templates.

Make a Comment

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

4 Responses to “Using Spreadsheet Mapper 2.0 with Google Earth & Google Maps”

RSS Feed for GeoChalkboard Comments RSS Feed

[…] – Spatial education site Geochalkboard has released a tutorial for the spreadsheet mapper which Google released that lets you create nice KML files without […]

[…] To my mind, it seems like that point would be the point that was farthest away from the nearest Starbucks. I’d love to get some comments on this idea, since I haven’t taken the time to prove that it’s correct, nor have I analyzed the running time (except casually). As an aside, this method also seems to ignore boundary conditions unless you build the mesh to include the land bounded by US borders but not by the outermost ring of Starbucks locations; I’m not sure how best to deal with that. You can get the relevant data (in latitude/longitude form, with some extra information) from here. […]

[…] Spreadsheet Mapper 2.0 KML Reference Code Using Spreadsheet Mapper with Google Earth and Google Maps […]

Thanks for the tutorial!
I successfully made a google map that reads my spreadsheet.

I can see it from, but I can’t embed it on my webpage. I tried embedding it by using the “link” feature on google maps, and copying the “Paste HTML to embed in website” But the embedded map does not have the data from my spreadsheet on it.

Can you tell me how to embed the map with the dynamic information from my spreadsheet, the way the map looks when I see it on google maps?

Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: