The Scenario
You're a route planning manager and you've inherited an optimization model that somebody else built. It needs a 10×25 driving distance matrix — 10 depot addresses against 25 delivery zone centroids — to compute the optimal assignment. The model is ready. The matrix is not.
250 origin–destination pairs. The person who was supposed to build this left last month.
The bad version:
- Open Google Maps, enter the first depot address as origin, the first delivery zone as destination, and read the driving distance and duration from the route summary
- Paste both values into the right cell in the matrix table in Sheet3
- Repeat for all 250 pairs — and when you mistype one address at pair 87, realize the entire column is off
The optimization model is waiting on this table. Every hour it's missing is an hour the routing decision is getting made on gut feel instead of data.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that runs inside your Google Sheet. It reads your depot list, your delivery zone list, calls the Google Maps Route Matrix API for all 250 combinations, and writes a results table into a new sheet — in one prompt.
Compute the driving distance and estimated travel time between every combination of the 10 origins in Sheet1 and 25 destinations in Sheet2 using Google Maps Route Matrix and write a results table into Sheet3
What You Get
- Sheet3 is created with a row for each of the 250 origin–destination pairs
- Columns include: origin address, destination address, driving distance (km), estimated duration (minutes)
- Pairs where routing fails (addresses unresolvable, no driving route) are flagged in a status column rather than silently omitted
What If the Data Is Not Quite Ready
The depot addresses in Sheet1 are postal codes, not full street addresses — the model needs consistent formatting
Before building the matrix, geocode each postal code in the Depot column of Sheet1 using Google Maps to get the canonical formatted address and use those formatted addresses as the origins in the Route Matrix computation. Write the matrix to Sheet3 with the formatted origin addresses.
Some delivery zone centroids have missing coordinates — need to handle them gracefully
Compute the driving distance and duration matrix between the origins in Sheet1 and the destinations in Sheet2 using Google Maps. For any destination row where address is blank, write "Missing destination" in the corresponding cells of Sheet3 rather than returning an error.
The model needs distance in miles and time in hours, not km and minutes
Build the origin–destination driving matrix using Google Maps Route Matrix with origins from Sheet1 column A and destinations from Sheet2 column A. Write distance in miles (converted from km) and travel time in hours (converted from minutes) into Sheet3 — columns: Origin, Destination, Distance (mi), Duration (hr).
Build the matrix, flag routes over 2 hours, and add a cost estimate using a per-km rate from Sheet4
Compute the full driving distance matrix for all origin–destination pairs using Google Maps, write results to Sheet3, flag any route where duration exceeds 120 minutes in a Status column, and calculate estimated transport cost using the rate in cell B2 of Sheet4 (cost per km) multiplied by distance.
One prompt — routing, flagging, and cost estimation happen together.
Try It
Get the 7-day free trial of SheetXAI and open any workbook where you need origin–destination routing data. For spatial data you already have, see how to geocode addresses to coordinates and run nearby place searches from those coordinates.