Что такое Google Apps Script и его применение в Google Sheets
Google Apps Script (GAS) — это облачная платформа скриптов на основе JavaScript для легкой автоматизации задач в продуктах Google Workspace, включая Google Sheets. В контексте Таблиц, GAS позволяет манипулировать данными, автоматизировать отчетность, создавать пользовательские функции и интегрироваться с внешними сервисами.
Основные понятия: Spreadsheet, Sheet, Range
Прежде чем получать данные, важно понимать иерархию объектов в Google Sheets:
- Spreadsheet: Представляет всю Google Таблицу (файл).
- Sheet: Обозначает отдельный лист внутри таблицы.
- Range: Представляет одну или несколько ячеек на листе.
Доступ к этим объектам осуществляется через сервисы SpreadsheetApp, Spreadsheet, и Sheet.
Обзор методов для получения значений ячеек
Для извлечения данных из ячеек Google Apps Script предоставляет два основных метода объекта Range:
getValue(): Возвращает значение одной ячейки.getValues(): Возвращает двумерный массив значений для диапазона ячеек.
Выбор метода зависит от того, нужно ли вам значение одной ячейки или данные из целого диапазона.
Получение значения одной ячейки
Метод getValue(): базовый пример и синтаксис
Метод getValue() извлекает содержимое указанной ячейки. Тип возвращаемого значения соответствует типу данных в ячейке (String, Number, Boolean, Date).
/**
* Получает активную электронную таблицу.
* @returns {GoogleAppsScript.Spreadsheet.Spreadsheet} Активный объект Spreadsheet.
*/
function getActiveSpreadsheet(): GoogleAppsScript.Spreadsheet.Spreadsheet {
return SpreadsheetApp.getActiveSpreadsheet();
}
/**
* Получает значение из ячейки A1 на активном листе.
* @returns {any} Значение ячейки A1.
*/
function getSingleCellValue(): any {
const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = getActiveSpreadsheet();
const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
const range: GoogleAppsScript.Spreadsheet.Range = sheet.getRange('A1');
const value: any = range.getValue();
Logger.log(`Значение в A1: ${value}`);
return value;
}
Указание ячейки по координатам (строка, столбец) через getRange()
Метод getRange(row, column) позволяет получить доступ к ячейке, используя ее числовые координаты (нумерация с 1).
/**
* Получает значение из ячейки в указанной строке и столбце.
* @param {number} row Номер строки (начиная с 1).
* @param {number} col Номер столбца (начиная с 1).
* @returns {any} Значение ячейки.
*/
function getValueByCoordinates(row: number, col: number): any {
const sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSheet();
// Пример: Получить значение из ячейки C5 (строка 5, столбец 3)
const range: GoogleAppsScript.Spreadsheet.Range = sheet.getRange(row, col);
const value: any = range.getValue();
Logger.log(`Значение в строке ${row}, столбце ${col}: ${value}`);
return value;
}
// Вызов функции для получения значения из C5
// getValueByCoordinates(5, 3);
Получение значения ячейки по A1 нотации (например, ‘A1’, ‘B12’)
Наиболее распространенный способ — использование A1 нотации в методе getRange(a1Notation).
/**
* Получает значение из ячейки, указанной в A1 нотации.
* @param {string} a1Notation Адрес ячейки в A1 нотации (например, 'B12', 'Sheet2!C3').
* @returns {any} Значение ячейки.
*/
function getValueByA1Notation(a1Notation: string): any {
const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Можно указывать имя листа: 'SheetName!A1'
const range: GoogleAppsScript.Spreadsheet.Range = ss.getRange(a1Notation);
const value: any = range.getValue();
Logger.log(`Значение в ${a1Notation}: ${value}`);
return value;
}
// Вызов функции для получения значения из B12 активного листа
// getValueByA1Notation('B12');
Примеры кода для различных сценариев получения одной ячейки
Предположим, в ячейке ‘A1’ находится API-ключ, а в ‘B1’ — базовый URL для запроса.
/**
* Читает конфигурацию API из ячеек.
*/
function readApiConfig(): void {
const sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSheet();
// Получение API ключа
const apiKey: string = sheet.getRange('A1').getValue();
// Получение базового URL
const baseUrl: string = sheet.getRange('B1').getValue();
if (!apiKey || !baseUrl) {
Logger.log('Ошибка: API ключ или базовый URL не найдены в ячейках A1/B1.');
return;
}
Logger.log(`API Key: ${apiKey}, Base URL: ${baseUrl}`);
// Здесь может быть логика использования этих значений для API запроса
}
Получение значений из диапазона ячеек
Метод getValues(): получение двумерного массива значений
Для чтения данных из нескольких ячеек используется метод getValues(). Он возвращает двумерный массив [][], где первый индекс соответствует строке, а второй — столбцу внутри диапазона. data[0][0] — значение верхней левой ячейки диапазона.
Использование getRange() для выбора диапазона (например, ‘A1:C5’)
Аналогично получению одной ячейки, для выбора диапазона используется getRange() с A1 нотацией (‘A1:C5’) или с указанием начальной строки/столбца и количества строк/столбцов (getRange(row, column, numRows, numColumns)).
/**
* Получает данные из указанного диапазона в виде двумерного массива.
* @param {string} a1Notation Диапазон в A1 нотации (например, 'A1:C5').
* @returns {any[][]} Двумерный массив значений.
*/
function getRangeValues(a1Notation: string): any[][] {
const sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSheet();
const range: GoogleAppsScript.Spreadsheet.Range = sheet.getRange(a1Notation);
const values: any[][] = range.getValues();
// Logger.log(values); // Выведет массив в лог
return values;
}
// Пример вызова для диапазона A1:B3
// const data: any[][] = getRangeValues('A1:B3');
Обработка полученного двумерного массива значений (итерация)
Полученный массив можно обрабатывать с помощью стандартных методов JavaScript для работы с массивами (forEach, map, filter) или циклов for.
/**
* Обрабатывает данные о рекламных кампаниях из диапазона.
* Предполагается, что в столбце A - ID кампании, B - Бюджет, C - Статус.
* @param {string} rangeA1Notation Диапазон данных ('A2:C10').
*/
function processCampaignData(rangeA1Notation: string): void {
const values: any[][] = getRangeValues(rangeA1Notation);
values.forEach((row: any[], index: number) => {
const campaignId: string = row[0]; // Первый столбец (A)
const budget: number = row[1]; // Второй столбец (B)
const status: string = row[2]; // Третий столбец (C)
// Пропускаем пустые строки или строки без ID
if (!campaignId) {
return;
}
Logger.log(`Строка ${index + 2}: ID=${campaignId}, Бюджет=${budget}, Статус=${status}`);
// Пример: Логика для приостановки кампаний с низким бюджетом
if (status === 'Active' && budget < 100) {
Logger.log(`-> Кампания ${campaignId} будет приостановлена (бюджет < 100).`);
// Здесь мог бы быть вызов функции для изменения статуса кампании
}
});
}
// Запуск обработки данных из A2:C10
// processCampaignData('A2:C10');
Примеры кода: чтение данных из таблицы и вывод в лог или другую ячейку
Этот пример читает данные из одного диапазона и записывает отфильтрованные данные в другой.
/**
* Читает данные из диапазона источника, фильтрует их
* и записывает результат в диапазон назначения.
* @param {string} sourceRangeA1 'A1:B10' - Откуда читать.
* @param {string} targetCellA1 'D1' - Куда (в какую ячейку) начать запись.
*/
function filterAndWriteData(sourceRangeA1: string, targetCellA1: string): void {
const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
// Чтение исходных данных
const sourceValues: any[][] = sheet.getRange(sourceRangeA1).getValues();
// Фильтрация данных (пример: только строки, где значение во втором столбце > 50)
const filteredValues: any[][] = sourceValues.filter((row: any[]) => {
// Проверяем, что второй столбец (индекс 1) существует и является числом > 50
return row.length > 1 && typeof row[1] === 'number' && row[1] > 50;
});
if (filteredValues.length === 0) {
Logger.log('Нет данных для записи после фильтрации.');
return;
}
// Получение диапазона для записи
// Определяем размеры нового диапазона по отфильтрованным данным
const targetRange: GoogleAppsScript.Spreadsheet.Range = sheet.getRange(
targetCellA1
).offset(0, 0, filteredValues.length, filteredValues[0].length);
// Запись отфильтрованных данных
targetRange.setValues(filteredValues);
Logger.log(`Отфильтрованные данные записаны начиная с ячейки ${targetCellA1}`);
}
// Пример вызова
// filterAndWriteData('A1:B10', 'D1');
Продвинутые методы и оптимизация
Использование getDataRange() для получения всех данных листа
Метод sheet.getDataRange() возвращает диапазон, охватывающий все ячейки листа, содержащие данные. Это удобно, когда вы не знаете точный размер таблицы данных.
/**
* Получает все данные с активного листа.
* @returns {any[][]} Двумерный массив всех данных листа.
*/
function getAllSheetData(): any[][] {
const sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSheet();
const dataRange: GoogleAppsScript.Spreadsheet.Range = sheet.getDataRange();
const allValues: any[][] = dataRange.getValues();
Logger.log(`Получено ${allValues.length} строк и ${allValues[0]?.length || 0} столбцов данных.`);
return allValues;
}
Оптимизация скорости чтения данных: пакетное получение значений
Ключевой принцип оптимизации в GAS — минимизация количества обращений к сервисам Google (например, getValue(), getValues()). Вместо чтения ячеек по одной в цикле, всегда читайте весь необходимый диапазон за один вызов getValues() и обрабатывайте данные в полученном массиве.
Плохо (медленно):
// НЕ ДЕЛАЙТЕ ТАК!
function slowDataRead(): void {
const sheet = SpreadsheetApp.getActiveSheet();
let sum = 0;
for (let i = 1; i <= 100; i++) {
// 100 вызовов getValue()
sum += sheet.getRange(i, 1).getValue();
}
Logger.log(sum);
}
Хорошо (быстро):
/**
* Быстрое чтение данных из диапазона и их суммирование.
*/
function fastDataRead(): void {
const sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSheet();
// Один вызов getValues()
const values: any[][] = sheet.getRange('A1:A100').getValues();
let sum: number = 0;
values.forEach((row: any[]) => {
if (typeof row[0] === 'number') {
sum += row[0];
}
});
Logger.log(`Сумма значений в A1:A100: ${sum}`);
}
Обработка пустых ячеек и ошибок при получении данных
Методы getValue() и getValues() возвращают пустую строку '' для пустых ячеек. При обработке данных важно проверять типы и наличие значений, особенно если ожидаются числа или даты.
/**
* Пример обработки данных с учетом возможных пустых ячеек или неверных типов.
*/
function safeDataProcessing(): void {
const values: any[][] = SpreadsheetApp.getActiveSheet().getRange('A1:B5').getValues();
values.forEach((row, rowIndex) => {
const valueA: any = row[0];
const valueB: any = row[1];
// Проверка на пустоту и тип перед числовой операцией
if (typeof valueA === 'number' && typeof valueB === 'number') {
Logger.log(`Строка ${rowIndex + 1}: ${valueA} + ${valueB} = ${valueA + valueB}`);
} else {
Logger.log(`Строка ${rowIndex + 1}: Неверные данные для сложения (A='${valueA}', B='${valueB}')`);
}
});
}
Примеры практического применения
Автоматизация обработки данных: чтение и запись в другие листы/таблицы
Чтение данных с одного листа (например, сырые данные из веб-аналитики), их обработка (агрегация, фильтрация) и запись результатов на другой лист (отчет) или даже в другую таблицу.
Создание пользовательских функций для работы с данными из ячеек
Вы можете создавать функции, которые будут доступны прямо в ячейках Google Sheets (например, =GET_CAMPAIGN_BUDGET(A2)), читая необходимые параметры из других ячеек.
Интеграция с другими сервисами Google: отправка данных по email, запись в Google Docs
Чтение email-адресов и данных из таблицы для персонализированной рассылки через MailApp или формирование отчета на основе данных из ячеек и его сохранение в Google Docs с помощью DocumentApp.