# Function to find distance between cities

I'm working with Google Docs and want to create a spreadsheet of the following form:

```Point A         Point B           Mileage
Los Angeles     Miami             292100
Palo Alto       San Francisco     90
```

In the spreadsheet, `Mileage` is automatically calculated. I am looking for "distance driven", not "as the crow flies".

## migrated from superuser.comJul 1 '11 at 16:57

This question came from our site for computer enthusiasts and power users.

• As the distance increases, the difference between "driving distance" and "crow flight" will diminish accordingly. In any case they probably won't differ by more than a few percent. – GalacticCowboy May 12 '10 at 0:11
• that seems like a pretty bold claim, cowboy. are you saying that driving from portland to atlanta is is roughly the same milage as flying? i don't think so. – Landon Kuhn Jul 11 '12 at 16:39
• @pnuts agreed, and i'm not gonna defend my 4 year old comment :) – Landon Kuhn Jan 9 '15 at 21:11
• Rosarch, why don't you revisit Web Applications after 4 years of absence and review all the nice answers you received on your question? – Jacob Jan Tuinstra Apr 6 '15 at 12:43

With the following little piece of code, you can retrieve the distance between two points.

## Code

``````function getDirection(city1, city2, unit) {
var directions = Maps.newDirectionFinder()
.setOrigin(city1).setDestination(city2)
.setMode(Maps.DirectionFinder.Mode.DRIVING)
.getDirections();
var d = directions.routes[0].legs[0].distance.text;

var distance, value = d.split(" ")[0].replace(",", ""), text = d.split(" ")[1];
if(text == unit) {
distance = value;
} else if(text == "km" && unit == "mi") {
distance = value / 1.6;
} else {
distance = value * 1.6;
}
return Math.round(distance);
}
``````

## Explained

The MAPs API, build on top of the Google Apps Script, will get the first direction (routes[0]) between the origin and destination. The returned value is stripped from comma's and parsed as an integer.

## Note

The distance returned can be either in kilometer or miles. I've added the calculation for "as the crow flies" as well (in miles).

Add the script under Tools>Script editor in the spreadsheet. Press the bug button in the script editor and you're on the go !!

## Example

I've created an example file for you: get driving distance

## References

• It looks like the formula in the sample doc is no longer working consistently. Could someone please edit it to make it work? I know nothing about scripting (more than copying and pasting) and I would love your help! All I'm looking for is the driving distance between zip codes. Thank you! – Melissa Nov 3 '16 at 22:19

``````=importXML("http://mapquestapi.com/directions/v1/route?
key=YOUR_KEY_HERE&outFormat=xml&from=" & A2 & "&to="
& B2,"//response/route/distance")
``````

where `A2` is the origin and `B2` is the destination.

You have to get an API key from mapquest and replace the value `YOUR_KEY_HERE` with it.

• How do you get the API key? – Jacob Jan Tuinstra Jul 1 '13 at 17:46
• @JacobJanTuinstra developer.mapquest.com – Neil McGuigan Jul 1 '13 at 19:18
• Neil, your alive !! Thanks for sharing that. – Jacob Jan Tuinstra Jul 1 '13 at 19:21
• +1 Apparently elegant solution, however MapQuest is a little lousy. I got this error when trying to use the key I was given: "This key is not authorized for this service. If you do not have a key, you can obtain a free key by registering at developer.mapquest.com." – Nick Bolton Nov 4 '14 at 13:49

This is what works for me:

``````=importXML("http://maps.googleapis.com/maps/api/distancematrix/xml?&origins="& <point.A> & "&destinations=" & <point.B>; "//distance/value")
``````

This will yield the driven distance between `<point.A>` and `<point.B>` in meters (according to Google Maps, of course).

• This is great! How about the as-the-crow-flies distance? – Dustin Kirkland Nov 29 '17 at 14:36

I do not think there is built in Excel function, or extension, to do this.

You might want to check out this Source Forge page: http://zips.sourceforge.net/

• It provides a CSV dataset to convert Zip Codes to Lat/Long (center point presumably)
• It provides Excel code to calculate the "crow flies" distance between two lat/long coordinates. (and includes equivalent code for Java, PHP and Python.

The Excel function is

``````=IF(SIN(Lat1) * SIN(Lat2) + COS(Lat1) * COS(Lat2) * COS(Long1 - Long2) > 1,
ACOS(SIN(Lat1) * SIN(Lat2) + COS(Lat1) * COS(Lat2) * COS(Long1-Long2)))
``````

The page notes that the angles have to be in Radians and that Excel has a degrees to Radians conversion function:

``````=RADIAN(degrees)
``````

Unless someone else has a slicker answer, this should get you what you want without too much work.

• yes, but what about distance driven instead of "as the crow flies"? – Rosarch Oct 18 '09 at 16:08
• That is considerable more difficult and would require knowledge of all roadways, as is done in Mapquest, Google Earth, Streets&Trips(or MapPoint). I don't know if any of these apps have a suitable API. – Kije Oct 18 '09 at 18:30
• ... on the other hand, if you were looking for a rough and ready approximation for a fairly limited number of cities, you could look for the driving distance tables that are fairly common in driving maps and atlases. Replicating that style of data in Excel and constructing a lookup to retrieve it for pairs of locations is certainly feasible in Excel. If that appeals, reply and I will could work up a function for it. – Kije Oct 18 '09 at 18:34
• OP uses Google Spreadsheets. – Jacob Jan Tuinstra Jul 1 '13 at 17:53
• +1 because of the interesting formula, however I think using this is probably impractical. – Nick Bolton Nov 4 '14 at 13:42

Google Developers offers a very robust script for map related functions in Google Sheets that I used and have really enjoyed. Full instructions are on the webpage as well.

• Could you summarize some of the key points of the "full instructions," please? Answers should stand alone and not depend on external sources (though I realize it's less likely that a Google site will disappear). Thanks! – jonsca Feb 13 '18 at 23:01

I am not familiar enough with the APIs to tell you exactly how to do this, but I would think it would be possible using the Google Maps API.

Google Maps, of course, can provide directions from one point to another through their API. It would be complicated, but possible to route the locations from the spreadsheet through the API and get driving directions, which would give you a driving distance between cities.

You can write the functions on your own which is similar like macro's