How To Calculate Distances in Google Sheets (via Maps API)?
Early June 2020 I wrote an article in Dutch on this matter in response to an earlier blog in 2018 by Sven Breckler with comments of Phil Schmidt. I noticed that most viewers did not understand well enough the content to make full use of it. Hereby the English variant.
In an other article I wrote about the Coda and a function to get the distance between two points asuming you draw a straight line. It is the Haversine function, also used by Tinder. Below the logic in Coda using the function Distance().
Distance(location1, location2, unit)Returns the distance (in kilometers) between two locations (lat/long) on earth using the Haversine formulaDistance(Location(33.9206418,-118.3303341), Location(37.4274787, -122.1719077))= 521.8529425485297
This solution is often not what people have in mind when they ask for the distance between two points. They assume a distance based on the path you follow or the route you take. Hence we enter the world of navigation.
In Coda you can have navigation using a pack that based on two addresses returns the distance in miles or km and the time it approximately takes.
In what follows I explain the google sheet logic, but it may be of interest that the Coda solution is easier to set up. I use this with clients active in people transport.
Google Sheets and ImportXML
Distances related to navigation is something that Google Maps offers. Google Sheets can easily import data from Google Maps via the function ImportXML(), below the syntax:
IMPORTXML(url; xpath-query)The two parts are first the URL of the page to examine (in our case the Google Map Server), including protocol (e.g. http://) and second after the comma the The XPath query to run on the structured data. We need both a distance and a duration.
To make this work we need a URL that does the math for us and we need some geolocations written as an address or in lon & lat. We keep it simple and go for the address logic. Google knows how to handle this perfectly. Since Google Sheets is a spreadsheet we use cells to enter the variables:
You see above the split between the country and the city and that the starting point is putting these two togeteher using a =concatenate(B2,”,”,A2). This is on purpose. Some villages appear in more than one country and to avoid confusion, this trick will do the job. Second we have the destination and here we are sure that this is the only one.
In the above the two slightly different formulas we apply one for the distance in meters and one for the time it takes in minutes.
=IMPORTXML("https://maps.googleapis.com/maps/api/directions/xml?origin="&C2&"&destination="&D2&"&key=123®ion=BE&mode=driving","//leg/distance/value")=IMPORTXML("https://maps.googleapis.com/maps/api/directions/xml?origin="&C2&"&destination="&D2&"&key=123®ion=BE&mode=driving","//leg/duration/value")
To avoid that Google reads my references as text I put the start & destination between quotation marks, now Google knows where to go.
The Google API Key
My Google API key is not visible in the above, but an essential compontent of the solution. Maybe this is the more tricky part of the solution. How to get one you find explained here. Please note that the key is not put between quotation marks. You have to check two conditions
- Is your project linked to a paid account? If not you receive a obscure notifications. Chances are little yo uhave to pay (though in my projects I paid)
- Activate in your project these applications
Directions API, GeoCoding API, Maps JavaScript API en Places API.
Once you have done this right, error messages as below should no longer appear:
Coda and the Google API -> Google Scripts
One can use Google Scripts to set up a logic that is simulair to the one described above and let ik talk to Coda.io. I did not yet complete a project like this, but it should not be too difficult to work out. Once I have done this, I’ll write a blog about it and I guess (I just thought about it) I’ll use additional stops between the starting and the endpoint to calculate intermediate distances and durations. That would come in handy when checking a time table.
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialised in serving SME in harvesting data and keeping it aligned. I mainly rely on AirTable, Coda, Mailjet, Zapier & Paperform to get the job done. Once in while I use Google Sheets, certainly if my clients ask for it.
Coda.io and how to keep data aligned in a table
