Google Sheets address validation - How to verify & geocode

Validate or geocode any US address in Google Sheets with these 8 simple steps:

1. Download Smarty's Google Sheets address validation & geocoding extension from the Google Workspace Marketplace and follow the install prompts.
2. If you don't already have one, sign up for a free account with Smarty and create a new secret key under "API Keys" on the account dashboard.
3. Open Google Sheets, go to the "Extensions" tab, go to "Smarty Address Verification," and "Add Credentials"
4. Add your secret key's "auth id" and "auth token" to the plugin by going to "Enter Credentials." Click submit.
5. Enter each address in a single column, one address per cell. Highlight the fields you want to validate and click "Validate Selected Addresses"
6. You're done. Do a happy dance!

Smarty™ provides the most accurate US Address Verification and US Rooftop Geocoding solutions. We now offer a custom add-on to use these products in Google Sheets, a platform with over 900 million active users monthly, as Sheets doesn't provide their own native solution to perform these capabilities.

Are you ready to try Google Sheets address verification and Google Sheets geocoding? These resources will help you get started:

In this article, we'll cover:

• Google Sheets address validation with Smarty
• [VIDEO] Getting started: preparing your address data
• Installing the Google Sheets address verification extension
• Validating addresses in Google Sheets
• Geocoding addresses in Google Sheets
• Capabilities and limitations
• Understanding the supplementary address data
• RDI - Residential delivery indicator
• DPV footnotes
• DPV match codes
• Conclusion

Google Sheets address validation with Smarty

This article details how to use Smarty's US Address Verification and US Rooftop Geocoding extension for Google Sheets. This add-on allows users to validate US addresses, obtain key metadata about them, and append rooftop-level geocodes to address data.

[VIDEO] Getting started: preparing your address data

Watch the intro video, or read the written instructions below:

To get started using our Google Sheets extension, open a new Google Sheet, go to "File," and choose "Import." This will allow you to select the file that contains your existing address data. You can also copy and paste your data directly into Google Sheets, or perhaps your address data is already in Google Sheets.

To use the Google Sheets extension, all address data for each address must be located in a single cell. If you need to, you can merge the data into one cell using the Concatenate function in Google Sheets. Once your data is merged into one cell, copy and paste the results into a new column or spreadsheet. When you paste the data, right-click and choose "Paste special" > "Paste values only." Be sure to leave the first row blank so that the column headings can be populated.

Installing the Google Sheets address verification extension

Now that each address is located in its own cell, you're ready to install the Google Sheets extension.

Step 1: Choose "Extensions" and select "Add-ons."

Step 2: Click "Add-ons," and the Google Workspace Marketplace will open. Type "Smarty Address Validation" into the search box and install it. Once that's done, you will see a status message that it has been installed.

Step 3: Go back to your Google Sheet, choose "Extensions," and you will see "Smarty Address Verification" is now listed. Click on that, and then choose "Add Credentials."

A pop-up screen will ask you for your "AuthID" and "AuthToken." To get this information, log in to your Smarty account.

To access your account, click "Log In" on the upper right portion of the screen.

Log in using your credentials. To use the Google Sheets extension, you must have an active US Address Verification license. You can view your licenses by clicking on "Subscriptions." If you don't have a license, you can download a free trial, which provides you with 1,000 US Address Verification lookups.

Once you have the correct Subscription, click on "API Keys." This is where you will find your "Auth Id" and "Auth Token" to add to the Google Sheets extension. This information can also be found in "Secret Keys".

Copy and paste your "AuthID" and "Auth Token" into the pop-up box and hit submit. Once submitted, you will see a "Success" status message.

Next, go to "Extensions," choose "Smarty Address Verification," and then select "Show Tools."

A new menu will pop up on the right-hand side of your screen. Now we're ready to validate your address data!

Validating addresses in Google Sheets

Select the addresses you would like validated from your imported list. Leave the first row blank so that the headers for each column can be populated.

Next, you have 2 options in the Match drop-down menu - "Strict" or "Enhanced":

