Google Sheets Feed Include Gap Rows

Mapping from a Google Spreadsheet

This tutorial shows you how to enter data in an on-line spreadsheet to generate a set of placemarks in Google Earth. 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:

Create 1000 placemarks - add more if needed.
Six simplified balloon design templates - add more if needed
Simplified publication process
Additional customization options

Spreadsheet Mapper 3 uses Google Apps Scripts to help generate the KML placemarks, and to provide useful tools.

Tutorial Contents

Before you begin
Let's get started...

Open the starter spreadsheet
Enter basic information and publish your spreadsheet
View the sample placemarks & templates in Google Earth

Enter your own information

Prepare your template
Enter placemark names and locations
Apply a template to each placemark
Optional: Setting Time, LookAt views and Snippets

Publish and view your changes
Need more or less rows?

Add more rows...
Remove some rows...
Messed up your rows or formulas?

Sharing your placemarks

Sharing your map as a layer in Google Earth

Discussion, Feedback & Questions
What's Next

More tutorials...

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.

Let's get started...

Open the starter spreadsheet

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
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...
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

On the start here sheet, complete "Author's Information" and "About your KML Document" in the Basic Information section.
Optional: Access the "Advanced/Optional Settings" by clicking the tab indicated on the left to un-hide rows.
From the menu bar, select File > Publish to the Web...
In the dialog box, expand the "Publish content & settings" section and un-check the Automatically republish when changes are made box.
Click the Publish button, and then click Close to dismiss the dialog.

View the sample placemarks & templates in Google Earth

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.
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.
The sample placemarks will be displayed in Google Earth. These placemarks show the starter balloon templates built into the spreadsheet.
Click on the sample placemarks and decide which balloon templates you would like to use for your project.
You will also be able to customize the HTML based balloon templates, or create your own.

Enter your own information

Prepare your template

Go to the sheet for the template(s) you've chosen.
Optional: Give the sheet a new name (eg: "Tem_OurSites").
Scroll down to the "Static Variables" section of the template and replace the values with your information, where applicable.
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

Go to the PlacemarkData sheet, and delete the sample data in the white cells.
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 at the pop up at the bottom center of the page, where the latitude and longitude will be listed. 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.
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

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.)
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.
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

You can enter advanced placemark information including LookAt (aka: snapshot-view), TimeStamp or TimeSpan (see valid time formats), and Snippet tags.
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.

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.
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.
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.

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.

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.
In the dialog box, enter the number of rows you want to add (up to 500 at a time).
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.

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.
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.
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:

Adding or deleting rows manually (directly in the spreadsheet, instead of using the tools in the Spreadsheet Mapper menu)
Dragging rows, cells, or columns of data to move them within the PlacemarkData sheet
Deleting or pasting over critical formulas in the PlacemarkData or KML sheets

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:

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.
Read the dialog box and click Ok to continue.
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.

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

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:

In Google Earth's Places panel, Right-Click your Network Link and choose Save Place As...
Give the file a descriptive name and save it to your computer as a KML or KMZ file.
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:

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.
Right-Click the top-level folder and choose Save Place As...
Give the file a descriptive name and save it to your computer as a KML or KMZ file.
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.

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

Google Sheets Feed Include Gap Rows

Mapping from a Google Spreadsheet

Tutorial Contents

Before you begin