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

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! Nov 3, 2016 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.

• +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." Nov 4, 2014 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? Nov 29, 2017 at 14:36
• This needs a API key too. Least now.. Oct 24, 2022 at 20:19

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, 2009 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, 2009 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, 2009 at 18:34
• +1 because of the interesting formula, however I think using this is probably impractical. Nov 4, 2014 at 13:42

Google provides some sample custom functions to demonstrate Apps Script and I've used one that is Maps-related and does what you need:

Calculate driving distance & convert meters to miles

Coding level: Beginner
Duration: 10 minutes
Project type: Custom function and automation with a custom menu

Using custom functions, you can calculate the driving distance between two locations and convert the distance from meters to miles. An additional automation provides a custom menu that lets you add step-by-step directions from the start address to the end address in a new sheet.

How it works

The script uses 2 custom functions and an automation.

• The `drivingDistance(origin, destination)` function uses the Maps Service to calculate the driving directions between two locations and return the distance between the two addresses in meters.
• The `metersToMiles(meters)` function calculates the equivalent number of miles for a given number of meters.
• The automation prompts the user to enter which row of start and end addresses to calculate the driving directions for and adds the step-by-step driving directions to a new sheet.

Apps Script services

This solution uses the following services:

• Base service–Uses the Browser class to prompt the user to enter a row number for directions and alerts the user if an error occurs.
• Utilities service–Updates templated strings with user-specified information.

Set up the script

1. Make a copy of the Calculate driving distance and convert meters to miles spreadsheet. The Apps Script project for this solution is attached to the spreadsheet: Link to spreadsheet
2. To add headers and demo data to your sheet, click Directions > Prepare sheet. You might need to refresh the page for this custom menu to appear.
3. When prompted, authorize the script. If the OAuth consent screen displays the warning, This app isn't verified, continue by selecting Advanced > Go to {Project Name} (unsafe).
4. Click Directions > Prepare sheet again.

Run the script

1. In cell `C2`, enter the formula `=DRIVINGDISTANCE(A2,B2)` and press Enter. If you're in a location that uses decimal commas, you might need to enter `=DRIVINGDISTANCE(A2;B2)` instead.
2. In cell `D2`, enter the formula `=METERSTOMILES(C2)` and press Enter.
3. (Optional) Add additional rows of start and end addresses and copy the formulas in columns `C` and `D` to calculate the driving distances between various places.
4. Click Directions > Generate step-by-step.
5. In the dialog, enter the row number of the addresses for which you want to generate directions and click OK.
6. Review the driving directions in the new sheet the script creates.