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.
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).
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. Do not 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 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.
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.
This updates the information to every instance of this spreadsheet.
Step 28
Switch to Google Earth and control+click "Link to spreadsheet" (1) and select refresh (2).
Step 29
The new location points with the changed color and changed label color are displayed.
Step 30
Click any location icon or sidebar list item to see the custom information and graphics displayed in the template.
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.
Step 32
None of the locations appear in the map.
Note the sidebar folders show, but have no content.
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.
Step 34
Copy the indicated header row cells, the empty latitude, longitude cells and the address information.
Step 35
Go to GPSVisualizer.com and click the "Geocode an address link."
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).
Step 37
Paste the copied Results information into a text editor.
Step 38
Save the file with a .csv (comma separated value) extension.
Use the Latitude and Longitude
Step 39
In a new Google Spreadsheet go to File>Import.
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.