Spreadsheet Mapper 3

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's web-based, collaborative editing allows you and 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.

Some of the new and improved features in Spreadsheet Mapper version 3 include:

Spreadsheet Mapper 3 uses Google Apps Scripts to help generate the KML placemarks, and to provide useful tools. If you want to use the older Spreadsheet Mapper 2, which only uses spreadsheet formulas, but limits the number of placemarks and balloon templates, you can still access it here: Spreadsheet Mapper 2.0 tutorial

Tutorial Contents:

Before You Begin

Spreadsheet Mapper is a powerful tool for mapping point locations. It starts with enough spreadsheet rows for 1,000 placemark points, and provides a tool for adding additional rows. Adding more than a few thousand rows/points will slow down the spreadsheet mapper, and produce a very large KML file, in which case you may want to look at other tools such as Google Fusion Tables.


Let's get started...

Open the starter spreadsheet

  1. To begin, open a copy of the starter spreadsheet using the link below, which will create a new spreadsheet in your Google Docs account. You may need to log into your Google Account to proceed. When asked if you want to make a new copy of the document, click "Yes, make a copy."

    Spreadsheet Mapper v3 - get a new copy

  2. Once opened in your account, give the new spreadsheet an appropriate name by clicking on the title, or from the menu bar, select File > Rename...

  3. Check that the Spreadsheet Mapper menu has appeared at the top (next to the 'Help' menu). If you don't see the Spreadsheet Mapper menu, try refreshing the web page. After the spreadsheet re-loads, wait a few seconds and the menu should appear.

Enter basic information and publish your spreadsheet

  1. On the start here sheet, complete "Author's Information" and "About your KML Document" in the Basic Information section.

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

  3. From the menu bar, select File > Publish to the Web...

  4. In the dialog box, un-check the Automatically republish when changes are made box.

  5. Click the Start publishing button, and then click Close to dismiss the dialog.

View the sample placemarks & templates in Google Earth

  1. Copy the Network Link KML
    Select the "Network Link KML" cell by single clicking the cell (do not double-click to edit). Copy the contents with Ctrl-C or Right-Click > Copy.

    Note that even though the network link will always appear, it will not work until your spreadsheet is published to the web.

  2. Paste into Google Earth
    Switch to Google Earth, select the My Places or Temporary Places folder in the left-hand panel, then Paste using Ctrl-V or Right-Click > Paste.

  3. The sample placemarks will be displayed in Google Earth. These placemarks show the starter balloon templates built into the spreadsheet.

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

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

View sample placemarks & templates in Google Maps

  1. Click the link on the start here sheet to view the sample placemarks in Google Maps.

  2. Or click here to view a sample map.


Enter your own information

Prepare your template

  1. Go to the sheet for the template(s) you've chosen.

  2. Optional: Give the sheet a new name (eg: "Tem_OurSites").

  3. Scroll down to the "Static Variables" section of the template and replace the values with your information, where applicable.

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

Enter placemark names and locations

  1. Go to the PlacemarkData sheet, and delete the sample data in the white cells.

  2. Start creating placemarks by entering your own data! A name and location are required for each placemark. A template sheet name is also required, but we'll get to that below. Location coordinates must be in decimal degree format (e.g. -122.34567).

    Tip: Don't know the latitude or longitude coordinates of your location? You can get a location's latitude and longitude from Google Maps. Simply go to maps.google.com, right-click on the desired location on the map and select "What's here?" from the menu. Then look in the search box at the top of the page, where the latitude and longitude will be listed. Another method in Google Maps is to activate one of the LatLng labs, using the "Maps Labs" link at the bottom of the left-hand panel. You can also use a number of free services, including geocoder.us and Batch Geocoder. Spreadsheet Mapper can also accept addresses instead of location coordinates, but it is not recommended, since a KML with addresses requires Google Earth to look up and geocode each address, every time a user opens the file.

  3. 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. One way to do this is to use the dropdown menu in the "Folder Name" column header, and select "Sort Sheet". This will sort all the data below the "freeze sheet" divider bar, and group your placemarks into folders.

