The Scenario
A logistics analyst at a regional delivery company has pulled 500 completed order rows into Sheet1 — scheduled times in column D, actual completion times in column E, driver name in column F. It is Friday afternoon. The weekly performance review goes to the ops director at 4 PM. The expectation is a summary dashboard showing on-time rate, average delay in minutes, and daily order counts — broken down by driver — not 500 raw rows.
You inherited this sheet three months ago from someone who left. There was no formula documentation. The "dashboard" tab has headers but no data. You have built similar summaries before using pivot tables, but the driver name field has inconsistent formatting — some rows have last name first, some have first name first — so the pivot groups are wrong.
The bad version:
- You build a COUNTIFS formula to calculate on-time orders per driver — it takes 20 minutes because the timestamp format is text, not a date value, so you have to add a helper column that parses it first.
- You start on the average delay calculation and realize "delay" needs to be defined: is it minutes late only, or do early completions count as negative delay? You send a Slack message to the director. She responds three hours later.
- The driver name inconsistency means the pivot has 14 driver entries instead of 8. You standardize the column by hand, row by row.
- It is 4:40 PM. The review started without the dashboard.
The analysis itself is not complicated. The setup ate the afternoon.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads the raw completion data in Sheet1 and can build the summary dashboard in Sheet2 directly — no formula setup, no pivot configuration, no helper columns.
Open the SheetXAI sidebar and ask:
Using the order data in Sheet1 — scheduled time in column D, actual time in column E, driver name in column F — calculate on-time percentage, average delay in minutes, and total orders per day for last week, then write a summary table to Sheet2.
What You Get
- Sheet2 gets a structured summary table with one row per driver.
- Columns: driver name, total deliveries, on-time count, on-time percentage, average minutes late.
- A second section shows daily totals across the entire fleet — total orders, on-time count, and on-time rate per day.
- Driver names are normalized before grouping — variants of the same name are consolidated into one row.
- Early completions are treated as on-time by default; the prompt can specify a different rule.
What If the Data Is Not Quite Ready
The timestamp fields are stored as text strings, not date values
The times in columns D and E are stored as text in the format "HH:MM AM/PM" — parse them before calculating delay, then build the summary dashboard in Sheet2 showing on-time rate and average delay by driver.
The summary needs to break down by both driver and day, not just one dimension
Analyze the completions in Sheet1 and produce a 'Performance' tab with two sections: one showing per-driver totals for the week, and one showing daily totals for the fleet — include on-time percentage and average delay in minutes for both.
Some rows have no actual completion time because the order was not fulfilled
Build the Sheet2 summary using the completions data in Sheet1 — exclude rows where column E is blank, count them separately as unfulfilled, and include an unfulfilled count column in the driver summary.
The director needs the full pipeline: pull data from OptimoRoute, calculate performance, and flag the three worst-performing drivers
Pull all OptimoRoute completion records from last week into Sheet1 with scheduled time in column D and actual time in column E, calculate on-time percentage and average delay by driver, write the summary to Sheet2, and add a column in Sheet2 marking the three drivers with the highest average delay as needing review.
One prompt goes from raw API data to a flagged summary. The director gets the table she asked for, not a spreadsheet that still needs work.
Try It
Get the 7-day free trial of SheetXAI and open the sheet where your weekly completion data lives, then ask it to build the driver performance summary you would normally spend an afternoon building. Also see how to export raw completion data from OptimoRoute, and how to search orders by status to dig into specific failures.