How to geocode a large list of addresses in Google Sheets

The short answer

To geocode a large list of addresses in Google Sheets, first count the distinct addresses that need coordinates, then choose a method that fits the list and its daily limits. Each address geocoded with MapRows costs one lookup. MapRows Free allows 100 lookups per day, and Pro allows 2,500 per day, subject to fair use and shared capacity. A large task can pause and resume without starting from the first row.

For a one-off job of 10,000 US addresses, the free Census batch geocoder may be enough. It accepts up to 10,000 addresses per file, with a 5 MB file limit, and you can import the results into your sheet. It needs a particular CSV layout and covers US addresses, including Puerto Rico. Our geocoding guide explains that route and includes a free Apps Script option.

MapRows is useful when you want to work in a sidebar, handle recurring lists or geocode addresses outside the Census tool’s coverage. If your list is small, a free method may be sufficient. If you need more volume than the standard allowance, compare the Pro option with your own Geoapify key against your needs and that provider’s plan limits.

Count the work

For a new list, start with the number of address rows you intend to send. One address geocoded is one lookup. If 500 rows each need a lookup, plan for 500 lookups. Other MapRows actions, such as inserting a map, also cost lookups, so the full daily allowance may not be available if you have already used some of it.

The table divides rows by the daily allowance and rounds up to a whole number of days. These are minimum days assuming every row costs one lookup and the full allowance is available each day. MapRows’ shared capacity can pause a task earlier. Errors that need another run can add work, too.

For example, 10,000 ÷ 2,500 is 4. A 500-row list fits within one full Pro daily allowance, but that does not mean you should assume all 2,500 lookups remain available. The sidebar shows today’s usage and reset time. Unused lookups do not roll over to the next day.

Skip rows already done is on by default. MapRows skips rows with OK or LOW_CONFIDENCE status on a later run, so those finished rows do not cost another lookup. Do not count every row in the sheet again just because you need to resume. Count the unfinished work and leave room to investigate results that need correction.

Shrink and prepare the list

Make a working copy of the list and keep the original row identifiers alongside the addresses. Remove blank rows from the work list, then look for repeated addresses. A list of 10,000 records may contain fewer than 10,000 distinct places. Geocoding each unique address once and bringing its result back to the matching records can reduce the number of lookups.

Suppose full addresses are in column A, with a header in A1 and data in A2:A10001. After removing blanks and cleaning the address text, put this formula in an empty cell on a separate work tab:

=UNIQUE(Sheet1!A2:A10001)

Replace Sheet1 with the name of your source tab. The formula produces a list of distinct cell values. Give the output column a header and geocode that list. Leave room for the list to expand and for the result columns. If you use a different source range, include all the rows you intend to process.

Consistent text matters. 123 Main St, Springfield, IL and the same address with an extra space can appear as different values. In a helper column, =TRIM(A2) removes extra ordinary spaces from the address in A2. Copy it down and review the cleaned values before using them as your unique-list input. For non-breaking spaces, our split-address guide shows a cleanup formula.

Do not assume every similar-looking address is the same place. Check house numbers, city, state, ZIP and country before combining records. Add missing address parts where you can. If a list spans countries, include the country so a place name does not stand alone. If a match is wrong, suite or attention text may be noise to remove for the geocoding input; keep the original details in your source list.

Once you have checked the unique results, bring them back to the original rows by matching the same cleaned address. For example, if the cleaned address is in B2 on the source tab, and a tab named Unique has cleaned addresses in column A and latitude in column B, use =XLOOKUP(B2, Unique!A:A, Unique!B:B, ""). Copy it down. Use the same pattern with the longitude result column. Check a few original rows against the unique list, including repeated addresses and missing matches.

Keep the unique address column and its result columns together when reviewing or sorting them. The lookup needs the address and coordinates from the same result row. A blank returned by the example formula can mean there was no matching cleaned address or that the result cell is blank, so investigate blanks before using the coordinates.

If US ZIP codes have lost leading zeros, restore a five-digit ZIP held as a number with =TEXT(D2, "00000"), where D2 contains the ZIP. For example, 2108 becomes the text 02108. Check the original source when you are unsure what the ZIP should be. The split-address guide covers separating address parts and preserving ZIP codes; splitting is not required when you already have a usable full-address column.

