Google API Geocoding in MS Excel for free

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 ArcGIS
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()