File exchange with a shipping partner: the file goes out by itself, and costs and tracking numbers come back into the sheet

The day's file builds itself from the date the operator enters

The operator enters the shipping date once a day, just before sending the batch. That date became both the filter for the rows and the signal to start. The watcher reads the sheet through the receiver in read-only mode. When it sees a new date, it waits until rows for that date stop being added and, 10 minutes later, posts the file to the group topic.

The pause protects the partner from an incomplete file: without it the file would go out while the operator was still entering dates. If orders turn up later, a new version of the file follows, up to three per date. Sending on a fixed schedule was dropped at the operator's request, because the file used to arrive before the batch was ready.

At first the watcher ran every 20 minutes. With the pause and the wait for the next run, the finished file arrived after about 50 minutes. The interval was cut to 5 minutes, and the wait dropped to about 15. For the "we need to send the table right now" moment there is a chat command that builds the file on the spot. The load on the sheet was measured in advance. One run reads four columns in parts, which is two read requests. Running every five minutes during working hours comes to about 380 requests a day, well within the limits.

The colours: the script reads the sheet's own rules and applies them

The owner asked that the file "lose nothing" compared with the manual export. The sheet has colour-coded products, red for "no tracking number" and orange for "shipped". These colours come from conditional formatting: a rule looks at the text in the product column and colours the cell, while the cell itself stays white. So reading a cell's colour tells you nothing; you have to read the rules themselves.

The receiver could already return those rules with a separate command. So the colouring was reproduced without a single change to the Apps Script and without releasing a new version of it, which would have been costly here.

Rules that depend on the row itself are checked row by row: a text match, empty or filled, above or below a number. The first matching rule wins, just as in the sheet. I skipped the COUNTIF rules on purpose. They highlight repeated order and tracking numbers and count across the whole column of the sheet, while the day's file has about thirty rows. There, "no repeats" would be untrue: the partner would see a clean table while the main sheet shows a warning. The script says plainly how many rules it skipped, so that partial colouring isn't taken for complete.

A test on a live file: 148 cells coloured, 2 rules skipped. I compared the result with a screenshot of the sheet colour by colour, and everything matched. The rule logic is covered by five tests: rule order, empty and filled cells, number thresholds in the sheet's "$13,02" format, skipping COUNTIF, and a rule that only colours the text and lets the next rule add a fill.

The highlighting goes away as soon as the partner enters the data

The operator's main requirement for the highlighting was that it should disappear once the partner fills in a cell. A plain fill can't do that. A red "no tracking number" cell would stay red after the number was entered, and the file would mislead people at the very moment they were using it.

So the "empty or filled" rules and the number thresholds went into the xlsx as real conditional formatting: ISBLANK on the tracking number, the tracking date and the "shipped" mark, and thresholds on the cost. Excel recalculates them by itself. Text rules, such as "return" and "replacement", stayed as fills: they don't depend on the partner's data and must always be visible.

At the operator's request the product colours were removed: they matter in the big sheet and only clutter the day's file. The file got the borders that used to be drawn by hand before sending. The columns now run up to "Notes", and the address arrives on several lines and centred, as in the sheet. Column widths used to follow the header, so the 26-character heading "Tracking number received on" stretched a column meant for ten-character values. Now the width follows the data and the header wraps. The tracking-date column shrank from 28 to 9 characters, the cost columns from 28–31 to 9, and the whole table from 412 to 217, almost half. Three more columns the warehouse never fills in are hidden without being deleted. The visible width came down to 191, and the data stays in the file, one click away.

Shipping cost: an exact key, and the original formula marks a free cell

The partner returns the same table that was sent to them. So there's no need to match rows by product name, which is where one-letter mistakes used to happen. The key is exact: the order number with the row suffix, for example 12345, 12345.2, 12345.3. The partner deletes about 10% of the rows, because those orders ship from other warehouses. So a row is looked up by number across the whole sheet. A search within a fixed range would miss one day, and the miss would look like "this order isn't in the sheet".

Of the six money columns the partner fills in only one, and it's never empty. It holds either a number the operator typed in by hand or the original division formula. So the usual safeguard, "write only into an empty cell", would protect nothing here. A cell counts as free while it still holds the text of the original formula. The receiver checks this at the moment of writing, because the operator can edit the sheet at any minute, and a check against a minute-old export could overwrite their work.