Geocode a large sheet with MapRows

Prepare a header row and one row per address you want to geocode. You can use a single full-address column or separate columns for parts such as street, city, state and ZIP. If you made a unique list, run the task on that list rather than the original repeated records. Decide which output fields you need before starting so you can review both the coordinates and the match.

1. Install MapRows from its Google Workspace Marketplace page if needed. Open the spreadsheet and choose Extensions → MapRows → Open MapRows. The first time, Google asks you to allow the requested permissions.
2. On the Geocode tab, choose the sheet and header row. Check that the sheet is the work list you intend to process.
3. Tick the address column, or tick the separate address-part columns in order. MapRows pre-selects columns with address-like headers, so review the selection rather than assuming it has chosen the intended input.
4. Optionally limit results to a country for better matches. This is especially useful when an address could describe places in more than one country.
5. Choose the output. Coordinates gives latitude and longitude. Standard adds a formatted address, city, state, postcode and country. Everything adds fields including confidence and match type. You can also tick fields individually.
6. Choose All rows or Selected rows, then press Geocode. Watch the progress card and review the first results before relying on the entire list.

Results appear in new columns to the right of your data. Their headers have notes starting with MapRows:. Keep those notes so later runs reuse the result columns instead of adding new ones. MapRows does not edit your own columns or formulas. Keep the address, status and result cells together when you review the sheet, and check a few rows after any rearrangement.

For a large job, include the formatted address and confidence in your output if you need to review match quality. Coordinates alone tell you where a result is, but the formatted address helps you compare the returned place with the input. You can choose individual fields if a preset includes more than you need.

Pause and resume a task

MapRows processes large sheets in chunks and shows a progress card. You can press Pause whenever you need to stop. If you close the sidebar during a task, progress is kept. Reopen it through Extensions → MapRows → Open MapRows and press Resume to continue.

A task also pauses when your allowance or MapRows’ shared daily capacity runs out. The allowance resets at 00:00 UTC, and the sidebar shows the reset time in your time zone. After the reset, press Resume. The task carries on from the next unfinished row. A shared capacity message means the shared capacity has been exhausted, even if your own planning assumed more lookups would be available.

You can have one unfinished task per spreadsheet. Starting a new task prompts you before replacing the unfinished one. Read that prompt carefully if you are working through a large list: a different selection or another job may not be the task you meant to continue. Check the progress card and resume the existing task when your intention is to finish it.

On a later run, the default skip setting treats OK and LOW_CONFIDENCE as done. A row marked ERROR can be retried on the next run; the status may describe a temporary problem such as a rate limit. Read the status before retrying. A low-confidence result is different: it has a match and is already considered finished, even though the match needs review.

Choose an option for a larger list

Wait for daily resets. MapRows Free is $0 and has no time limit. If the job is occasional and can wait, use its 100 lookups per day, pause at the limit and resume after each reset. Reduce duplicates first, and allow for shared capacity rather than treating the table above as a promised schedule.

Use Pro. MapRows Pro costs $9 per month or $59 per year, with tax included. It provides 2,500 lookups per day, subject to fair use and shared capacity. That may fit a recurring job or a list that would otherwise take many daily allowances. See the pricing page for the subscription terms.

Use your own Geoapify key with Pro. Add the key on the Account tab. MapRows limits then stop applying, and your Geoapify plan’s limits and pricing apply instead. Check those limits and costs in your Geoapify account before choosing this route. It can suit a larger volume requirement, but the key does not remove the need to check matches.

Use the free Census batch geocoder for US addresses. For a one-off 10,000-row US-only job, this may be a practical free choice. The Census tool accepts up to 10,000 rows and 5 MB per file, including addresses in Puerto Rico. It handles street addresses, not business names. Prepare a CSV with exactly five columns: unique ID, street address, city, state and ZIP, without a header row.

After downloading the Census results and importing them into a new tab, match them back to your list by ID. Do not rely on the result rows returning in the original order. Census coordinates arrive as one longitude,latitude field, so take care with the order. Review No_Match and Tie rows, improve their address details and resubmit only the unmatched rows. The geocoding guide gives the full CSV steps and explains the result fields.

