## 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](https://mapsplatform.google.com/) such as [Address Validation
API](https://developers.google.com/maps/documentation/places/web-service/overview),
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](https://developers.google.com/maps/documentation/places/web-service/overview) 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](https://www.google.com/sheets/about/) that
comes with custom [Apps Script](https://developers.google.com/apps-script)
functions which will perform requests in sequence to the [Address Validation
API](https://developers.google.com/maps/documentation/places/web-service/overview).
> [!TIP]
> **Tip:** This example is meant for integration guidance only. You should ensure quality before use with your applications. Support is available on the products such as Address Validation API used by this example.
You can run addresses by the API following the steps below (requires a [Google
Account](https://support.google.com/accounts/answer/27441) and an [API
Key](https://developers.google.com/maps/documentation/address-validation/get-api-key#creating-api-keys)):
1. Navigate to the [Sample
Spreadsheet](https://docs.google.com/spreadsheets/d/1NoEjMNIlKPhMHbMCaAant3TCF4wgcW-p27zpmEnaCkY/edit?usp=sharing&resourcekey=0-TmBtRxAmgzlyIXlWgEdwjg) and from the Menu select: File \> Make a copy
(you need to Log in your [Google Account](https://www.google.com/account/about/) or create one)
2. Enable [Address Validation
API](https://developers.google.com/maps/documentation/address-validation/get-api-key#creating-api-keys) and generate an API Key from [Cloud
Console](https://console.cloud.google.com).
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.
> [!CAUTION]
> **Caution:** Secure your API key by restricting its use to the Address Validation API only. To prevent unexpected usage, make sure to only share your sheet with trusted parties, and delete your key in the Console after you are done with your analysis.
>
> [!TIP]
> **Tip:** in order to comply with the Google Maps Platform Terms, make sure to delete the Spreadsheet after 30 days as Google Sheets has built-in history.
### 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)](https://cloud.google.com/maps-platform/terms/maps-service-terms):
- 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](https://developers.google.com/maps/documentation/address-validation/usage-and-billing.md.txt) 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:
- [Address Validation for Ecommerce Checkout](https://developers.google.com/maps/architecture/ecommerce-checkout-address-validation.md.txt)
- [High Volume Address Validation library](https://developers.google.com/maps/architecture/high-volume-address-validation-library-oss.md.txt)
## Contributors
Google maintains this article. The following contributors originally wrote it.
Principal author:
[Thomas Anglaret](https://www.linkedin.com/in/thomas-anglaret/) \| Solutions Engineer\\