The Scenario
You run daily route cost estimates for a regional distribution operation. Every morning, a Google Sheet lands in your inbox — one depot address in B1, 25 customer delivery stops down column A, and two empty columns waiting for driving distance and travel time. The previous analyst built the numbers by hand, one address pair at a time, using GraphHopper's API playground. That person left six weeks ago. Now it falls to you.
The bad version:
- Open GraphHopper's Matrix API documentation, copy the request format, and manually assemble a JSON body with all 25 origin-destination pairs.
- Paste each result row out of the nested response array into the correct cell, making sure row 8 in the response maps to row 9 in the sheet (the header offset you keep forgetting).
- Repeat tomorrow, and the day after, because the sheet is regenerated fresh each morning with new addresses.
You're a fleet analyst. This is supposed to be the setup step before the actual analysis, not the job itself.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads the sheet structure, sees the depot in B1 and the addresses in column A, and through its GraphHopper integration it calls the Matrix API and writes results back into your sheet. No JSON, no API playground, no manual extraction.
Build a driving distance and duration matrix from the depot address in B1 to each customer address in column A and fill driving distance in km into column B and travel time in minutes into column C
What You Get
- Column B fills with driving distance in km for each customer row — matched to the correct address on that row.
- Column C fills with travel time in minutes for the same pairs.
- Rows with unparseable addresses are flagged inline rather than silently returning zeros.
- The depot row (if it appears in column A) is skipped automatically.
What If the Data Is Not Quite Ready
The addresses are in different formats — some have postcodes, some don't
Normalize the addresses in column A so they all include a full street address and postcode, then build a driving distance and duration matrix from the depot in B1 to each normalized address and write results to columns B and C
Some rows have blank or placeholder values in column A
Skip any row in column A where the cell is blank or contains "TBD", then compute driving distance and travel time from the depot in B1 to each valid address and write results to columns B and C — leave blank rows blank
The depot address is in a separate sheet called "Config" cell B2
Pull the depot address from the Config sheet cell B2, then compute a driving distance and time matrix from that depot to every address in column A of this sheet and write results into columns B and C
Do the cleanup, compute the matrix, and flag outliers in one pass
Normalize any malformed addresses in column A, compute driving distance in km and travel time in minutes from the depot in B1 to each cleaned address, write results into columns B and C, and flag any customer where the driving time exceeds 90 minutes by writing "LONG HAUL" in column D
The pattern: address normalization and matrix computation don't have to be separate steps. Ask for both in the same prompt.
Try It
Get the 7-day free trial of SheetXAI and open a Google Sheet with your depot address and a list of delivery stops — then ask it to build the distance and time matrix in one shot. You might also find these useful: Optimize a Delivery Route From Stops in a Google Sheet and the GraphHopper overview.