Google Sheets Geocoding Macro

Geocode from addresses to latitude / longitude, and vice versa using Google Sheets.

Test Sheet

Try the script out on a Test Sheet with sample address data. You can enter your own address data and geocode it in the rows below.

You must be logged into a Google Account before the Geocode menu will appear.

Any data you enter will be automatically deleted every Sunday at 4AM CEST, this isn't for long term storage.

Multicolumn Addresses → Latitude, Longitude

Now it supports geocoding using address data spread across multiple columns.

The way this works is: You select a set of columns containing the data, and the geocoding process puts the latitude, longitude data in the rightmost two columns. It will overwrite any data in those two columns.

Some care is needed, as it will concatenate all columns except the rightmost two columns to create the address string.

Latitude, Longitude → Nearest Address

It also supports reverse geocoding.

Simply select the latitude, longitude columns and it will place the nearest address data in the rightmost column. It will overwrite any data in that column.

Less care is needed, as it will automatically use the leftmost two columns as the latitude, longitude pair.

Latitude, Longitude → Address Components

It now supports reverse geocoding and splitting the address components into different columns.

See the Reverse To Components tab in the Test Sheet.

Map Cells

It now supports mapping the Latitude, Longitude pairs in the Mapping tab.

Adding It To Your Own Sheet

Step 1. Create or Open a Google Sheet and add addresses to it.

Step 2. Tools -> Script Editor

Step 3. Copy this script code into the Code.gs editor, replacing everything.

Step 4. Save

Step 5. Reload Sheet

Step 6. Run Geocode, Click Through Warnings

That's it.

Troubleshooting

• I don't see the Geocode menu!

  You must be logged into a Google Account before the Geocode menu will appear. Anonymous / not logged-in users will not work, Incognito Mode will not work.

• It gives me a bunch of warnings when I run it the first time.

  If you're using the Test Sheet, this means that the script will have access to the data you are entering. Don't enter anything you wouldn't want me to see, because as the owner of the shared Sheet, I see the data that gets put into it.

  If you've added the script to your own sheet, this means that the script will have access to the data you are entering. Since you're the owner of your Sheet, this isn't an issue. You can always audit the script by reading the source code in this repository.

• It returns latitude / longitude data using "," instead of "." separators.

  There's not much I can do about the return formats, but a user reported that adding the following array formula to the latitude / longitude columns changes the separators for them: =ARRAYFORMULA(SUBSTITUTE(C2:C;",";".")).

  Make sure you specify the correct columns.