I hate manual processes.
NOTE: If I get one more email from someone nagging me to finish that Quickbooks SDK Tutorial.. I’m going do it!!! 😉
Recently, a client wanted a market analysis showing how far customers traveled to get to their location. They were having employees manually check addresses using Google maps to determine this information and entering this on a sheet. The data was later entered in a spreadsheet. All of this data, minus the distances, is in their database.
Their database is a somewhat archaic, closed system.. But custom queries allowed for the creation of a csv file.
Simple Solution using VBA and the Google Maps API
Instead of manually retrieving this information I wrote a VBA routine that connects to the Google Maps API and retrieves the distances. I prompt the user to select the csv file, prepare it a little for readability, retrieve the distances, then add some functions to the top to show averages of some key fields.
I’ve also written a post analysis routine that takes all the city and state information and determines and list the cities where the bulk of their guests are from. This file is then uploaded to Google Maps to show a visual representation of where their customers are from.
The Google Maps API limits request to 10 per second (or something like that) before returning an “OVER_QUERY_LIMIT” error/message.
When I get that message, I pause the routine 2 seconds and try again. Actually, you’ll see in code that I retry up to 5 times. That’s because when I retried once or twice, it sometimes got the same error – even with a few seconds between.
Nothing fancy here but it forms a basis for you to retrieve Google Maps API data for multiple addresses. You can also retrieve travel time and directions.
You may download the sample sheet below.
Here is a video of the code executing on my sample spreadsheet.