Apply a template to each placemark

  1. Each placemark needs a template, to determine its icon, info-balloon layout, and other styles. Designate a template for each placemark by entering the desired Template Sheet Name into column H. The Template Sheet Name is the name of the Template's sheet/tab at the bottom of your spreadsheet. (Spreadsheet Mapper version 2 used a unique name defined in each template, but in version 3, we just use the template sheet names.)

  2. Each template uses the spreadsheet's columns for different pieces of balloon content (e.g. for Template #1, column J = "Subtitle", but for Template #3, column J = "Image URL"). 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, and to show you which column is which for each template, you can highlight a specific template by entering its sheet name in cell I9. That template's column headers will appear in row 10, at the top of your data columns, as shown below.

Optional: Setting Time, LookAt views and Snippets

  1. You can enter advanced placemark information including LookAt (aka: snapshot-view), TimeStamp or TimeSpan (see valid time formats), and Snippet tags.

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


Publish and view your changes

Each time you make a change to the information in your spreadsheet, by editing the placemark data, the start here sheet, or one of your balloon templates, you must re-publish your changes in order to see them in your KML on Google Earth or Maps.

  1. Ensure that your KML is ready by looking at the KML Processing Status box at the top of your PlacemarkData sheet. If there are Errors indicated, scroll down the PlacemarkData sheet to find which rows they are in and fix them, otherwise those rows may be missing from your KML. If there are Warnings, those rows will usually have KML generated, but may not be correct.

  2. From the menu bar, select: File > Publish to the web..., and click the Republish now button. Click the Close button to dismiss the dialog box.

    Note: In the 'Publish to the web' dialog, there is a checkbox to "Automatically republish when changes are made". We recommend that you do NOT check this box. This will help prevent errors caused by the spreadsheet automatically republishing when the KML generation is in progress.

  3. For Google Earth: In the Places panel, Right-Click on the Network Link 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.

  4. For Google Maps: close the window or tab where you had Google Maps showing your KML, and use the Google Maps link on the start here page to open a new copy of it with your new KML. Simply refreshing the Google Maps page will not usually show your changes because the KML may be cached on the server.

    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.

    Note: 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 or the Google Earth Plugin."


Need more or less rows?

Add more rows...

If you want to create more than 1000 placemarks, you can easily add more rows to Spreadsheet Mapper.

  1. From the menu bar, select Spreadsheet Mapper > add more rows.

    If you don't see the Spreadsheet Mapper menu, try refreshing the web page. After the spreadsheet re-loads, wait a few seconds and the menu should appear.

  2. In the dialog box, enter the number of rows you want to add (up to 500 at a time).

  3. Click Ok and wait for the confirmation that rows have been added. The script will add the requested number of rows to both the 'PlacemarkData' sheet, and the 'kml' sheet.

    Note: Adding more rows will cause the scripts to be slower at processing your data and producing your KML. It also increases the size of your KML, which will already be rather large if you are using all 1000 rows.

Remove some rows...

If you added more rows than you needed, or you simply want to optimize your spreadsheet and KML by removing unused rows, we've provided a tool for that too.

  1. From the menu bar, select Spreadsheet Mapper > remove some rows.

    If you don't see the Spreadsheet Mapper menu, try refreshing the web page. After the spreadsheet re-loads, wait a few seconds and the menu should appear.

  2. In the dialog box, enter the number of rows you want to remove (up to 500 at a time) from the bottom of your PlacemarkData sheet.

  3. Click Ok and wait for the confirmation that rows have been removed. If the rows to be removed from your PlacemarkData sheet contain data, you will get a warning message and a chance to cancel the operation.

Messed up your rows or formulas?

There are a number of ways that the rows and formulas in your spreadsheet might get messed up and stop working correctly. Some common mistakes include:

If you have done any of these things, or are seeing unusual errors in your KML, then it is possible that the number of placemark generation rows in your KML sheet no longer corresponds to the number of data rows in your PlacemarkData sheet, or that one or more formulas in those sheets were corrupted. If so, we provide a script that will attempt to repair such errors:

  1. From the menu bar, select Spreadsheet Mapper > repair rows & formulas.

    If you don't see the Spreadsheet Mapper menu, try refreshing the web page. After the spreadsheet re-loads, wait a few seconds and the menu should appear.

  2. Read the dialog box and click Ok to continue.

  3. The script will add or remove placemark generation rows in the KML sheet to make them match the data rows in your PlacemarkData sheet. It will then replace the formulas in columns A and B of your PlacemarkData sheet, and in Column A (row 11 and below) in your KML sheet. This should repair most row and formula issues.


