Обмен файлами с партнёром по отгрузке: файл дня туда, стоимость и треки обратно

Файл дня собирается по дате, которую ставит оператор

Оператор ставит дату отправки один раз в день, перед отправкой партии, и эта дата стала и фильтром, и пусковым сигналом. Сторож читает книгу через приёмник в режиме «только чтение», замечает новую дату и присылает файл в тему группы через 10 минут после того, как строки за эту дату перестали прибавляться. Пауза защищает партнёра от неполного файла: без неё выгрузка ушла бы в момент, когда оператор ещё проставляет даты. Если заказы приходят позже, уходит вторая версия файла, до трёх на одну дату. Фиксированные часы отправки отменены по просьбе оператора: они присылали файл до того, как партия собрана.

Первая сетка была раз в 20 минут, и вместе с паузой и следующим запуском ожидание готового файла доходило примерно до 50 минут. Сетка доведена до 5 минут, ожидание сократилось примерно до 15, а для случая «сели только отправить таблицу» в чате появилась команда, которая собирает файл сразу. Нагрузку на чтения измерили заранее: один прогон читает четыре колонки книги постранично, это два чтения, и пятиминутная сетка в рабочем окне даёт около 380 чтений в сутки при большом запасе до квот.

Раскраска книги: правила читаются и считаются на нашей стороне

Владелец попросил, чтобы в файле «не терялось» ничего из ручной выгрузки: в книге цветные товары, красный «нет трека», оранжевый «отправлено». Цвета книги держатся на условном форматировании: правило смотрит на текст в колонке товара и красит ячейку, сама ячейка при этом остаётся белой. Чтение цвета ячейки поэтому бесполезно, читать нужно сами правила. Приёмник уже умел отдавать их служебным действием, так что раскраска воспроизведена без единой правки в Apps Script и без выкатки, которая здесь стоила бы дорого.

Правила, зависящие от самой строки, считаются построчно: совпадение по тексту, пусто или заполнено, больше или меньше числа. Первое совпадение выигрывает, как в самой таблице. Правила на COUNTIF, которые подсвечивают дубли номера и трека, пропущены осознанно: они считают по всей колонке книги, в файле дня три десятка строк, и «дублей нет» там было бы ложью: партнёр увидел бы чистую таблицу там, где в книге горит предупреждение. Число пропущенных правил печатается вслух, потому что молчаливое частичное покрытие хуже отсутствующего.

Прогон на живом файле: покрашено 148 ячеек, пропущено 2 правила. Сверено со снимком экрана книги по каждому цвету: совпало. Вычисление правил закрыто пятью тестами: порядок правил, пустые и заполненные ячейки, числовые пороги в формате книги «$13,02», пропуск COUNTIF и правило, которое красит только текст и уступает следующему правилу с заливкой.

Подсветка гаснет в момент ввода

Оператор сформулировал главное требование к подсветке: она обязана исчезать, когда партнёр заносит данные. Заливкой этого не добиться: красная ячейка «нет трека» осталась бы красной после того, как трек вписан, и файл начал бы врать ровно в момент, когда им пользуются. Правила «пусто или заполнено» и числовые пороги перенесены внутрь xlsx настоящим условным форматированием: ISBLANK на треке, дате трека и отметке «отправлено», пороги на стоимости. Excel пересчитывает их сам. Правила по тексту, например «возврат» и «замена», остались заливкой: от ввода партнёра они не зависят и должны быть видны всегда.

По просьбе оператора убраны цвета товаров: важны они в большой книге, в файле дня они лишние. Добавлена сетка, которую раньше рисовали руками перед отправкой, колонки доведены до «Примечаний», адрес приезжает многострочным и по центру, как в книге. Ширина колонок считалась по шапке, поэтому заголовок «Дата получения трек номера» на 26 знаков растягивал колонку под десятизначное значение. Теперь ширина берётся по данным, а шапка переносится: колонка даты трека 28 → 9, стоимости 28–31 → 9, суммарная ширина таблицы 412 → 217 знаков, почти вдвое. Ещё три колонки, которые склад никогда не заполнял, спрятаны без удаления: видимая ширина дошла до 191, данные остаются в файле и раскрываются одним движением.

Стоимость доставки: точный ключ и формула как признак свободной ячейки

Партнёр возвращает ту же таблицу, которую ему выгрузили, и это снимает сопоставление по названиям товара, на котором раньше случались ошибки в одну букву. Ключ точный: номер заказа с суффиксом строки, например 12345, 12345.2, 12345.3. Из файла партнёр удаляет около 10% строк, заказы с других складов, поэтому строка ищется по номеру во всей книге; окно строк однажды промахнулось бы, и промах выглядел бы как «такого заказа в книге нет».

Из шести денежных колонок партнёр заполняет ровно одну, и она никогда не пуста: в ней либо число, которое оператор вписал руками, либо исходная формула деления. Привычный предохранитель «пишем только в пустую ячейку» здесь не защитил бы ничего. Признаком свободной ячейки сделан текст исходной формулы, и проверяет его приёмник в момент записи. Оператор правит книгу руками прямо сейчас, и проверка по выгрузке минутной давности означала бы затереть работу оператора.

