🐑 Отчёты по шаблону

Копировать таблицу Google скриптом.

Как по шаблону таблицы Google сделать серию индивидуальных отчётов менеджерам. Как при помощи скриптов Google 💾копировать шаблон, 👀считывать информацию с листа, 📮писать письма на почту, ✏️менять данные на листе, 🚪открывать доступ по почте.

👀 Лучше один раз увидеть...

#1. Копия таблицы Google по шаблону. Как автоматизировать работу

← Это видео поможет вам разобраться в коде.

🔗 Ссылка на пример

Перейдя по этой ссылке, вы получите свою копию таблицы-шаблона.

Код скрипта здесь, или здесь.

💰 Почему это полезно

Вы получите шаблон, который по ключу меняет своё содержимое. Внутри шаблона лежит скрипт.

Скрипт умеет:

  1. 💾 копировать шаблон,

  2. 🔑 менять в шаблоне ключ: имя менеджера в ячейке,

  3. 👩‍💼👨‍💼 открыть права на редактирование менеджеру, которому предназначена копия,

  4. ✉️ уведомить менеджера по почте о получении нового файла,

  5. ↩️ пункты 1-4 сделать несколько раз для каждого менеджера.

В этой статье подробно опишем, как этим пользоваться, и как адаптировать решение под себя.

🎯 Решение

Копируйте себе шаблон для ведения денежных операций.

Вкладка \Отчёт/ выглядит так:

↑ Отчёт формируется автоматически.

Выберите менеджера в ячейке B1, и формулы перестроят отчёт.

О том, как формулами создавать подобные отчёты, мы поговорим в следующей статье. В этой статье обсудим работу скрипта.

Скрипт — это около 100 строк кода, которые позволят вам из одного шаблона создавать несколько документов:

Скрип забирает данные о менеджерах с листа.

Чтобы решить задачу о создании отчётов, необходимо:

  1. 👪Определить место, где будет хранится список менеджеров. Имя листа, диапазон с именами менеджеров и их почтой.

  2. 🗝️Решить, где в шаблоне будет меняться его ключ. В нашем случае ключ — это имя менеджера из списка.

  3. 📊 По ключу, имени менеджера в ячейке, при помощи формул настроить отчёт для этого менеджера.

  4. 📋 Решить, как скрипт будет забирать эти данные, чтобы скрипт смог выполнить свою задачу

  5. 🚀 Запустить скрипт

👪 Данные в шаблоне

Данные в шаблоне организованы на разных вкладках-листах:

Первый лист для заполнения — Менеджеры

Менеджеры — используйте этот лист для определения списка менеджеров. Почта менеджеров используется для создания индивидуальных отчётов, чтобы делиться файлами с менеджерами.

↑ Обратите внимание:

  1. В примере везде указана моя почта. У вас будет в каждой строке почта менеджера, кому придет письмо.

  2. Принципиально нет разницы, что использовать как ключ отчёта. Вы можете оставить только почту, и по ней формировать отчет. В моём примере это имя менеджера, для наглядности.

Список менеджеров — ключевая для бизнеса информация. Это справочник. Еще одним справочником является лист Категории

Категории — используйте этот лист для определения категорий расходов, затрат и доходов. Категории используются для выпадающего списка на листе Операции.

↑ Лист Категории, нужен только для этого отчёта, из примера. Он необходим для правильного ввода информации. В вашем файле может быть несколько таких справочных листов, или ни одного. 👉 Рекомендую создавать такие листы с перечнями для всех вещей, которые имеют конечное число значений: валюты, люди, статьи расходов, платёжные инструменты. Это поможет вам наладить правильный учёт. На лист Категории опирается колонка "Категория" на листе Операции

Операции — на этом листе вводите операции:

Потратили деньги — суммы должны быть отрицательными.

+ Все полученные деньги должны быть положительными.

↑ Лист Операции — это основной рабочий лист в примере. Каждый объект учёта, в нашем случае деньги, будет иметь хотя бы одну такую таблицу. Важно: менеджеры и категории выбираются из выпадающего списка. Список этот формируется из листов Менеджеры и Категории. Такой подход помогает избежать ошибок при вводе ключевой информации.

На основании листа с операциями формируется отчёт ↓

🗝️Ключ в шаблоне

Отчёт — это сводка по одному менеджеру. Формулы в отчёте настроены на изменение имени менеджера в ячейке B1:

Ячейка с именем менеджера является ключевой для этого шаблона. Отчёт формируется в зависимости от ключа. В нашем случае это менеджеры, вы можете в качестве ключа использовать любые значения.

У вас может быть свой ключ, и свой шаблон. Настройте ключ, и место куда он вставляется по своему усмотрению. Далее в скрипте вы сможете поменять это в настройках ↓

📋 Настройка скрипта

Если вы еще не скопировали мой файл с примером, вот еще раз ссылка:

https://docs.google.com/spreadsheets/d/1aRfj-7Ytl9dTCN_nRK7WfxJNgbrLDCCr_enhtLAzQ2M/copy

В этот раз я не вставлял скрипт внутрь шаблона, через меню Файл > Редактор скриптов. Вместо этого я создал новый отдельный скрипт.

Предлагаю для теста работы скрипта сделать то же:

https://script.new

↑ Перейдя по ссылке выше, вы создадите себе новый скрипт. Вставьте в него мой пример кода.

Перед тем, как использовать скрипт, поменяйте настройки:

Меняйте значения в одинарных кавычках ↓

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 функций:

  1. getTeplateSettings_— настройки.

  2. createReportsByTemplate — главная.

  3. createReportByTemplate_ — создание одного отчёта по шаблону.

Каждая из функций находится на своём месте и выполняет свою роль в коде:

  • первая функция кода содержит все необходимые настройки

  • вторая — запускает механизм по кругу методом перебора.

  • третья — сам выполняющий код.

Такой способ работы с таблицами может быть удобен для быстрого создания отчётов по единому шаблону. Целостность отчётов достигается благодаря одной ячейке с ключом. Значение ключа меняется, и формулы пересчитывают отчёт. Далее срабатывает механизм создания отчёта. В нашем примере это создание копии таблицы. А возможно и другое действие, например, печать документа в формате PDF, отправка его Боту, и прочее.

Кроме этого способа, есть и более эффективные, когда не приходится создавать копии одного файла. Об этом в видео:

↑ Это видео — другой взгляд на проблему создания онлайн-отчётов. Вместо написания кода, я предлагаю воспользоваться студией данных Google.

↑ Это видео — пример мобильного приложения, в котором менеджеры не только смогут увидеть свои данные, но и внести расходы и доходы с мобильного.

Спасибо, что дочитали статью до конца. Надеюсь, теперь вы точно разделяете мое мнение:

Онлайн-таблицы — это круто!

Я потратил на создание статьи более 19 часов. Буду признателен, если вы потратите пару секунд, и поделитесь ссылкой на сайт в соцсетях: https://www.cooltables.online.

Спасибо!

Макс Махров

Разработчик онлайн-таблиц