How to geocode addresses in Google Sheets

Prepare your addresses

Every geocoder does better with complete, consistent addresses. Before you start:

• One row per place, with a header row at the top.
• Either one full-address column ("1600 Main St, Springfield, IL 62701") or separate columns for street, city, state and ZIP. To join separate columns into one, use =TEXTJOIN(", ", TRUE, B2:E2).
• Add the country if your list spans countries. "Paris" alone is ambiguous: France or Texas?
• Remove suite and unit noise ("Ste 400", "Attn: Billing") if matches come back wrong. Geocoders match buildings, not suites.

1. One address: copy coordinates from Google Maps

For a handful of addresses, this is the fastest way and needs no setup.

1. Search for the address on Google Maps.
2. Right-click the pin. The first item in the menu is the coordinates, for example 40.74844, -73.98566. Click it to copy.
3. Paste into your sheet. To split "lat, lng" into two columns, use =SPLIT(A2, ",").

This doesn't scale past a few dozen rows, and it's easy to make copy-paste mistakes.

2. Hundreds of addresses: a free Apps Script

Google Sheets has no built-in geocoding function. But every Sheet comes with Apps Script, which includes Google's Maps Geocoder service. You don't need an API key or a billing account for it. The script below adds a menu that geocodes the cells you select and writes the results as plain values, so they don't recalculate and spend your quota again.

1. Insert three empty columns to the right of your address column. The script writes Latitude, Longitude and Matched address there.
2. Open Extensions → Apps Script, delete the sample code, paste the code below and click Save.
3. Reload the spreadsheet. A Geocode menu appears.
4. Select the address cells (one column, without the header) and choose Geocode → Geocode selected addresses. The first time, Google asks you to authorize the script.

/**
 * Adds a "Geocode" menu to the spreadsheet.
 * Paste into Extensions > Apps Script, save, then reload the sheet.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Geocode')
    .addItem('Geocode selected addresses', 'geocodeSelection')
    .addToUi();
}

/**
 * Select ONE column of addresses, then run Geocode > Geocode selected addresses.
 * Writes Latitude, Longitude and the matched address into the 3 columns to the
 * right of the selection. Rows that already have a latitude are skipped, so you
 * can run it again after a quota stop or a timeout.
 */
function geocodeSelection() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const sel = sheet.getActiveRange();
  const addresses = sel.getValues();
  const out = sheet.getRange(sel.getRow(), sel.getColumn() + 1, sel.getNumRows(), 3);
  const results = out.getValues();
  const geocoder = Maps.newGeocoder(); // optional: .setRegion('us') to prefer US matches
  const started = Date.now();

  for (let i = 0; i < addresses.length; i++) {
    const address = String(addresses[i][0]).trim();
    if (!address || results[i][0] !== '') continue;        // blank, or already done
    if (Date.now() - started > 5 * 60 * 1000) break;        // stop before the 6-minute limit
    try {
      const res = geocoder.geocode(address);
      if (res.status === 'OK' && res.results.length) {
        const r = res.results[0];
        results[i] = [r.geometry.location.lat, r.geometry.location.lng, r.formatted_address];
      } else {
        results[i] = ['', '', res.status];                    // e.g. ZERO_RESULTS
      }
    } catch (e) {
      // Usually the daily quota: "Service invoked too many times for one day: geocode"
      out.setValues(results);
      SpreadsheetApp.getUi().alert('Stopped at row ' + (sel.getRow() + i) + ': ' + e.message);
      return;
    }
  }
  out.setValues(results);
}

Limits you will hit:

• Daily quota. Apps Script allows 1,000 geocode calls per day on a personal Google account and 10,000 on Google Workspace, per user. Past that, you get Service invoked too many times for one day: geocode. The script stops, keeps what it finished, and skips finished rows when you run it again. See how to deal with that error.
• 6-minute run limit. One run of a script can't last longer than 6 minutes. The script stops itself at 5 minutes, so just run it again to continue.
• Terms. The results come from Google Maps. Google's Maps Platform service terms limit how long geocoding results may be stored (the Geocoding API section allows caching latitude and longitude for up to 30 days) and don't allow showing them on non-Google maps. If you plan to keep coordinates permanently or plot them on a non-Google map, read those terms first. This is our reading, not legal advice.

Record how good each match is

Every Google result includes a location_type that says how precise it is. Saving it in a fourth column lets you filter the doubtful rows instead of checking every row by eye:

To record it, insert a fourth empty column and change two lines in the script:

