Google Apps Script — О редакторе сценариев.

starstarstarstarstar 4.9
Всего оценок: 74
Последнее обновление:

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

Гай Юлий Цезарь

Древнеримский государственный и политический деятель, полководец, писатель…

Мудрый был! Не поспоришь! До сих пор рассказывают, что он с легкостью выполнял несколько дел одновременно. В смысле, он мог не только с книжкой в руках суп хлебать, нет, тут все серьезно.

Гай Юлий был способен спокойно надиктовывать двум-трем писцам послания государственной важности (на секундочку!), при этом находясь верхом на лошади, на марше, а в те времена не использовали стремян и удержаться в седле было совсем непросто.

Неплохо, правда? Если б каждый так мог…

Но не спешите вешать нос, у Юлия не было Google Apps Script.

Постановка задачи и инфа к размышлению

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

А вот тут мы дружно вспоминаем афоризм Цезаря и, не тратя время на страх и сомнения, с головой окунемся в дивный мир новых возможностей.

Для начала стоит определиться с задачей. Тут все просто:

  • Более-менее ознакомиться с функционалом редактора скриптов от гугл.
  • И попутно узнать и научиться чему-нибудь новому, что в будущем пригодиться.

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

Автономные сценарии

Это отдельные файлы GAS, несвязанные с другими файлами приложений G Suite. Они отображаются системой и их без труда можно открыть прямо с Google Диска.

Конечно, при необходимости скрипт реально развернуть как веб-приложение или опубликовать его как add-on (при создании которых, кстати, google рекомендует как раз использование автономных скриптов).

Скрипты, связанные с контейнером

К примеру, есть у вас файл Таблицы или того же Документа, или Формы, или Слайдов, или Google Сайт, так вот, связанный скрипт прикручен к нему и работает как бы в его обеспечении. Файл к которому привязан скрипт называют родительским или контейнером.Такой сценарий не найти на Диске Google.

Редактировать его возможно через открытый файл-контейнер по пути Инструменты > Редактор скриптов.

G-Apps-Script | Фото - Как открыть редактор скриптов в Google Таблице

Довольно продолжительное время это был единственный способ.

Однако не так давно, Google породил менеджер скиптов ( script.google.com ), который позволяет запускать любой доступный проект и вносить необходимые правки.

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

К связанным скриптам относят и пользовательские функции. Я думаю здесь как раз все понятно. Не нашли нужной формулы среди стандартных в Таблицах Google? Не беда! Напишем свою, собственную, которая будет работать по тому же принципу. Вызывать ее надо прямо в ячейке =myFunctionName() в точности как и стандартную.

Говоря о связанных скриптах нельзя не упомянуть надстройки (дополнения) — Add-ons for G Suite. Любой скрипт, будь он связанный или автономный можно опубликовать как дополнение. Так сказать, явить его миру. После чего скрипт станет доступен для установки всем желающим. Зайдя в хранилище аддонов гугл, вы обнаружите великое множество таких сценариев.

G-Apps-Script | Фото - Как установить дополнение в Google Таблицу

G-Apps-Script | Фото - Поиск дополнения для Google Таблицы

И наконец последний тип.

Веб-приложения

Любой скрипт можно развернуть как веб приложение. Главное чтобы скрипт содержал функцию doGet(e) или doPost(e). Проще простого. Фактически можно создать веб-сервер с помощью одной строки (Шутка!)

function doGet() { return HtmlService.createHtmlOutputFromFile('Index'); }

Веб-приложения открывают широчайшие возможности.

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

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

Данный проект, Web-приложение от stomaks — это посредник между пользователем и Google Apps Script. Приложение дает шанс в полной мере насладиться возможностями продвинутой платформы от Google.

Среди доступных программ-модулей приложения уже доступны такие как:

  • Ведение домашней бухгалтерии (Google +)
  • Боты для социальных сетей Facebook, Telegram, LinkedIn, Twitter, Pinterest и др.
  • Автоматическая e-mail рассылка, с поддержкой отчетов
  • Парсер, синтаксический анализатор (Google +)
  • и другие.

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

Тема веб-приложений заслуживает отдельного внимания и мы к ней обязательно вернемся в последующих статьях.

Бесспорно, что каждый тип требует гораздо более подробных разъяснений и примеров. С другой стороны не переводить же все гайды* подряд…

(* от. англ. guide — руководство, мануал, справочник)

По ходу углубления знаний и навыков работы с этой гугловской платформой многие вопросы отпадут сами собой.

А поэтому давайте-ка приступим к практике.

Таблицы и редактор скриптов

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

К примеру, научимся находить таблицу на Google Диске, доставать из нее значения и выводить их в журнал (по мере изучения GAS с полученными данными будем совершать более сложные манипуляции, а пока так…).

Поехали…

Предположим, что на вашем диске есть такая таблица (если нет, то надо бы ее создать или кликни сюда чтобы скопировать ее к себе на Google Диск) :

Самый простой способ получить доступ к содержимому таблицы — это знать ее уникальный идентификатор (ID).

Как получить id таблицы?

Можно, его просто посмотреть, он указывается всегда в одном и том же месте в адресной строке https://…/d/{id}/edit

G-Apps-Script | Фото - Местоположение уникального идентификатора Google Таблицы

( {id} и есть id таблицы, стоит отметить что принцип получения id документа сохраняется и для других сервисах гугл ).

можно его получить и с помощью скрипта.

/*
 * Функция getSpreadsheetID() возвращает идентификатор текущей таблицы
 *
 * @return {string} spreadsheet_id - Идентификатор (id) текущей таблицы
 */
function getSpreadsheetID () {
  var spreadsheet_id = SpreadsheetApp.getActive().getId();
  
  Logger.log( spreadsheet_id );
  
  return spreadsheet_id;
}

Данный код работает только в скрипте связанным с таблицей.

И возвращает id-шник таблицы с которой связан.

SpreadsheetApp.getActive() возвращает ссылку на текущую таблицу, а затем уже получаем нужный нам идентификатор директивой — getId().

Или другими словами.

Cлужба SpreadsheetApp позволяет скриптам создавать, получать доступ и изменять файлы Google Таблиц.

Функция getActive() возвращает текущую активную электронную таблицу или null, если ее нет.

А функция getId() возвращает уникальный идентификатор (id) для этой таблицы..

Собственно, надо бы сказать и пару слов о редакторе….

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

Только мы начнем вводить имя класса или какой-нить функции, как нам тут же предлагают его закончить…

G-Apps-Script | Фото - Код - Получить id текущей таблицы

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

Вернемся к коду, стало быть таблица у нас есть, ее id мы получили, так давайте получим и все остальное…

Пример 1. Как получить данные из активной таблицы активного листа?

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

Обратите внимание что это пример для скрипта, связанного с таблицей.

/*
 * Функция getValuesByActiveSheet() получает и возвращает все данные из активной
 * таблицы активного листа.
 *
 * @return {array} sheet_values - Данные в виде двумерного массива
 */
function getValuesByActiveSheet () {
  var spreadsheet_link = SpreadsheetApp.getActive(),
      sheet_link = spreadsheet_link.getActiveSheet(),
      sheet_values = sheet_link.getDataRange().getValues();
  
  Logger.log( sheet_values );
  
  return sheet_values;
}

Разберем подробнее.

Строки 1-6. Уже знакомая нам конструкция комментария /* */. Кстати говоря, такой формат оформления комментария общепринят для описания к функций в языке JavaScript.

В строке 7 тоже знакомая нам директива function, которая создает новую функцию с названием getValuesByActiveSheet. Параметров в функцию не передаем, а значит в круглых скобках ничего не указываем () ну и { что значит что следующий код, это код данной функции вплоть до строки 15, где }, который символизирует о конце функции.

