Shipping-partner file exchange: day file out, shipping cost and tracking back

The day file builds itself from a date the operator sets

The operator stamps the send date once a day, right before sending the batch, and that date became both the filter and the trigger. A watcher reads the book through the receiver in read-only mode, spots a new date and drops the file into the group topic 10 minutes after the rows for that date stop growing. The pause protects the partner from an incomplete file: without it the export would go out while the operator is still stamping dates. If orders keep coming in later, a second version follows, up to three per date. Fixed send times were dropped at the operator's request: they sent the file before the batch was assembled.

The first grid was every 20 minutes, and with the pause and the next tick the wait for a finished file ran to roughly 50 minutes. The grid is now 5 minutes, the wait is down to about 15, and for the "we sat down just to send the table" moment there is a chat command that builds the file on the spot. Read load was measured first: one run reads four columns of the book page by page, two reads in total, and the five-minute grid inside the working window comes to about 380 reads a day with plenty of headroom against the quotas.

The book's colouring: rules are read and computed on our side

The owner asked that the file "lose nothing" of what the manual export carried: the book has colour-coded products, red for "no tracking number", orange for "shipped". The book's colours live in conditional formatting: a rule looks at the text in the product column and paints the cell, while the cell itself stays white. Reading a cell's colour is therefore useless; the rules themselves are what to read. The receiver could already return them through a service action, so the colouring was reproduced with zero edits to the Apps Script and no deployment, which would have been costly here.

Rules that depend on the row itself are computed row by row: a text match, blank or filled, above or below a number. The first match wins, exactly as in the spreadsheet. COUNTIF rules, which highlight duplicate order and tracking numbers, are skipped on purpose: they count across the whole column of the book, a day file holds about thirty rows, and "no duplicates" there would be a lie: the partner would see a clean table where the book shows a warning. The number of skipped rules is printed out loud, because silent partial coverage is worse than none.

A run on a live file: 148 cells coloured, 2 rules skipped. Checked colour by colour against a screenshot of the book: a match. The rule engine is pinned by five tests: rule order, blank and filled cells, numeric thresholds in the book's "$13,02" format, the COUNTIF skip, and a text-only rule yielding to the next rule with a fill.

Highlighting that goes out the moment data is typed in

The operator's key requirement for the highlighting: it has to disappear when the partner enters the data. A plain fill cannot do that: a red "no tracking number" cell would stay red after the number is typed in, and the file would start lying at the exact moment it is being used. The blank-or-filled rules and the numeric thresholds are written into the xlsx as real conditional formatting: ISBLANK on the tracking number, the tracking date and the "shipped" mark, thresholds on cost. Excel recalculates them itself. Text-based rules such as "return" and "replacement" stay as fills: they never depend on the partner's input and must always be visible.

At the operator's request the product colours were dropped: they matter in the big book and clutter the day file. The borders that used to be drawn by hand before sending are in. Columns run through "Notes", and the address arrives multi-line and centred, as in the book. Column widths were driven by the header, so a 26-character heading like "Tracking number received on" stretched a column under ten-character values. Widths now follow the data and the header wraps: the tracking-date column went 28 → 9, cost 28–31 → 9, the table's total width 412 → 217 characters, close to half. Three more columns the warehouse never fills are hidden without being deleted: visible width reached 191, and the data stays in the file one click away.

Shipping cost: an exact key, and a formula as the sign of a free cell

The partner returns the very table that was exported to them, which removes matching by product name, the source of earlier one-letter mistakes. The key is exact: order number plus row suffix, say 12345, 12345.2, 12345.3. The partner deletes about 10% of the rows, orders shipping from other warehouses, so a row is looked up by number across the whole book; a row window would eventually miss, and the miss would look like "no such order in the book".

Of six money columns the partner fills exactly one, and it is never empty: it holds either a number the operator typed by hand or the original division formula. The usual "write only into an empty cell" guard would protect nothing here. The sign of a free cell is the text of the original formula, and the receiver checks it at the moment of writing. The operator edits the book by hand in real time, and a check against a minute-old export would mean overwriting their work.

