Geocode Addresses in Google Sheets

In this section, you’ll learn how to geocode data by installing a free Google Sheets add-on tool. This allows you to geocode addresses directly inside your spreadsheet, which will be very useful when using Leaflet map code templates in Chapter 13.

Geocoding means converting addresses or location names into geographic coordinates (or x- and y-coordinates) that can be plotted on a map, as shown in Figure 3.13. For example, the Statue of Liberty in the New York City area is located at 40.69, -74.04. The first number is the latitude and the second is the longitude. Since the equator is 0 degrees latitude, positive latitude is the northern hemisphere, and negative latitude is in the southern hemisphere. Similarly, the prime meridian is 0 degrees longitude, which passes through Greenwich, England. So positive longitude is east of the meridian, and negative longitude is west, until you reach the opposite side of the globe, roughly near the International Date Line in the Pacific Ocean.

To map addresses, you first need to geocode them.

Figure 3.13: To map addresses, you first need to geocode them.

If you have just one or two addresses, you can quickly geocode them with Google Maps. Search for an address, right-click on that point, and select What’s here? to reveal a popup window with its latitude and longitude, as shown in Figure 3.14.

To geocode one address, search in Google Maps and right-click What’s here? to show coordinates.

Figure 3.14: To geocode one address, search in Google Maps and right-click What’s here? to show coordinates.

But what if you need to geocode a dozen or a hundred addresses? To geocode multiple addresses inside your spreadsheet, install a free Google Sheets Add-on called Geocoding by SmartMonkey, created by Xavier Ruiz, the CEO of SmartMonkey, a geographic route-planning company in Barcelona, Spain. Add-ons are created by third-party companies to expand features for Google Sheets, Google Documents, and related tools. Add-ons are verified to meet Google’s requirements and distributed through its G Suite Marketplace.

  1. Sign into your Google Drive account, go to the Geocoding by SmartMonkey Add-on page, and click the blue button to install it in your Google Sheets. The Add-on will ask for your permission before installing, and if you agree, press Continue. In the next window, choose your Google Drive account, and if you agree with the terms, click Allow to complete the installation. Google will email you to confirm that you have installed this third-party app with access to your account. You can always review permissions and revoke access in the future, if desired.

  2. Go to your Google Drive and create a new Google Sheet. Select the Add-ons menu to see the new Geocoding by SmartMonkey options, and select Create Template. The Add-on will create a new tab, named Geocoding, and automatically insert three sample addresses, as shown in Figure 3.15.

In the Google Sheets Add-On menu, select Geocoding by SmartMonkey – Create Template.

Figure 3.15: In the Google Sheets Add-On menu, select Geocoding by SmartMonkey – Create Template.

  1. Select the Geocoding by SmartMonkey > Geocode Details menu. The Add-on will create another spreadsheet tab, called Geocoding Details, and display the results from Google services for three new columns—latitude, longitude, and address found—as shown in Figure 3.16. Always review the quality of geocoded results by comparing the Address found column to the original Address entered.
Select Geocode Details to view latitude, longitude, and address found for each entry.

Figure 3.16: Select Geocode Details to view latitude, longitude, and address found for each entry.

When you paste your own address data into the spreadsheet to geocode it, follow these guidelines:

  • Each cell requires a full address. If your original data splits street, city, state, and zip code into different columns, see how to Combine Data into One Column in Chapter 5: Clean Up Messy Data.
  • The address should follow the format of the national postal service of the country where it is located. Separate terms with spaces.
  • Do not skip any rows in the Address column.
  • You can leave the Country column blank, but its default value is the United States. To specify other nations, use their top-level Internet domain code, such as es for Spain.
  • Give the tool time to work. For example, if you enter 50 addresses, expect to wait at least 15 seconds for your geocoded results.
  • Always inspect the quality of your results, and never assume that geocoding services from any provider are accurate.

If you need a faster geocoding service for US addresses, which can handle up to 10,000 requests in one upload, see bulk geocoding with the US Census in Chapter 14: Transform Your Map Data.

Now that you know how to use a Google Sheets Add-on to geocode addresses, in the next section you will learn how to collect data using an online form, and access it as a spreadsheet.