Google Earth Outreach
Change language:

Creating 'Spreadsheet Mapper' Templates

The spreadsheet-to-KML tool featured in the Spreadsheet Mapper 2.0 tutorial allows you to use six ready-made balloon designs. But you can also use the tool's simple HTML templating system to modify our designs, create your own, or import/export templates.

This tutorial will explain the various parts of a template, and how to edit them.

Template Anatomy

  • Template Name - Defines the name of a template (e.g., "Wide Photo", Text Photo Banner", "WWF template").


  • Variables - Act as placeholders in your HTML design which are replaced with the appropriate values from the template sheets and PlacemarkData sheet.

    • "KML Style Variables" - Controls the look of the placemark icons and labels, and the balloon background. 'Highlight' style values apply when then user hovers the mouse over a placemark. Tip: use "Label Scale Highlight" = 0 to make placemark names appear only when the user puts their mouse over the placemark.

    • "Static Variables" - Elements that are the common to all of the placemarks using this template, such as text colors, logos or footers.

    • "Unique Variables" - Elements that are unique to each placemark, such as titles, descriptions or photos. Values for these elements are entered on the PlacemarkData sheet.

    • "Special Variables" - Access attributes of the placemarks themselves (e.g., name, address, etc. as specified on the PlacemarkData sheet). They cannot be renamed or altered, but can be used in the "Balloon HTML Layout".


  • Layouts - These layouts are the framework for your balloon design and KML styles. By inserting the above variables into them, your balloon content will be injected into the appropriate places.

    • The "Balloon HTML Layout" contains the HTML design of your balloon, defining the look of the balloon content. This might include the placement and formatting of titles, text, images and links. The "HTML Variable Summary" (to the right of the "Balloon HTML Layout"), provides a list of all the variables available to insert in your HTML.

    • For advanced KML users: The "KML Style Layout" contains the code for the template's KML <StyleMap>. It should only be modified by KML developers, and is accessible in the hidden row at the bottom of the sheet.

Example: Template #3

Live preview of a template

The box below is a live preview of Template #3 ("Text Photo Banner") from the starter spreadsheet in the Spreadsheet Mapper 2.0 tutorial.

Screenshot of a sample placemark

This screenshot shows a sample placemark using Template #3, and where the different types of variables are used in the template.

Template 3: diagram showing which types of variables are used in this template

Balloon HTML Layout

The "static" and "unique variables" below, wrapped in {curly_brackets}, are color-coded to match the balloon screenshot above.

  • Static elements of the HTML code which are common to all placemark balloons (photo widths, organization name, etc.) are in red.
  • Unique elements which are unique to each individual placemark balloon are in blue. 
<table width="{static_Balloon_Width}" border="0" cellpadding="5" cellspacing="0">
  <tr>
    <td><img src="{unique_Top_Photo_URL}" width="{static_Top_Photo_Width}" align="left" /></td>
  </tr>
  <tr>
    <td><h2><font color="{static_Title_Text_Color}">{unique_Title_Text}</font></h2></td>
  </tr>
  <tr>
    <td><blockquote>
        <p>{unique_Paragraph_1_Text}</p>
        <p>{unique_Paragraph_2_Text}</p>
        <p>
          <a href="{unique_Read_More_Link_URL}">{static_Read_More_Link_Text}</a>
        </p>
      </blockquote></td>
  </tr>
  <tr><td>&nbsp;</td>
  </tr>
  <tr>
    <td>
      <table width="100%" border="0" cellpadding="0" cellspacing="0" bgcolor="#e8e9cb">
        <tr>
          <td width="99%" align="right" valign="middle">
            <table border="0" cellpadding="0" cellspacing="0">
              <tr>
                <td align="right" valign="top">                  
                  <h2><font color="{static_Organization_Text_Color}">{static_Organization_Name}</font></h2>
                </td>
              </tr>
              <tr align="right" valign="top">
                <td><a href="{static_Organization_Link_URL}">{static_Organization_Link_Text}</a></td>
              </tr>
            </table>
          </td>
          <td width="1%" align="right" valign="middle"><img src="{static_Logo_URL}" width="{static_Logo_Width}" /></td>
        </tr>
      </table>
    </td>
  </tr>
</table>

Editing templates

Jump to: modifying, creating, or importing templates.

Modifying an existing template

  1. Select the sheet of the template you want to modify. Change the value of the "Template Name" if desired.

  2. Edit the values of the "KML Style Variables" to change the look of the placemark.

  3. Specify names and values for the "Static Variables", which are elements common to all balloons.

  4. Define names for the "Unique Variables", which pulls in data values unique to each placemark from the PlacemarkData sheet.

  5. Edit the "Balloon HTML Layout" to position and format the content in your balloon. You can use programs like Dreamweaver (commercial product) or NVU (free and open source) to edit your HTML.

    Details:
    • Copy the "HTML Variable Summary" cell (to the right of the "Balloon HTML Layout") into an HTML editor. The summary contains all the static, unique and special variables available in the template. Because it is wrapped with HTML comment tags (<!-- comment here -->) the summary won't interfere with your design.

    • Copy and paste the "Balloon HTML Layout" into the HTML editor, below the variable summary, and modify to you liking.

    • Simply cut and paste the variables (including the curly brackets) from the summary section into your HTML design where needed.

    • When transferring your HTML back into the spreadsheet, make sure to copy only the design portion back into the spreadsheet.

    • Important: When pasting HTML layouts back into the spreadsheet, you must first edit the cell (double-click or press F2) and paste directly into the edit box. Otherwise, the line returns in the HTML will overwrite other portions of the template. See this step of the "Importing a template" section for more details.

Creating a new template

  1. Either overwrite one of the existing templates and follow the modification instructions above (easy)

    or...

    Duplicate one of the existing template sheets and continue with the steps below (more complex).

    duplicate a sheet


  2. Give the copied sheet a unique name (Rename) - later you'll load this template using its sheet name.

    Note: Consider re-naming duplicate template sheets something like "TemplateA", "TemplateB", or "My Template". This is because there are six active slots available for loading templates, and that slot number is how you specify which template a placemark should use.

    Creating new sheet names that end in numbers (e.g., "Template7") could cause confusion. For example, you might name your new sheet "Template7" and activate it by placing its name in the first slot (#1) of the template list on the PlacemarkData sheet. In this case you would have to specify "1" for a placemark to use the template found on sheet "Template7".

  3. Give the "Template Name" variable a unique value.

    give the new sheet a unique name


  4. Edit the template as described in the Modifying an existing template section above.

  5. New template sheets must be activated before you placemarks can use them. See the Activating templates section.

Importing a template

  1. Follow steps 1 & 2 from the Creating a template section or overwrite an existing template by completing these steps on an existing template sheet ("Template1-6").

  2. Go to the template you want to import. Copy all the cells under the Names and Values columns (cells B4 to C85) down to, but not including, the "Balloon HTML Layout".

    Note: The HTML code from the source template must be handled separately from other variables to avoid pasting problems due to line-returns in the HTML.

    select cells B4 to C85


  3. Select cell B4 on your new template sheet, and Paste the copied variable names and values from the source template.

    paste copied template variables into cell B4


  4. Copy the "Balloon HTML Layout" cell (B86) in the source template.

    copy the HTML layout


  5. Go to the new template sheet and either double-click or press F2 on the "Balloon HTML Layout" cell (B86) to edit it, place the cursor in the edit box, delete its contents, and then paste.

    Note: If you simply select cell B86 on the new template with a single click, and then paste, the line-returns in the HTML code will overwrite other parts of the template. Make sure to double-click or press F2 to edit a cell before pasting. This way all the line-returns in the HTML stay within cell B86.

    paste the HTML layout into the new template by double clicking on cell B86.

Activating templates

Up to six active templates

Six (6) template-holding sheets are provided in the starter spreadsheet ("Template1-6"). These can be duplicated if you want to have more templates in the spreadsheet. However, the spreadsheet's design is limited to having only six active templates at a time. Only active template can be used the placemarks you add to the PlacemarkData sheet. You can activate templates by putting their sheet names in the list on the top of the PlacemarkData sheet, in one of the six available slots.

How to activate a template

  1. Enter the sheet name which contains your new template to the list at the top of the PlacemarkData sheet by replacing one of the others.

    enter template name into list
    Pictured above: the template on sheet Template1 is now disabled, and the new one on the My Template sheet is activated.

  2. Use the new template's number in your new placemarks (e.g., "Outreach Template" on the "My Template" sheet is #1).

  3. Edit or add placemark data on the PlacemarkData sheet to fit the new template's unique variables.

Reminder: Re-publish your spreadsheet and refresh your network link to see your changes.

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?

Want to make your layer more attractive and easier to use? Check out this tutorial which shows you how!

Note: This tutorial replaces the following tutorials: Adding Time as a Fourth Dimension and Creating KML From a Spreadsheet.

©2011 Google - About Google - Terms and Conditions - Privacy Policy