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.

You will need basic spreadsheet skills for this tutorial.

Getting Started

Step 1

Login to Google and search Google Spreadsheet Mapper 2.0.

Click the link to go to the Tutorial: Spreadsheet Mapper 2.0 page.

googlesearch

or, click on the following link: http://earth.google.com/outreach/tutorial_mapper.html

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

tutorials

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.

Make sure you edit the White cells

Step 6 

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

rename

Step 7

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

Enter your basic information

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. Do not double-click the cell to copy.

paste

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.

myplaces

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.

places

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.

map

Step 14

Examine the Sample (#3) location.

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

sample location

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.

template

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.

row81

Step 17

Google Maps displays the locations.

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

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.

placemarks

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.

whitecells

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.

changeinfo

Step  21

Switch to the PlaceMark Data worksheet.

placemarkdata

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 Maps require latitude and longitude information to work. Google Earth can use either latitude and longitude information or raw address information to place location markers.

worksheet

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.

changeinfo

Step 24

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

Enter the Actual Data

Step 25

Enter the actual data for your Google Map.

actual data

Step 26

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

top of the column should match

Step 27

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

This updates the information to every instance of this spreadsheet.

republish

Step 28

Switch to Google Earth and control+click “Link to spreadsheet” (1) and select refresh (2).

refresh

Step 29

The new location points with the changed color and changed label color are displayed.

new points

Step 30

Click any location icon or sidebar list item to see the custom information and graphics displayed in the template.

see custom info

Get the Latitude and Longitude

Step 31

Return to the first page of the spreadsheet and scroll to row 81, control+click the link to view the map in Google Maps.

return

Step 32

None of the locations appear in the map.

Note the sidebar folders show, but have no content.

map

 

Step 33

The spreadsheet Placemark Data page shows there is no latitude or longitude information.

Note the bold red error message. Google Maps require latitude and longitude coordinates.

error

Step 34

Copy the indicated header row cells, the empty latitude, longitude cells and the address information.

copy

Step 35

Go to GPSVisualizer.com and click the “Geocode an address link.”

gpsvisualizer

On the next page select:

2. Geocode multiple addresses

Step 36

Use the form to find the latitude and longitude of your addresses.

Paste in the copied information (1). Click the “start geocoding” button (2). Copy the results information (3).

geocode address

Step 37

Paste the copied Results information into a text editor.

texteditor

Step 38

Save the file with a .csv (comma separated value) extension.

save

Use the Latitude and Longitude

Step 39

In a new Google Spreadsheet go to File>Import.

import

Step 40

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

import

Step 41

The information is displayed in a new spreadsheet.

new spreadsheet

Step 42

Copy the cells with the latitude and longitude information.paste

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.

latlong

Step 44

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

starthere worksheet

Step 45

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

final

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.