Using Spreadsheet Data in Google Maps and Google Earth

Tutorial: Using Spreadsheet Data in Google Maps and Google Earth

Introduction

This step-by-step tutorial will get you publishing map mashups without writing any code. By using the free Spreadsheet Mapper 2.0 from Google you can create quickly create maps containing dozens or even hundreds of map points.

Step 2

This page provides access to tutorials and links.

Showcase (1) is a great way to see the capabilities of Google Earth. Case Studies (2) provides information on how other organizations are using Google Earth. For more information on how to use Google Earth go to the Help link (3). If you don’t already have Google Earth installed you can download the latest version (4).

Step 3

Scroll down on this page until you see the Video Demo.

Watch the demo video–it provides a clear overview of the process.

Do not skip this video!

Starting the Spreadsheet

Step 5

This is the Spreadsheet Mapper template.

Basic instructions are included in the body of the spreadsheet. Note the ALL CAPS red warning to only edit white cells.

Step 6

Click the File menu and select Rename… Provide a name and click OK.

Step 7

On the first page of the spreadsheet enter the appropriate basic information about your organization.

Step 8

Click the Publish tab and then click the Publish now button.

Step 9

This publishes your spreadsheet to the web. The spreadsheet now has a URL. Copy the link to the spreadsheet.

Step 10

Paste the link to the published spreadsheet into the white cell under the “Publish spreadsheet” heading (1). Then click in the highlighted cell (2) and copy the resulting code. Donot double-click the cell to copy.

Use Google Earth

Step 11

Launch Google Earth.

Control+click My Places (1) in the navigation side bar. Select Paste (2) from the contextual menu. This pastes the code from your Google Spreadsheet to the Google Earth application.

Step 12

The link is processed and the location information is displayed.

Note the network icon (1) and the folders with category names (2). Each folder contains two versions of a pop-template.

Step 13

Google Earth displays the locations defined in the template.

Click the reveal triangle to display the two locations that use the Text & Photo Banner (#3) template for the pop-up.

Step 14

Examine the Sample (#3) location.

This is a complete display of what this location pop-up template looks like with content.

Step 15

Examine the Template (#3) location.

This is the #3 template with placeholder information. You will setup this template to display your own information.

Use Google Maps

Step 16

Return to the Google Spreadsheet and scroll to Row 81. The Spreadsheet Mapper Worksheet also produces Google Maps.

Control+click the link in row 82 to display the demo locations in Google Maps.

Step 17

Google Maps displays the locations.

Click the location icons to see the six pop-up templates displayed in Google Maps.

Change the Spreadsheet Information

Step 18

Return to the spreadsheet and scroll down to the Create Placemarks section.

To use Template #3, the first step is to customize the information that will be the same on all the pop-ups that use location template #3 Click the worksheet named Template3 at the bottom of the spreadsheet.

Step 19

This displays the Template #3 static information input page. This is where you change the static information that will appear in all of your location balloons.

Note the reminder to only modify white cells.

Step 20

This shows several elements that have been manually changed.

The icon color and highlight color are changed (1). The label color and label highlight color are changed (2). The organization information and link information are changed (3). The link for the logo URL and the size of the logo are changed.

Step 21

Switch to the PlaceMark Data worksheet.

Step 22

Note this is where the folder name category is set (1) and the placemark name (2). For example, if you wanted to organize listings on your published map into “coffee shops” and “restaurants” categories, you would set the folder name for each coffee shop to “coffee shops” and the folder name for each restaurant to “restaurants.” Note: it is important that you group all of the “coffee shops” and “restaurants” individual entries sequentially for them to appear grouped in the same folder. All of the coffee shops must be grouped. All the the restaurants must be grouped.

In the image below, the latitude and longitude for the template locations are set, but there is no address entry (3). Google Mapsrequire latitude and longitude information to work. Google Earth can use either latitude and longitude information or raw address information to place location markers.

Step 23

This step explains how to use the spreadsheet to select a balloon template style and enter the information that will appear in your final published map.

The first step is to enter number of the location balloon template that you want to use to display the individual location information. Enter the temple you want to use in the Template cell (1).

After you enter the template number and hit the Enter key the header row (2) changes and now displays the information you need to enter.

You can mix pop-up styles on a map.

The information for each location row must match the heading row for the template number. For example, to use the template 6 pop-up for the location in the last row below, change the template number (1) to 6 and enter 6 in the template number identifier column for the last row (3). The header row information (2) will change to match the information in the template 6 header row (4). You would enter the individual location information for template six base on the new information displayed in the header row.

Step 24

Select all the existing demonstration information and Delete>Clear section.

Enter the Actual Data

Step 25

Enter the actual data for your Google Map.

Step 26

The information in each cell matches the information at the top of the column.

Step 27

Go to the Publish tab (1) and re-publish the document (2) to update the information.

Step 38

Use the Latitude and Longitude

Step 39

Step 40

Import the .csv file and click “open now.”

Step 41

The information is displayed in a new spreadsheet.

Step 42

Copy the cells with the latitude and longitude information.

Step 43

Switch to the Placemark Data worksheet and paste in the latitude and longitude information. Make sure you have selected the top left cell in the latitude column before you paste in the copied information.

Step 44

Go to the Start here worksheet, scroll to row 81, control+click the link to view the map in Google Maps.

Step 45

Google Maps now has all the information to display the location points. Congratulations!

About this Tutorial

Tutorial presented by the Knight Digital Media Center at the University of California, Berkeley

Republishing Policy

This content may not be republished in print or digital form without express written permission from KDMC. Please see our Content Redistribution Policy at kdmc.berkeley.edu/license.

How valuable? Extremely, immensely, incredibly valuable. I expected this workshop to teach me a few things, like how to edit video. I did not know that, instead, I would learn a thousand things and would leave feeling like I just spent a semester at school again, in the best way. This workshop gave me a lightning bolt. I feel giddy again.