Как использовать примеры скриптов Google Apps Script для Google Sheets
Введение в Google Apps Script и Google Sheets
Что такое Google Apps Script и его возможности в Google Sheets
Google Apps Script (GAS) — это облачный язык сценариев, основанный на JavaScript, который позволяет автоматизировать задачи и расширять функциональность Google Workspace, включая Google Sheets. Он предоставляет доступ к Google Sheets API, позволяя программно взаимодействовать с таблицами, листами, ячейками и диапазонами.
С помощью GAS можно:
- Автоматизировать рутинные задачи, такие как форматирование данных, отправка электронных писем и создание отчетов.
- Интегрировать Google Sheets с другими сервисами Google (Drive, Calendar, Gmail и т. д.) и внешними API.
- Создавать пользовательские функции (Custom Functions) для использования непосредственно в формулах Google Sheets.
- Реализовывать сложные бизнес-логики и рабочие процессы.
Настройка редактора скриптов в Google Sheets
Для начала работы с Google Apps Script необходимо открыть редактор скриптов. Это можно сделать следующим образом:
- Откройте Google Sheets.
- Выберите Инструменты > Редактор скриптов.
Откроется новое окно с редактором кода Google Apps Script. Здесь вы будете писать и запускать свои скрипты.
Основы синтаксиса и структуры скриптов Google Apps Script
Google Apps Script основан на JavaScript, поэтому если вы знакомы с JavaScript, вам будет легко освоить GAS. Основные отличия заключаются в специфичных API для работы с сервисами Google. Вот простой пример скрипта:
/**
* @OnlyCurrentDoc
*/
/**
* Функция для записи "Hello, World!" в ячейку A1 текущего листа.
*/
function writeHelloWorld() {
// Получаем активную таблицу.
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Получаем активный лист.
const sheet = spreadsheet.getActiveSheet();
// Записываем текст в ячейку A1.
sheet.getRange('A1').setValue('Hello, World!');
}
Ключевые моменты:
@OnlyCurrentDoc— аннотация, указывающая, что скрипт будет работать только с текущим документом.SpreadsheetApp— сервис для работы с Google Sheets.getActiveSpreadsheet()— метод для получения активной таблицы.getActiveSheet()— метод для получения активного листа.getRange(адрес_ячейки)— метод для получения диапазона ячеек.setValue(значение)— метод для записи значения в ячейку.
Примеры скриптов для автоматизации задач в Google Sheets
Скрипт для автоматической отправки email уведомлений
Этот скрипт отправляет email уведомление, если значение в определенной ячейке изменилось.
/**
* Функция для отслеживания изменений в ячейке и отправки email.
*/
function onEdit(e) {
/**
* @param {GoogleAppsScript.Events.SheetsOnEditEvent} e
*/
// Определяем ячейку для отслеживания.
const monitoredCell = 'A1';
// Определяем email адрес для отправки уведомлений.
const emailAddress = 'your_email@example.com';
// Получаем значение измененной ячейки.
const cell = e.range.getA1Notation();
// Проверяем, была ли изменена отслеживаемая ячейка.
if (cell === monitoredCell) {
// Получаем новое значение ячейки.
const newValue = e.value;
// Формируем тему и текст письма.
const subject = 'Изменение значения в ячейке ' + monitoredCell;
const body = 'Значение в ячейке ' + monitoredCell + ' было изменено на ' + newValue;
// Отправляем email.
MailApp.sendEmail(emailAddress, subject, body);
}
}
Этот скрипт использует триггер onEdit, который автоматически запускается при изменении любой ячейки в таблице. Функция onEdit(e) принимает объект события e, содержащий информацию об изменении. MailApp.sendEmail() используется для отправки электронной почты.
Скрипт для фильтрации и сортировки данных в таблице
Этот скрипт фильтрует данные в таблице по определенному критерию и сортирует их по заданному столбцу.
/**
* Функция для фильтрации и сортировки данных.
*/
function filterAndSortData() {
// Получаем активную таблицу.
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Получаем активный лист.
const sheet = spreadsheet.getActiveSheet();
// Определяем диапазон данных.
const dataRange = sheet.getDataRange();
// Получаем все значения из диапазона.
const values = dataRange.getValues();
// Определяем столбец для фильтрации (например, столбец 1).
const filterColumn = 0; // Индексация начинается с 0.
// Определяем критерий фильтрации (например, значения больше 10).
const filterCriteria = 10;
// Определяем столбец для сортировки (например, столбец 2).
const sortColumn = 1; // Индексация начинается с 0.
// Фильтруем данные.
const filteredData = values.filter(row => row[filterColumn] > filterCriteria);
// Сортируем данные.
filteredData.sort((a, b) => a[sortColumn] - b[sortColumn]);
// Очищаем текущий диапазон данных.
dataRange.clearContent();
// Записываем отфильтрованные и отсортированные данные.
sheet.getRange(1, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
}
Этот скрипт использует методы filter() и sort() массивов JavaScript для фильтрации и сортировки данных. clearContent() очищает содержимое диапазона перед записью новых данных. Важно учесть, что нумерация столбцов начинается с 0.
Скрипт для импорта данных из внешних источников (API)
Этот скрипт импортирует данные из внешнего API (в данном случае, JSONPlaceholder) и записывает их в таблицу.
/**
* Функция для импорта данных из API.
*/
function importDataFromAPI() {
// URL API.
const apiUrl = 'https://jsonplaceholder.typicode.com/todos';
// Получаем данные из API.
const response = UrlFetchApp.fetch(apiUrl);
// Парсим JSON ответ.
const data = JSON.parse(response.getContentText());
// Получаем активную таблицу.
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Получаем активный лист.
const sheet = spreadsheet.getActiveSheet();
// Преобразуем данные в формат, подходящий для записи в таблицу.
const values = data.map(item => [item.userId, item.id, item.title, item.completed]);
// Записываем данные в таблицу.
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
UrlFetchApp.fetch() используется для получения данных из API. JSON.parse() преобразует JSON строку в объект JavaScript. Массив values содержит данные, подготовленные для записи в таблицу.
Скрипт для создания пользовательских функций (Custom Functions)
Google Apps Script позволяет создавать пользовательские функции, которые можно использовать непосредственно в формулах Google Sheets. Например, функция, которая умножает два числа.
/**
* Умножает два числа.
*
* @param {number} a Первое число.
* @param {number} b Второе число.
* @customfunction
*/
function MULTIPLY(a, b) {
// Проверяем, являются ли аргументы числами.
if (typeof a !== 'number' || typeof b !== 'number') {
throw new Error('Аргументы должны быть числами.');
}
// Возвращаем результат умножения.
return a * b;
}
Чтобы использовать эту функцию в Google Sheets, просто введите =MULTIPLY(A1, B1) в ячейку. @customfunction — важная аннотация, указывающая, что это пользовательская функция.
Расширенные примеры и техники использования Google Apps Script
Работа с триггерами: автоматический запуск скриптов по расписанию или событию
Триггеры позволяют автоматически запускать скрипты по расписанию (например, каждый час или каждый день) или по событию (например, при открытии таблицы или при изменении ячейки). Настроить триггеры можно в редакторе скриптов (меню Редактировать > Триггеры текущего проекта). Примеры триггеров:
onOpen— запускается при открытии таблицы.onEdit— запускается при изменении ячейки.time-driven— запускается по расписанию.
Интеграция с другими сервисами Google (Drive, Calendar, Docs)
Google Apps Script позволяет интегрировать Google Sheets с другими сервисами Google. Например, можно создать скрипт, который автоматически сохраняет копию таблицы в Google Drive или создает событие в Google Calendar на основе данных из таблицы. Пример интеграции с Google Drive:
/**
* Функция для сохранения копии таблицы в Google Drive.
*/
function saveCopyToDrive() {
// Получаем активную таблицу.
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Получаем ID таблицы.
const spreadsheetId = spreadsheet.getId();
// Получаем папку в Google Drive, куда нужно сохранить копию.
const folderId = 'your_folder_id'; // Замените на ID вашей папки.
const folder = DriveApp.getFolderById(folderId);
// Создаем копию таблицы.
const file = DriveApp.getFileById(spreadsheetId);
file.makeCopy(spreadsheet.getName() + ' - копия', folder);
}
Использование библиотек Google Apps Script
Библиотеки Google Apps Script позволяют повторно использовать код в разных проектах. Можно создать собственную библиотеку с общими функциями и подключить ее к другим скриптам. Чтобы создать библиотеку, создайте новый проект Google Apps Script и скопируйте в него код общих функций. Затем опубликуйте проект как библиотеку и добавьте ее в другие проекты через меню Ресурсы > Библиотеки.
Отладка и тестирование скриптов Google Apps Script
Использование логгера (Logger) для отслеживания работы скрипта
Logger.log() используется для записи отладочной информации в журнал. Журнал можно посмотреть в редакторе скриптов (меню Вид > Журналы). Это полезно для отслеживания значений переменных и хода выполнения скрипта.
function exampleFunction() {
let myVariable = 10;
Logger.log('Значение myVariable: ' + myVariable);
myVariable = myVariable * 2;
Logger.log('Новое значение myVariable: ' + myVariable);
}
Инструменты отладки в редакторе скриптов
Редактор скриптов предоставляет инструменты для отладки, такие как точки останова (breakpoints) и пошаговое выполнение кода. Вы можете установить точку останова, щелкнув по серой области слева от строки кода. При запуске скрипта выполнение остановится на точке останова, и вы сможете просмотреть значения переменных.
Обработка ошибок и исключений
Важно обрабатывать ошибки и исключения в скриптах, чтобы предотвратить их аварийное завершение. Используйте блоки try...catch для перехвата исключений.
try {
// Код, который может вызвать ошибку.
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Несуществующий лист').getRange('A1').getValue();
} catch (e) {
// Обработка ошибки.
Logger.log('Произошла ошибка: ' + e.message);
}
Практические советы и рекомендации по работе с Google Apps Script в Google Sheets
Оптимизация производительности скриптов
- Избегайте частого обращения к Google Sheets API. Лучше получить данные один раз и обработать их в памяти.
- Используйте пакетные операции для записи данных в таблицу.
- Оптимизируйте алгоритмы и используйте эффективные структуры данных.
- Используйте кеширование для хранения часто используемых данных.
Безопасность скриптов и разрешений
- Внимательно проверяйте скрипты, которые вы устанавливаете из ненадежных источников.
- Предоставляйте скриптам только необходимые разрешения.
- Не храните конфиденциальную информацию (например, пароли) в коде скриптов.
Обмен скриптами и совместная работа
- Используйте систему контроля версий (например, Git) для управления кодом скриптов.
- Пишите понятный и хорошо документированный код.
- Используйте библиотеки для повторного использования кода.