Reverse geocode in Google Sheets: coordinates to addresses

Get your coordinates in shape

• Use decimal degrees: 34.0522, not 34°3'8"N.
• Latitude goes from −90 to 90 and longitude from −180 to 180. If many of your points land in Antarctica or the ocean, latitude and longitude are probably swapped. Some exports, like the U.S. Census geocoder, list longitude first.
• If both values are in one cell ("34.0522, -118.2437"), split them with =SPLIT(A2, ",").
• Make sure the cells are numbers, not text. =ISNUMBER(A2) should return TRUE.

Just one or two points: Google Maps

For a single point you don’t need a script. Paste the coordinates, for example 34.0522, -118.2437 (latitude first, with a comma), into the search box on Google Maps and press Enter. Maps drops a pin and shows the nearest address or place. Copy what you need back into the sheet. If the pin lands in the ocean or on the wrong continent, the numbers are swapped or a minus sign is missing.

A free reverse geocoding script

Apps Script's Maps Geocoder has a reverseGeocode(latitude, longitude) method. This script adds a menu that runs it over the rows you select and writes plain values, so nothing recalculates.

1. Put Latitude and Longitude in two neighbouring columns, then insert five empty columns to their right.
2. Open Extensions → Apps Script, paste the code below, click Save and reload the spreadsheet.
3. Select the latitude and longitude cells (both columns, without the header) and choose Reverse geocode → Selected latitude/longitude to address. Authorize the script the first time.

/**
 * Adds a "Reverse geocode" menu to the spreadsheet.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Reverse geocode')
    .addItem('Selected latitude/longitude to address', 'reverseSelection')
    .addToUi();
}

/**
 * Select TWO columns (Latitude, Longitude), then run the menu item.
 * Writes Address, City, State, Postal code and Country into the 5 columns to
 * the right of the selection. Rows that already have an address are skipped.
 */
function reverseSelection() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const sel = sheet.getActiveRange();
  if (sel.getNumColumns() !== 2) {
    SpreadsheetApp.getUi().alert('Select exactly two columns: latitude, then longitude.');
    return;
  }
  const coords = sel.getValues();
  const out = sheet.getRange(sel.getRow(), sel.getColumn() + 2, sel.getNumRows(), 5);
  const results = out.getValues();
  const geocoder = Maps.newGeocoder();
  const started = Date.now();

  for (let i = 0; i < coords.length; i++) {
    const lat = Number(coords[i][0]), lng = Number(coords[i][1]);
    if (coords[i][0] === '' || coords[i][1] === '' || results[i][0] !== '') continue;
    if (!(Math.abs(lat) <= 90 && Math.abs(lng) <= 180)) { results[i] = ['INVALID', '', '', '', '']; continue; }
    if (Date.now() - started > 5 * 60 * 1000) break;
    try {
      const res = geocoder.reverseGeocode(lat, lng);
      if (res.status !== 'OK' || !res.results.length) { results[i] = [res.status, '', '', '', '']; continue; }
      const r = res.results[0];
      const part = function (type) {
        const c = r.address_components.find(function (c) { return c.types.indexOf(type) !== -1; });
        return c ? c.long_name : '';
      };
      results[i] = [r.formatted_address, part('locality') || part('postal_town'),
                    part('administrative_area_level_1'), part('postal_code'), part('country')];
    } catch (e) {
      out.setValues(results);
      SpreadsheetApp.getUi().alert('Stopped at row ' + (sel.getRow() + i) + ': ' + e.message);
      return;
    }
  }
  out.setValues(results);
}

Rows with impossible coordinates are marked INVALID. If a run stops, at the quota or the 6-minute limit, run it again: rows that already have an address are skipped.

City, state and ZIP from coordinates

The script reads the parts of the address from Google's address_components:

U.S. county and FIPS code, free

County is a common need (sales territories, tax rates, health data), and the script above leaves it out. For U.S. points there’s a second free source: the Census Bureau geocoder can say which county a point falls in, with its five-digit FIPS code, and it needs no key. This custom function calls it:

/**
 * U.S. county and its 5-digit FIPS code for a latitude and longitude, from the
 * free U.S. Census Bureau geocoder. =COUNTY(A2, B2) fills two cells.
 *
 * @param {number} lat Latitude in decimal degrees.
 * @param {number} lng Longitude in decimal degrees.
 * @return County name and FIPS code.
 * @customfunction
 */
