The Scenario
You manage field surveys for a utility company. Your team of 12 field agents spent the last three weeks collecting GPS waypoints on mobile devices—300 coordinates in total, now sitting in columns A and B of a Google Sheet. Before those points can be imported into the CRM, the operations team needs each one converted to a street address and postal code. The CRM import template has columns for address, city, and postal code, and it won't accept raw coordinates.
The bad version:
- Open a reverse geocoding tool or Ambee's geocoding endpoint, paste the first coordinate pair, copy the returned address components one by one, open the sheet, paste address into column C, city into column D, postal code into column E.
- Move to row 3. Repeat.
- At row 40, you realize you've been copying the "formatted_address" string instead of the discrete address components the CRM template requires, so the last 39 entries are in the wrong format and need to be re-done.
Three hundred coordinates, each one requiring a manual lookup and three separate copy-paste operations, is not a data cleaning task—it's a data entry project that nobody budgeted time for.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet. It reads the coordinate columns, calls Ambee's reverse geocoding API for each pair, and writes address, city, and postal code into the columns you specify—across all 300 rows in a single run.
Here is the prompt for this task:
For each lat/lng pair in columns A and B, use Ambee to reverse-geocode the coordinates and write the address, city, and postal code into columns C–E
What You Get
- Column C: Street address (number and street name)
- Column D: City name
- Column E: Postal code
- Coordinates that don't resolve to a known address show "No address found" in column C and blanks in D–E
What If the Data Is Not Quite Ready
Some coordinate pairs have commas inside a single cell rather than across two columns
Column A has coordinate pairs formatted as "37.7749, -122.4194" in a single cell. Split each pair on the comma, use the first value as latitude and the second as longitude, reverse-geocode via Ambee, and write address, city, and postal code into columns B–D.
The CRM template also needs a country code column
For each lat/lng pair in columns A and B, reverse-geocode via Ambee and write street address into column C, city into column D, postal code into column E, and ISO 3166-1 alpha-2 country code into column F.
The output needs to be validated—flag any address that appears to be a rural or unincorporated area
Reverse-geocode each lat/lng pair in columns A and B using Ambee, write address, city, and postal code into columns C–E, and flag any row in column F where the returned address lacks a street number or where the city field is blank—these likely represent rural or off-grid waypoints.
Kill-chain: reverse-geocode, validate format, flag unusable rows, export a clean CRM import tab
For each lat/lng pair in columns A and B, reverse-geocode via Ambee and write address, city, and postal code into columns C–E. Flag rows where any field is missing with "INCOMPLETE" in column F. Then create a new tab called "CRM Import" containing only the rows where column F is blank, formatted with headers: Address, City, Postal Code.
One prompt produces the validated import file alongside the flagged rows that need follow-up.
Try It
Open any Google Sheet with GPS coordinates collected from field work and get the 7-day free trial of SheetXAI. Ask it to reverse-geocode the lat/lng pairs in columns A and B into address, city, and postal code using Ambee, and your CRM import file is ready before the next data sync window. Also useful: bulk AQI enrichment by address or the full Ambee integration overview.