The Scenario
You are a customer service manager. It is a Tuesday morning and a regional sales director forwarded you a complaint thread: three customers reporting deliveries that never arrived last week. Before you can reach out to arrange redeliveries, you need to find all the failed orders from the past two weeks — not just those three, but all of them. There could be others.
OptimoRoute has the data. You need it in a sheet so you can triage, assign follow-up owners, and track redelivery status. The ops team is already using OptimoRoute for this week's routes and cannot stop to run a custom export for you.
The bad version:
- You log into OptimoRoute and navigate to the completion search screen, filter by "failed" status, and set the date range. OptimoRoute returns 34 matching records.
- The export gives you a CSV with all completion fields — you need 6 of them. You open it in Sheets, delete the columns you do not need, and paste it into your tracking workbook.
- You realize the "failed" filter does not include "attempted" status — orders where the driver showed up but could not complete delivery. You go back, run a second export with "attempted" status, and merge the two CSVs.
- Twenty minutes in, you have a working list. The sales director is waiting for an update you promised in 10 minutes.
The data was there. The extraction process was not designed for the speed this situation required.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It can query OptimoRoute for orders matching specific criteria — by status, by date range, by any filter the API supports — and write the matching records directly into the sheet.
Open the SheetXAI sidebar and ask:
Search OptimoRoute for all orders with a failed completion status between the dates in cells A1 and A2, and write each order's number, address, driver, and completion notes into this sheet.
What You Get
- Every order matching the status and date range criteria is written into the sheet — one row per order.
- Columns: order number, address, assigned driver, completion status, completion notes.
- If OptimoRoute distinguishes between "failed" and "attempted," the query can include both — just ask.
- Rows are written starting from row 2, leaving row 1 for headers.
- The count of matching records is written into a summary cell so you know immediately how many need follow-up.
What If the Data Is Not Quite Ready
The date range needs to come from cells, not be hardcoded
Search OptimoRoute for all orders with failed or attempted completion status between the dates in cells B1 and B2, and write each matching order's number, address, driver, and notes into the 'Failed Orders' sheet.
The results need to be highlighted by severity — failed vs. attempted
Pull all OptimoRoute orders with failed or attempted status from the past two weeks and write them into this sheet — highlight rows where the status is "failed" in red and "attempted" in yellow, so I can see which cases are the most critical.
You need to find unassigned orders alongside failed ones
Search OptimoRoute for all orders from the date range in cells A1 and A2 and write them into the 'Order Search' sheet — then highlight any row where the status is "failed," "attempted," or "unassigned."
You need the full triage pipeline: find failed orders, assign follow-up owners from a second tab, and flag priority cases
Search OptimoRoute for all failed and attempted orders from the past two weeks and write them into the 'Failed Orders' tab, then match each order's address to the territory assignments in the 'Territories' tab and write the responsible follow-up owner into column F — flag any order with no territory match in column G.
The match and the extraction run together. You have a triage list with owners assigned before the next message arrives.
Try It
Get the 7-day free trial of SheetXAI and open the tracking sheet where you manage redelivery follow-up, then ask it to pull all failed OptimoRoute orders for your target date range and write the results in. Also see how to export completion data for broader KPI analysis, and how to look up scheduling details for specific order numbers.