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?