Google Sheets: Address Validation Add-on documentation

The Google Sheets: Address Validation Add-on validates and returns relevant metadata for US and international addresses. To access the full capabilities of the tool, you’ll need to start a free trial or purchase a subscription to US Address Verification or International Address Verification. With a US Rooftop Geocoding or International Geocoding subscription, users can also geocode their address list. Learn more about the features and capabilities of the Google Sheets: Address Validation Add-on here.

Quick start video

Getting started

Install the add-on here.

After installation from the store, go to "Extensions" on the top ribbon and down to "Smarty Address Verification". To use this tool, you will need to have an active Smarty license. 

Go through a quick authentication process

1. Select the "Add Credentials" button.

   

2. Enter your auth ID and auth token.

   These values are found within your Smarty account under the "API Keys" tab.
   
   Auth ID and auth token are stored securely within the sheet’s memory. This allows the sheet to be worked on and shared collaboratively.

3. Click submit.

Address formatting

Organize your data into columns, using one of the combinations shown below. (The more data provided, the better.) The top row MUST consist of column titles, spelled exactly as you see here.

For US addresses:

street | city | state | zipcode
street | city | state
street | zipcode
address (entire address in a single column) 

If you have secondary information (apartment/suite/etc.) in a separate column, label that column secondary. Such a column can be added to any of the first three combinations shown above. For example:
street | secondary | city | state | zipcode

For international addresses:

country | address1 | locality | administrative_area | postal_code
country | address1 | locality | administrative_area
country | address1 | postal_code
country | freeform
 

Still not quite sure? Take a peek at this sample spreadsheet.

Using the add-on

Once you have your column names and addresses in the sheet, you are ready to go! To open and use the add-on, follow these steps:

1. Select “Extensions” → “Smarty Address Verification” → “Show Tools”.

   

   

2. Choose your product.

3. If using US Address Verification, choose between an "enhanced or strict" match type.

   Learn more about strict vs enhanced here.

4. Choose a Validation Type.
   1. Validate Only
      Returns:
        – Status message ex. "Address matched"
        – Validated addresses
        – DPV footnotes
   2. Validate + Geocode
      Returns the same info as ‘Validate Selected Addresses’, plus:
        – Geocodes
        – Geocode precision level
   3. Validate + All Metadata
      Returns all the data available from the US Street Address API, including the validated address and metadata.
   4. Extra options may pop up in "Advanced Settings" for different products and validation types, including:
        – Advanced License Input (For those with special licenses)
        – Format
        – County Source
        – Features
        – Output Language
       
5. Choose to insert the results on the same page you’re currently working in (adjacent to the highlighted cells) or to insert the results into a new sheet.
6. Highlight the addresses you would like to verify.
7. Click “Validate” and watch the magic happen!

Note: Any existing data in cells where the new address data is placed will be overwritten.

4 more ways to validate in bulk

1. Bulk Address Validation: web interface
2. Microsoft Excel add-In
3. QGIS mapping software
4. Command-line interface (CLI)