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:
-
create our API key
-
restrict it to Geocode API option
-
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?
Best Answer
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.
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.