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

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

Оператор ставит дату отправки раз в день, перед отправкой партии. По этой дате скрипт отбирает строки, и она же служит сигналом к запуску. Сторож читает таблицу через приёмник в режиме «только чтение». Заметив новую дату, он ждёт, пока строки за неё перестанут добавляться: если 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 строки, заполненные вручную, включая все 7 сумм по крупным товарам. На 50 строках файла нашлось одно расхождение: ручное число отличалось от числа в файле партнёра. Его оставили оператору как вопрос.

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

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

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

Переносить деньги наугад нельзя. Колонку выбрали явно и сначала показали результат в режиме просмотра. Когда оператор подтвердил, что партнёр перепутал колонки, в таблицу вписали 92 строки за один день, пропусков 0. Перед записью сохранили список затронутых строк, чтобы правку можно было откатить. Три строки остались нетронутыми: там стояли числа, вписанные вручную, и приёмник по своему правилу их не трогает.

Чтобы такой случай не повторился незаметно, я добавил узкое правило. Если привычная колонка пуста по всему файлу, а соседняя заполнена, берётся соседняя, и бот сообщает об этом в чат. Если в привычной есть хоть одно число, всё работает как раньше.

Тогда же приёмник входящих файлов научился забирать их из личной переписки: человек присылает файл туда, где разговаривает. Отчёт в тему группы уходит, только когда что-то вписано. Повторный запуск те же файлы не трогает: обработанные сообщения запоминаются по номеру. Приёмник проверяет чат каждые 2 минуты с 7 до 22 по Москве. Одна проверка — это один запрос к Telegram, таблицы она не касается.

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

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

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

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

Это поймала проверка перед записью, и в таблицу ошибка не попала. Случай закреплён тестом. Если счёт не равный, таблица остаётся нетронутой, а в тему группы уходит сообщение с трек-номерами и составом комплекта. Разложить их вручную — дело одной минуты.

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

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

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

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

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