The file held the "big tents subtlety" the operator had warned about: a tent's row absorbs the cost of its accessory rows, which ship as separate parcels, while the accessory rows keep their cost as well. Only parts of one product are summed; different products in one order stand on their own. The rule was derived from the operator's manual work and confirmed word for word afterwards. Zeros are never written: a $0 from the partner means "no cost in this parcel", and the warehouse leaves such rows with the formula. A row missing from the book is never created; a matching number with a different product is skipped and shown; a row with a tracking number and no cost is called out as a question for the partner.

Checked against the operator's manual work: the algorithm reproduced 34 of 34 hand-filled rows verbatim, including all 7 sums for big items. One divergence across the file's 50 rows: the hand-typed number differed from the partner's, and it went to the operator as a question. For the first six days the module ran in preview mode on a separate page of the book. After two confirmations of "correct", preview mode closed and writes went into the working rows: 13 and 43 rows on live files, 0 skipped. The preview stays alongside the write as a change log: it shows exactly what was entered.

Money in a different column

A day after live writes began, the operator reported that the cost from the latest file was missing from the book. The file turned out to have the money in a different money column, with the usual one empty throughout. The module read only the usual column and reported, truthfully, "tracking present, cost missing". Money is never moved on a guess: the column was selected explicitly, a preview was built, and only after the operator confirmed the partner had mixed up the columns were 92 rows written in one day, 0 skipped. The list of affected rows was saved before writing so the change could be rolled back. Three rows stayed untouched: they held hand-typed numbers, and the receiver walks around those by its own rule.

To keep this from repeating in silence, an auto-pickup with a narrow boundary went in: if the usual column is empty across the whole file while the neighbouring one is filled, the neighbour is taken and it is said out loud. If the usual column has even one number, behaviour is unchanged. The inbox also learned to pick files up from direct messages, because people send a file where they are talking. A report goes to the group topic only when something was written, a repeat run leaves the same files alone, and memory is by message id. The inbox polls the chat every 2 minutes within a 07:00–22:00 MSK window; a poll is one Telegram call and never touches the spreadsheets.

Bundle tracking numbers spread across the sheet's rows

In the store a bundle is one line item: "tent + stove + floor". In the book, at the operator's request, it is three rows. Tracking numbers were grouped by the line item's title, the receiver found only the tent row by it, and the second and third numbers were dropped. The stove and floor rows stayed empty, looked unshipped and went back to the partner for re-shipment.

The layout order now follows the sheet's rows. Laying out by the parts of the title would have put the stove's number on the floor: in the title the stove comes before the floor, while on the sheet tents print first and the rest follows the name order the operator set. The hypothesis was tested on an independent order the operator had filled by hand and that was never seen during design: the bot's proposal matched the manual entry on all three tracking numbers, digit for digit.

The operator's rule: as many tracking numbers as products shipped, and products never travel together under one number. So the layout happens only when the counts are equal: exactly as many numbers as rows. A live counter-example showed the price of guessing: on an order with two numbers for three rows the operator had put the second number on the stove, sheet order would have handed it to the floor, and one number would have landed on two products. Caught by a state check before writing, it never reached the book, and it is pinned by a test. When the counts differ, the book stays untouched and the group topic gets a message with the tracking numbers and the bundle's contents, so the manual layout takes a minute.

An A/B of the old and new layouts on 970 live orders: exactly 2 orders affected, 0 changes under the same key. The tracking lane has 110 green tests, four of them new, including the reference order and the ban on guessing.

A second signal: the order is fulfilled in full, yet rows have no tracking number

The cost of a missed defect equals re-shipping the goods to the customer and paying for delivery twice. Until now it was caught only by the operator's attention: two orders noticed, in the operator's words, "by some instinct". A separate module lists such rows and writes nothing to the book.

There are two signals. The narrow one, "a tracking number got lost on the way", would miss a bundle with one number on three rows: the number is in the book, nothing is lost, yet two rows still look unshipped. The second signal is independent of the first: the order is fulfilled in full in the store, and the book still has rows for it with an empty tracking cell. Coverage was checked so the signal could be trusted: 726 of 924 active orders qualify as "fulfilled in full". A run over 60 days and 924 active orders produced one finding: the very order with a lost number and two bundle rows.