How to Calculate the Distance Between Two Addresses in Google Sheets

Are you looking to calculate the distance between two addresses within your Google Sheets? Whether you’re planning logistics routes, analyzing customer data, or calculating commuting distances, knowing how to perform this task can save you time and effort. In this blog post, we will guide you through the process of calculating the distance between two addresses using Google Apps Script in Google Sheets.

As of now, in 2023, Google Sheets does not include a built-in function for calculating the distance between addresses or coordinates. However, there are various methods available to accomplish this within Google Sheets.

In a previous blog post, I demonstrated how you can automate Google Sheets to retrieve travel time and distance from Google Maps. If you’re interested in collecting travel time in traffic over a specific period, I recommend referring to that post. However, if you only need to calculate the shortest path or distance under normal conditions, the method explained below is more suitable.

Using Google Apps Script to Calculate Distance

Google Apps Script is a powerful tool that integrates seamlessly with Google Sheets, enabling users to extend the functionality of their spreadsheets. By utilizing Google Apps Script, you can automate tasks, create custom functions, and even connect with other Google products like Google Maps. This integration allows you to enhance your Google Sheets with features such as calculating distances between addresses, retrieving travel times, and performing geocoding. With the combined power of Google Apps Script and Google Sheets, you can streamline workflows, analyze data more effectively, and unlock new possibilities for productivity.

Using Google Maps is the easiest way to calculate distances with more details. However, if you have to calculate distances between a larger number of origins and destinations, it is not efficient to do it one by one. In such cases, you will find the method described here very useful.

Let’s start with an example

The following sample dataset contains a list of tourist destinations in the US. Let’s say you need to fund driving distance to each destination from New York City.

Step 01: Create a Custom Function to calculate the distance between two addresses

To calculate driving distances within Google Sheets, you can create custom functions using Google Apps Script. This allows you to use the function seamlessly within your Google Sheets.

Here’s how you can do it:

1. Open your Google Sheets and navigate to the “Extensions” menu.
2. Select “Apps Script” to open the Apps Script editor.
3. Replace the default code snippet with the following code.
4. Save the script.

Step 02: Use the function to calculate the distances

Now you can type the above function “CALCULATE_DRIVING_DISTANCE” in your Google Sheet and get the driving distance by providing the “origin” and “destination” as below.

=CALCULATE_DRIVING_DISTANCE("1 Centre St, New York, NY 10007, United States","601 S Hope St, Los Angeles, CA 90017, United States")

Or, by providing Geographic Coordinates as below.

=CALCULATE_DRIVING_DISTANCE("40.712784, -74.005941","34.052234, -118.243688")

You can also provide cell references to the origin and destination data as below.

=CALCULATE_DRIVING_DISTANCE(B2,C2)

To easily get started with calculating distances, you can make a copy of the Google Sheets I’ve prepared for the example mentioned above. Simply click on the following link to access the sheet. It includes the Apps Script code for the “CALCULATE_DRIVING_DISTANCE” function, allowing you to begin calculating distances right away.

Extend this function to get more things done

The function shown above utilizes the DirectionFinder class from the Google Maps Service in Google Apps Script. You have the flexibility to enhance the function by utilizing various methods offered within the DirectionFinder class to meet your specific needs.

For example, if you want to get the Transit distance between your address, you can modify the Mode in line 9 of the above code snippet as below.

.setMode(Maps.DirectionFinder.Mode.TRANSIT)

I hope this post has helped you to learn how to calculate the distance between two addresses in Google Sheets. If you have any questions, please feel free to leave a comment below.

Wrapping Up

In conclusion, calculating the distance between two addresses in Google Sheets can be achieved by leveraging the power of Google Apps Script. Although Google Sheets lacks a built-in function for this purpose, you can create a custom function using Apps Script to calculate distances efficiently.

By following the steps outlined in this blog post, you can enhance your Google Sheets with the ability to calculate driving distances. This feature becomes particularly handy when dealing with multiple origins and destinations, eliminating the need for manual calculations.