Calculate Driving Distance with Google Sheets

Gregory Jul 09, 2026

Ever found yourself planning a road trip or delivery route and wished for a quick, accurate way to calculate driving distances? Google Sheets, with its extensive suite of built-in functions, offers just the tool you need. By harnessing the power of Google Maps Distance Matrix API, you can calculate driving distances directly within your Google Sheets, saving you time and effort.

A NEW Google Drive Cheat Sheet
A NEW Google Drive Cheat Sheet

Before we dive into the details, ensure you have enabled the Google Maps API for your project and have an API key. This key will allow your Google Sheets to communicate with the Google Maps Distance Matrix API. Once set up, you're ready to calculate driving distances with ease.

The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros

Setting Up the Google Maps Distance Matrix API

To use the Google Maps Distance Matrix API in your Google Sheets, you'll first need to enable it and obtain an API key. Follow these steps:

The Essential Google Drive Keyboard Shortcuts You Need to Know
The Essential Google Drive Keyboard Shortcuts You Need to Know

1. Go to the Google Cloud Console (console.cloud.google.com).

2. Create a new project or select an existing one.

the google drive guidelines are displayed in this screenshot
the google drive guidelines are displayed in this screenshot

3. Enable the Distance Matrix API for your project.

4. Go to "APIs & Services" > "Credentials" and create a new API key.

Obtaining an API Key

Google Sheets: Student Practice Template - Teacher Tech with Alice Keeler
Google Sheets: Student Practice Template - Teacher Tech with Alice Keeler

To generate an API key, follow these steps:

1. In the Google Cloud Console, go to "APIs & Services" > "Credentials".

2. Click on "Create Credentials" and select "API key".

Sherri Miller - The Techie Chick
Sherri Miller - The Techie Chick

3. Copy the generated API key. You'll need this to authenticate your Google Sheets.

Using the API Key in Google Sheets

Debt Management What's Your Real $/Hour After Gas & Miles? | Google Sheets 📈
Debt Management What's Your Real $/Hour After Gas & Miles? | Google Sheets 📈
Google Sheets: Add Page Breaks - Teacher Tech with Alice Keeler
Google Sheets: Add Page Breaks - Teacher Tech with Alice Keeler
6 Crazy Google Sheets Formulas You Should Know
6 Crazy Google Sheets Formulas You Should Know
How To Create Checklists with Google Sheets
How To Create Checklists with Google Sheets
Comment bien utiliser Google Docs ?
Comment bien utiliser Google Docs ?
Google Sheets Checklists
Google Sheets Checklists
23 Best formulas for Google Sheets (+ Examples)
23 Best formulas for Google Sheets (+ Examples)
Make Your Own Word Search in Google Sheets - Teacher Tech with Alice Keeler
Make Your Own Word Search in Google Sheets - Teacher Tech with Alice Keeler
Google Sheets: Construct Formulas with the INDIRECT Function - Teacher Tech with Alice Keeler
Google Sheets: Construct Formulas with the INDIRECT Function - Teacher Tech with Alice Keeler
How to Calculate Running Total in Google Sheets
How to Calculate Running Total in Google Sheets
Snap a Receipt & Let AI Do the Rest — No Tech Skills Needed!
Snap a Receipt & Let AI Do the Rest — No Tech Skills Needed!
10 Important Google Sheets Tips for Teachers (Educational Technology and Mobile Learning)
10 Important Google Sheets Tips for Teachers (Educational Technology and Mobile Learning)
Google Sheets
Google Sheets
Easily Add Google Maps to Your Google Sheets Automatically and for Free
Easily Add Google Maps to Your Google Sheets Automatically and for Free
How to Auto Sort in Google Sheets
How to Auto Sort in Google Sheets
Create a Google Sheets Second Brain
Create a Google Sheets Second Brain
the ultimate guide to google's search engine info for every vehicle in the world
the ultimate guide to google's search engine info for every vehicle in the world
Google Sheets Tutorial: How to Open a Spreadsheet
Google Sheets Tutorial: How to Open a Spreadsheet

Once you have your API key, you can use it in your Google Sheets to authenticate your requests to the Google Maps Distance Matrix API. Here's how:

1. In a new or existing Google Sheets, click on "Extensions" > "Apps Script".

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

function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index');
}

3. Click on the "+" icon at the bottom of the toolbar and select "File" > "HTML file". Name it "Index.html".

4. In the "Index.html" file, add the following code, replacing 'YOUR_API_KEY' with your actual API key:

html:
<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
  </head>
  <body>
    <input type="text" id="origin" placeholder="Origin (e.g., New York)">
    <input type="text" id="destination" placeholder="Destination (e.g., Los Angeles)">
    <button onclick="calculateDistance()">Calculate Distance</button>
    <p id="result"></p>
    <script>
      function calculateDistance() {
        var origin = document.getElementById('origin').value;
        var destination = document.getElementById('destination').value;
        google.script.run
          .withSuccessHandler(function(response) {
            document.getElementById('result').innerHTML = 'Distance: ' + response.rows[0].elements[0].distance.text;
          })
          .calculateDrivingDistance(origin, destination, 'YOUR_API_KEY');
      }
    </script>
  </body>
</html>

Calculating Driving Distances in Google Sheets

Now that you have set up the Google Maps Distance Matrix API and obtained an API key, you can calculate driving distances directly in your Google Sheets. Here's how:

1. In your Google Sheets, enter the origin and destination addresses in two separate cells (e.g., A1 and B1).

2. In another cell (e.g., C1), enter the following formula, replacing 'YOUR_API_KEY' with your actual API key:

=INDEX(SPLIT(calculateDrivingDistance(A1, B1, "YOUR_API_KEY"), "~"))

3. Press Enter. The driving distance between the origin and destination will be displayed in the cell.

Formula Explanation

The formula used to calculate the driving distance consists of several parts:

1. `calculateDrivingDistance(A1, B1, "YOUR_API_KEY")`: This custom function calls the Google Maps Distance Matrix API with the origin, destination, and API key as arguments.

2. `SPLIT(..., "~")`: This function splits the response from the API into an array, separating the distance text from the other data.

3. `INDEX(...)`: This function returns the first element of the array, which is the distance text.

Calculating Multiple Distances

To calculate driving distances for multiple origins and destinations, you can use the `MAP` function in combination with the `calculateDrivingDistance` function. Here's an example:

1. In your Google Sheets, enter the origins and destinations in two separate columns (e.g., A2:A5 and B2:B5).

2. In another column (e.g., C2), enter the following formula, replacing 'YOUR_API_KEY' with your actual API key:

=MAP(A2:A5, B2:B5, LAMBDA(origin, destination, calculateDrivingDistance(origin, destination, "YOUR_API_KEY")))

3. Press Enter. The driving distances between the origins and destinations will be displayed in the column.

With these steps, you can now easily calculate driving distances directly within your Google Sheets using the Google Maps Distance Matrix API. This powerful tool can save you time and effort in planning road trips, delivery routes, or any other project that requires calculating driving distances.