Calculating Distance between two locations in Google Spreadsheet

Google Spreadsheets provide a lot of useful functions, but unfortunately nothing that calculates the distance between two locations. However, this is easy to add as a custom function using the Maps service available in Google Apps Script.

Most of the other approaches I found online seemed to involve using the external APIs provided by Google Maps or MapQuest. But both these approaches required registering for a Developer API Key. The approach I describe here seems a lot simpler.

I'll show how to create the following functions and how they can be used in Google Spreadsheets

DrivingMeters(origin, destination) - Driving Distance in Meters
DrivingMiles(origin, destination) - as above but in Miles
DrivingSeconds(origin, destination) - Driving Time in Seconds
DrivingHours(origin, destination) - as above but in Seconds

Using these functions in a Google Spreadsheet will be straightforward. If you have "New York" in cell A2, and "San Francisco" in cell B2, then you could put "=DrivingMiles(A2, B2)" in another cell to see the driving distance in miles between those cities. Locations can be expressed in any way that Google Maps recognizes as valid. So where a city name is sufficiently unique, the name alone will suffice. If a city name is common, then append the state or country, like "Lexington, NC". Street addresses and zip codes will also work.

To get started let's create a Google Spreadsheet with some city names arranged so as to form a matrix. The locations will be listed vertically in Column A, and horizontally in Row 1. For example:

In Cell B2 we'll insert "=DrivingMeters($A2, B$1)". Since we haven't written the custom function yet, this will return "#NAME?". Before we write the function, let's copy this into all the cells. Here I'm showing it copied into all cells below the diagonal line from upper left to lower right, since the values above the line would just be duplicates.

Now let's open the Google Script Editor and write the custom function. So click Tools > Script Editor and create a blank script. This will create an empty script which you will replace with the following

function DrivingMeters(origin, destination) {
  var directions = Maps.newDirectionFinder()
  .setOrigin(origin)
  .setDestination(destination)
  .getDirections();
  return directions.routes[0].legs[0].distance.value;
}

If you save this and switch back to the spreadsheet you should now see that the driving distances are filled in.

This shows a matrix of driving distances between the cities entered in meters. However, you probably want to see the distances in miles. Or you might want to see the driving times. Both of those can be done by going back into the Script Editor and adding the following functions.

function DrivingMiles(origin, destination) {
  return DrivingMeters(origin, destination)/1609.34;
}

function DrivingSeconds(origin, destination) {
  var directions = Maps.newDirectionFinder()
  .setOrigin(origin)
  .setDestination(destination)
  .getDirections();
  return directions.routes[0].legs[0].duration.value;  
}

function DrivingHours(origin, destination) {
  return DrivingSeconds(origin, destination)/(60*60);
}

To use these you'll just replace "=DrivingMeters(" in your spreadsheet with the corresponding function, like "=DrivingMiles(". That's all there is to it. I've put this sample code in a public google spreadsheet which you can copy by clicking the link. Feel free to ask any questions below.

Note about quota allowance: a lot of people ask about how to increase their quota allowance to be able to call this function many times. This used to be possible by purchasing a Google Maps APIs Premium Plan and passing the client ID and signing key from this to the Maps.setAuthentication method. However, Google no longer offers this plan, and there is no alternative. The method does not work with API keys. So unless you already have a Google Maps APIs Premium Plan, then you are stuck with the default quota allowance.