const out = sheet.getRange(sel.getRow(), sel.getColumn() + 1, sel.getNumRows(), 4);
...
results[i] = [r.geometry.location.lat, r.geometry.location.lng, r.formatted_address,
              r.geometry.location_type + (r.partial_match ? ' (partial)' : '')];
...
results[i] = ['', '', res.status, ''];

The second change also adds “(partial)” when Google didn’t match the whole address and filled in the gaps, which is a strong sign of a typo or a wrong town. Then filter the fourth column for APPROXIMATE or partial, and fix those addresses first.

3. Thousands of US addresses, free: the Census batch geocoder

For US addresses, the U.S. Census Bureau geocoder is free and handles up to 10,000 addresses per file.

1. Make a tab with exactly these five columns: Unique ID, Street address, City, State, ZIP. Use the row number as the ID and remove the header row.
2. File → Download → Comma-separated values (.csv).
3. On the Census site, choose Find Locations Using… → Address Batch, upload the file and download the results.
4. Import the results into a new tab (File → Import) and bring them back to your list with XLOOKUP or VLOOKUP on the ID.

Limits: US addresses only (including Puerto Rico), 10,000 rows and 5 MB per file, and it only knows street addresses, not business names. The Census Bureau's own FAQ says match rates can vary between runs, so resubmit only the unmatched rows. Note that the results return the coordinates as one "longitude,latitude" field (in that order), so split it carefully.

Reading the Census results

We ran a test batch through the Census geocoder. Each row of the results file looks like this:

"1","1600 Pennsylvania Ave NW, Washington, DC, 20500","Match","Exact","1600 PENNSYLVANIA AVE NW, WASHINGTON, DC, 20500","-77.035189204737,38.898702605246","76225813","L"
"3","123 Nowhere Imaginary Rd, Faketown, ZZ, 00000","No_Match"
"4","1 Main St, Springfield, , ","Tie"

• Column A is your ID and B the address you sent. Don’t rely on the rows coming back in your order; always match them on the ID.
• Column C is Match, No_Match or Tie (several equally good candidates, typically when the city or ZIP is missing). Unmatched rows stop after this column.
• Column D is Exact or Non_Exact. Non-exact matches had to correct something, so compare column E, the matched address, with your input.
• Column F holds the coordinates, longitude first. With the results imported to a tab named Census and your ID in A2, the latitude is =IFERROR(INDEX(SPLIT(XLOOKUP(A2, Census!A:A, Census!F:F), ","), 2), "") and the longitude is the same with 1 instead of 2.

For No_Match and Tie rows, add or fix the city, state and ZIP, and send only those rows again, or geocode them with the Apps Script method.

4. An add-on

An add-on gives you a sidebar instead of code. Before you install one, check:

• The real free limit, stated before you install. A common complaint in Marketplace reviews is "not free as stated".
• Permissions. Geocoding needs access to the open spreadsheet and to an external service. It shouldn't need your Gmail or all of your Drive.
• Whether you may keep the results. This depends on the data provider's terms (see the terms note above).
• What happens on a big sheet: does it resume after a limit, or start over?

Which method to use

Check your results

No geocoder is perfect. New buildings, rural routes and PO boxes are common misses. Two quick checks:

• Compare the matched address to your input. If you asked for a street address and got only a city or ZIP back, the geocoder fell back to the city center.
• Look for duplicates. Many rows with identical coordinates usually means they all fell back to the same city or ZIP centroid. A =COUNTIFS on latitude and longitude makes these easy to spot.

Next: put your coordinates on a map or calculate distances between them.

Troubleshooting

The Geocode menu isn’t there. Reload the spreadsheet after saving the script. The menu is added each time the file opens.

“Google hasn’t verified this app” when authorizing. This is normal for a script you added yourself. Click Advanced, then Go to your project, and allow it. It only gets the permissions the code uses.

Results land in the wrong columns, or overwrite data. The script writes into the three columns immediately to the right of the selection. Select only the address column, and insert empty columns first.

Nothing happens to some rows. They already have something in the latitude column, so the script treats them as done. Clear the three result cells to geocode a row again.

The Census upload fails or returns nothing. The file must be CSV with exactly five columns and no header row, no more than 10,000 rows and 5 MB. Commas inside an address must be inside quotes; Sheets’ CSV download does that for you.

Many rows have exactly the same coordinates. They fell back to a city or ZIP center. Use the quality column above to find them, and complete those addresses.

Frequently asked questions

Related guides

• The best Google Sheets add-ons for maps, geocoding and distances
• How to get the ZIP code from an address in Google Sheets
• A latitude and longitude formula for Google Sheets
• How to map addresses from Google Sheets
• Reverse geocode in Google Sheets: coordinates to addresses
• 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.