• Strict: The extension will return detailed output only if a valid match is found.
• Enhanced: The extension employs a sophisticated fuzzy-logic algorithm to match malformed addresses to their valid counterparts and will match to a more comprehensive address dataset beyond postal address data. Learn more by reading our docs.

With our address list selected and the "Enhanced" option chosen for "Match", we then add our "License" (in this case, we are using the "us-core-cloud license"). This information is found in your Smarty Account under "Subscriptions." Now, we are ready to "Validate and Retrieve All Data" to perform the address verification.

The extension will quickly perform the validation, and the results will be appended to your Google Sheet.

Geocoding addresses in Google Sheets

Smarty's Google Sheets extension can use the same process to append geocodes with rooftop-level precision to your addresses. To perform this function, you will need a US Rooftop Geocoding license. You can add this paid subscription in your account area under "Subscriptions."

In Google Sheets, select the list of addresses you want to have geocodes appended to (make sure the first row is blank) and select "Validate and Retrieve Geocodes." In our example, we have chosen "Enhanced" and indicated we're using the "us-rooftop-geocoding-cloud" license.

Once we submit, we can see that lat/long coordinates have been appended, along with the precision-level results.

Capabilities and limitations

The Google Sheets extension can be used to validate thousands of US addresses—as many as can fit in Google Sheets and allowed by your subscription level. To help you get started, we offer a free trial that provides you with 1,000 address lookups. You can also purchase a monthly subscription, including our unlimited plan, depending on your estimated volumes.

If you don't want to go through the effort of adding an extension to Google Sheets, or you have lots of addresses, you can also use our bulk address validation and geocoding tool. This tool can do everything our extension can, but it can also process international addresses, find cities by ZIP Codes, find ZIP Codes by city, and more. Full documentation can be found here.

For your largest files, use our CLI tool that has no size limitations other than your subscription level.

Understanding supplementary address data

Smarty offers up to 55 points of data with every US address validated, such as RDI, DPV footnotes, and DPV match codes.

To see these additional points of data while using the Google Sheets extension, you can validate addresses using the "Validate and Retrieve All Data" button, and you'll see all the address components, metadata points, and detailed analyses we have. Here are a few examples of what you'll see:

RDI - Residential delivery indicator

This data point identifies whether the USPS classifies an address as residential or commercial. This distinction is essential for businesses that need to optimize shipping costs and logistics.

Some shipping carriers charge different rates for residential versus commercial deliveries, and knowing this classification upfront helps businesses plan more efficiently to avoid unexpected costs.

RDI helps businesses streamline their delivery routes, manage resources better, and tailor services or marketing efforts based on the address type. By using RDI in Smarty's Google Sheets add-on, companies can enhance operational efficiency and reduce shipping expenses.

DPV footnotes

Delivery Point Validation (DPV) is the process of verifying that an address is actually deliverable, meaning that mail can be sent to that address. DPV footnotes provide valuable analysis of the deliverability of each address.

For example, footnotes like "AA" indicate that the street address, city, state, and ZIP Code are all valid. In contrast, footnotes like "N1" or "N3" might indicate that the address is missing a secondary unit (like an apartment or suite) or that the address couldn't be fully validated. These footnotes help users understand the specific issues or verifications related to a particular address.

DPV match codes

DPV Match Codes offer an even more granular look at how closely an address matches with known USPS delivery points. Codes such as "Y" (Yes) indicate a full match and that the address is valid for delivery, whereas "N" (No) signifies that the address doesn't match any known delivery points.

These match codes ensure mailing accuracy, reduce the risk of undeliverable mail, and help organizations maintain clean, verified address lists. By analyzing DPV Footnotes and DPV Match Codes, you'll gain insights into the accuracy of the addresses in your data.

See all possible data points and their definitions in the US Address Verification API doc.

Conclusion

The Google Sheets address verification and geocoding extension offers a comprehensive solution for managing and enhancing address data within Google Sheets. Try the extension today, and sign up for a free trial, too.