#StandWithUkraine - Stop the Russian invasion

Join us and donate. We are contributing all book royalties from 2022 to present to:
Save Life in Ukraine and Ukraine Humanitarian Appeal.

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

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 2.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 2.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 the first entry to copy its latitude and longitude, as shown in Figure 2.14.

To geocode one address, search in Google Maps and right-click to copy its coordinates.

Figure 2.14: To geocode one address, search in Google Maps and right-click to copy its 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 Extensions menu to see the new Geocoding by SmartMonkey options, and select Geocode Details menu. The geocoding tool will create a new sheet with sample data and display results for three new columns: Latitude, Longitude, and Address found, as shown in Figure 2.15. Always review the quality of geocoded results by comparing the Address found column to the original Address entered.

Select Extensions–Geocoding by SmartMonkey–Geocode Details to display sample data with results for three new columns: Latitude, Longitude, and Address found.

Figure 2.15: Select Extensions–Geocoding by SmartMonkey–Geocode Details to display sample data with results for three new columns: Latitude, Longitude, and Address found.

  1. Paste your own address data to replace the sample data in the sheet, and geocode it as you did in the step above. Follow these guidelines to improve the quality of your results:
  • Do not skip any rows in the Address column.
  • Insert the full address using the format of the national postal service of the country where it is located. Separate terms with spaces.
  • 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.
  • If your original data splits street, city, state, and zip code into different columns, see how to Combine Data into One Column in Chapter 4: Clean Up Messy Data.
  • Give the tool time to work. For example, if you enter 50 addresses, expect to wait at least 15 seconds for your geocoded results.
  • Gecoding results may be limited to approximately 500 addresses per day per account.
  • 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 13: 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.