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
Example: Template #3
Editing templates
Activating templates

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

Modifying an existing template

Select the sheet of the template you want to modify. Change the value of the "Template Name" if desired.
Edit the values of the "KML Style Variables" to change the look of the placemark.
Specify names and values for the "Static Variables", which are elements common to all balloons.
Define names for the "Unique Variables", which pulls in data values unique to each placemark from the PlacemarkData sheet.
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 () 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

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).
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".
Give the "Template Name" variable a unique value.
Edit the template as described in the Modifying an existing template section above.
New template sheets must be activated before you placemarks can use them. See the Activating templates section.

Importing a template

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").
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 cell B4 on your new template sheet, and Paste the copied variable names and values from the source template.
Copy the "Balloon HTML Layout" cell (B86) in the source template.
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.

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

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.

Pictured above: the template on sheet Template1 is now disabled, and the new one on the My Template sheet is activated.
Use the new template's number in your new placemarks (e.g., "Outreach Template" on the "My Template" sheet is #1).
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?

Go to next tutorial: Building Better Balloons.
See video tutorial page.
See all tutorials.

Earth Outreach