Строки 8-10. Нас встречает директива var что значит начало объявления переменных (и это мы тоже уже знаем).

С базовыми вещами разобрались теперь по сути кода.

  • Переменная spreadsheet_link в которую сразу же заносим ссылку на активную таблицу следующим кодом SpreadsheetApp.getActive(),
  • В переменную sheet_link заносим ссылку на активный лист getActiveSheet(),
  • и в переменную sheet_values занесем все данные с активного листа, getDataRange() так получаем ссылку на весь диапазон данных, а после берем значения из этого диапазона функцией getValues().

В строке 12 отправляем данные в журнал (это не обязательно).

Ну и в строке 14 возвращаем данные.

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

Старайтесь разбивать ваш код на множество мелких и универсальных функций.

Это делает разработку более гибкой и комфортной.

Так можно вызвать любую функцию:

// ... ваш код
getValuesByActiveSheet(); // Вызываем функцию с именем getValuesByActiveSheet
// ваш код ...

А если функция возвращает данные, как наша, мы эти данные можем записать в переменную так:

// ... ваш код
var data = getValuesByActiveSheet(); // Вызываем функцию getValuesByActiveSheet, а результат ее работы заносим в переменную data
// ваш код ...

Результат работы скрипта (журнал):

G-Apps-Script | Фото - Результат работы кода - Получим все данные из активной таблицы активного листа

Код выше работает в сценариях связанных с таблицами. А следующий код может работать как в автономных скриптах, так и в связанных с контейнером.

Пример 2. Как получить все данные из листа зная id таблицы?

Код ниже позволяет получить все данные из листа «Июнь» зная id таблицы.

/*
 * Функция getValuesBySheet() получает и возвращает все данные из таблицы
 * по ее id и листа с именем "Июнь".
 *
 * @return {array} sheet_values - Данные в виде двумерного массива
 */
function getValuesByActiveSheet () {
  var spreadsheet_id = "1bubLwq8J-hpvRywKH1m6lqRDWIJg2XmQTryrjff26jM",
      spreadsheet_link = SpreadsheetApp.openById( spreadsheet_id ),
      sheet_name = "Июнь",
      sheet_link = spreadsheet_link.getSheetByName( sheet_name ),
      sheet_values = sheet_link.getDataRange().getValues();
  
  Logger.log( sheet_values );
  
  return sheet_values;
}

Разберем подробнее код выше, при этом пропустим описание аналогичных частей кода.

В строке 9 мы используем функцию openById(), которая получает ссылку на таблицу по ее id. При этом, как вы уже могли заметить, вызывая функцию, в круглых скобках, мы передаем id таблицы с которой хотим работать.

А в строке 12 мы используем функцию getSheetByName(), которая получает ссылку на лист по его имени, соответственно передав имя листа в функцию.

Результат работы скрипта такой же, как и в примере 1.

Пример 3. Как получить данные только из указанного диапазона?

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

/*
 * Функция getValuesByRanges() получает данные из указанных диапазов.
 *
 * @return {boolean} false - Ничего не возвращаем
 */
function getValuesByRanges () {
  var spreadsheet_id = "1bubLwq8J-hpvRywKH1m6lqRDWIJg2XmQTryrjff26jM",
      spreadsheet_link = SpreadsheetApp.openById( spreadsheet_id ),
      sheet_name = "Июнь",
      sheet_link = spreadsheet_link.getSheetByName( sheet_name ),
      sheet_range1_values = sheet_link.getRange("A3:E").getValues(),
      sheet_range2_values = sheet_link.getRange(3, 1).getValue(),
      sheet_range3_values = sheet_link.getRange(3, 1, 2, 3).getValues();
  
  Logger.log( sheet_range1_values );
  Logger.log( sheet_range2_values );
  Logger.log( sheet_range3_values );
  
  return;
}

В предыдущих примерах мы брали данные из диапазона getDataRange(), который охватывал все ячейки с данными.

