The Scenario
The new territory mapping tool is ready to go — your sales director has already divided the country into regions, assigned reps, and built the visualization. The only thing standing between you and a working map is 800 rows of lead data in a Google Sheet where the address column looks like someone's text messages: "Chicago area," "near downtown Austin," "SF Bay," "1240 Oak ave san jose ca." No coordinates. No standardized postal codes. No chance the mapping tool ingests any of it without rejecting half the rows on import.
You volunteered to clean it up before the kickoff call Thursday morning. That was two days ago.
The bad version:
- Copy addresses in batches into a geocoding tool, download coordinates as a CSV, open both files, and VLOOKUP the lat/lng back against the original lead ID
- Discover that 60 addresses didn't geocode at all because the tool couldn't parse free-text city descriptions, then research each one manually using Google Maps
- Re-run the whole batch after discovering the downloaded coordinates are in a different decimal format than the mapping tool expects
The kickoff call is in 18 hours and you still have 300 rows to reconcile. This is analyst work the way drywall is carpentry — adjacent, but not the same thing.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads the data, calls Google Address Validation for each row to verify and standardize the address and return geocoordinates, and writes everything back to the columns you specify — in one pass.
For every row in my Google Sheet where column A has an address, call Google Address Validation, fill in the verified full address, postal code, latitude, and longitude in new columns, and mark rows where the API returned an incomplete or unresolved result
What You Get
- A verified full address column with standardized formatting for every row Google Address Validation could resolve
- A postal code column populated with the correct 5-digit code, even for rows where the original entry only had a city name
- Latitude and longitude columns in decimal degree format, ready for direct import into a territory mapping tool
- A status column flagging any row where the API returned UNCONFIRMED or could not resolve a coordinate — so you know exactly which 12 rows need a manual review before the call
What If the Data Is Not Quite Ready
Some address entries are city-level or neighborhood-level, not street-level
About a third of your leads came in through a web form that only asked for city and state — there's no street address at all.
For each row in my Google Sheet, validate the address in column A using Google Address Validation — for rows that only have a city and state, return the city centroid coordinates and mark the Precision column as "CITY" instead of "ROOFTOP" so we know which coordinates are approximate
The address column contains country names that shouldn't be there
International leads got a "United States" or "USA" appended somewhere in the pipeline, and the geocoder is returning results outside the US because it's reading the country field.
Validate each address in column A of my Google Sheet — before calling Google Address Validation, strip any trailing country name or country code, then write the standardized US address, latitude, and longitude to columns B, C, and D
Leads are split across first-line address, city, state, and zip in separate columns
Columns B through E hold the four components and you need them assembled, validated, and geocoded as a unit.
For each row in my Google Sheet, assemble the full address from columns B (street), C (city), D (state), and E (zip), validate and geocode it using Google Address Validation, write the verified full address to column F, latitude to column G, longitude to column H, and flag any row where the address could not be confirmed in column I
Standardize, geocode, deduplicate by territory, and flag the result in one shot
Before the import, you want to collapse leads that resolve to the same zip code into one geocoded entry and flag which territory each lands in based on a mapping in another tab.
In my Google Sheet, validate and geocode every address in column A using Google Address Validation, write the lat/lng to columns C and D, look up the zip code from the standardized address against the Territory tab where column A is zip code and column B is territory name, write the territory to column E, and mark any duplicate zip codes in column F
The approach: combine the geocoding, the lookup, and the territory assignment into one prompt rather than doing them in separate passes with intermediate saves.
Try It
Get the 7-day free trial of SheetXAI and open your lead sheet — the one with the free-text addresses — then ask it to validate, standardize, and add lat/lng coordinates to each row. Hub: How to Connect Google Address Validation to Google Sheets. Related: Quality-Score and Clean Addresses Before CRM Import.