Address Validation API in Google Sheets

Objective

In this document, we will look into how to use the Address Validation API in Google Sheets in order to test the service in a quick and light way.

As a non-developer, while willing to work with data coming from Google Maps Platform such as Address Validation API, you may wish to find a way to get started with the service without too much help from technical teams.

Use cases

Now let's understand the use cases where Address Validation API in Google Sheets is useful:

  • Trial: As a business you may want to quickly test the capabilities of the Address Validation API using your own addresses.
  • Compare: Compare Address Validation API results with other endpoints results such as Geocoding API or Places API.
  • A/B Test: A/B Test variations of the same dataset to understand what might provide more suitable results.

Google Sheets template

The solution is a Google Sheets that comes with custom Apps Script functions which will perform requests in sequence to the Address Validation API.

You can run addresses by the API following the steps below (requires a Google Account and an API Key):

  1. Navigate to the Sample Spreadsheet and from the Menu select: File > Make a copy
    (you need to Log in your Google Account or create one)
  2. Enable Address Validation API and generate an API Key from Cloud Console.
  3. In the “API Key + Dashboard” Sheets tab, replace B2 cell with your API Key.
  4. Copy/Paste Addresses in the template (in a separate sheet to format the data: concatenate street number, name, city, postcode)
  5. Select sheets cells and then select from Menu “Address Validation” > “Address Validation API selection”
  6. On first execution, you will an “Authorization Required” message will appear to provide access to the Apps Script.



Google Maps Platform Terms

The Solution is provided for test purposes on a limited set of addresses: Caching period allowed: 30 consecutive calendar days, after which Customer must (1) delete the cached Google Maps Content or (2) replace with End User data provided through End User confirmation or correction. Refer to Google Maps Platform Terms of Service Table 11.3.1 (Caching Permissions):

  • placeId
  • Location: latitude values, longitude values
  • verdict
  • formattedAddress
  • postalAddress
  • addressComponent: componentName
  • USPS Data standardizedAddress

Considerations

  • Performance: Proceed in chunks of 1000 rows not more at once. The script can eventually stop, for instance when exceeding API quota per second, proceed from row where the script stopped.
  • You can access the code from Menu > Extensions > Apps Script and make your own version of the script tailored to your need
  • Pricing for Address Validation API.

Conclusion

Address Validation API in Google Sheets allows non-developers to start querying the Address Validation API and understand how it can be strategic to their business. This document attempts to demonstrate how anyone can start querying the API by creating an API Key and using the provided Google Sheets template.

Next Actions

Suggested Further Reading:


Contributors

Google maintains this article. The following contributors originally wrote it.
Principal author:

Thomas Anglaret | Solutions Engineer\