Гугл Apps Script и с чем его едят
Всем привет! Я Леша Штанько, системный админ и интегратор (а время от времени к тому же JS-разработчик) в AGIMA.
Если представить экосистему Гугл как айсберг, то наверху окажутся Гугл Sheets, Calendar и остальные пользующиеся популярностью сервисы. А вот Гугл Apps Script остается в тени, хотя он неописуемо полезен.
Эта статья – для тех, кто о нем еще не слышал. Я расскажу, как работает сервис, в чем его достоинства, и приведу практические примеры использования.
Невзирая на популярность Гугл-продуктов, в Рф не много кто употребляет Apps Script. А ведь он бесплатен и может серьезно упростить почти все процессы.
В конце статьи будут ссылки на полезные ресурсы, если возжелаете изучить инструмент поглубже.
Что такое Гугл Apps Script и на чем он работает
Apps Script – платформа на базе JavaScript для резвой и обычный разработки бизнес-решений. Apps Script привязан к определенной версии JavaScript (ES5). Но чтоб можно было применять современный синтаксис и функции JavaScript, в Apps Script была добавлена поддержка среды V8, которая, в свою очередь, поддерживает Chrome и Node.js.
Apps Script содержит в себе последующие сервисы:
-
DocumentApp – для работы с Гугл Docs.
-
GmailApp – для работы с Gmail.
-
SlidesApp – для работы с Гугл Slides.
-
SpreadsheetApp – для работы с Гугл Sheets.
-
FormApp – для работы с Гугл Form.
В статье мы коснемся сервиса SpreadsheetApp для работы с Гугл Sheets.
Сходу к делу. Фишки Apps Script
Приступим к обзору этого чуда. Красота Гугл Apps Script состоит в том, что при правильной настройке ваша Гугл-табличка может жить полностью без помощи других: скрипт будет в фоновом режиме собирать, хранить, перебирать, сшивать и ассоциировать данные. Для этого довольно настроить триггеры на вызов функции, когда для вас комфортно.
У нас в AGIMA есть большой «ящик» различных инструментов, мы можем настроить под себя любые системы и вынудить их работать так, как нам необходимо. И при всем этом обилии мы интенсивно используем автоматические таблички Гугл Sheets.
Все наши внутренние команды имеют как минимум одну автоматическую табличку. К примеру, для установок – это рентабельность по проекту с учетом трека времени, данных из Битрикса, подрядчиков и самого управляющего проекта.
Еще наш финотдел интенсивно употребляет автоматические таблички: спецы смотрят за расхождениями меж затратами, за сроками выплат и актуальностью статусов по документам и т. д. Так 50–100 строчек кода раз в день сберегают нам по 2–3 часа на остальные наиболее приоритетные задачки.
Но вы сможете поразмыслить: «Пффф… И это все? Одна табличка?!» А вот и нет! Благодаря Гугл Apps Script вы сможете дружить меж собой столько табличек, сколько для вас нужно. И я на данный момент говорю не про листы, а про отдельные файлы даже на остальных аккаунтах.
И это еще не все. Благодаря API мы можем тянуть данные из всех систем по воззванию, а опосля – преобразовать их, как нам комфортно.
Подробнее про API
Например, благодаря API-запросам мы тянем данные из нашего таск-трекера и Bitrix и интегрируем их в наши возлюбленные таблички. Вот вам наглядный пример API-запроса в формате JS:
Тут нет проверок на ошибки, чтоб этот фрагмент кода не перевоплотился в большой блок.
|
/** Функция воззвания к таск трекеру по API */ function taskTrackerAuth() { const sourceUrl = ‘https://your_taskTracker_url/rest/tempo-timesheets/4/worklogs/search’; const options = { ‘headers’: { ‘Authorization’: ‘Basic *******************’ }, ‘method’: ‘post’, ‘contentType’: ‘application/json’, ‘Accept’: ‘application/json’, /** Нужная перегрузка настраивается персонально, то, что обозначено здесь, можно очистить */ ‘payload’: JSON.stringify({‘from’: [],’to’: [], ‘worker’: [], ‘projectKey’: [], ‘taskKey’: [], ‘filterId’: [] }), } const taskTrackerResponse = UrlFetchApp.fetch(sourceUrl, options); const data = JSON.parse(taskTrackerResponse.getContentText()); //Вывод сообщения о получении данных if (data.length > 0) { SpreadsheetApp.getActiveSpreadsheet().toast(‘Данные Timesheets получены’, ‘(V)_O_o_(V)’, 2); } else { SpreadsheetApp.getActiveSpreadsheet().toast(‘Данные Timesheets не получены’, ‘(V)_O_o_(V)’, 2); } } |
Этот запрос обращен на получение данных из таск-трекера. Если его мало переработать, можно получить запрос и в остальные системы, и выловить данные через API откуда угодно.
Пример работы с Apps Script
На этом я покончу с духотой. Можем перейти к {живым} примерам. Разглядим легкий пример работы с Apps Script, который сумеет повторить хоть какой человек даже без познания программирования. Все, что для вас будет нужно, – это всеми возлюбленная композиция ctrl-C ctrl-V.
Подготовка
Сделаем документ Гугл Sheets у себя на диске Гугл Drive.

И назовем его, как для вас угодно. В моем случае – это файл Demo Habr:

Дальше мы открываем файл и идем в режим редактирования Apps Script. В верхней графе отыскиваем пункт «Расширения» и в выпавшем меню находим священную строку Apps Script.

И попадаем в зазеркалье 🙂

По сути мы попали в редактор кода. Тут и начинается вся мистика.
В качестве примера предлагаю написать пару функций. Одна будет заполнять страницу данными, а 2-ая будет их очищать. А чтоб было еще увлекательнее, я буду заполнять таблицу данными из иной таблицы.
Итак, создаю 2-ой документ Гугл Sheets и заполняю его рандомными данными:

Опосля сотворения первого и второго документа я дам наименования листам снутри, чтоб было удобнее:
В первом документе Импорт данных.
Во 2-м документе Данные.
Дальше пишем этот код в редакторе кода первой таблицы.
Код вышел длинноватым, потому что я попробовал расписать любой шаг очень тщательно и добавил пару проверок. По сути весь этот блок можно создать в пару раз компактнее:
|
/** Функция получения данных */ function getData() { /** Сокращение */ const ss = SpreadsheetApp.getActiveSpreadsheet(); /** ID документа, с которого будем забирать данные */ const sheetId = ‘1AxL1WddtqlanF-WwkpF-2qTj3V1NCHc2xRzVLF7v3ho’; /** Имя листа, с которого будем забирать данные */ const exportSheetName = ‘Данные’; /** Имя листа на который будем вставлять данные */ let importSheetName = ‘Импорт данных’; /** Проверка на правильность ID странички */ try { SpreadsheetApp.openById(sheetId); } catch (e){ ss.toast(‘Ошибка в ID странички’, ‘(V)_O_o_(V)’, 4); } /** Открываем таблицу с обозначенным идентификатором */ const openSheet = SpreadsheetApp.openById(sheetId); /** Открываем лист с обозначенным именованием */ const openList = openSheet.getSheetByName(exportSheetName); /** Проверка на существование листа, с которого забираем данные */ try { openList.getDataRange().getValues(); } catch (e){ ss.toast(‘Экспорт: Странички с таковым именованием не существует’, ‘(V)_O_o_(V)’, 4); } /** Забираем данные с листа */ const data = openList.getDataRange().getValues(); /** Маленькая проверка на существование данных */ if (Object.values(data) != 0) { /** Выведем сообщение о получении данных */ ss.toast(‘Данные получены’, ‘(V)_O_o_(V)’, 4); /** Если таковой лист уже есть, то сделаем иной*/ if (ss.getSheetByName(importSheetName) != 0) { /** Зафиксируем текущее время */ let todayDate = new Date (Date.now()); /** Если лист с таковым именованием уже есть, то добавляем ему в заглавие old + текущее время */ SpreadsheetApp.getActive().getSheetByName(importSheetName).setName(importSheetName + » old » + todayDate.toUTCString()); /** Создаем свежайший лист */ ss.insertSheet().setName(importSheetName); } else { /** Создаем свежайший лист, если такового не было ранее */ ss.insertSheet().setName(importSheetName); } /** Импортируем данные на страничку */ ss.getSheetByName(importSheetName).getRange(1, 1, data.length, data[0].length).setValues(data); /** Выведем сообщение о вставке данных */ ss.toast(‘Данные вставлены на лист’, ‘(V)_O_o_(V)’, 4); } else { /** Выведем сообщение о отсутствии данных */ ss.toast(‘Эх.. А данных то нет’, ‘(V)_O_o_(V)’, 4); }; } /** Функция чистки листа */ function clearList() { /** Сокращение */ const ss = SpreadsheetApp.getActiveSpreadsheet(); /** Имя листа, который будем очищать */ const clearListName = ‘Импорт данных’; /** Выделим лист, с которым будем работать */ const clearSheet = ss.getSheetByName(clearListName); /** Подсчитаем количество всех строк на листе */ const maxRowsCount = clearSheet.getMaxRows(); /** Подсчитаем количество всех столбцов на листе */ const maxColumnsCount = clearSheet.getMaxColumns(); /** Очистим весь лист */ clearSheet.getRange(1, 1, maxRowsCount, maxColumnsCount).clear({ contentsOnly: true }); // Дозволяет опосля чистки перезаписывать контент /** Выведем сообщение о отсутствии данных */ ss.toast(‘Лист очищен’, ‘(V)_O_o_(V)’, 4) }; /** Функция отображения выпадающего перечня с клавишами в интерфейсе Гугл Sheets */ function onOpen() { let ui = SpreadsheetApp.getUi(); ui.createMenu(‘Печеньки здесь’) .addItem(‘Загрузить данные’, ‘getData’) .addItem(‘Очистить данные’, ‘clearList’) .addToUi(); } |
В этом коде представлены три функции:
-
getData() – функция получения всех данных из другого документа.
-
clearList() – функция чистки листа от всех данных, которые находятся в нем.
-
onOpen() – функция вызова доп меню в интерфейсе самой таблички.
И четыре параметра, которые для вас необходимо будет поменять, чтоб все сработало.
1-ый параметр:
| const sheetId = ‘1AxL1WddtqlanF-WwkpF-2qTj3V1NCHc2xRzVLF7v3ho’; |
Здесь необходимо поменять id странички. Проще всего его взять из url самой станицы. Тут id я выделил жирным шрифтом:
https://docs.гугл.com/spreadsheets/d/1AxL1WddtqlanF-WwkpF-2qTj3V1NCHc2xRzVLF7v3ho/edit#gid=0
Копируете id и вставляете в ваш код.
-
Принципиально! Необходимо вставить id странички, с которой вы желаете забрать данные.
2-ой параметр:
| const exportSheetName = ‘Данные’; |
Сюда вводите заглавие листа, из которого желаете забрать данные. По дефолту это будет Лист 1.
3-ий параметр:
|
const importSheetName = ‘Импорт данных’; } |
Сюда вводите заглавие листа, на который желаете вставить данные. По дефолту это будет Лист 1.
4-ый параметр:
| const clearListName = ‘Импорт данных’; |
Сюда вводите заглавие странички, которую желаете очистить от данных. Заглавие обязано быть такое же, как в 3-ем параметре.
Непременно соблюдайте кавычки. Без их ничего работать не будет 🙂
В коде я расположил проверки. Если вы где-нибудь допустите ошибку, то получите сообщение с пояснением, где конкретно ошибка.
-
Данный код имитирует функцию IMPORTRANGE() в Гугл Sheets.
Запускаем код
С главными функциями окончили. Но сейчас основной вопросец. Как запустить код? Для этого есть несколько методов:
1. В редакторе избираем функцию getData() и жмем выполнить:

2. В основном интерфейсе находим пункт «Печеньки здесь»:

Наведя на этот блок, вы получите выпадашку с 2-мя пт:
-
Загрузить данные.
Получаете данные с другого листа. Если лист пустой, то функция просто выдаст сообщение, что данных нет. -
Очистить лист.
Стираете все данные с листа.
При нажатии на хоть какой из пт будет производиться одна из этих 2-ух функций. Сможете потыкать и поглядеть, что произойдет.
Опосля первого вызова функции, Гугл попросит вас предоставить права на выполнение операций. Даем ему разрешения, а позже опять избираем подходящую функцию. Для всякого новейшего документа проходим этот процесс один раз.
Если вдруг вы получили ошибку, то проверьте пристально, верно ли вы выполнили все шаги и не упустили ли что-то.
Пример весьма обычный и на его реализацию уйдет 5–10 минут со всеми опциями и отладками. Но на самом деле мы уже получили табличку, которой можно воспользоваться неординарно.
Почему Apps Script, а не обыденные функции Гугл Sheets
Все весьма просто! Ни одна из дефолтных функций Гугл Sheet не дозволяет получать данные из закрытых авторизацией систем, также миксовать данные меж собой перед импортом. В которой-то момент мы начали упираться в то, что обычные функции не могут размеренно переварить весьма огромные объемы данных. В особенности это касалось импорта данных. Для IMPORTRANGE() это ограничение составляет всего 10 Мб.
Вот вам наглядный пример:

Одна выгрузка данных за пару месяцев смотрится приблизительно вот так. Практически 46000 строк данных, которые нужно на сто процентов перебрать и сравнить с данными из иной системы
Потому все огромные вычисления мы проводим под капотом, а обычными функциями мы просто регулируем, как это обязано отображаться для конечного юзера.
И снова же, это грандиозная экономия времени. Сотрудник приходит днем и открывает таблицу со всеми посчитанными и отсортированными данными. Пока он отдыхал и набирался сил, скрипты в фоном режиме ночкой обрабатывали большие массивы данных и сортировали их по юзерам.
Потому симбиоз обычных функций и мало скриптологии дают впечатляющие результаты. Но одно не отменяет другого: что-то удобнее создать средством кода, а что-то внутренними функциями. Так сказать, берем наилучшее из обоих миров.
Пример архитектуры, которую мы выстроили при помощи Гугл Sheets
Как я уже гласил ранее, мы получаем данные из различных систем, обрабатываем их и выдаем конечным юзерам персонально.

Как видно из схемы, по дефолту каждую ночь (то есть темное время суток) все данные летят в буферную таблицу, там они сортируются и обогащаются данными из остальных систем, а позже данные попадают любому юзеру согласно настроенным фильтрам.
Но не совершенно комфортно, когда у тебя есть возможность получать животрепещущие данные лишь раз в день. Потому у всякого юзера на борту имеется пачка скриптов, которая дозволяет при воззвании получить прямые данные в обход буферной таблицы. Но буферная таблица все-же нужна, чтоб отлавливать ошибки в работе скрипта либо ошибки в оформлении, когда сотрудники сами заносят конфигурации в собственных системах.
Благодаря таковой системе мы можем получать и обрабатывать данные за полгода и наиболее. Если вдруг кое-где влезла ошибка, то она будет отображаться в таблице юзера либо буферной таблице. Согласитесь, ведь выходит резвее и эффективнее, когда ты буквально знаешь, где ошибка, а не тратишь полдня на поиск разногласия.

Одна из сложностей, для которой есть решение
Если даже взять тот пример, который мы сделали в рамках данной нам статьи, и просто скопировать, то вы продублируете и код, который прикреплен к таблице. Звучит хорошо. Но здесь есть одно весьма жирное но! Если вы внесете изменение в код в одном документе, то код во 2-м документе остается без конфигураций.
Чтоб упростить взаимодействие юзеров с табличками, мы создаем всепригодное решение для всех. У нас есть шаблон той либо другой таблички, юзер закачивает ее, вводит свои данные для авторизации (да, конкретно так, никаких захардкорженных данных для входа снутри кода) и начинает ей воспользоваться.
Таковым образом у нас выходит 40–50 дублей данной нам таблички, которые работают независимо друг от друга.
При обновлениях функциональности, по идее, нам необходимо брать ноги в руки, бегать по всем табличкам и заносить конфигурации вручную. Но здесь нам на помощь приходят библиотеки! Библиотека в Apps Script смотрится как отдельный файл с кодом, который подключается к табличкам в меню редактирования кода. Из доп плюсов этого решения – можно быть уверенным, что никто туда излишний раз не залезет и не наворошит там своими очумелыми ручками 🙂
У нас не раз были истории, когда приходит сотрудник и просит о помощи: «Хелп, ахтунг, алерт, у меня ничего не работает!». И ты с лицом лягушки Пепе бежишь глядеть, что все-таки там вышло.

Выясняется, что сотрудник уже пробовал починить все без помощи других, и случаем удалил часть кода. Чтоб избежать таковой задачи мы используем библиотеки.
Из плюсов:
-
Главный код лежит подальше от любознательных глаз и рук. Недозволено пофиксить то, что ты не видишь 🙂
-
Конфигурации в основном файле используются ко всем документам, к которым прикреплена эта библиотека.
-
Становится легче найти ошибку кода либо локальную ошибку.
Из минусов: по сути юзеры могут редактировать библиотеку, но для этого им необходимо вынуть ссылку на скрипт.

Заключение
В почти всех странах люди платят огромные средства за настройку автоматических табличек для сбора метрик даже из соц сетей. Надеюсь, что такие статьи, как эта, сумеют поправить ситуацию с незаслуженной непопулярностью Apps Script у нас.
Материалов по обучению работе с Apps Script и его кодом весьма много на забугорных веб-сайтах. А есть еще примеры в открытых репозиториях на GitHub.
Как и обещал, оставлю здесь полезные ссылочки на материалы:
-
Туториал от самого Гугл: https://developers.гугл.com/apps-script?hl=ru
-
Описание способов Apps Script для Гугл Sheets: https://developers.гугл.com/apps-script/reference/spreadsheet?hl=ru
Оригинал статьи на SEOnews