Автоматизируем процесс закупки товаров для сети кафе через Google таблицы

Кейс для отдела снабжения. Автоматизируем прием заявок на закупку товаров с нескольких кафе и распределяем их по поставщикам.

Предистория
Появилась задача быстро собрать инструмент для сбора и обработки заявок на закуп от кафе и как обычно, чтобы бесплатно было.

Теряем много времени на сбор и обработку заявок с кафе. Заявки на закупку товара от кафе поступают в разном формате: на листочке, в viber, по телефону и т.п. на обработку подобных заявок уходит много времени.

Действующие лица

Используемые таблицы

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

Лист «Товары» _ Справочник
На этом листе будет список всех утвержденных отделом снабжения товаров, доступных для покупки. С этого листа через скрипт, мы будем обновлять список товаров в таблицы: Заявка кафе 1 и Таблица закупщика.

Лист «Скрипт» _ Справочник
На этом листе мы проверяем, как отработал скрипт по обновлению списка товаров, чтобы во всех таблицах таких как: «Заявка кафе 1» и «Таблица закупщика», у нас было одинаковое кол-во строк(товаров) = кол-ву строк(товаров) на листе «Товары».

Примечание:
Обновление списка товаров через importrange, не сделал потому, что отдел Снабжения постоянно работает со списком товаров и динамическое изменение тут не подходит.

В этой таблице сотрудники кафе: Администратор, Су шеф, Ст. Бармен заполняют заявку на закупку товаров

Что бы например Администратор включая фильтр видел только те товары которые ему нужны, мы отмечаем товары метками «А» Админ, «К» Кухня, «Б» Бар. Столбцы нужны чтобы отмечать в каком именно кафе эти товары доступны, например: в кафе1 товар «Апероль» доступен, а в кафе4 он не нужен, напоминаю, что весь этот диапазон мы копируем с таблицы «Справочник» во все таблицы «Заявка кафе 1» и т.п.

Каждый день сотрудник кафе в период с 14:00 до 1:00 заполняет заявку на закупку продуктов на листе «Текущая». Администратор заполняет заявку в столбце AL, Бармен в AM, Ст. Повар в AN. Мы разделили сотрудников на 3 направления «Сервис зона», «Бармены» и «Кухня», у каждого направления есть корпоративная Gmail почта, что бы Администратор случайно не заполнил заявку Бармена, мы защищаем диапазоны и открываем доступ к столбцам по этим gmail почтам.

В столбцах AL, AM, AN работает условное форматирование, сотрудник сначала указывает дату заполнения заявки в ячейках AL1 или AM1 или AN1. В столбце AT указаны дни недели в которые можно заказывать товар. Если сегодня «Ср»(среда) и в столбце AT есть «Ср», значит этот товар заказывать можно, если нельзя, ячейка товара будет закрашена красным.
Это сделано через условное форматирование, формулы и скрипты можно посмотреть скопировав таблицу Заявка кафе 1. Ниже формула по которой работает условное форматирование:
=НЕ(regexmatch(AT3:A; двссыл(«Справочни! $D3»)))

Каждый день в 5:00 утра срабатывает скрипт и копирует диапазон R1:BC с товарами и заявкой на следующий лист и обновляет диапазон заказа(AL, AM, AN) на листе «Текущая». Скрипт копирует данные с листа Текущая => ЗакВчера => ЗакПозВч => 4День => 5День => 6День. Это свое рода краткая история движения товара.
ВАЖНО! С листа «ЗакВчера»_Заявка кафе 1, Таблица закупщика забирает данные о заявке кафе, через функцию importrange.

Ссылка на таблицу Закупщика.
В этой таблице у нас собираются все заявки на закупку товара из таблиц «Заявка кафе 1», «Заявка кафе 2» и тд.

Лист «ЗаказТек»_Таблица закупщика
На этом листе у нас собираются все заявки на закупку товара из таблиц «Заявка кафе 1» с лист «ЗакВчера».

Каждый день, сотрудник отдела снабжения после завершения работы, запускает скрипт «Завершить день», который копирует данные с листа на лист создавая «резервную копию» и обновляя лист «ЗаказТек»: ЗаказТек=>ЗакВчера=>ЗакПозВч

Лист «База»_Таблица закупщика
На этом листе находятся все наши ссылки на таблицы (переменные)

Примечание:
Лист: ЗаявПостСводВсеРест нужен для формирования заявки Закупщику.
Листы: ЗаявРестВся, ЗаявРестПост нужны для отправки заявки Поставщику.

На это все, надеюсь эта схема была вам чем то полезна)
Спасибо за внимание ^_^. Если у вас есть интересные кейсы в которых можно принять участие или вопросы по этому, пишите мне в facebook или vk.

Кому интересно вот еще пару моих статей.

Лучший телеграмм канал о Google таблицах с кучей классных кейсов t.me/google_sheets

Классный телеграмм чат про Google таблицыt.me/google_spreadsheets_chat