function COUNTY(lat, lng) {
  if (lat === '' || lng === '') return [['', '']];
  lat = Number(lat);
  lng = Number(lng);
  if (!(Math.abs(lat) <= 90 && Math.abs(lng) <= 180)) return [['', 'INVALID']];
  const cache = CacheService.getDocumentCache();
  const key = 'cty:' + lat.toFixed(5) + ',' + lng.toFixed(5);
  const hit = cache.get(key);
  if (hit) return [JSON.parse(hit)];
  const url = 'https://geocoding.geo.census.gov/geocoder/geographies/coordinates' +
    '?x=' + lng + '&y=' + lat +
    '&benchmark=Public_AR_Current&vintage=Current_Current&layers=Counties&format=json';
  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  if (res.getResponseCode() !== 200) return [['', 'HTTP ' + res.getResponseCode()]];
  const geo = JSON.parse(res.getContentText()).result.geographies || {};
  const counties = geo.Counties || [];
  const value = counties.length ? [counties[0].NAME, counties[0].GEOID] : ['', 'NOT IN A U.S. COUNTY'];
  cache.put(key, JSON.stringify(value), 21600);   // 6 hours
  return [value];
}

Paste it into Extensions → Apps Script and save. Then =COUNTY(A2, B2) (latitude, longitude) fills two cells: the county name and the FIPS code. For a point in downtown Los Angeles (34.0522, −118.2437) the Census service returns Los Angeles County and 06037; for a point outside the United States it returns no county, and the function says NOT IN A U.S. COUNTY.

• The first two digits of the FIPS code are the state (06 is California), the last three the county. If the leading zero disappears, format the column as plain text or display it with =TEXT(C2, "00000").
• The county comes from the Census boundary files, so it’s exact for the point, even when the nearest street address is across a county line.
• It uses Apps Script’s URL Fetch quota (20,000 calls a day on a personal account), not the Maps quota. Answers are cached for 6 hours; paste finished rows as values.
• Census results are public U.S. government data, so the Google storage restrictions on this page don’t apply to this column.

What to expect from the results

• The nearest address, not the exact one. A GPS point in a parking lot comes back as the nearest street address, which may be the building next door.
• Points with no address (fields, forests, water) return the nearest road, town or region. That's still useful for city, state and country.
• Coordinates that are rounded to 2 decimal places are only accurate to about 1 km (0.6 mi). Use 4 or more decimals for street-level results.

Addresses in another language

Google returns the address in a default language. To choose it, change the geocoder line of the script to, for example, Maps.newGeocoder().setLanguage('fr') for French or setLanguage('ja') for Japanese. This changes how names are written (“Londres” instead of “London”), not which place is found. Use the same language for the whole column so the results sort and filter consistently.

Limits

Reverse geocoding uses the same Apps Script geocode quota as forward geocoding: 1,000 a day on a personal account and 10,000 on Google Workspace. Results come from Google Maps, and the Maps Platform service terms restrict storing them long-term. If you need a permanent address column, check those terms or use a provider that allows storage.

With an add-on

Troubleshooting

The Reverse geocode menu doesn’t appear. Menus are added when the spreadsheet opens. Save the script, then reload the spreadsheet tab, and wait a few seconds after it loads.

“Google hasn’t verified this app”. This appears the first time you run any script you wrote yourself. Check that the project name is yours, click Advanced, then Go to the project, and allow access. Only do this for code you’ve read and trust.

“Select exactly two columns”. Select the latitude and longitude cells together, side by side, latitude first. If your longitude comes first (as in Census exports), swap the columns or change the order in the script.

ZERO_RESULTS. Google has no address for that point, typically far out at sea. Points on land with no street usually still return a road, town or region.

Addresses in the wrong country. The coordinates are swapped or a minus sign is missing. =ABS(A2)<=90 should be TRUE for every latitude.

The run stops part-way. At 5 minutes the script stops itself to stay within Apps Script’s 6-minute limit, and at the daily quota it shows the error. Select the same cells and run it again: finished rows are skipped.

Frequently asked questions

Related guides

• How to geocode addresses in Google Sheets
• How to map addresses from Google Sheets
• A latitude and longitude formula for Google Sheets
• All guides

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.