✦Template Printable Ideas Insights

Google Sheets Driving Distance Calculator

By

Efficiently planning routes and calculating driving distances are crucial tasks for businesses, logistics, and personal travel alike. Google Sheets, with its powerful suite of tools, allows you to calculate driving distances effortlessly. Let's explore how you can use Google Sheets to find driving distances between multiple points, and how to leverage these calculations for smarter route planning.

Google Sheets provides the TRAPEZOID function to calculate driving distances between two points. However, for calculating distances between multiple points, we'll need to create a custom function using scripts.

Calculating Driving Distance Using TRAPEZOID Function

The TRAPEZOID function in Google Sheets computes the driving distance between two points. It takes two sets of coordinates (latitude and longitude) as input and returns the distance in kilometers or miles. Let's break down how to use this function effectively.

How To Create Checklists with Google Sheets
How To Create Checklists with Google Sheets
Calculate Mileage Reimbursement in Google Sheets: Fast, Easy & Accurate Formula Tutorial
Calculate Mileage Reimbursement in Google Sheets: Fast, Easy & Accurate Formula Tutorial
the google drive guidelines are displayed in this screenshot
the google drive guidelines are displayed in this screenshot

Formula: `=TRAPEZOID(point1_latitude, point1_longitude, point2_latitude, point2_longitude, [unit])`

Understanding Function Parameters

The TRAPEZOID function requires three sets of coordinates - latitude and longitude for two points and an optional fourth parameter to specify the unit of measurement (km or mi). If left blank, it defaults to miles.

The Essential Google Drive Keyboard Shortcuts You Need to Know
The Essential Google Drive Keyboard Shortcuts You Need to Know
the 13 google sheets shortcuts list is shown in green and has white writing on it
the 13 google sheets shortcuts list is shown in green and has white writing on it
the google sheets has hidden gems you're not using on your webpages
the google sheets has hidden gems you're not using on your webpages

For example, use this formula to find the driving distance between London (51.5074° N, 0.1278° W) and Paris (48.8566° N, 2.3522° E) in kilometers:

=TRAPEZOID(51.5074, -0.1278, 48.8566, 2.3522, "km")

Limitations and Workarounds

While TRAPEZOID is a powerful function, it calculates the driving distance in a straight line, which is not always the most efficient route. To find realistic driving distances, we'll need to create a custom function using Google Apps Script.

the google sheet shortcut keys are shown in different colors and sizes, including blue, yellow
the google sheet shortcut keys are shown in different colors and sizes, including blue, yellow
10 Essential Google Sheets Formulas Everyone Should Know
10 Essential Google Sheets Formulas Everyone Should Know
the google drive reference guide is shown in this screenshote screen, which shows how to
the google drive reference guide is shown in this screenshote screen, which shows how to
Snap a Receipt & Let AI Do the Rest — No Tech Skills Needed!
Snap a Receipt & Let AI Do the Rest — No Tech Skills Needed!
6 Crazy Google Sheets Formulas You Should Know
6 Crazy Google Sheets Formulas You Should Know
Delivery Gig Worker Mileage Tracker Expense Digital Log Tax Ready Daily Mileage Log Tracker Sheet Google Sheets
Delivery Gig Worker Mileage Tracker Expense Digital Log Tax Ready Daily Mileage Log Tracker Sheet Google Sheets

Calculating Realistic Driving Distance with Google Apps Script

Google Apps Script allows you to extend the functionality of Google Sheets by creating custom functions. To calculate realistic driving distances, we'll use the Distance Matrix API from Google Maps Platform, which considers actual road networks and traffic conditions.

Before proceeding, make sure you have an active Google Maps Platform project and have enabled the Distance Matrix API. You'll also need an API key with billing enabled.

Creating the Custom Function

1. Click on Extensions > Apps Script.

10 Google Drive Activities Teachers Can Do Using Chrome Apps (Educational Technology and Mobile Learning)
10 Google Drive Activities Teachers Can Do Using Chrome Apps (Educational Technology and Mobile Learning)

2. Delete any existing code in the 'Code.gs' file and paste the following:

function DRIVING_DISTANCE(origins, destinations, mode, units) {
  var key = '**YOUR_API_KEY**'; // replace with your API key
  var url = 'https://maps.googleapis.com/maps/api/distancematrix/json?origins=' + origins + '&destinations=' + destinations + '&mode=' + mode + '&units=' + units + '&key=' + key;

  var response = UrlFetchApp.fetch(url).getContentText();
  var data = JSON.parse(response);

  return data.rows[0].elements[0].distance.value / 1000; // returns distance in km or mi
}

3. Save the project.

Using the Custom Function

Now you can use the custom function `DRIVING_DISTANCE` in your Google Sheet. It takes four parameters:

  • origins: A string of comma-separated latitude-longitude coordinates.
  • destinations: A string of comma-separated latitude-longitude coordinates.
  • mode: The travel mode, either 'driving', 'walking', 'transit', or 'bicycling'.
  • units: The preferred units for the distance, either 'metric' or 'imperial'.

For example, use this formula to find the driving distance between multiple points:

=DRIVING_DISTANCE("51.5074,-0.1278","48.8566,2.3522,40.7128,-74.0060", "driving", "km")

This will calculate the driving distance from London to Paris and then to New York.

Google Sheets' ability to calculate driving distances unlocks numerous possibilities, from logistics planning to travel optimization. With the custom `DRIVING_DISTANCE` function, you can now consider road networks and traffic conditions for more accurate route planning.

Eager to explore more about data analysis and automation? Check out our upcoming guides on using Google Apps Script for more advanced tasks. Happy planning!