В файле нашлась «тонкость с большими палатками», о которой оператор предупреждал: строка палатки вбирает стоимость своих строк-аксессуаров, уехавших отдельными посылками, при этом аксессуарные строки сохраняют стоимость и у себя. Складываются только части одного товара; разные товары одного заказа стоят каждый за себя. Правило выведено из ручной работы оператора и подтверждено им дословно уже после вывода. Нули не пишутся: у партнёра $0 означает «в этой посылке стоимости нет», и такие строки склад оставляет с формулой. Строка, которой нет в книге, не создаётся; если номер совпал при другом товаре, строка пропускается и показывается; строка с треком и без стоимости называется вслух как вопрос партнёру.

Проверка на ручной работе оператора: алгоритм воспроизвёл 34 из 34 заполненных вручную строк дословно, включая все 7 сумм по крупным товарам. Расхождение на 50 строках файла одно: ручное число отличалось от числа в файле партнёра, и оно оставлено оператору как вопрос. Первые шесть дней модуль работал в режиме витрины на отдельной странице книги. После двух подтверждений «верно» режим закрыт, и запись пошла в рабочие строки: 13 и 43 строки на живых файлах, пропусков 0. Витрина сохранена рядом с записью как журнал правок: видно, что именно вписано.

Деньги в другой колонке

Через день после включения записи оператор сообщил, что стоимости из очередного файла в книге нет. Разбор показал: в этом файле партнёр заполнил другую денежную колонку, привычная пустовала по всему файлу. Модуль читал только привычную и честно докладывал «трек есть, стоимости нет». Деньги по догадке не переносятся: колонка выбрана явно, собрана витрина, и только после подтверждения оператора, что партнёр перепутал колонки, вписано 92 строки за один день, пропусков 0. Перед записью сохранён список затронутых строк, чтобы правку можно было откатить. Три строки остались нетронутыми: там стояли числа, вписанные рукой, и приёмник по своему правилу их обходит.

Чтобы случай не повторился молча, добавлен автоподхват с узкой границей: если привычная колонка пуста по всему файлу при заполненной соседней, берётся соседняя, и об этом говорится вслух. Если в привычной есть хоть одно число, поведение прежнее. Тогда же приёмник входящих научился забирать файлы из личной переписки, потому что человек присылает файл туда, где разговаривает. В тему группы уходит отчёт только когда что-то вписано, повторный прогон те же файлы не трогает, память по идентификатору сообщения. Приёмник опрашивает чат каждые 2 минуты в окне 7–22 МСК; опрос стоит один вызов Telegram и таблиц не касается.

Треки комплекта раскладываются по строкам листа

Комплект в магазине одна позиция: «палатка + печь + пол». В книге по просьбе оператора это три строки. Треки группировались по названию позиции, приёмник находил по нему только строку палатки, второй и третий трек отбрасывались. Строки печи и пола оставались пустыми, выглядели неотправленными и уходили партнёру на повторную отгрузку.

Порядок раскладки взят по строкам листа. Раскладка по частям названия положила бы трек печи на пол: в названии позиции печь идёт раньше пола, на листе же порядок другой, палатки первыми, остальное по порядку имён, который задал оператор. Гипотеза проверена на независимом заказе, который оператор заполнил руками и который при проектировании виден не был: предложение бота совпало с ручным заполнением по всем трём трекам до цифры.

Правило оператора: сколько треков, столько товаров уехало, товары никогда не едут вместе под одним треком. Поэтому раскладка делается только при равном счёте: треков ровно столько же, сколько строк. Живой контрпример показал цену догадки: у заказа с двумя треками на три строки оператор поставил второй трек на печь, порядок листа отдал бы его полу, и один трек лёг бы на два товара. Поймано проверкой состояния до записи, в книгу не ушло, контрпример закреплён тестом. При неравном счёте книга остаётся нетронутой, в тему группы уходит сообщение с номерами треков и составом комплекта, чтобы ручная раскладка заняла минуту.

Сравнение старой и новой раскладки на 970 живых заказах: затронуто ровно 2 заказа, изменений при том же ключе 0. В контуре треков 110 зелёных тестов, четыре из них новые, включая эталонный порядок и запрет выдумывать.

Второй признак: заказ отгружен целиком, а строки без трека есть

Цена пропущенного дефекта равна повторной отправке товара клиенту и повторной оплате доставки. До этого его ловило только внимание оператора, который «не знаю каким чутьём» присмотрелся к двум заказам. Отдельный модуль ищет такие строки списком и в книгу ничего не пишет.

Признаков два. Узкий признак «трек потерялся по дороге» пропустил бы комплект с одним треком на три строки: трек в книге стоит, ничего не потеряно, и две строки всё равно выглядят неотправленными. Второй признак независим от первого: заказ в магазине отгружен целиком, и в книге у него остались строки с пустым треком. Покрытие проверено, чтобы признак не оказался мёртвым: 726 из 924 активных заказов проходят как «отгружен целиком». Прогон за 60 дней по 924 активным заказам дал одну находку: тот самый заказ с потерянным треком и двумя строками комплекта.