When you use Google API with Excel, you can easily convert your addresses to GPS coordinates, and the other way round, GPS to location
Contact a Service with a Google API
An API (Application Programming Ie) is a service. That means, you contact a server with a specific URL and in return, the server returns a result.
So, imagine, your call a friend and you say. "Hey ! What are the GPS coordinates for this address"
- 171, 1st Avenue, New York, NY 10003, USA
And your friend replies instantly :
- 40.7292330 lat, -73.9845100 long
Wow! Your friend is impressive 😮😮😮😮😮
And that's what a Google API can provide you 😉
How to used a Google API
Before to use an API in Excel, you must connect to the Google API Library
And then, create an API Key
How Much does it Cost?
When you use an external service, you can be charge for the use of the service. In the case of the GPS Google API, you can perform 40,000 request per month without been charged.
But to avoid to be charge, you can set a limitation of requests
Convert your address to GPS Coordinates
Since Excel 2013, the WEBSERVICE function makes the connection between Excel and an API. But before to use it, it's very important to understand all the informations returned.
Then, you can easily extract a specific result with the FILTERXML function
You can easily check if the answer is correct by adding a URL link to Google Maps in your worksheet
Convert GPS Coordinates to Address
The opposite is also possible. If you collect only GPS coordinates from pictures or tracking device, you would like to know the address corresponding to this location.
But in this situation, the API doesn't returned only 1 result. You can have the full address, the district, the city, the states or the country.
So in this situation, you can customize the XPath of your FILTERXML function to specify the answer you want to return in Excel
Distance between cities
Finally, you can use another Google API, Distance Matrix API, to calculate the distance or the time travel between 2 cities.