The file had the "big tent quirk" the operator had warned about. A tent's row includes the cost of its accessories, which ship as separate parcels, and the accessory rows keep their own cost as well. Only parts of the same product are added up; different products in one order are counted separately. I worked the rule out from the operator's manual work, and the operator confirmed it word for word.

Zeros are never written: for the partner, $0 means "no cost in this parcel", and the warehouse leaves those rows with the formula. The script never creates a row that isn't in the sheet. If the number matches and the product differs, the row is skipped and shown to the operator. A row with a tracking number and no cost is listed as a question for the partner.

A check against the operator's manual work: the algorithm reproduced 34 of 34 hand-filled rows exactly, including all 7 sums for large items. Across the file's 50 rows there was one difference: the number typed by hand differed from the one in the partner's file, and it went to the operator as a question. For the first six days the module ran in preview mode: it showed its result on a separate sheet and wrote nothing to the working rows. After the operator confirmed "correct" twice, writing was switched on: 13 and 43 rows on live files, with 0 skipped. The preview sheet stayed as a change log that shows exactly what was entered.

The money in a different column

A day after writing was switched on, the operator reported that the costs from the latest file were missing from the sheet. It turned out that in this file the partner had filled in a different money column, and the usual one was empty throughout. The module read only the usual column and reported, correctly, "tracking number present, cost missing".

Money is never moved on a guess. The column was chosen explicitly, and the result was first shown in preview mode. Once the operator confirmed that the partner had mixed up the columns, 92 rows were written in one day, with 0 skipped. The list of affected rows was saved before writing, so the change could be rolled back. Three rows were left as they were: they held numbers typed in by hand, and the receiver doesn't touch those, by its own rule.

So that this can't happen again unnoticed, I added a narrow rule. If the usual column is empty throughout the file and the next one is filled, the next one is used, and the bot says so in the chat. If the usual column has even one number, everything works as before. At the same time the inbox learned to pick up files from direct messages too, since people send a file wherever they're talking. A report goes to the group topic only when something was written. A repeat run doesn't touch files it has already handled, because processed messages are remembered by their ID. The inbox checks the chat every 2 minutes from 07:00 to 22:00 Moscow time. One check is one request to Telegram, and it doesn't touch the sheets.

A bundle's tracking numbers are spread across its rows

In the store a bundle is one line item: "tent + stove + floor". In the sheet, at the operator's request, it is three rows. Tracking numbers used to be grouped by the line item's title. The receiver found only the tent's row by that title and dropped the second and third numbers. The stove and floor rows stayed empty, looked unshipped and went back to the partner to be shipped again.

Now the order follows the rows on the sheet. Spreading the numbers 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 the order is different, with tents first and the rest in the order the operator chose. I tested this on another order the operator had filled in by hand, one that wasn't used during development. The bot's suggestion matched the manual entry on all three tracking numbers, digit for digit.

The operator has a rule: as many tracking numbers as products shipped, and products never travel together under one number. So the numbers are spread only when the counts match, with exactly as many numbers as rows. A live example showed why guessing is dangerous. On an order with two tracking numbers for three rows, the operator had put the second number on the stove. The sheet order would have given it to the floor, and one number would have ended up on two products. The check before writing caught this, so the error never reached the sheet, and a test now covers the case. If the counts differ, the sheet stays untouched, and the group topic gets a message with the tracking numbers and the contents of the bundle. Sorting them out by hand takes a minute.

A comparison of the old and new methods on 970 live orders: exactly 2 orders were affected, and the rest had 0 changes. This part has 110 passing tests, four of them new, including the reference order and the ban on guessing.

A second sign: the order shipped in full, and rows without a tracking number remain

A missed mistake here costs a second shipment to the customer and paying for delivery twice. Until now only the operator's attention caught it: in the operator's words, it was "some kind of instinct" that made them look closer at two orders. Now a separate module finds such rows and lists them without writing anything to the sheet.

There are two signs. 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 sheet and nothing is lost, and yet two rows look unshipped. The second sign is independent of the first: the order is shipped in full in the store, and in the sheet it still has rows with no tracking number. First I checked that the sign actually fires: 726 of 924 active orders count as "shipped in full". A run over 60 days and 924 active orders found one case: that very order with a lost tracking number and two bundle rows.