Как автоматически выдавать сертификаты об окончании онлайн-курса или участии в конференции

… при помощи всего 2-х плагинов к Google Sheets.

Долгое время, пока мы проводили 1–2 курса в месяц, мы делали свидетельства об окончании полностью вручную. Казалось, это не занимает много времени, проблема была в другом — делать эту работу было лениво (на фоне других задач) и часто возникали ситуации, что прошла неделя после курса, а свидетельства не выданы и выпускники пишут и трясут про них.

Когда потоков стало 3–4 в месяц, я задумался об автоматизации. Писать полностью свой скрипт я смысла не вижу, покупать какие-то сервисы на таком объёме — тоже.

Плагин-генератор документов AutoCrat

Мне попался на глаза замечательный плагин AutoCrat, который работает с Google Sheets.

Экран настройки связывания переменных макета и таблицы с данными:

Он позволяет из каждой строки таблицы исходных данных автоматически создать документ формата Google Doc или Google Slide, при необходимости сконвертировать его в PDF и послать на произвольную почту.

Поскольку мы для каждого потока создавали список участников в Google Sheets, плагин нам вполне подошёл.

Я переделал макет свидетельства из Google Doc в Google Slide, поизучал визард настройки AutoCrat и настроил его для генерации свидетельств и писем по списку участников курса.

Пример вёрстки макета в Google Slide:

Плагин позволил сократить время на подготовку свидетельств с получаса до 10 минут.

У плагина нашлось ещё одно полезное применение, когда мы провели онлайн-конференцию на 200 участников — я смог сгенерировать свидетельства опять же всего за 10 минут.

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

Видеоинструкция по AutoCrat:
https://www.youtube.com/watch?v=QzPN2Ea5A1o

Но для выпускников курсов осталась одна закавыка — чтобы запустить скрипт, надо было подготовить исходные данные в Google Sheets, на что всё равно нужно найти время:

указать склонение имени участника — мужское или женское, потому что мы используем слово «прошёл / прошла» (см. макет выше)
указать дату выдачи свидетельства (в списке это делается очень быстро)
указать номер свидетельства (тоже быстро).

Кроме того, мы стараемся выдавать свидетельства не всем подряд, а только тем, кто заполнил анкету обратной связи. И это приводит к задержкам, когда 4 человека заполнили анкету и ждут остальных, ведь удобно генерить все свидетельства по потоку за один заход.

Так что спустя пару месяцев я начал думать про дальнейшую автоматизацию — как сделать так, чтобы свидетельство выдавалось совсем автоматически и без нашего участия.

Плагин-генератор строк CopyDown

Так нашёлся второй плагин, который умеет формировать значения переменных для новых строк таблицы, опираясь на формулу — CopyDown.

Я встроил этот плагин уже не в таблицу участников потока, а в таблицу обратной связи, которая заполняется выпускниками, когда они заполняют Google Form’у обратной связи:

Для вычисления значений я использую такие формулы:

Дата =СЕГОДНЯ()
Номер свидетельства =СЦЕПИТЬ(“SIO “; JOIN(“-”; ГОД(СЕГОДНЯ()); МЕСЯЦ(СЕГОДНЯ()); СТРОКА()))
Окончание склонения =ЕСЛИ(И(ЕОШИБКА(ПОИСК(“а “; D2)); ЕОШИБКА(ПОИСК(“и “; D2)); ЕОШИБКА(ПОИСК(“ь “; D2)); ЕОШИБКА(ПОИСК(“я “; D2))); “ёл”; “ла”) — формула определяет пол участника по последней букве имени (у женщин обычно стоит “а”,”и”,”я” или “ь”). Формула очень простая и примитивная, но в 95% случаев её хватает. Если формула ошибается — мы правим и переделываем вручную одно свидетельство, исходники которого уже сгенерированы плагином :)

где SIO — это префикс-код курса, а D2 — поле с именем выпускника.

Имя, Фамилию и почту указывают сами выпускники при заполнении анкеты.

Теперь выпускник может заполнить форму обратной связи в любое время дня и ночи и в течение пары минут ему на почту придёт свеженькое свидетельство. PROFIT! :)

Если нужна помощь по настройке этих плагинов — стучитесь в телеграм @beskov, помогу.

Обучение+4