Упрощаем работу с Google-формами: Apps Script и выпадающее меню

Я работаю в отделе физической безопасности одной технической компании. Для улучшения рабочих процессов я пользуюсь JavaScript в G Suite. Сам я не люблю монотонную работу, так что для меня писать скрипты, чтобы избежать повторяющихся муторных задач, — как спасательная капсула. А теперь покажу вам примеры.

За прошлый год наши операционные задачи по сотрудникам выросли практически втрое и соответственно увеличился оборот. Поэтому распорядок дежурств постоянно меняется. Почему так важно, чтобы список был обновлен? Потому что он используется в выпадающем меню более, чем 6-ти форм Google.

После создания новых форм ко мне в течение дня или недели ходят сотрудники и просят добавить или удалить чьи-то имена из выпадающего меню в нескольких формах. Это те самые монотонные задачи, которые я не хочу выполнять каждый раз, а делиться доступом к редактированию этих форм просто опасно из-за возможности пользовательских ошибок.

Чтобы решить, как быть в этой ситуации, я взял второй инструмент из своего любимого набора — Apps Script. Я написал скрипт, который берет список имен из главной таблицы, и когда данные в ней меняются, автоматически обновляются выпадающие меню во всех Google формах. Вот так я решил для себя задачу с оптимизацией монотонной работы.

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

А теперь подробно разберем весь процесс.

Первое и самое важное, про что я хочу вам рассказать, — мой любимейший инструмент, который управляет буквально всем, — поиск Google! Я открыл новое окошко и запросил “Обновить выпадающие списки при помощи Google Apps Script”. Ну или что-то подобное. Конечно же я нашел не один ресурс с подсказками.

Почитал не один урок и кое-какие посты со Stack Overflow. Я узнал про новое окно для скриптов приложений в главной таблице. Чтобы в него попасть, нужно пройти по пути Tools -> Script Editor (Инструменты->Редактор скриптов)

Никто еще не добавлял скрипты для моей таблицы, так что я увидел чистый лист.

Переименовал скрипт для обновления списков и добавил функцию onEdit(e). Об этом чуть позже.

Затем я собрал уникальные ID из формы, которую я собирался обновлять. Сделал это так: прошел по ссылке в окошке редактирования форм и скопировал ID.

Я сохранил номера ID в переменную в новой функции под названием updateLists().

Далее мне нужно было сохранить список в массив. Для этого я создал одну страницу в таблице с основным списком и назвал ее DropDownNames. Она нужна мне для функции IMPORTRANGE, чтобы дать доступ к первому и последнему именам в главной таблице. Потом первая и последняя записи совмещаются в третьем столбце при помощи функции CONCATENATE. И вот в третьем столбце я собираю все необходимые имена.

Главная страница со списком Страница с именами для выпадающих меню

Я сохранил имена из столбца C страницы DropDownNames в массив. Сделал это за несколько шагов: сначала метод getActiveSpreadsheet() класса SpreadsheetApp, чтобы извлечь таблицу, которую я показывал выше, а потом — метод getSheetByName("DropDownNames"), чтобы пройтись по конкретной странице и собрать данные.

Затем с помощью функции getRange("C1:C") я собрал список имен из столбца C. Я собрал записи столбцов A и B в ‘C1:C’. И наконец я вызвал функцию getValues(), которая сохранит значения в ячейки диапазона во вложенном массиве. Вот как это выглядит:

Перед тем как двинуться дальше, нужно убедиться, что данные чистые. В них не должно быть пробелов в конце и в начале, а также не должно быть пустых имен. Как проверить? Запустить список имен в цикл при помощи метода trim(). Теперь передаем эти значения в пустой массив cleanEmployeeList. Вот как это выглядит:

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

Помните функцию onEdit(e), которую мы оставили ранее пустой? Она особенная. Такие функции называют триггерами и они запускаются каждый раз, когда редактируется таблица. Весь код, который мы собрали для обновления списка, не будет работать, пока мы явно не укажем ему, что делать. Мы можем сделать это, нажав кнопку воспроизведения в редакторе скрипта приложений или прикрепив функцию к другому триггеру. Список обновляется с onEdit(e) каждый раз. То же самое происходит и с любой частью таблицы, какой бы код в функции onEditне выполнялся.

Последний шаг — добавляем метод updateList() в функцию onEdit(e).

Чтобы сделать это, я потратил два часа. Сначала возникли ошибки, а потом все начало работать корректно. Я реализовал этот код около восьми месяцев назад и до сих пор он работает идеально. Лучшая часть во всем этом — люди пользуются формами, и когда им нужно обновить данные, они могут запросто это сделать сами. А это сильный мотиватор для моей работы над подобными проектами. Я люблю помогать людям, упрощая их жизнь на работе при помощи Apps Script. Так что два часа моей работы сэкономили десятки часов, которые больше не нужно тратить каждый месяц на очистку данных или обновление форм. Это очень ценно.

---

Перевод статьи Christopher Holmes: Making work easier with Apps Script: Drop Down menus in Google Forms