Google Apps Script и с чем его едят
Всем привет! Меня зовут Леша Штанько, я системный администратор и интегратор (если остается время, еще и JS-разработчик: D) в AGIMA. Если представить Google большим айсбергом, то на его верхушке точно разместятся всеми любимые и популярные инструменты, вроде Google Sheets и Google Calendar, а Google Apps Script скроется где-то под толщей воды. Но я считаю, что больше людей должны попробовать этот классный инструмент. Поэтому в этой статье расскажу о его главных достоинствах и поделюсь практическими примерами.
Несмотря на популярность продуктов Google, с Apps Script работают немногие в России. Даже слышали о нем не все. Хотя он доступен, и главное — он бесплатный и очень полезный.
Shut up and give it to me for free
Моя статья носит просветительский характер и ориентирована на тех, кто никогда не пробовал Google Apps Script. В ней я приведу примеры из практики работы с Apps Script в AGIMA. А в конце оставлю ссылки на полезные и обучающие материалы.
Что такое Google Apps Script и на чем он работает
Apps Script — платформа на основе JavaScript для быстрой и простой разработки бизнес-решений. Apps Script привязан к конкретной версии JavaScript (ES5). Но чтобы можно было использовать современный синтаксис и функции JavaScript, в Apps Script была добавлена поддержка среды V8, которая, в свою очередь, поддерживает Chrome и Node.js.
Apps Script включает в себя следующие сервисы:
DocumentApp — для работы с Google Docs.
GmailApp — для работы с Gmail.
SlidesApp — для работы с Google Slides.
SpreadsheetApp — для работы с Google Sheets.
FormApp — для работы с Google Form.
В статье мы коснемся сервиса SpreadsheetApp для работы с Google Sheets.
Сразу к делу. Фишки Apps Script
Приступим к обзору этого чуда. Прелесть Google Apps Script заключается в том, что при правильной настройке ваша Google-табличка может жить абсолютно самостоятельно: скрипт будет в фоновом режиме собирать, хранить, перебирать, сшивать и сравнивать данные. Для этого достаточно настроить триггеры на вызов функции, когда вам удобно.
У нас в AGIMA есть огромный «ящик» разных инструментов, мы можем настроить под себя любые системы и заставить их работать так, как нам нужно. И при этом изобилии мы активно используем автоматизированные таблички Google Sheets.
Все наши внутренние команды имеют как минимум одну автоматизированную табличку. Например, для команд — это рентабельность по проекту с учетом трека времени, данных из Битрикса, подрядчиков и самого руководителя проекта.
Еще наш финотдел активно использует автоматизированные таблички: следят за расхождениями между затратами, за сроками выплат и актуальностью статусов по документам и т. д. Так 50–100 строчек кода ежедневно экономят нам по 2–3 часа на другие более приоритетные задачи.
Но вы можете подумать: «Пффф… И это всё? Одна табличка?!» А вот и нет! Благодаря Google Apps Script вы можете дружить между собой столько табличек, сколько вам необходимо. И я сейчас говорю не про листы, а про отдельные файлы даже на других аккаунтах.
И это еще не всё. Благодаря API мы можем тянуть данные из любых систем по обращению, а после — конвертировать их, как нам удобно.
Подробнее про API
К примеру, благодаря API-запросам мы тянем данные из нашего таск-трекера и Bitrix и интегрируем их в наши любимые таблички. Вот пример API-запроса в формате JS:
Hidden text
Здесь нет проверок на ошибки, чтобы этот фрагмент кода не превратился в крупный блок.
/** Функция обращения к таск трекеру по 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.
Подготовка:
Создадим документ Google Sheets у себя на диске Google Drive.
И назовем его, как вам угодно. В моем случае — это файл Demo Habr:
Далее мы открываем файл и идем в режим редактирования Apps Script. В верхней графе ищем пункт «Расширения» и в выпавшем меню находим заветную строчку Apps Script.
И попадаем в зазеркалье:)
На самом деле мы попали в редактор кода. Здесь и начинается вся магия.
В качестве примера предлагаю написать пару функций. Одна будет заполнять страничку данными, а вторая будет их очищать. А чтобы было еще интереснее, я буду заполнять таблицу данными из другой таблицы.
Итак, создаю второй документ Google Sheets и заполняю его рандомными данными:1
После создания первого и второго документа я дам названия листам внутри, чтобы было удобнее:
В первом документе Импорт данных
Во втором документе Данные
Далее пишем этот код в редакторе кода первой таблицы.
Код получился длинным, так как я попытался расписать каждый шаг максимально подробно и добавил пару проверок. На самом деле весь этот блок можно сделать в несколько раз компактнее:
/** Функция получения данных */
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)
};
/** Функция отображения выпадающего списка с кнопками в интерфейсе Google Sheets */
function onOpen() {
let ui = SpreadsheetApp.getUi();
ui.createMenu('Печеньки тут')
.addItem('Загрузить данные', 'getData')
.addItem('Очистить данные', 'clearList')
.addToUi();
}
В этом коде представлены три функции:
getData () — функция получения всех данных из другого документа.
clearList () — функция очистки листа от любых данных, которые находятся в нем.
onOpen () — функция вызова дополнительного меню в интерфейсе самой таблички.
И четыре параметра, которые вам нужно будет поменять, чтобы всё сработало.
Первый параметр
const sheetId = '1AxL1WddtqlanF-WwkpF-2qTj3V1NCHc2xRzVLF7v3ho';Тут нужно поменять id страницы. Проще всего его взять из url самой станицы. Здесь id я выделил жирным шрифтом:
https://docs.google.com/spreadsheets/d/1AxL1WddtqlanF-WwkpF-2qTj3V1NCHc2xRzVLF7v3ho/edit#gid=0
Копируете id и вставляете в ваш код.
Важно! Нужно вставить id страницы, с которой вы хотите забрать данные.
Второй параметр
const exportSheetName = 'Данные';Сюда вводите название листа, из которого хотите забрать данные. По дефолту это будет Лист 1.
Третий параметр
const importSheetName = 'Импорт данных';Сюда вводите название листа, на который хотите вставить данные. По дефолту это будет Лист 1.
Четвертый параметр
const clearListName = 'Импорт данных';Сюда вводите название страницы, которую хотите очистить от данных. Название должно быть такое же, как в третьем параметре.
Обязательно соблюдайте кавычки. Без них ничего работать не будет :)
В коде я разместил проверки. Если вы где-нибудь допустите ошибку, то получите сообщение с пояснением, где именно ошибка.
Данный код имитирует функцию IMPORTRANGE () в Google Sheets.
Запускаем код
С основными функциями закончили. Но теперь главный вопрос. Как запустить код? Для этого есть несколько способов:
В редакторе выбираем функцию getData () и нажимаем выполнить:
2. В главном интерфейсе находим пункт «Печеньки тут»:
Наведя на этот блок, вы получите выпадашку с двумя пунктами:
Загрузить данные.
Получаете данные с другого листа. Если лист пустой, то функция просто выдаст сообщение, что данных нет.Очистить лист.
Стираете все данные с листа.
При нажатии на любой из пунктов будет выполняться одна из этих двух функций. Можете потыкать и посмотреть, что произойдет.
После первого вызова функции, Google попросит вас предоставить права на выполнение операций. Даем ему разрешения, а потом снова выбираем нужную функцию. Для каждого нового документа проходим этот процесс один раз.
Если вдруг вы получили ошибку, то проверьте внимательно, правильно ли вы выполнили все шаги и не упустили ли что-то.
Пример очень простой и на его реализацию уйдет 5–10 минут со всеми настройками и отладками. Но по сути мы уже получили табличку, которой можно пользоваться нестандартно.
Почему Apps Script, а не обычные функции Google Sheets
Все очень просто! Ни одна из дефолтных функций Google Sheet не позволяет получать данные из закрытых авторизацией систем, а также миксовать данные между собой перед импортом. В какой-то момент мы начали упираться в то, что стандартные функции не могут стабильно переварить очень большие объемы данных. Особенно это касалось импорта данных. Для IMPORTRANGE () это ограничение составляет всего 10 Мб.
Вот пример:
Одна выгрузка данных за пару месяцев выглядит примерно вот так. Почти 46000 строк данных, которые надо полностью перебрать и сопоставить с данными из другой системы.
Поэтому все большие вычисления мы проводим под капотом, а стандартными функциями мы просто регулируем, как это должно отображаться для конечного пользователя.
И опять же, это колоссальная экономия времени. Сотрудник приходит утром и открывает таблицу со всеми посчитанными и отсортированными данными. Пока он отдыхал и набирался сил, скрипты в фоном режиме ночью обрабатывали огромные массивы данных и сортировали их по пользователям.
Поэтому симбиоз стандартных функций и немного скриптологии дают впечатляющие результаты. Но одно не отменяет другого: что-то удобнее сделать посредством кода, а что-то внутренними функциями. Так сказать, берем лучшее из обоих миров.
Пример архитектуры, которую мы выстроили с помощью Google Sheets
Как я уже говорил ранее, мы получаем данные из разных систем, обрабатываем их и выдаем конечным пользователям индивидуально.
Как видно из схемы, по дефолту каждую ночь все данные летят в буферную таблицу, там они сортируются и обогащаются данными из других систем, а потом данные попадают каждому пользователю согласно настроенным фильтрам.
Однако, не совсем удобно, когда у тебя есть возможность получать актуальные данные только раз в сутки. Поэтому у каждого пользователя на борту имеется пачка скриптов, которая позволяет при обращении получить прямые данные в обход буферной таблицы. Но буферная таблица всё-таки нужна, чтобы отлавливать ошибки в работе скрипта или ошибки в оформлении, когда сотрудники сами вносят изменения в своих системах.
Благодаря такой системе мы можем получать и обрабатывать данные за полгода и более. Если вдруг где-то закралась ошибка, то она будет отображаться в таблице пользователя или буферной таблице. Согласитесь, ведь получается быстрее и эффективнее, когда ты точно знаешь, где ошибка, а не тратишь полдня на поиск разногласия.
Одна из сложностей, для которой есть решение
Если даже взять тот пример, который мы сделали в рамках этой статьи, и просто скопировать, то вы продублируете и код, который прикреплен к таблице. Звучит неплохо. Но тут есть одно очень жирное но! Если вы внесете изменение в код в одном документе, то код во втором документе останется без изменений.
Чтобы упростить взаимодействие пользователей с табличками мы создаем универсальное решение для всех. У нас есть шаблон той или иной таблички, пользователь скачивает ее, вводит свои данные для авторизации (да, именно так, никаких захардкорженных данных для входа внутри кода) и начинает ей пользоваться.
Таким образом у нас получается 40–50 дублей этой таблички, которые работают независимо друг от друга.
При обновлениях функциональности, по идее нам нужно брать ноги в руки, бегать по всем табличкам и вносить изменения вручную. Но тут нам на помощь приходят библиотеки! Библиотека в Apps Script выглядит как отдельный файл с кодом, который подключается к табличкам в меню редактирования кода. Из дополнительных плюсов этого решения — можно быть уверенным, что никто туда лишний раз не залезет и не наворошит там своими очумелыми ручками :)
У нас не раз были истории, когда приходит сотрудник и просит о помощи: «Хелп, ахтунг, алерт, у меня ничего не работает!». И ты с лицом лягушки Пепе бежишь смотреть, что же там произошло.
Выясняется, что сотрудник уже пытался починить всё самостоятельно, и случайно удалил часть кода. Чтобы избежать такой проблемы мы используем библиотеки.
Из плюсов:
Основной код лежит подальше от любопытных глаз и рук. Нельзя пофиксить то, что ты не видишь :)
Изменения в главном файле применяются ко всем документам, к которым прикреплена эта библиотека.
Становится легче определить ошибку кода или локальную ошибку.
Из минусов:
На самом деле пользователи могут редактировать библиотеку, но для этого им нужно вытащить ссылку на скрипт.
Заключение
Во многих странах люди платят большие деньги за настройку автоматизированных табличек для сбора метрик даже из социальных сетей. Надеюсь, что такие статьи, как эта, смогут исправить ситуацию с незаслуженной непопулярностью Apps Script у нас.
Материалов по обучению работе с Apps Script и его кодом очень много на зарубежных сайтах. А еще есть примеры в открытых репозиториях на GitHub.
Как и обещал, оставлю тут полезные ссылочки на материалы:
Туториал от самого Google:
https://developers.google.com/apps-script? hl=ruОписание методов Apps Script для Google Sheets:
https://developers.google.com/apps-script/reference/spreadsheet? hl=ru
P.S. Буду рад ответить на вопросы в комментах. А еще приглашаю вас в наш уютный телеграм-канал про разработку.
