Задача
У нас была сеть музыкальных школ, в ней 45 филиалов и 362 менеджера по продажам. Не то чтобы они плохо продавали, но если уж начал строить систему отчётности, то нужно идти до конца.
Менеджеры каждый вечер готовили сводки по количеству лидов, продаж, продлений, получившимся конверсиям и т. п. Они скидывали эти сводки в чат филиала, а управляющий филиала далее их как-то проверял, пинал отлынивающих, сам сводил в отчёт по филиалу и передавал информацию управляющей компании. Данные сходились не всегда, да и приходили с отставанием.
Заказчик захотел видеть общую картину по продажам по всей сети и в каждом филиале отдельно в виде сводок и PnL (расходы и доходы). Отчёты должны строиться в реальном времени и без ручного труда с присущими ему ошибками.
Выбор решения
У заказчика уже использовалась CRM, она хорошо подходит для работы с клиентами-учениками и учёта занятий, но не для построения отчётности по продажам.
Можно разработать полностью свой сервис, но это дорого. Готовых, чтобы можно было всё настроить под требования, навскидку не нашли.
Решили делать на Google Таблицах. Вернее так: я умел пользоваться молотком, поэтому увидел сходство между задачей заказчика и гвоздём. Он сам этим молотком баловался, поэтому сходство одобрил.
Ниже расскажу о некоторых особенностях построения многопользовательской отчётной системы на базе Google Таблиц. И когда подобная задача возникнет у вас, вы будете в курсе, что это решаемо и что можно не спешить заказывать интеграцию какой-нибудь ERP.
Единый формат таблиц
Это база. Если каждый менеджер будет городить структуру таблиц по своему усмотрению, то сводить их воедино — тот же ручной труд, только ещё и с рисками стабильно получать некорректные данные, если какой-нибудь пользователь вдруг добавит лишнюю колонку.
Поэтому есть файл-шаблон, который управляющий филиала должен скопировать, переименовать, заполнить в нём ФИО менеджера по продажам и дату начала работы, дать доступ этому менеджеру и добавить ссылку в таблицу филиала. Всё это пошагово расписано в инструкции.
Уже от самого менеджера требуется ежедневно вводить фактические показатели в специально отведённые ячейки. Даже даты проставляются автоматически, чтобы у него понедельник не начинался в субботу.
Ограничения на редактирование
Менеджеров много, могут косячить, ломать формулы. Одних обучил, потом другие пришли.
Пусть редактируют только те ячейки, в которые должен вносить данные. Уже в шаблоне таблицы заданы защищённые диапазоны, поэтому после предоставления менеджеру доступа к таблице он может редактировать только их. Правда, приходится возиться с предоставлением разрешений самим управляющим, но при желании можно это автоматизировать через Apps Script (см. далее).
И да, у каждого менеджера строго своя таблица, а не какая-то общая, где все одновременно что-то делают. Одна таблица — один ответственный.
Из важных нюансов: если ARRAYFORMULA() сама создаёт нужное количество дат построчно, то нужно заранее предусмотреть запас строк на листе. Потому что если их не хватит, а у пользователя доступ только к ограниченным диапазонам, то Google Таблицы сами не добавят недостающих строк, будут ругаться.
Подсветка незаполненных строк
Сразу видно, что менеджер что-то недозаполнил. Тут не буду сильно останавливаться, делается через условное форматирование.
Импорт в таблицу филиала
Используем =IMPORTRANGE(). Именно для использования этой возможности в таблице филиала есть отдельный лист со ссылками на все дочерние таблицы менеджеров. Добавили ссылку, предоставили доступ — теперь таблица может спокойно импортировать нужные данные.
Импортируются данные из таблиц менеджеров в один большой лист в таблице управляющего, из которого потом можно строить какие угодно сводки. Сам этот лист можно и скрыть.
Интересная задачка — импортировать нужно из заранее неизвестного количества таблиц диапазон с заранее неизвестным количеством строк. Можно, конечно, предусмотреть с запасом для каждого менеджера много строк, и само количество менеджеров предусмотреть большое, но тогда мы импортируем много пустых строк, да и формулы вставлять неудобно так, большой риск ошибиться.
На помощь приходит хитроватая формула с лямбда-функцией:
=LET( links; UNIQUE(FILTER('Список сотрудников'!A2:A; 'Список сотрудников'!A2:A<>"")); REDUCE( ; links; LAMBDA(acc; u; LET( blk; IFERROR(QUERY(IMPORTRANGE(u; "Показатели по дням!A3:AN"); "select * where Col1 is not null"; 0); ); IF(OR(ISBLANK(blk); ROWS(blk)=0); acc; IF(ISBLANK(acc); blk; VSTACK(acc; blk)) ) ) ) ) )
Она берёт все ссылки на таблицы менеджеров с первого листа, импортирует данные из этих таблиц и собирает в одну большую таблицу.
Импорт в таблицу управляющей компании
Таблица управляющей компании — самая главная, она одна, в неё стекаются данные со всей сети школ.
Принцип импорта тот же — через IMPORTRANGE() по списку ссылок на таблицы управляющих. То есть, при добавлении филиала ссылку на него нужно добавить в таблицу управляющей компании.
Здесь возможны два варианта:
Можно импортировать непосредственно из всех таблиц менеджеров (ссылки на них подтянутся автоматически из таблиц управляющих). Но тогда нужно и на каждую таблицу менеджера открывать доступ. Если забыть, то данные в отчёте будут неполными.
Можно импортировать из таблиц филиалов уже собранные данные из таблиц менеджеров. Из преимуществ — меньше импортов, чуть надёжнее работает.
В какой-то момент я сталкивался с тем, что функция IMPORTRANGE() не слишком расторопна, особенно если таких импортов много в таблице. Может просто отлынивать, мол, "Внутренняя ошибка при импорте диапазона". Помогает просто удалить формулу из ячейки, а потом вернуть.
Построение PnL с хитрым форматированием
К PnL было предъявлено интересное требование: для каждого месяца должно выводиться по три строки — План, Факт, Процент выполнения. Причём процент выполнения должен раскрашиваться.
Такое условное форматирование можно сделать, задав вручную правила для диапазона, но придётся выделять отдельно каждую третью строку. Тем более, непонятно заранее, сколько понадобится строк. И если какой-то оттенок надо будет чуть поменять, то это ведь всё заново, вручную.
Тут на сцену выходит Apps Script.
Apps Script позволяет писать код на JavaScript-подобном языке, обновляя данные в таблицах и делая с ними почти что угодно. Иногда удобнее использовать его, чем громоздить многострочные формулы в ячейках, оберегая их от случайного редактирования пользователями. Скрипт можно запускать по расписанию, чтобы он перестраивал PnL н�� всех предоставленных в его распоряжение таблицах, учинял там какое угодно форматирование в нужных ячейках.
Теперь нюансы:
Время его выполнения ограничено 6 минутами.
И лучше бы в коде предусмотреть ограничение времени выполнения скрипта, чтобы корректно его завершить, а не в середине перестройки какого-нибудь листа. Но даже это может не помочь.Он может упасть потому что вдруг не смог получить доступ к документу.
Это удивительно, ведь он работает на серверах Google и обращается к Google Таблицам, но так иногда происходит.Трудно настроить, чтобы обновление запускалось по мере изменения документа.
Триггер нужно настраивать в каждом конкретном файле, нельзя централизованно (я не нашёл способа). Поэтому и был выбран вариант с обновлением по расписанию.Некоторые простые действия занимают у скрипта неожиданно много времени.
Например, получение даты последнего изменения документа занимает 1 секунду. В итоге приходится придумывать какие-то костыли, чтобы при каждом запуске скрипт продолжал работу с того документа, на котором остановился. Иначе до последнего документа он так никогда и не доберётся в силу нюанса №1.
Имеет смысл применять комбинированный подход, когда скрипт занимается только обновлением формул и правил условного форматирования в ячейках/новых строках, но не подгружает новые данные из других таблиц — для этого всё же лучше IMPORTRANGE().
Режим фильтрации
Если таблицу использует больше одного человека, то обычный фильтр не подходит, так как он применяется сразу для всех пользователей.
Спасает "режим фильтрации". Если лист строится скриптом с нуля, то режим фильтрации также можно добавлять через код.
Массовое обновление таблиц
Предположим, в шаблоне таблицы менеджера/филиала что-то не учли или решили доработать формулу. Пробегаться по сотням таблиц, созданных на основе этого шаблона, и вносить изменения — задача для терпеливых. Для этого отлично подходит Apps Script и концепция миграций.
Пишется отдельная функция-миграция: в какой ячейке какую формулу нужно установить. Далее запускается прогон этой функции по всем таблицам, которые добавлены в виде ссылок в таблицу управляющей компании + таблица шаблон.
Заключение
Надеюсь, вы почерпнули пару новых идей об использовании Google Таблиц и их взаимодействии между собой, а уж конкретный код и формулы всегда поможет дописать сами-знаете-кто. Или я.
Если какие-то из идей остались непонятны — пишите в комментариях, с радостью раскрою мысль.