Sharing your placemarks

Anyone can view the placemarks generated by your spreadsheet in Google Earth and Maps. You can help make it easy for them with the techniques below.

Sharing your map as a layer in Google Earth

There are two ways to share your placemark map with Google Earth users. One is to use a link directly to the spreadsheet, so that Google Earth can always get the latest updated data/KML from your spreadsheet. This is useful for datasets that you update frequently, and for users who will always have an internet connection. The other method is to make a static snapshot of your data, and share that as a KML file with all the placemark data inside it. This is useful for datasets that don't change as much, or for users who want to view the KML when they are working offline. Sharing a static version of our KML is also useful if you want to continue editing your Spreadsheet Mapper, without your users seeing the edits... until you create a new static snapshot.

To share access to an automatically updating KML linked directly from the spreadsheet:

  1. In Google Earth's Places panel, Right-Click your Network Link and choose Save Place As...

  2. Give the file a descriptive name and save it to your computer as a KML or KMZ file.

  3. Email the KML/KMZ file to your colleagues and friends, or post the file on your website. This file contains a KML Network Link, so when users open it in Google Earth, it will retrieve the latest data directly from your published spreadsheet.

To share a static snapshot of your current KML, which contains all the data but will not get updates from the spreadsheet:

  1. In Google Earth's Places panel, find the top-level folder of your map. It is the first item/folder under the network link, and will likely have have a blue globe icon.

  2. Right-Click the top-level folder and choose Save Place As...

  3. Give the file a descriptive name and save it to your computer as a KML or KMZ file.

  4. Email the KML/KMZ file you saved to your colleagues and friends, or post the file on your website. This file contains a static snapshot of your map data. Users of this file will not have access to updates direct from the spreadsheet, but the file will work even when offline, since it does not need a connection to the spreadsheet.

Sharing your map in Google Maps

  1. Use the link on the 'start here' sheet to open your KML in Google Maps

  2. In Google Maps, click the Link button (chain-link icon) at the top of the left-hand-panel.

  3. Copy the top URL in the dialog box. This is a link to Google Maps with the KML loaded from your spreadsheet.

  4. Paste the URL/link into an email and send the link to your colleagues or friends, or post the link on your website.

Embedding your map 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. One option is to use Google Sites as a quick & easy place to save a file on the web.

  3. Go to the Embedded KML Viewer Google Gadget.

  4. In the gadget settings, enter the URL for your uploaded KML file.

  5. Choose the "View mode" you want: 2D (Google Maps) or 3D (Google Earth Plugin).

  6. Select the other options you want and click the Preview Changes button to see your map.

  7. Once you're happy with the map, click the 'Get the Code" button, and copy/paste the HTML snippet into your website where you want the map to appear.

  8. For more information on using the Embed KML Gadget, see the tutorial: Embed your Google Earth Project in your Website.


Discussion, Feedback & Questions

Do you have questions about this tutorial or about Spreadsheet Mapper? Do you want to give us feedback or discuss with other users and experts? Visit the "Nonprofit Outreach / Education" section on the Google Earth discussion forum to find answers, ask questions, and discuss with others:

Google Earth Forum - Nonprofit Outreach / Education
 


What's next?

Promote your KML

For tips on promoting your KML and sharing your map with the world, see the tutorial: Promoting Your KML.

Design your own info-balloons

Want to make your own HTML balloon designs? Check out our Creating 'Spreadsheet Mapper' Templates tutorial which shows you how. That tutorial was made for Spreadsheet Mapper version 2.0, but it should give you the basic knowledge needed to make your own balloon templates in version 3.0 as well.

Map even more data

Do you have too many placemarks, that are making Spreadsheet Mapper slow, or producing too large a KML? Try Google Fusion Tables for a quick way to map large datasets. We also have some Fusion Tables tutorials available: Map sample data with Fusion Mapper, and Map your own data with Fusion Mapper.

More tutorials...

See all the tutorials and tools available from Earth Outreach.

Go back to the main tutorial page.