Documentation

Install

Open a Google Sheet, install the add-on from the Google Workspace Marketplace and authorize it the first time. It asks for access to the current spreadsheet only, not your Drive. The menu appears under Extensions → Geocode & Maps for Sheets: Geocode a column, Show map, Pro, Help (docs, feedback, usage statistics).

To check that it works, type this in any cell:

=GEOCODE("Gran Via 1, Madrid")

Two cells fill in: latitude and longitude. The add-on has no limits of its own; what you have is Google's daily quota for the Maps service (see quota).

Functions

All four accept a cell or a one-column range (A2:A200), and return one result per row. Each address is looked up in a cache first, so repeated addresses cost nothing against the quota (see quota). A formula stops at about 25 seconds, before Google's 30 second limit; rows it did not reach show Continue with “Geocode a column” and the sidebar finishes them.

GEOCODE

=GEOCODE(address, [country], [output])

• address: text, or a range of addresses.
• country (optional): two letters ("ES", "US") or a name. It helps with ambiguous addresses and is part of the cache key.
• output (optional): "latlng" (default, two columns), "lat", "lng", or "full": latitude, longitude, precision, normalized address and provider (five columns).

Precision is exact, interpolated, center or approximate, with partial added when the service only matched part of the address. A center or approximate point is the middle of a street, a town or a region, not a door.

=GEOCODE(A2)
=GEOCODE(A2; "ES"; "lat")
=GEOCODE(A2:A100; "US"; "full")

REVERSE_GEOCODE

=REVERSE_GEOCODE(lat, lng, [language])

Returns the formatted address nearest to the coordinates. language is a code such as "es" or "en".

DISTANCE

=DISTANCE(origin, destination, [mode], [unit])

• origin, destination: an address, or coordinates as text, "40.4168,-3.7038".
• mode: "driving" (default), "walking", "bicycling", "transit", or "straight": the straight line (haversine), computed locally with no quota.
• unit: "km" (default) or "mi".

DRIVETIME

=DRIVETIME(origin, destination, [mode], [unit])

The same arguments, returning travel time. unit is "min" (default) or "h". "straight" has no travel time, so it is not accepted here. Origin and destination can be two one-column ranges, paired row by row (a single value is repeated for every row). Road modes use Google's directions service and count against a separate daily quota.

Errors

The message says what failed and what to do. These are the common ones.

Geocode a column (sidebar)

For long lists, use Extensions → Geocode & Maps for Sheets → Geocode a column. Pick the sheet and header row, up to three address columns (joined with a comma), an optional default country, and where to write: latitude, longitude, precision, normalized address and provider. A column you choose as New column is added after the last one with a plain English header.

• In batches. The sidebar works through the rows in calls of up to 50, each far below Google's 6 minute limit, so a column of thousands of rows is no problem as long as the quota lasts.
• Resumable. Progress is saved in the document. If you close the sidebar or the browser, reopen it and press Resume. If the daily quota stops the run, resume tomorrow, from another account, or with your own key.
• It never overwrites. A row whose latitude or longitude cell already has content is skipped and counted. Nothing you wrote is touched.
• Precision column. exact, interpolated, center or approximate (plus partial). Filter on it to review the doubtful rows.
• Readable errors per row, with a summary at the end: NOT_FOUND, QUOTA, INVALID.
• Provider column. It records where each result came from. Keep it if you publish the data: Google results need the Geocoding by Google notice (see terms).

What it geocodes is also saved in a hidden Geocode cache tab, which the formulas read. That is why a sheet you geocoded with the sidebar recalculates its GEOCODE formulas without spending quota.

Map and export

Extensions → Geocode & Maps for Sheets → Show map draws the points of a sheet: choose the latitude and longitude columns, and optionally a label column (shown on click) and a group column (one colour per group). It uses Leaflet with OpenStreetMap tiles, groups points into clusters from 200 points, and shows at most 2,000 points: filter the sheet to see the rest. Attribution is always visible, with Geocoding by Google added when a point came from Google.

