Route Optimization with Google Apps Script and Google Maps

Route optimization with Google Apps Script can be a game-changer whether you’re managing a delivery service, planning a road trip, or coordinating field service operations. Finding the most efficient route between multiple stops can be challenging, but with Google Apps Script, Google Maps API, and Google Sheets, you can build a powerful route optimization tool—without the need for expensive routing software.

Video Tutorial

If you’d prefer written instructions with additional tips, just keep reading.

What is Route Optimization?

Route optimization is the process of determining the most efficient path between multiple destinations while considering various constraints. Unlike simple navigation between two points, route optimization solves the complex “Traveling Salesman Problem” – finding the shortest possible route that visits each location exactly once and returns to the origin.

Why Simple A-to-B Navigation Isn’t Enough

Consider delivering packages to 10 different locations. There are over 3.6 million possible sequences to visit these stops! While a human might make educated guesses, mathematical optimization can find truly efficient routes that:

• Minimize total distance traveled
• Reduce fuel consumption
• Save valuable time
• Decrease vehicle wear and tear
• Improve service delivery timing

Who Can Benefit from This Tool?

• Delivery Services: Companies can streamline delivery routes, ensuring timely deliveries and fuel efficiency.
• Sales Representatives: Sales teams can plan their client visits more effectively, covering more ground in less time.
• Travel Enthusiasts: Individuals planning multi-stop road trips can determine the most efficient routes.
• Field Service Technicians: Technicians can optimize their daily schedules, reducing travel time between appointments.

Why Use Google Apps Script with Google Sheets?

Google Apps Script is a powerful scripting language that allows you to extend the functionality of Google Workspace applications. By integrating it with Google Sheets and Google Maps, you can create a dynamic tool that:

• Automates Route Calculations: Eliminates manual entry and reduces errors.
• Provides Real-Time Updates: Reflects changes in routes or destinations instantly.
• Enhances Collaboration: Share and collaborate on route plans with team members seamlessly.

How Google Sheets, Apps Script, and Google Maps Work Together for Efficient Route Optimization

For beginners, here’s how these three components connect:

1. Google Sheets: Acts as a simple interface where users input locations (origin, destination, and waypoints) and view optimized results.
2. Google Apps Script: Automates the process by fetching location data from the sheet, calling Google Maps Directions API via its built-in Maps.newDirectionFinder(), and updating the sheet with optimized routes.
3. Google Maps Directions API: Processes the request, optimizes the waypoint order, calculates distance and duration, and returns results to Apps Script.

Since Apps Script provides direct integration with Google Maps services, we don’t need to handle API keys or complex requests manually. This makes route optimization easy and efficient within Google Sheets.

you may also interested in: Automate Google Sheets to get Google Map Travel Time and Distance

Step-by-Step Guide to Route Optimization with Google Apps Script and Google Maps

Optimizing routes can save time, fuel, and effort, making travel more efficient. This guide walks you through how to use Google Sheets, Google Apps Script, and Google Maps to automate and optimize multi-destination routes seamlessly.

Setting Up Your Google Sheets for Route Optimization

Let’s begin by setting up the Google Sheets workbook. This method is designed to take three inputs from the users: the origin, destination, and waypoints. The following images illustrate the interface of the Google Sheets workbook.

You can create a copy of the Google Sheets (which includes the Apps Script project discussed here) using the link provided below.

Link to Google Sheets with Apps Script Code

This Google Sheets includes the following three input fields.

• Origin (starting point)
• Destination (end point)
• Waypoints (intermediate stops)

The following outputs

• Optimized waypoint order
• Total distance
• Total duration
• Route details with a step-by-step breakdown
• Google Maps link for navigation

Google Apps Script Code for Route Optimization

The Google Apps Script code for route optimization is provided below. If you’ve started by copying the Google Sheet mentioned above to your Google Drive, the code is already included.

To access it, go to Extensions > Apps Script to open the Script Editor.

If you’ve copied the above Google Sheets workbook, you’ll see that the script is already present in the Script Editor.

However, if you’re starting with a new Google Sheets workbook, copy the code below and replace the default code in the Script Editor with it. Save the project with a meaningful name.

Code Snippets

Preparing input Data

I have set up the project to take latitude and longitude values as input in the format “latitude,longitude.” You can easily obtain these values from Google Maps.

To get the latitude and longitude of the origin, destination, and waypoints:

1. Open Google Maps.
2. Search for your location and zoom in.
3. Right-click on the location. The latitude and longitude values will appear at the top of the right-click menu.
4. Click on the latitude and longitude values to copy them to the clipboard.
5. Paste these values into the relevant cells of the Google Sheets workbook.

Waypoints Limit

In the above attached Google Sheets workbook, the waypoints are added in row 7. In the code (line 13), I have considered 8 waypoints (up to Column I) by providing the range B7:I7. You can add up to 23 waypoints by changing this input range. However, these API calls are subject to usage limits.

Run the route optimizer

This code adds a custom menu to the Google Sheets UI, allowing you to run the script without opening the Google Apps Script editor.

To use the custom menu, go to Routing Tools > Optimize Route.

When you click this menu for the first time, you will see a message saying “Authorization Required.” You need to grant permission for the script to run with your Google Account. To do this:

1. Click OK.
2. Select the Google Account you are using.
3. Click Advanced.
4. Click the link with your Apps Script project name at the bottom of the pop-up window.
5. Click Allow on the next screen.

After completing these steps, the script will populate the output area with relevant values. If your input data is incorrect or no routes are available between one or more waypoints, the script will display meaningful error messages. Correct the input and re-run the script as explained above.

Understanding Your Route Optimization Results

After running the route optimization script in your Google Sheet, you’ll see several outputs including the optimized waypoint order, total distance, duration, route details, and a Google Maps link. These results help you understand and implement your optimized route effectively.

The “Waypoint Order” field shows you the optimal sequence for visiting your stops as a series of numbers (e.g., “2, 0, 1, 3”). These numbers correspond to the order of your waypoints as entered in row 7. The Waypoint Order field returns values which are zero-based. So, I have numbered the waypoint input fields in row 6, starting from “0,” for easy reference.

When you click the Google Maps link generated in B14, Google Maps opens in a new tab with your complete route pre-loaded in the optimized order. This makes it easy to start navigation or share the route with your team, and it works seamlessly on both desktop and mobile devices.

Read More About Waypoint Optimization

In this project, we set the setOptimizeWaypoints argument to true to obtain the optimized waypoint order from the Google Maps Directions API. This optimization is based on the Traveling Salesperson Problem. You can learn more about waypoint optimization in the Google Maps documentation.

Wrapping Up

By leveraging route optimization with Google Apps Script, you can create a cost-effective tool that boosts efficiency and saves time. Combining Google Apps Script with Google Maps API and Google Sheets streamlines route planning for deliveries, road trips, or field services—all without expensive software. With automated calculations and real-time updates, your team can focus on execution rather than manual planning.