Use the free Apps Script in that guide. It geocodes selected address cells, writes results as values and skips rows that already have a latitude when you run it again. It is an option if you are comfortable adding and authorizing a script. Check the Google service quotas and applicable storage terms in your account before planning a large job around it. Leave the required result columns empty before running the script.

These methods involve different preparation and review. Census requires a CSV round trip and a US street address; the script needs setup; MapRows provides a sidebar and resumable task. Choose based on coverage, how often you need to repeat the work, the allowance you can use and how you plan to store or publish the results.

Check the results

A completed run is not the same as a verified address list. MapRows results come from Geoapify using OpenStreetMap data and can be wrong or incomplete, particularly for new buildings and rural addresses. Filter the status column and work through the groups that need attention before using the coordinates for a decision.

• OK: the address matched with good confidence. Sample-check these rows against the input and formatted address.
• LOW_CONFIDENCE: the match has confidence below 0.6 and may identify only a street or town. Compare the formatted address, add a city or ZIP, or use a country filter.
• NO_MATCH: nothing was found. Check for typos and add missing address parts.
• EMPTY_INPUT: the input cells were blank. Check whether the blank was intentional or the wrong columns were selected.
• ERROR: read the accompanying reason and run again after addressing the problem. Temporary errors are retried on the next run.

If you selected confidence as an output, sort by it to bring lower-confidence matches together. Keep complete rows together when sorting, and compare the input with the formatted address. A returned city or postcode center may be much less specific than the building you intended. Identical coordinates across many different addresses can also be a reason to investigate whether the matches fell back to the same area.

Take a sample across the sheet, not just the first few rows. Include different cities, address formats and status groups, as well as any locations where an error would matter. Correct the source details when needed and check the resulting match. For the original repeated records, verify that the lookup formula brings back the coordinates for the correct cleaned address.

You may keep the results stored in your sheet. If you publish them, keep the attribution “© OpenStreetMap contributors” as described in the MapRows documentation. Keep your review notes with the work so someone using the list can distinguish checked locations from results that still need attention.

Common problems

The task paused. Check the progress card and today’s usage. An exhausted allowance or shared daily capacity pauses the task. Progress is kept; press Resume after the reset shown in the sidebar. If you paused it yourself, use Resume when you are ready to continue.

Nothing happens after the reset. Reopen the sidebar if you closed it and press Resume. Check that you are in the spreadsheet with the unfinished task and that the displayed reset has passed. If you started another task, check whether you accepted the prompt to replace the earlier one. Read any status or shared capacity message before trying again.

Matches are in the wrong country. Check the input for an ambiguous town or incomplete address. Add city, state, ZIP or country as appropriate, and use the optional country limit on Geocode. Compare the formatted address with the input; coordinates by themselves may not make the mistake obvious.

Repeated addresses still appear in the work list. Check for extra spaces and differences in the actual text, then use a cleaned helper column as the input to UNIQUE. Review the addresses before combining them: different house numbers or cities can represent different places. Check that your result lookup uses the same cleaned value as the unique list.

Rows were skipped. Check their status and the Skip rows already done setting. It is on by default and skips both OK and LOW_CONFIDENCE. A low-confidence match still needs human review even though MapRows counts it as done. Check also whether you chose all rows or only selected rows, and whether the input cells are blank.

A row says ERROR or NO_MATCH. Read the status first. An error can be temporary and is retried on the next run. For NO_MATCH, correct typos or add missing address parts. If you need help interpreting a persistent status, the documentation explains how to contact support with the status text and what you were doing.

Frequently asked questions

Related

• How to geocode addresses in Google Sheets
• Split an address into columns in Google Sheets
• A latitude and longitude formula for Google Sheets
• Reverse geocode in Google Sheets
• Find the nearest location in Google Sheets
• MapRows documentation
• All guides

Disclosure: we make MapRows, the Google Sheets add-on mentioned in this guide. The Census batch geocoder and the Apps Script in our geocoding guide work without the add-on.

Google Sheets and Google Workspace are trademarks of Google LLC. MapRows and Cellbrook are not affiliated with or endorsed by Google.