А в этом примере мы явно укажем диапазон ячеек с которыми хотим работать. И делается это функцией getRange().

Данная функция может принимать разный набор параметров (входящих данных, диапазонов).

Первый вариант, строка 11. «A3:E» привычный вариант указания диапазона, при условии что у вас есть опыт написания формул для таблицы. Данный способ возвращает диапазон, указанный в нотации A1 или нотации R1C1.

Второй вариант, строка 12. номер строки, номер столбца возвращает диапазон с верхней левой ячейки в заданных координатах.

Третий вариант, строка 13. номер строки, номер столбца, количество строк, количество столбцов возвращает диапазон с верхней левой ячейки в заданных координатах с заданным количеством строк и столбцов.

Есть еще один вариант, который мы не приминили в коде, это номер строки, номер столбца, количество строк, такой способ возвращает диапазон с верхней левой ячейки в заданных координатах и с заданным количеством строк. При этом берет только один столбец.

Также, как вы могли заметить, при получении данных, в одном случае мы используем getValue(), а в другом getValues(). Разница в том что:

  • getValue() — возвращает значение ячейки верхнего левого диапазона. Значение может быть типа Number, Boolean, Date или String в зависимости от значения ячейки. Пустые ячейки возвращают пустую строку.
  • getValues() — Возвращает прямоугольную сетку значений для этого диапазона.Возвращает двумерный массив значений, индексированный по строке, а затем по столбцу. Значения могут иметь тип Number, Boolean, Date или String, в зависимости от значения ячейки. Пустые ячейки представлены пустой строкой в массиве. Помните, что, когда индекс диапазона начинается с 1, 1, массив JavaScript индексируется из [0] [0].

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

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

Результат работы скрипта (журнал):

G-Apps-Script | Фото - Результат работы кода - Получить данные из указанного диапазона

Как наклацаете код и приметесь его тестировать, вспомните о комментариях. А точнее, о такой замечательной вещи как CTRL + /. Таким макаром очень удобно и главное быстро временно отключать участки кода в процессе его отладки.

Logger.log( sheet_range1_values );
// Logger.log( sheet_range2_values );

История версий скрипта

Также придется к месту полезная штука, которая прячется по пути Файл > Смотреть историю версий.

G-Apps-Script | Фото - Смотреть историю версий

G-Apps-Script | Фото - Окно: "История версий"

В случае, когда в скрипт закрался bug размером с носорога или по каким-то причинам требуется откатить немного назад (а CTRL + Z уже не помогает) — этот инструмент просто незаменим.

А знаете, что в этом самое замечательное? Это то, что эти бэкапы делаются автоматически! Конечно можно это сделать и вручную, вот только кто об этом вспоминает? На мой взгляд, просто чудесная функция, в критическую минуту сбережет ваши нервы, а возможно и спасет вашу технику от полета в окно…

Вернемся к нашему сценарию.

Предположим, что код написан, также предположим, что вы не забыли как его запустить и как посмотреть в Журналах ( CTRL + Enter ).

Предположим даже, что вам все понятно… 🙂

Если это так, то вы можете смело себя поздравить. Вы совершили еще один уверенный шаг к освоению Google Apps Script.

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

Говорят, Гай Юлий Цезарь мог делать несколько дел одновременно…

Взять упорство плюс гугл аппс скрипт

и…что там несколько…

СОТНИ дел одновременно не хотите?

На этом пока все.

В следующей статье продолжим знакомство с редактором и пристальнее поглядим на работу с таблицами…

Итоги

А теперь, коротко, подведем итоги:

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

Полезные ссылки и источники

  • Официальная документация по всем методам класса Приложение для электронных таблиц (на английском).
  • Документация, тоже официальная, по всем методам класса Лист (на английском).
  • Ну и еще одна официальная документация по всем методам класса Диапазон (на английском).

Бонус (расширение для редактора скриптов)

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

Подробнее о расширении смотри тут.

Author: Максим Стоянов

Разработчик Google Apps Script.