Export. The dialog downloads the points as KML (open it in Google Earth or My Maps) or GeoJSON. Both are built in your browser and need no access to your Drive.

Share a map (Pro). A read-only snapshot at a link on this site (/map/?id=…) with the title, coordinates, labels and groups, up to 2,000 points. It expires after 30, 90 or 180 days, you can delete it at any time, and only what you publish leaves your sheet. See the privacy policy.

Quota, and how not to spend it

The free add-on geocodes with the Maps service of Google Apps Script, which runs under your Google account. Google limits it to about 1,000 geocoding requests per day on personal Google accounts and 10,000 on Google Workspace, and routes (distance and travel time by road) have their own daily limit. The count resets at midnight Pacific Time. There is no trial that expires and no limit of ours: this is Google's quota, which is free to use.

When it runs out, the message says so, and the sidebar stops cleanly and resumes later. To make the quota last:

• Cache. Every result is remembered for 6 hours in Google's cache and, if you used the sidebar, in the hidden Geocode cache tab. The key is the address in lower case with spaces collapsed, plus the country, so Calle Mayor 1, Madrid and calle mayor 1, madrid count once.
• straight distance. When a straight line is enough, =DISTANCE(A2; B2; "straight") is computed locally and costs nothing.
• Geocode once, then reuse. Fill a column with the sidebar and keep the coordinates, instead of recalculating GEOCODE over and over. Coordinates as text ("40.41,-3.70") can be used directly as origin or destination.
• A better address gives one try, not three. Add the city and country (the country argument) so the first answer is the right one.
• Pro, your own key. A Google Maps Platform, OpenCage or Geoapify key replaces the daily quota with the one of your own plan.

=DISTANCE("40.4168,-3.7038"; "41.3874,2.1686"; "straight"; "km")

Limits of Apps Script

The add-on runs on Google Apps Script, and these limits come from Google:

• 30 seconds per formula. A formula over a long range stops at about 25 seconds and leaves the remaining rows marked to continue with the sidebar.
• 6 minutes per menu or sidebar run. The sidebar works in calls of 50 rows and cuts each one at 4.5 minutes, then chains the next.
• Daily quotas for geocoding and routes, per Google account (see quota).
• Formulas cannot write. A custom function can only return values, so only the sidebar fills the Geocode cache tab.
• Triggers run at most hourly, which is why Pro auto-geocode sweeps every hour.
• The map shows at most 2,000 points; the exports have the same limit.
• Several Google accounts in one browser can make the sidebar load empty: use your first (default) account or a private window.

Pro

Pro is Sheets Pro by Engranalab: one subscription that also unlocks the Pro features of the other Engranalab add-ons for Sheets. It is free during the beta (below), and then 5 € per month or 48 € per year, VAT included. Everything in the sections above stays free.

• Auto-geocode. Geocodes new rows by itself: when you edit a cell, when a Google Form adds a row, and with an hourly sweep (the minimum frequency Google allows an add-on). Set it per spreadsheet. It never overwrites a cell that has content.
• Distance matrix. Distance or travel time between every origin and every destination, up to 25 × 25 per run, written to a new sheet.
• Route order. Orders a list of stops by nearest neighbour from the start row, one way or round trip, using straight-line distance or, up to 25 stops, distance by road. Adds an order column and the total distance.
• Your own key. Use a key of Google Maps Platform (Geocoding API), OpenCage or Geoapify to go past the daily quota. The key is stored in your Google account, never in the sheet and never on our servers; the sidebar and auto-geocode use it. OpenCage and Geoapify return open data.
• Shared map. A read-only link to a map of your points, for people who do not have the sheet. See Map and export.

Free during the beta

Open Extensions → Geocode & Maps for Sheets → Pro and choose Join the free Pro beta. No card, nothing to type: your Google account email is used. If you already joined from another Engranalab add-on, you already have it. We email you before the beta ends and before anything is charged; more about Pro and the beta.