Google Sheets latitude longitude formula & quota errors

Is there a built-in formula?

No. Google Sheets has functions for text, maths, lookups and even GOOGLEFINANCE, but nothing for geocoding. Any =GEOCODE()-style formula you see in a tutorial is a custom function: either one you paste in yourself with Apps Script, or one added by an add-on.

Add a =LATLNG() formula with Apps Script

Open Extensions → Apps Script, paste the code below and click Save.
Back in the sheet, type =LATLNG(A2). Latitude and longitude fill two cells, so keep the cell to the right empty.
For a short column, =LATLNG(A2:A40) fills a block of two columns in one call.

/**
 * Latitude and longitude of an address, from Google Maps.
 * =LATLNG(A2) fills two cells. =LATLNG(A2:A40) fills a block (keep ranges small).
 *
 * @param {string|Array<Array<string>>} address The address, or a column of addresses.
 * @return Latitude and longitude.
 * @customfunction
 */
function LATLNG(address) {
  if (Array.isArray(address)) {
    return address.map(function (row) { return geocodeOne_(row[0]); });
  }
  return [geocodeOne_(address)];
}

function geocodeOne_(address) {
  address = String(address || '').trim();
  if (!address) return ['', ''];
  const cache = CacheService.getDocumentCache();
  const key = 'll:' + Utilities.base64EncodeWebSafe(
    Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, address));
  const hit = cache.get(key);
  if (hit) return JSON.parse(hit);

  const res = Maps.newGeocoder().geocode(address);
  if (res.status !== 'OK' || !res.results.length) return ['', res.status];
  const loc = res.results[0].geometry.location;
  const value = [loc.lat, loc.lng];
  cache.put(key, JSON.stringify(value), 21600);   // 6 hours: the longest CacheService allows
  return value;
}

How it works: it calls Google's Maps Geocoder and remembers each answer for 6 hours with CacheService, the longest Apps Script allows. If an address isn't found, the second cell shows the reason, for example ZERO_RESULTS. Custom functions don't need an authorization prompt for the Maps service.

Stop wrong-country matches

An address like “10 Church Street” or “Cambridge” matches somewhere, just not always where you meant. Three fixes, from simplest to strongest:

Put the full address in the cell, with city, region and country. You can build it from separate columns inside the formula: =LATLNG(B2&", "&C2&", "&D2&", USA").
Prefer a country. In the script, change Maps.newGeocoder() to Maps.newGeocoder().setRegion('us') (or 'uk', 'ca', 'au'…). Google then favours results in that country. It’s a preference, not a filter, so a clear foreign address still resolves abroad.
Prefer an area. setBounds(south, west, north, east) favours results inside a box of coordinates, for example .setBounds(32.5, -124.5, 42.0, -114.1) for roughly California.

After changing the script, existing cells keep their cached answers for up to 6 hours. Retype the address (or the formula) to geocode again.

Why geocoding formulas hit limits

A daily quota per user. Apps Script allows 1,000 geocode calls a day on a personal account and 10,000 on Google Workspace. Google says these quotas can change without notice.
Formulas re-run. A custom function runs again when its input changes, and in practice also more often than you'd expect: when the file is reopened, when rows are inserted, or when an ARRAYFORMULA wraps it and recalculates every row. A 300-row sheet that recalculates four times uses 1,200 calls. That's over the personal quota in one day.
30 seconds per call. A custom function must finish within 30 seconds or it shows Exceeded maximum execution time. That's why =LATLNG(A2:A40) should stay small: a range of hundreds will time out.
The cache expires. CacheService forgets after at most 6 hours, so a recalculation tomorrow pays again.

Fix "Service invoked too many times for one day: geocode"

The full error reads Exception: Service invoked too many times for one day: geocode. For driving-distance formulas it ends in route. It means your Google account used up today's Maps service quota. Google's quotas page says quotas reset 24 hours after the first request, not at midnight. What helps:

Stop recalculation: once a row has coordinates, turn them into plain values (see the next section). This is the single biggest fix.
Only geocode new rows. Instead of one formula over the whole column, use a menu script that skips rows that already have coordinates. The script in our geocoding guide does this.
Don't wrap geocoding custom functions in ARRAYFORMULA over open-ended ranges like A2:A.
Use a Google Workspace account if you have one. Its quota is 10 times higher.
Use your own API key for the Google Geocoding API (called with UrlFetchApp). It isn't capped by the Apps Script quota, but it needs a Google Cloud billing account and is billed per request beyond any free allowance.

Waiting until the quota resets is the only way to make the same account work again today. There's no setting that raises it.

Freeze results as values

Select the cells the formula filled.
Copy them (Ctrl+C, or ⌘+C on a Mac).
Paste them back with Edit → Paste special → Values only (Ctrl+Shift+V).

Now they're numbers, not formulas, and they never call the geocoder again. Keep the =LATLNG() formula only on new rows. One caveat: these are Google Maps results, and Google's terms limit how long you may store them.

Troubleshooting

“Unknown function: LATLNG”. The script isn’t saved in this spreadsheet’s Apps Script project. Open Extensions → Apps Script from this file, paste, click Save, and retype the formula.

“#REF! Array result was not expanded”. The formula fills two cells and the cell to the right isn’t empty. Clear it, or move the formula.

Cells stuck on “Loading…”. Too many calls are running at once, or each is slow. Fill fewer rows at a time, and don’t point one formula at a long range.

ZERO_RESULTS in the second cell. Google couldn’t find the address. Check for typos, a missing city, or extra text such as “Attn:” or a suite number.

A plausible but wrong location. The address was ambiguous and Google picked another town. Add the region and country, or set a region as shown above. Rows that share exactly the same coordinates are a warning sign: they often fell back to a city or ZIP center.

When to stop using a formula

Formulas are great for a few dozen addresses you look at once. Switch to something else when:

the list is in the hundreds or grows every week: use a menu script that only processes new rows;
you hit the quota regularly: use a Workspace account, your own API key, or an add-on;
you need results you can keep permanently: use a provider whose terms allow storage.

Frequently asked questions

What does "Service invoked too many times for one day: geocode" mean?

Your Google account has used today's Apps Script quota for the Maps geocoder: 1,000 calls a day on a personal account or 10,000 on Google Workspace. It resets 24 hours after the first request. Converting finished results to plain values and only geocoding new rows stops it from recurring.

Why does my geocoding formula use so many calls?

Custom functions can recalculate when the file reopens, when rows are inserted or when referenced cells change, and an ARRAYFORMULA over a whole column re-runs every row. Each recalculation is a new call unless the result is cached or converted to values.

Why do I get "Exceeded maximum execution time"?

A custom function call must finish within 30 seconds. Geocoding a large range in one call takes longer than that. Use smaller ranges, or a menu-run script, which can run for up to 6 minutes.

Is there a Google Sheets formula to get latitude and longitude without a script?

Not a built-in one. You need a custom function (Apps Script) or an add-on that provides one.

How do I make the formula prefer addresses in my country?

Edit the script so the geocoder is created with Maps.newGeocoder().setRegion('us'), using your country's code. Google then favours matches in that country, though it isn't a strict filter. Adding the country to every address is the most reliable fix.

Can I return the matched address as well as the coordinates?

Yes. The geocoder result also contains formatted_address. In the script, add res.results[0].formatted_address as a third item in the value it returns, and keep a third empty column to the right. Comparing it with your input is the quickest way to spot bad matches.

Disclosure: we make MapRows, a Google Sheets add-on mentioned in this guide. We describe the free methods as fairly as we can, including where they are the better choice.