Step 4 

Scroll under the video and click the link to open the starter spreadsheet.

You will need a free Google account. 

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! 

Related Links