Google API Geocoding in MS Excel for free

Question

I am seeking a solution enabling me to do address geocoding in Excel using the Google Maps Geocode API.

Basically, the task is possible via Google Sheets, like has been discussed at How to Geocode a Google SpreadSheet?

and explained in details here:

http://www.mkrgeo-blog.com/the-costless-way-to-geocoding-addresses-in-excel/

The Google Sheets have a good plugin - Awesome Table, which can do it for you up to 1000 records daily. The major awkward thing is, that it works somewhat on an external platform, which the Google Spreadsheet is and anytime requires the transfer to our Excel worksheet.

I would like to have I straight in Excel. Unfortunately, due to Google Maps policy, the geocoding API is not free anymore (even for the small amount of queries).

The policy changed in the summer 2018, however I spot something in the web, that might be helpful to get around this problem, even without the VBA macros involvment.

According to the thread below:

https://www.reddit.com/r/excel/comments/a2r3aq/converting_addresses_to_lat_and_long/eb0mkys/

this issue can be solved up to 2500 records per day, when we:

1. create our API key

2. restrict it to Geocode API option

3. Paste into our Excel formula, which looks as follows:

   =WEBSERVICE("https://maps.googleapis.com/maps/api/directions/xml?key=PUTYOUROWNAPIKEYHERE&origin="&SUBSTITUTE(A1," ","%20")&"&destination="&SUBSTITUTE(A2," ","%20"))

Translating it into my example (with my own Geocode API key) it looks as follows:

  =WEBSERVICE("https://maps.googleapis.com/maps/api/directions/xml?key=AIzaSyCiAhXX59p2G2PworpQeu8ADcqIo0s8h8A&origin="&SUBSTITUTE(Z18," ","%20")&"&destination="&SUBSTITUTE(Z19," ","%20"))

Unfortunately, it doesn't work, as I am getting the string like this:

REQUEST_DENIED <error_message>This API project is not authorized to use this API.</error_message>

I though, that it means, that my newly created API is not enabled. I went here and did it.

It didn't help too. So does it mean, that there is no option for free geocoding in Google? Can anyone clarify, who encountered this problem or was willing to do some geocoding via Google Maps?

Answer 1

Using the Python package GeoPy you can geocode using Google Maps v3 API. https://geopy.readthedocs.io/en/stable/#googlev3

According to https://developers.google.com/maps/documentation/geocoding/usage-and-billing, it appears that there is no longer a free tier.

"Reminder: To use the Geocoding API, you must include an API key with all API requests and you must enable billing on each of your projects."

That being said, I have used this package with other geocoder options such as Nominatim and ArcGIS successfully.

The code below can be used to geocode addresses in each row of a column for each sheet in an excel spreadsheet, and write each output to a new column. I have it set up to geocode a "city, state" pair and write out lat and long to individual columns.

import pandas as pd
from geopy.geocoders import GoogleV3
from openpyxl import load_workbook
import xlrd
from geopy.extra.rate_limiter import RateLimiter

geolocator = GoogleV3(api_key,  "maps.googleapis.com")

#alter min_delay param if getting too many request error. May not need if using ArcGIS geocoder
geocode = RateLimiter(geolocator.geocode) #min_delay_seconds=0.5

# Spreadsheet path
adr_tble = (r"")

#get sheet names
sheets = [i for i in xlrd.open_workbook(adr_tble, on_demand = True).sheet_names()]

book = load_workbook(adr_tble)
writer = pd.ExcelWriter(adr_tble, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)


for i in sheets:
    print("Sheet: " + i + "\n")

    #read in dataframe
    df = pd.read_excel(adr_tble, sheet_name = i)

    #check if city state column exists
    if "CITY_STATE" in df:
        # create empty lists
        list_lat = []
        list_long = []

        for index, row in df.iterrows():
            cityState = row['DUTY_CITY_STATE']
            location = geocode(cityState)
            lat = location.latitude
            long = location.longitude

            print(cityState + " = " +  str(lat), str(long))

            list_lat.append(lat)
            list_long.append(long)

        df['lat'] = list_lat   
        df['lon'] = list_long   

        df.to_excel(writer, sheet_name=i)
        print("\n-----------------------------------------------------------------------------------\n")

writer.save()
writer.close()

Answer 2

Just use this method: https://www.youtube.com/watch?v=ozjSCoJGgVc Super easy+you can create your map with results.

Answer 3

The request you are pasting in (https://maps.googleapis.com/maps/api/**directions**/xml) is for Directions API, but you have only enabled Geocoding API for your project. That's why you're getting the error that your API key is not authorized for the service (Directions API).