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

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.

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