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):
- 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) - Enable Address Validation
API
and generate an API Key from Cloud
Console.
- In the “API Key + Dashboard” Sheets tab, replace B2 cell with your API Key.
- Copy/Paste Addresses in the template (in a separate sheet to format the data: concatenate street number, name, city, postcode)
- Select sheets cells and then select from Menu “Address Validation” >
“Address Validation API selection”
- 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\