Гугл 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 содержит в себе последующие сервисы:

  1. DocumentApp – для работы с Гугл Docs.

  2. GmailApp – для работы с Gmail.

  3. SlidesApp – для работы с Гугл Slides.

  4. SpreadsheetApp – для работы с Гугл Sheets.

  5. 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();

}

В этом коде представлены три функции:

  1. getData() – функция получения всех данных из другого документа.

  2. clearList() – функция чистки листа от всех данных, которые находятся в нем.

  3. 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-мя пт:

  1. Загрузить данные.

    Получаете данные с другого листа. Если лист пустой, то функция просто выдаст сообщение, что данных нет.

  2. Очистить лист.

    Стираете все данные с листа.

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

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

Если вдруг вы получили ошибку, то проверьте пристально, верно ли вы выполнили все шаги и не упустили ли что-то.

Пример весьма обычный и на его реализацию уйдет 5–10 минут со всеми опциями и отладками. Но на самом деле мы уже получили табличку, которой можно воспользоваться неординарно.

Почему Apps Script, а не обыденные функции Гугл Sheets

Все весьма просто! Ни одна из дефолтных функций Гугл Sheet не дозволяет получать данные из закрытых авторизацией систем, также миксовать данные меж собой перед импортом. В которой-то момент мы начали упираться в то, что обычные функции не могут размеренно переварить весьма огромные объемы данных. В особенности это касалось импорта данных. Для IMPORTRANGE() это ограничение составляет всего 10 Мб.

Вот вам наглядный пример:

Одна выгрузка данных за пару месяцев смотрится приблизительно вот так. Практически 46000 строк данных, которые нужно на сто процентов перебрать и сравнить с данными из иной системы

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

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

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

Пример архитектуры, которую мы выстроили при помощи Гугл Sheets

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

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

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

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

Одна из сложностей, для которой есть решение

Если даже взять тот пример, который мы сделали в рамках данной нам статьи, и просто скопировать, то вы продублируете и код, который прикреплен к таблице. Звучит хорошо. Но здесь есть одно весьма жирное но! Если вы внесете изменение в код в одном документе, то код во 2-м документе остается без конфигураций.

Чтоб упростить взаимодействие юзеров с табличками, мы создаем всепригодное решение для всех. У нас есть шаблон той либо другой таблички, юзер закачивает ее, вводит свои данные для авторизации (да, конкретно так, никаких захардкорженных данных для входа снутри кода) и начинает ей воспользоваться.

Таковым образом у нас выходит 40–50 дублей данной нам таблички, которые работают независимо друг от друга.

При обновлениях функциональности, по идее, нам необходимо брать ноги в руки, бегать по всем табличкам и заносить конфигурации вручную. Но здесь нам на помощь приходят библиотеки! Библиотека в Apps Script смотрится как отдельный файл с кодом, который подключается к табличкам в меню редактирования кода. Из доп плюсов этого решения – можно быть уверенным, что никто туда излишний раз не залезет и не наворошит там своими очумелыми ручками 🙂

У нас не раз были истории, когда приходит сотрудник и просит о помощи: «Хелп, ахтунг, алерт, у меня ничего не работает!». И ты с лицом лягушки Пепе бежишь глядеть, что все-таки там вышло.

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

Из плюсов:

  1. Главный код лежит подальше от любознательных глаз и рук. Недозволено пофиксить то, что ты не видишь 🙂

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

  3. Становится легче найти ошибку кода либо локальную ошибку.

Из минусов: по сути юзеры могут редактировать библиотеку, но для этого им необходимо вынуть ссылку на скрипт.

Заключение

В почти всех странах люди платят огромные средства за настройку автоматических табличек для сбора метрик даже из соц сетей. Надеюсь, что такие статьи, как эта, сумеют поправить ситуацию с незаслуженной непопулярностью Apps Script у нас.

Материалов по обучению работе с Apps Script и его кодом весьма много на забугорных веб-сайтах. А есть еще примеры в открытых репозиториях на GitHub.

Как и обещал, оставлю здесь полезные ссылочки на материалы:

  1. Туториал от самого Гугл: https://developers.гугл.com/apps-script?hl=ru

  2. Описание способов Apps Script для Гугл Sheets: https://developers.гугл.com/apps-script/reference/spreadsheet?hl=ru

Оригинал статьи на SEOnews

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *