Google Sheets add-on: geocode addresses in a spreadsheet
On this page
Add latitude and longitude to a column of addresses, look up the address at a point, and put map images next to your data, right in Google Sheets. Results written into the sheet are yours to keep on paid plans.
Set it up (about 3 minutes)
The add-on is on its way to the Google Workspace Marketplace. Until then, paste it into your spreadsheet:
Get a key. Sign in at the dashboard (free, no card) and create a key with no website limit: keys limited to certain websites only work inside web pages, so Google Sheets gets "This key only works on the websites listed for it".
Open your spreadsheet and choose Extensions → Apps Script. At the top left, rename Untitled project to MapIron.
In Code.gs, select everything, delete it, and paste the script:
Next to Files, click + → HTML and name it Sidebar. Replace its contents with:
Recommended: click Project Settings (the gear), tick Show "appsscript.json" manifest file in editor, open appsscript.json and replace it with the manifest below. It limits the script to this spreadsheet and to connecting to MapIron only.
Press Ctrl+S (⌘+S on a Mac). Pick onOpen in the function list and press Run. Google asks you to authorize it; because it's your own copy, Google says it hasn't verified the app: choose Advanced → Go to MapIron, then Allow.
Reload the spreadsheet, open Extensions → MapIron → Set key, paste your key and press Check and save.
Anyone who can edit the spreadsheet can also see a pasted script, so paste it only into spreadsheets you share with people you trust.
Formulas
Each address or point is 1 address lookup. Results are cached for 6 hours, so recalculating doesn't use lookups again.
| Formula | Gives you |
|---|---|
=MAPIRON_GEOCODE(A2) | latitude and longitude in two cells |
=MAPIRON_GEOCODE(A2:A100) | the same for every row of a range |
=MAPIRON_GEOCODE(A2:C2) | street, city and state in separate columns, joined into one address |
=MAPIRON_GEOCODE(A2, "label,postcode,country") | the fields you list, across columns |
=MAPIRON_LATLNG(A2) | 41.878100,-87.629800 in one cell |
=MAPIRON_REVERSE(B2, C2) | the address nearest to a latitude and longitude |
Fields: found, latitude, longitude, label (the one-line address), name, housenumber, street, postcode, city, district, county, state, country, countrycode, osm_type, osm_id, type. A formula has 30 seconds to finish; for long lists use the menu.
Menu: Extensions → MapIron
- Geocode a column: pick the address column (or several, like street, city and state) and where the results go. Results are written as plain values, so they stay in the sheet and are never looked up again. Rows that already have results are skipped, so you can stop and run it again at any time. It checks how many lookups your plan has left first.
- Reverse geocode: the same, from latitude and longitude columns.
- Insert map image for selected row: a map of that row's address next to your data; choose Privacy area to show the neighborhood instead of the exact spot. Each image is 1 static map image.
- Usage: your plan and what's left this month.
Limits, costs and keeping results
- Hard monthly caps, no overage bills. When a plan's cap is reached, the add-on stops with a message that says when the cap resets and links to upgrade or a $20 top-up pack.
- Speed. The Free plan allows 5 requests a second; the add-on slows down and retries on its own when asked to.
- Keep what you look up. On paid plans you may store coordinates and map images indefinitely; on Free, for 30 days.
- Credit. Keep "© OpenStreetMap contributors" wherever you show results or maps. Map images already include it.
Privacy
The add-on sends only the addresses or points you choose to api.mapiron.com. Your key is kept in your Google account's settings for this script and sent only in the request header, never written into a cell, a URL or a formula. If you tick Use my key for formulas in this spreadsheet, the key is saved with the spreadsheet instead, and everyone who can edit it can see the key: use a separate key for shared spreadsheets. See the privacy policy.
Troubleshooting
| You see | Do this |
|---|---|
| "Set your MapIron key…" | Extensions → MapIron → Set key. If you aren't the spreadsheet's owner, tick "Use my key for formulas in this spreadsheet". |
| "MapIron didn't accept this key" | Copy the key again from the dashboard. |
| "This key only works on the websites listed for it" | Make a key with no website limit. |
| "…cap (…) is used up" | Wait for the reset date, or upgrade or add a top-up pack. |
| "Too many rows for one formula" | Use Geocode a column. |
Not using Google Sheets? The batch geocoder does the same for a CSV from Excel or Numbers.
Other ways to use MapIron without code: no-code setups.
Map data © OpenStreetMap contributors (opens in a new tab). Google Sheets is a trademark of its owner; MapIron isn't affiliated with or endorsed by it.