Эта страница переведена с помощью Cloud Translation API.

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

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

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

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

Начать

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Подключение к Google Forms

Apps Script позволяет связать Google Forms с Google Sheets через службы форм и электронных таблиц . Эта функция может автоматически создавать форму Google на основе данных из электронной таблицы. 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.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const cell = sheet.getRange('B2:C2');
  cell.setFontStyle('italic');
}

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

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

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  const cell = SpreadsheetApp.getActive().getRange('B4');
  const 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.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];

  const 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 Sheets. В отличие от пользовательских функций, вы активируете их с помощью сочетания клавиш или через меню Google Sheets. Для получения дополнительной информации см. раздел «Макросы Google Sheets» .

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

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

Производительность и масштабируемость

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

Следуйте передовым практикам : ознакомьтесь с руководством по передовым практикам , чтобы получить советы по минимизации обращений в службу поддержки и использованию пакетных операций.
Оптимизируйте формулы : если ваша электронная таблица тормозит из-за сложных формул (таких как VLOOKUP , ARRAYFORMULA или IMPORTRANGE ), рассмотрите возможность использования Apps Script для выполнения этих вычислений в памяти и записи результатов обратно партиями.
Рассмотрите альтернативные базы данных : для очень больших наборов данных (около 10 миллионов ячеек) или для высокочастотного ввода данных (например, множество связанных форм) рассмотрите возможность использования Google Cloud SQL с JDBC или BigQuery .

Триггеры

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

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

Расширение Google Таблиц Оптимизируйте свои подборки Сохраняйте и классифицируйте контент в соответствии со своими настройками.