Geocode in Google Sheets: The =GEOCODE() Formula (Free, No Script)

Every guide to geocoding in Google Sheets makes you do one of two things: paste a wall of Apps Script into the script editor, or create a Google Cloud project, enable the Geocoding API, generate a key, and attach billing. Both work. Both are also a lot of ceremony for 'turn this address into coordinates'.

There's now a third way: a formula. Install the InstaMaps add-on once, and =GEOCODE() works in any sheet like a built-in function, the way it always should have.

The =GEOCODE() Formula

Type =GEOCODE(A2) next to an address and the latitude and longitude spill into two adjacent cells. Point it at a range, =GEOCODE(A2:A500), and it geocodes the whole list in one pass, batched behind the scenes.

Addresses don't need to be perfect. Partial addresses, postcodes alone, and international formats resolve through the same cascade the InstaMaps web geocoder uses, and every result is cached so recalculating your sheet doesn't repeat the work (or the quota).

1. =GEOCODE(A2), one address → latitude + longitude

2. =GEOCODE(A2:A500), a whole column, batched

3. =REVERSE_GEOCODE(51.5074, -0.1278), coordinates → the nearest address (uses Sheets' own built-in geocoder, so it doesn't touch your InstaMaps quota)

Clean and Enrich the Address List While You're There

Geocoding is usually step two. Step one is that the address column is a mess, and the columns you actually need (postcode, town, county) are missing. The same function family fills them:

1. =CLEAN_ADDRESS(A2), '123 main st apt 4 austin' → the full standardised postal address

2. =POSTCODE(A2), extract or look up the postcode / ZIP

3. =CITY(A2), =COUNTY(A2), =STATE(A2), =COUNTRY(A2), fill the missing columns from the address alone

4. =DISTANCE(A2, B2, "mi"), straight-line distance between two addresses or lat,lng pairs

From Coordinates to a Live Map — and a Driving Route

Two more formulas finish the job. =INSTAMAP(A2:D50, "My map") geocodes the range and returns a live, hosted, shareable map URL, same title, same URL, and the map updates itself when the data changes. Multiple =INSTAMAP() formulas give one spreadsheet as many maps as you need.

=ROUTE_LINK(A2:A6) builds an official Google Maps directions link through the rows in order, open it on your phone and navigate the whole run. =WAZE_LINK(A2) does the same for a single stop in Waze. Both are plain links, so they cost nothing and work in any browser.

Formula vs. Apps Script vs. API Key: an Honest Comparison

The classic Apps Script approach (Maps.newGeocoder() in a custom function you paste yourself) is genuinely free and fine for small lists, but you maintain the script per spreadsheet, there's no caching, no address cleaning, and the daily quota is easy to hit with recalculation loops.

The Google Geocoding API route is the most robust at scale but requires a Cloud project, an API key, attached billing, and $5 per 1,000 lookups after the monthly credit, plus you still have to write the script that calls it.

The add-on formula sits in the middle deliberately: no setup, cleaning and enrichment built in, caching by default, and a free tier that covers everyday lists, with a bulk option for the occasional 10,000-row job.

Quotas, Caching, and Costs

The free tier meters 100 lookups/day per spreadsheet. Running Extensions → InstaMaps → '⚡ Set up formulas' and adding an email raises that to 1,000/day, free. Results are cached for hours, so editing elsewhere in the sheet doesn't re-spend quota on addresses already resolved.

If a formula shows a limit message, it tells you exactly which tier you're on and how to raise it, nothing fails silently.

Common Questions