🐑 Отчёты по шаблону
Копировать таблицу Google скриптом.
Как по шаблону таблицы Google сделать серию индивидуальных отчётов менеджерам. Как при помощи скриптов Google 💾копировать шаблон, 👀считывать информацию с листа, 📮писать письма на почту, ✏️менять данные на листе, 🚪открывать доступ по почте.
👀 Лучше один раз увидеть...
#1. Копия таблицы Google по шаблону. Как автоматизировать работу
← Это видео поможет вам разобраться в коде.
🔗 Ссылка на пример
Перейдя по этой ссылке, вы получите свою копию таблицы-шаблона.
💰 Почему это полезно
Вы получите шаблон, который по ключу меняет своё содержимое. Внутри шаблона лежит скрипт.
Скрипт умеет:
💾 копировать шаблон,
🔑 менять в шаблоне ключ: имя менеджера в ячейке,
👩💼👨💼 открыть права на редактирование менеджеру, которому предназначена копия,
✉️ уведомить менеджера по почте о получении нового файла,
↩️ пункты 1-4 сделать несколько раз для каждого менеджера.
В этой статье подробно опишем, как этим пользоваться, и как адаптировать решение под себя.
↑ Отчёт формируется автоматически.
Выберите менеджера в ячейке B1, и формулы перестроят отчёт.
О том, как формулами создавать подобные отчёты, мы поговорим в следующей статье. В этой статье обсудим работу скрипта.
Скрипт — это около 100 строк кода, которые позволят вам из одного шаблона создавать несколько документов:
Скрип забирает данные о менеджерах с листа.
Чтобы решить задачу о создании отчётов, необходимо:
👪Определить место, где будет хранится список менеджеров. Имя листа, диапазон с именами менеджеров и их почтой.
🗝️Решить, где в шаблоне будет меняться его ключ. В нашем случае ключ — это имя менеджера из списка.
📊 По ключу, имени менеджера в ячейке, при помощи формул настроить отчёт для этого менеджера.
📋 Решить, как скрипт будет забирать эти данные, чтобы скрипт смог выполнить свою задачу
🚀 Запустить скрипт
👪 Данные в шаблоне
Данные в шаблоне организованы на разных вкладках-листах:
Первый лист для заполнения — Менеджеры ↓
Менеджеры — используйте этот лист для определения списка менеджеров. Почта менеджеров используется для создания индивидуальных отчётов, чтобы делиться файлами с менеджерами.
↑ Обратите внимание:
В примере везде указана моя почта. У вас будет в каждой строке почта менеджера, кому придет письмо.
Принципиально нет разницы, что использовать как ключ отчёта. Вы можете оставить только почту, и по ней формировать отчет. В моём примере это имя менеджера, для наглядности.
Список менеджеров — ключевая для бизнеса информация. Это справочник. Еще одним справочником является лист Категории ↓
Категории — используйте этот лист для определения категорий расходов, затрат и доходов. Категории используются для выпадающего списка на листе Операции.
↑ Лист Категории, нужен только для этого отчёта, из примера. Он необходим для правильного ввода информации. В вашем файле может быть несколько таких справочных листов, или ни одного. 👉 Рекомендую создавать такие листы с перечнями для всех вещей, которые имеют конечное число значений: валюты, люди, статьи расходов, платёжные инструменты. Это поможет вам наладить правильный учёт. На лист Категории опирается колонка "Категория" на листе Операции ↓
Операции — на этом листе вводите операции:
– Потратили деньги — суммы должны быть отрицательными.
+ Все полученные деньги должны быть положительными.
↑ Лист Операции — это основной рабочий лист в примере. Каждый объект учёта, в нашем случае деньги, будет иметь хотя бы одну такую таблицу. Важно: менеджеры и категории выбираются из выпадающего списка. Список этот формируется из листов Менеджеры и Категории. Такой подход помогает избежать ошибок при вводе ключевой информации.
На основании листа с операциями формируется отчёт ↓
🗝️Ключ в шаблоне
Отчёт — это сводка по одному менеджеру. Формулы в отчёте настроены на изменение имени менеджера в ячейке B1:
Ячейка с именем менеджера является ключевой для этого шаблона. Отчёт формируется в зависимости от ключа. В нашем случае это менеджеры, вы можете в качестве ключа использовать любые значения.
У вас может быть свой ключ, и свой шаблон. Настройте ключ, и место куда он вставляется по своему усмотрению. Далее в скрипте вы сможете поменять это в настройках ↓
📋 Настройка скрипта
Если вы еще не скопировали мой файл с примером, вот еще раз ссылка:
https://docs.google.com/spreadsheets/d/1aRfj-7Ytl9dTCN_nRK7WfxJNgbrLDCCr_enhtLAzQ2M/copy
В этот раз я не вставлял скрипт внутрь шаблона, через меню Файл > Редактор скриптов. Вместо этого я создал новый отдельный скрипт.
Предлагаю для теста работы скрипта сделать то же:
↑ Перейдя по ссылке выше, вы создадите себе новый скрипт. Вставьте в него мой пример кода.
Перед тем, как использовать скрипт, поменяйте настройки:
Меняйте значения в одинарных кавычках ↓
1. id
Строка 12 кода — меняйте на айди своей копии шаблона. Скопируйте айди из ссылки на файл:
↑ Подробнее о том, что такое айди, зачем оно, и как работает смотрите в предыдущей статье.
2. folder
Строка 16 кода — меняйте на айди папки, в которую необходимо поместить копии.
↑ Речь идёт о папке на вашем диске Google:
https://drive.google.com/drive/my-drive
Там нажмите на большой [ + ] чтобы создать новую папку для теста.
3. managers
Строки с 17 по 21 кода — меняйте место в шаблоне, где у вас будут ключи отчёта. Данные о менеджерах:
managers: {
sheet: 'Менеджеры', // Лист где храниться информация о менеджерах
names: 'B7:B', // Диапазон где хранятся имена менеджеров
emails: 'C7:C' // Диапазон, где хранятся адреса электронных почт менеджеров
},
Меняйте sheet — имя листа, где находится список менеджеров, names— адрес диапазона с именами менеджеров, и emails — адрес диапазона с почтами менеджеров. Диапазоны могут быть открытыми: B7:B — не указана "строка по", то есть вы в будущем сможете свободно добавлять новые строки в этот список. Скрипт будет срабатывать.
Третий параметр — emails — можно не указывать, если вы не хотите отправлять уведомления о готовом отчёте на почту. Для этого удалите строку 20 кода или закомментируйте:
managers: {
sheet: 'Менеджеры', // Лист где храниться информация о менеджерах
names: 'B7:B', // Диапазон где хранятся имена менеджеров
// ❌ emails: 'C7:C' // Диапазон, где хранятся адреса электронных почт менеджеров
},
↑ Подробнее о том, что такое комментарии к коду, мы говорим в первой статье в разделе про составные части кода.
4. out
Строки с 22 по 25 кода — меняйте место в отчёте, где будет находиться ячейка с ключом в отчёте:
out: {
sheet: 'Отчёт', // Лист на котором скрипт меняет имя менеджера
range: 'B1' // Адрес ячейки, в которую надо записать имя менеджера
}
Меняйте sheet — имя листа, где в вашем шаблоне будет находится ключ, и range — адрес ячейки с ключом. В нашем примере это ячейка B1 где мы меняем имя менеджера на листе Отчёт ↓
🚀 Запуск скрипта
Запустите и авторизуйте скрипт. Подробнее процесс авторизации смотрите тут. К счастью, это требуется только при первом запуске скрипта.
👉 Совет: копирование файлов на диске — «дорогая» по времени операция. Чтобы создать большое количество отчётов таким способом, запускайте ключи из справочника частями. Иначе скрипт может упереться в лимит по времени, и выдать ошибку. Подробнее о лимитах скриптов Google читайте в справке.
🤓 Объяснение
Весь проект состоит из 3 функций:
getTeplateSettings_— настройки.
createReportsByTemplate — главная.
createReportByTemplate_ — создание одного отчёта по шаблону.
Каждая из функций находится на своём месте и выполняет свою роль в коде:
первая функция кода содержит все необходимые настройки
вторая — запускает механизм по кругу методом перебора.
третья — сам выполняющий код.
Такой способ работы с таблицами может быть удобен для быстрого создания отчётов по единому шаблону. Целостность отчётов достигается благодаря одной ячейке с ключом. Значение ключа меняется, и формулы пересчитывают отчёт. Далее срабатывает механизм создания отчёта. В нашем примере это создание копии таблицы. А возможно и другое действие, например, печать документа в формате PDF, отправка его Боту, и прочее.
Кроме этого способа, есть и более эффективные, когда не приходится создавать копии одного файла. Об этом в видео:
↑ Это видео — другой взгляд на проблему создания онлайн-отчётов. Вместо написания кода, я предлагаю воспользоваться студией данных Google.
↑ Это видео — пример мобильного приложения, в котором менеджеры не только смогут увидеть свои данные, но и внести расходы и доходы с мобильного.
Спасибо, что дочитали статью до конца. Надеюсь, теперь вы точно разделяете мое мнение:
Онлайн-таблицы — это круто!
Я потратил на создание статьи более 19 часов. Буду признателен, если вы потратите пару секунд, и поделитесь ссылкой на сайт в соцсетях: https://www.cooltables.online.
Спасибо!
Макс Махров