Расширение Google Таблиц

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

Большинство скриптов, разработанных для Google Таблиц, работают с массивами, взаимодействуя с ячейками, строками и столбцами электронной таблицы. Если вы не знакомы с массивами в JavaScript, Codecademy предлагает отличный обучающий модуль по этим функциям . (Обратите внимание, что этот курс не был разработан Google и не связан с ней.)

Для быстрого ознакомления с использованием Apps Script с Google Таблицами см. 5-минутное руководство по быстрому старту для макросов, меню и пользовательских функций .

Начать

Apps Script включает специальные API, позволяющие программно создавать, читать и редактировать данные в Google Таблицах. Apps Script может взаимодействовать с Google Таблицами двумя основными способами: любой скрипт может создавать или изменять электронную таблицу, если у пользователя скрипта есть соответствующие разрешения для работы с ней, а также скрипт можно привязать к электронной таблице, что даёт ему особые возможности для изменения пользовательского интерфейса или реагирования на открытие таблицы. Чтобы создать привязанный скрипт, выберите «Расширения» > «Apps Script» в Google Таблицах.

Служба электронных таблиц обрабатывает Google Таблицы как сетку, работающую с двумерными массивами. Чтобы извлечь данные из таблицы, необходимо получить доступ к таблице, в которой они хранятся, получить диапазон ячеек, содержащий эти данные, а затем получить значения ячеек. Apps Script упрощает доступ к данным, считывая структурированные данные из таблицы и создавая для них объекты JavaScript.

Чтение данных

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

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

function logProductInfo() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i < data.length; i++) {
    Logger.log('Product name: ' + data[i][0]);
    Logger.log('Product number: ' + data[i][1]);
  }
}

Просмотреть журналы

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

Запись данных

Чтобы сохранить данные, например название и номер нового продукта, в электронной таблице, добавьте следующий код в конец скрипта.

function addProduct() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}

Приведённый выше код добавляет новую строку в конец таблицы с указанными значениями. Если вы запустите эту функцию, вы увидите новую строку, добавленную в таблицу.

Пользовательские меню и пользовательские интерфейсы

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

Вы также можете прикрепить функцию скрипта к изображению или рисунку в электронной таблице; функция будет выполнена при щелчке пользователя по изображению или рисунку. Подробнее см. в статье «Изображения и рисунки в Google Таблицах» .

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

Подключение к Google Формам

Apps Script позволяет подключать Google Forms к Google Sheets через сервисы «Формы» и «Электронные таблицы» . Эта функция автоматически создает Google Forms на основе данных из электронной таблицы. Apps Script также позволяет использовать триггеры , например, onFormSubmit для выполнения определенного действия после того, как пользователь ответит на форму. Чтобы узнать больше о подключении Google Sheets к Google Forms, ознакомьтесь с 5-минутным руководством по управлению ответами в Google Forms .

Форматирование

Класс Range содержит методы, такие как setBackground(color) для доступа к формату ячейки или диапазона ячеек и его изменения. В следующем примере показано, как задать стиль шрифта для диапазона:

function formatMySpreadsheet() {
  // Set the font style of the cells in the range of B2:C2 to be italic.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var cell = sheet.getRange('B2:C2');
  cell.setFontStyle('italic');
}

Проверка данных

Apps Script позволяет получить доступ к существующим правилам проверки данных в Google Таблицах или создать новые. Например, в следующем примере показано, как настроить правило проверки данных, которое допускает в ячейке только числа от 1 до 100.

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  var cell = SpreadsheetApp.getActive().getRange('B4');
  var rule = SpreadsheetApp.newDataValidation()
     .requireNumberBetween(1, 100)
     .setAllowInvalid(false)
     .setHelpText('Number must be between 1 and 100.')
     .build();
  cell.setDataValidation(rule);
}

Дополнительные сведения о работе с правилами проверки данных см. в SpreadsheetApp.newDataValidation() , DataValidationBuilder и Range.setDataValidation(rule)

Диаграммы

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

function newChart() {
  // Generate a chart representing the data in the range of A1:B15.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.BAR)
     .addRange(sheet.getRange('A1:B15'))
     .setPosition(5, 5, 0, 0)
     .build();

  sheet.insertChart(chart);
}

Дополнительную информацию о встраивании диаграммы в электронную таблицу см. в разделе EmbeddedChart и специальных конструкторах диаграмм, например EmbeddedPieChartBuilder .

Пользовательские функции в Google Таблицах

Пользовательская функция похожа на встроенную функцию электронной таблицы, например =SUM(A1:A5) за исключением того, что поведение функции определяется с помощью Apps Script. Например, вы можете создать пользовательскую функцию in2mm() , которая преобразует значение из дюймов в миллиметры, а затем использовать эту формулу в электронной таблице, введя =in2mm(A1) или =in2mm(10) в ячейку.

Чтобы узнать больше о пользовательских функциях, попробуйте 5-минутный краткий обзор «Меню и пользовательские функции» или ознакомьтесь с более подробным руководством по пользовательским функциям .

Макросы

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

Дополнения для Google Таблиц

Дополнения — это специально упакованные проекты Apps Script, которые работают в Google Таблицах и могут быть установлены из магазина дополнений Google Таблиц. Если вы разработали скрипт для Google Таблиц и хотите поделиться им со всем миром, Apps Script позволяет опубликовать его как дополнение, чтобы другие пользователи могли установить его из магазина дополнений.

Триггеры

Скрипты, привязанные к файлу Google Таблиц, могут использовать простые триггеры , такие как функции onOpen() и onEdit() для автоматического реагирования, когда пользователь, имеющий доступ на редактирование электронной таблицы, открывает или редактирует ее.

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