Google Apps Script позволяет вам делать новые и интересные вещи с помощью Google Sheets. Вы можете использовать Apps Script для добавления в Google Таблицы пользовательских меню , диалоговых окон и боковых панелей . Он также позволяет вам писать собственные функции для Таблиц, а также интегрировать Таблицы с другими сервисами Google, такими как Календарь, Диск и Gmail.
Большинство скриптов, разработанных для Google Sheets, манипулируют массивами для взаимодействия с ячейками, строками и столбцами в электронной таблице. Если вы не знакомы с массивами в JavaScript, Codecademy предлагает отличный обучающий модуль по массивам . (Обратите внимание, что этот курс не был разработан и не связан с Google.)
Краткое введение в использование Apps Script с Google Sheets см. в 5-минутном кратком руководстве по макросам, меню и пользовательским функциям .
Начать
Apps Script включает специальные API, позволяющие программно создавать, читать и редактировать Google Таблицы. Apps Script может взаимодействовать с Google Sheets двумя основными способами: любой скрипт может создавать или изменять электронную таблицу, если у пользователя скрипта есть соответствующие разрешения для электронной таблицы, а скрипт также можно привязать к электронной таблице, что дает скрипту особые возможности для изменения. пользовательский интерфейс или отвечать при открытии электронной таблицы. Чтобы создать привязанный скрипт, выберите «Расширения» > «Скрипт приложений» в Google Sheets.
Служба электронных таблиц рассматривает 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 на основе данных в электронной таблице. 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 Sheets и могут быть установлены из магазина дополнений Google Sheets. Если вы разработали сценарий для Google Таблиц и хотите поделиться им со всем миром, Apps Script позволяет опубликовать его как надстройку, чтобы другие пользователи могли установить его из магазина дополнений.
Триггеры
Скрипты, привязанные к файлу Google Sheets, могут использовать простые триггеры , такие как функции onOpen()
и onEdit()
для автоматического реагирования, когда пользователь, имеющий доступ к редактированию электронной таблицы, открывает или редактирует электронную таблицу.
Как и простые триггеры, устанавливаемые триггеры позволяют Google Sheets автоматически запускать функцию при возникновении определенного события. Однако устанавливаемые триггеры обеспечивают большую гибкость, чем простые триггеры, и поддерживают следующие события: открытие, редактирование, изменение, отправка формы и управление по времени (часы).