2

Good Morning,

I am seeking the solution enabling me to do any address geocoding in Excel with using Google Maps Geocode Api.

Basically, the task is possible via Google Sheets, what has been discussed in the thread below:

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 This API project is not authorized to use this API.

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?

enter image description here

  • How many geocodes in total do you have? Some like Google Maps API will only let you geocode if your showing it on a google map and not storing it in a file or database. – Mapperz May 20 at 12:58
  • basically 2 postcodes. According to the Reddit thread it's feasible via the WEBSERVICE function – MKR May 20 at 13:11
  • I appreciate it, however my goal is to make the geocoding straight from the excel sheet when possible. I know about Nominatim and Bing Apis, however I want the clarification regarding the Google Maps Api. I knew, that it's payable, until I found this hint on Reddit which tripped me out. – MKR May 20 at 14:01
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 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()
| improve this answer | |
  • Thank you, I will check this out! – MKR May 20 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.