# 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.

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&region=BE&mode=driving","//leg/distance/value")=IMPORTXML("https://maps.googleapis.com/maps/api/directions/xml?origin="&C2&"&destination="&D2&"&key=123&region=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.

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: