Spreadsheet Mapper 2.0

Upgrade Now!

Spreadsheet Mapper v3 is now available, and offers more placemarks, simpler publishing, and other benefits. We recommend that you use Spreadsheet Mapper v3 instead of the tutorial below.

Some of the features of Spreadsheet Mapper 2.0 include:

  • Six ready-made balloon designs to quickly create up to 400 attractive placemarks
  • A simple HTML templating system to create your own balloon designs.
  • Works in Google Earth and Maps.

This tutorial shows you how to enter data in an on-line spreadsheet to generate a set of placemarks in Google Earth and Maps. Google Docs' web-based, collaborative editing allows your team members to simultaneously enter data and instantly publish updates.

Using this tool you could: showcase your organization's projects, program sites or partners; map your offices, volunteers, or resources; or visualize your data on local, regional or global scales.

download kml See Edge of Existence Mammal and Amphibian layers that were created using this tool!

This video demonstrates how to use the Spreadsheet Mapper tool to map data in a spreadsheet in Google Earth (2:50).

Prerequisites

  • Absolutely no programming skills needed!
  • You will need Google Earth installed on your computer. Download the latest version here. (It's free!)

Before You Begin

Spreadsheet Mapper is a powerful tool for mapping up to 400 locations. If you have more points you want to put on the map, try the "Export to KML" feature in Google Fusion Tables.

 

          This video offers step-by-step in-depth instructions on how to use the Spreadsheet Mapper. (5:00).

Getting Started

Open the starter spreadsheet

  1. To begin, open the starter spreadsheet. You may need to log into your Google Account to proceed. If you are asked if you want make a new copy of the document, click "Yes, make a copy."
  2. Once loaded, give it an appropriate name: click the File button above and choose Rename.

Enter Basic Information

  1. On the start here sheet, complete "Author's Information"; and "About your KML Document" sections of the Basic Information form.

  2. Enable "Google Maps Compatibility"; if you want your layer to work in Google Maps and older versions of Google Earth (pre-4.2).

  3. Optional: Access the "Advanced/Optional Settings"; by clicking the tab indicated on the left to un-hide rows.

Publish your spreadsheet

  1. Click Share button and choose Publish as a web page.

    publish your spreadsheet

  2. In the next window, un-check the box next to "Automatically republish when changes are made," and click the Start publishing button.

  3. Under Get a link to the published data, select "TXT (Plain text)" from the drop-down menu.

  4. Copy the URL by right-clicking on the link and choosing "Copy."

    copy your spreadsheet published url

  5. Paste the URL into the white cell provided under "Publish spreadsheet".

    paste the published url into the indicated cell

View templates in Google Earth

  1. Copy the "Network Link KML" cell.

    copy the network link KML

  2. Switch to Google Earth, select My Places, then right-click and Paste.

    paste network link KML into Google Earth

  3. Sample placemarks for the six available templates will be displayed in Google Earth.

  4. Click on the sample placemarks and decide on which balloon templates you'd like to use for your project.

    sample placemarks for six available templates

  5. You can also customize the six HTML based balloon templates, or create your own. See the Creating 'Spreadsheet Mapper' Templates tutorial to learn how.

View templates in Google Maps

  1. "Backwards Compatibility" mode must be enabled to view your layer in Google Maps and Earth 3.0-4.1.

  2. Control-click the link on the start here sheet to view the sample placemarks in Google Maps.

    click link to view placemarks in Google Maps
    View sample map

Enter your information

Prepare your template

  1. Go to the sheet for each template you've chosen and replace the values of the "Static Variables" with your information.

    static variables

  2. Optional: To change the look of the placemark icons and labels, modify the "KML Style Variables" as desired

    style variables

Enter placemark names and locations

  1. Go to the PlacemarkData sheet, delete the sample data, and start creating your own placemarks.

  2. A name and location are required for each placemark. Coordinates must be in decimal degree format (e.g., -122.34567).

    Tip: Don't know the latitude or longitude of your location? Go to Batch Geocoder.

    • Optional: Put your placemarks into folders by specifying a "Folder name". All the placemarks in the same folder must be grouped together, otherwise duplicate folders with the same name will be created. Use the

      sort bar on the "Folder Name" column to keep placemarks in the right order.
    • enter names and locations for each placemark

Apply a template to each placemark

  1. Enter the desired template number for each placemark into column H. Available templates are listed in the table at the top of the sheet.

  2. Each template uses the spreadsheet's columns for different pieces of balloon content (e.g., Template #1, column I = "Left Column Header"; Template #2, column I = "Title").

    Make sure you enter each placemark's data according to the appropriate column headers as show in the template list.

  3. To make data entry easier, highlight a specific template by entering its number in cell H10 (see steps a, b, c below). That template's column headers will be highlighted in the template list, and will also appear in row 10, right above the gray header bar.

    choosing a template for each placemark

Optional: Setting Time, LookAt views and Snippets

  1. You can enter advanced placemark information including views (LookAt), time stamps (see valid formats), and Snippets.

  2. Click the indicated tab in the upper right of the sheet to un-hide the extra columns, and enter values for your placemarks.

    unhide advanced placemark options

Publish your changes

  1. Go to the Share button on your spreadsheet, choose Publish as a web page, and click the Re-publish now.

  2. For Google Earth: In the Places panel, right-click on the Network Link (named "Link to - Spreadsheet") you added earlier, and select Refresh to load your changes. A second refresh of the Network Link may be required to see style changes made on the template sheets.

    Note: You can check "Automatically re-publish when changes are made" in the spreadsheet publish options, but note that re-publishing after changes isn't instantaneous (as with above). Thus, you might have to wait a few minutes before changes will be visible in Google Earth when refreshing the Network Link.

    refresh the network link in Google Earth
  3. For Google Maps: control-clicking the Google Maps link on the start here page.

    Note: Sometimes the spreadsheet doesn't respond quickly enough to the Google Maps request to view your map. In this case you will see a "File not found at..." error. Try refreshing the browser window so that Google Maps tries to download the file again.

    If you want to embed this map on your website in Google Earth, it's best not to pull the map directly from the spreadsheet (for the reason above). Follow the instructions below under the section "Embedding your layer on a website using Google Maps of the Google Earth Plugin."

Sharing your placemarks

Anyone can view the placemarks generated by your spreadsheet in Google Earth and Maps.

Sharing your layer in Google Earth

For access to up-to-date data directly from the spreadsheet:

  1. In Google Earth's Places panel, right-click on your Network Link (named "Link to - Spreadsheet") and choose Save As...

  2. Give the file a descriptive name and save it to your computer. The resulting KML/KMZ file will always retrieve the latest data direct from your spreadsheet.

  3. Email the KML/KMZ file you saved above to your colleagues and friends, or post the file on your website.

For a static snapshot of your current map (will not connect directly to the spreadsheet):

  1. In Google Earth's Places panel, right-click on top-level folder of your map (a blue globe icon) and choose Save As... This is the first item underneath the "Link to - Spreadsheet" Network Link.

  2. Give the file a descriptive name and save it to your computer. The resulting KML/KMZ file will be a static snapshot of your map. Users will not have access to updates direct from the spreadsheet.

  3. Email the KML/KMZ file you saved above to your colleagues and friends, or post the file on your website.

Embedding your layer on a website using Google Maps or the Google Earth Plugin

  1. Follow the steps above to save a static version of your map as a KML/KMZ file.

  2. Upload your KML/KMZ file to a webserver.

  3. Use the Embedded KML Viewer Google Gadget described in the Embed Earth in your website tutorial.

Promoting your KML

Also see the Tutorial: Promoting Your KML for more tips on sharing your layer with the world.

Discussion & feedback

Have questions about this tutorial? Want to give us some feedback? Visit the Google Earth Outreach Discussion Group to discuss it with others.

What's next?