Работа с данными в Google Таблицах с помощью Google Apps Script неизбежно связана с понятием диапазона (Range). Эффективное манипулирование диапазонами — ключ к автоматизации большинства задач, от простого чтения/записи данных до сложной обработки и форматирования.
Что такое диапазон в Google Таблицах и зачем он нужен
Диапазон — это ссылка на одну или несколько ячеек на листе Google Таблицы. Это может быть одна ячейка, строка, столбец или прямоугольная область ячеек. Диапазоны являются основным объектом для взаимодействия скриптов с данными таблицы. С их помощью мы можем:
Читать значения из ячеек.
Записывать новые значения.
Применять форматирование (цвет, шрифт, границы).
Управлять структурой (вставка/удаление строк/столбцов).
Выполнять другие операции (очистка, копирование, перемещение).
Основные понятия: ячейка, строка, столбец, диапазон
Ячейка (Cell): Минимальная единица таблицы, расположенная на пересечении строки и столбца (например, A1, B2).
Строка (Row): Горизонтальный ряд ячеек (например, строка 1, строка 5).
Столбец (Column): Вертикальный ряд ячеек (например, столбец A, столбец C).
Диапазон (Range): Прямоугольная область, включающая одну или несколько ячеек. Определяется либо адресом (A1:C5), либо координатами (строка 1, столбец 1, 5 строк, 3 столбца).
Получение доступа к Google Таблице и листу (Spreadsheet, Sheet)
Прежде чем работать с диапазонами, необходимо получить доступ к самой таблице и конкретному листу. Стандартный способ:
/**
* Получает активный лист текущей Google Таблицы.
* @returns {GoogleAppsScript.Spreadsheet.Sheet} Активный лист.
* @throws {Error} Если не удается получить доступ к таблице или листу.
*/
function getActiveSheet(): GoogleAppsScript.Spreadsheet.Sheet {
const ss = SpreadsheetApp.getActiveSpreadsheet();
if (!ss) {
throw new Error("Не удалось получить активную таблицу.");
}
const sheet = ss.getActiveSheet();
if (!sheet) {
throw new Error("Не удалось получить активный лист.");
}
return sheet;
}
// Пример использования
function main() {
try {
const sheet: GoogleAppsScript.Spreadsheet.Sheet = getActiveSheet();
Logger.log(`Активный лист: ${sheet.getName()}`);
// Дальнейшая работа с листом...
} catch (error) {
Logger.log(`Ошибка: ${error.message}`);
}
}Получение диапазонов в Google Таблицах
Класс Sheet предоставляет несколько методов для получения объекта Range.
getRange() — основной метод получения диапазона
Метод getRange() является универсальным и имеет несколько перегруженных версий для различных сценариев.
Получение диапазона по A1 нотации (например, ‘A1:C5’)
Наиболее распространенный способ — использование A1 нотации, знакомой пользователям Google Таблиц.
/**
* Получает диапазон данных рекламной кампании по A1 нотации.
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист Google Таблицы.
* @returns {GoogleAppsScript.Spreadsheet.Range} Диапазон с данными.
*/
function getCampaignDataRangeA1(sheet: GoogleAppsScript.Spreadsheet.Sheet): GoogleAppsScript.Spreadsheet.Range {
// Предполагаем, что данные кампании (Дата, Клики, Показы, Стоимость) находятся в A2:D100
const rangeA1Notation: string = 'A2:D100';
return sheet.getRange(rangeA1Notation);
}
// Пример использования
function demoGetRangeA1() {
const sheet = getActiveSheet();
const campaignRange = getCampaignDataRangeA1(sheet);
Logger.log(`Диапазон получен: ${campaignRange.getA1Notation()}`);
}Получение диапазона по номерам строк и столбцов (getRow(), getColumn(), getNumRows(), getNumColumns())
Этот способ удобен при динамическом определении диапазона или при работе с индексами строк/столбцов.
getRange(row, column): Получает одну ячейку.
getRange(row, column, numRows): Получает столбец или его часть, начиная с row, column, высотой numRows.
getRange(row, column, numRows, numColumns): Получает прямоугольный диапазон.
/**
* Получает диапазон метрик (Клики, Показы) для первых 10 строк данных.
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист Google Таблицы.
* @returns {GoogleAppsScript.Spreadsheet.Range} Диапазон с метриками.
*/
function getMetricsRangeByCoords(sheet: GoogleAppsScript.Spreadsheet.Sheet): GoogleAppsScript.Spreadsheet.Range {
const startRow: number = 2; // Первая строка данных (после заголовка)
const clicksColumn: number = 2; // Столбец B (Клики)
const impressionsColumn: number = 3; // Столбец C (Показы)
const numRowsToGet: number = 10;
const numColumnsToGet: number = 2; // Клики и Показы
// Получаем диапазон B2:C11
return sheet.getRange(startRow, clicksColumn, numRowsToGet, numColumnsToGet);
}
// Пример использования
function demoGetRangeCoords() {
const sheet = getActiveSheet();
const metricsRange = getMetricsRangeByCoords(sheet);
Logger.log(`Диапазон метрик: ${metricsRange.getA1Notation()}`);
}Получение последнего заполненного диапазона (getLastRow(), getLastColumn())
Часто необходимо обработать все данные на листе. Методы getLastRow() и getLastColumn() возвращают номер последней строки и столбца, содержащих данные.
/**
* Получает весь диапазон с данными на листе, начиная с A1.
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист Google Таблицы.
* @returns {GoogleAppsScript.Spreadsheet.Range | null} Диапазон данных или null, если лист пуст.
*/
function getDataRegion(sheet: GoogleAppsScript.Spreadsheet.Sheet): GoogleAppsScript.Spreadsheet.Range | null {
const lastRow: number = sheet.getLastRow();
const lastCol: number = sheet.getLastColumn();
if (lastRow === 0 || lastCol === 0) {
// Лист пуст
return null;
}
return sheet.getRange(1, 1, lastRow, lastCol);
}
// Пример использования
function demoGetDataRegion() {
const sheet = getActiveSheet();
const dataRange = getDataRegion(sheet);
if (dataRange) {
Logger.log(`Весь диапазон данных: ${dataRange.getA1Notation()}`);
} else {
Logger.log('Лист пуст.');
}
}Чтение данных из диапазона
После получения объекта Range можно читать из него данные.
getValue() — получение значения одной ячейки
Возвращает значение одной ячейки. Если метод вызван для диапазона из нескольких ячеек, вернется значение левой верхней ячейки.
/**
* Читает заголовок первого столбца (A1).
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
* @returns {any} Значение ячейки A1.
*/
function readFirstHeader(sheet: GoogleAppsScript.Spreadsheet.Sheet): any {
const cell: GoogleAppsScript.Spreadsheet.Range = sheet.getRange('A1');
return cell.getValue();
}getValues() — получение значений из диапазона в виде двумерного массива
Возвращает двумерный массив (Object[][]), где каждый вложенный массив представляет строку данных. Это наиболее эффективный способ чтения больших объемов данных, так как он требует одного вызова API.
/**
* Читает данные рекламной кампании (например, из A2:D) и возвращает их как массив.
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
* @returns {Object[][]} Двумерный массив данных.
*/
function readCampaignData(sheet: GoogleAppsScript.Spreadsheet.Sheet): Object[][] {
const lastRow = sheet.getLastRow();
if (lastRow < 2) {
return []; // Нет данных для чтения (только заголовок или пусто)
}
// Предполагаем 4 столбца данных: Дата, Клики, Показы, Стоимость
const range = sheet.getRange(2, 1, lastRow - 1, 4); // A2:D
return range.getValues();
}
// Пример использования
function demoReadData() {
const sheet = getActiveSheet();
const data: Object[][] = readCampaignData(sheet);
if (data.length > 0) {
Logger.log(`Прочитано строк данных: ${data.length}`);
// Пример обработки: вывод первой строки данных
Logger.log(`Первая строка: ${JSON.stringify(data[0])}`);
} else {
Logger.log('Нет данных для чтения.');
}
}Примеры чтения данных различных типов (текст, числа, даты)
getValues() возвращает данные в их естественных типах JavaScript:
Текст: string
Числа: number
Даты/Время: Date object
Булево: boolean
Пустые ячейки: '' (пустая строка)
function processReadData() {
const sheet = getActiveSheet();
const data: Object[][] = readCampaignData(sheet);
data.forEach((row, index) => {
const dateVal: any = row[0];
const clicks: any = row[1];
const cost: any = row[3];
if (dateVal instanceof Date) {
Logger.log(`Строка ${index + 2}: Дата ${dateVal.toLocaleDateString()}`);
}
if (typeof clicks === 'number' && typeof cost === 'number' && clicks > 0) {
const cpc = cost / clicks;
Logger.log(`Строка ${index + 2}: CPC = ${cpc.toFixed(2)}`);
}
});
}Особенности обработки пустых ячеек
Как упомянуто, getValues() возвращает пустую строку '' для пустых ячеек. Важно учитывать это при проверках и вычислениях, чтобы избежать ошибок, например, при делении на ноль или при проверке на null/undefined.
Запись данных в диапазон
Запись данных так же важна, как и чтение.
setValue() — запись значения в одну ячейку
Записывает одно значение. Если вызван для диапазона из нескольких ячеек, значение будет записано во все ячейки диапазона.
/**
* Записывает текущую дату и время в ячейку F1.
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
*/
function setTimestamp(sheet: GoogleAppsScript.Spreadsheet.Sheet): void {
const cell: GoogleAppsScript.Spreadsheet.Range = sheet.getRange('F1');
cell.setValue(new Date());
}setValues() — запись значений в диапазон из двумерного массива
Записывает данные из двумерного массива Object[][] в указанный диапазон. Размеры массива должны точно совпадать с размерами целевого диапазона. Это самый эффективный способ записи множества данных.
/**
* Рассчитывает CTR и записывает его в столбец E для существующих данных.
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
*/
function calculateAndWriteCTR(sheet: GoogleAppsScript.Spreadsheet.Sheet): void {
const lastRow = sheet.getLastRow();
if (lastRow < 2) return; // Нет данных
// Читаем Клики (столбец B) и Показы (столбец C)
const dataRange = sheet.getRange(2, 2, lastRow - 1, 2); // B2:C
const values: Object[][] = dataRange.getValues();
const ctrResults: Object[][] = [];
values.forEach(row => {
const clicks: number = Number(row[0]) || 0;
const impressions: number = Number(row[1]) || 0;
let ctr: number | string = 0;
if (impressions > 0) {
ctr = clicks / impressions;
} else {
ctr = 'N/A'; // Или 0, в зависимости от требований
}
ctrResults.push([ctr]); // Массив для записи должен быть двумерным
});
// Определяем целевой диапазон для записи CTR (столбец E)
const targetRange = sheet.getRange(2, 5, ctrResults.length, 1); // E2:E
// Устанавливаем формат (процентный)
targetRange.setNumberFormat('0.00%');
// Записываем результаты
targetRange.setValues(ctrResults);
}
// Пример использования
function demoWriteCTR() {
const sheet = getActiveSheet();
calculateAndWriteCTR(sheet);
Logger.log('Расчет и запись CTR завершены.');
}Примеры записи данных различных типов
setValues() принимает массив, элементы которого могут быть типа string, number, boolean или Date. Google Apps Script автоматически преобразует их в соответствующие форматы ячеек (хотя иногда может потребоваться явное форматирование с помощью setNumberFormat() и т.д.).
Автоматическое изменение размера диапазона перед записью (setValues)
Важно помнить: setValues() не изменяет размер листа или диапазона автоматически для соответствия размеру входного массива. Размеры передаваемого массива данных Object[][] должны строго совпадать с размерами диапазона, полученного через getRange(). Если размеры не совпадают, будет выброшено исключение.
Например, если getRange('A1:B2') (2×2), то и массив для setValues() должен быть [[val1, val2], [val3, val4]].
Операции с диапазонами: форматирование и другие действия
Объект Range предоставляет множество методов для форматирования и управления структурой.
Изменение формата ячеек (цвет фона, шрифт, выравнивание)
/**
* Форматирует заголовки таблицы (первая строка).
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
*/
function formatHeaders(sheet: GoogleAppsScript.Spreadsheet.Sheet): void {
const lastCol: number = sheet.getLastColumn();
if (lastCol === 0) return;
const headerRange: GoogleAppsScript.Spreadsheet.Range = sheet.getRange(1, 1, 1, lastCol);
headerRange
.setBackground('#4a86e8') // Синий фон
.setFontColor('white')
.setFontWeight('bold')
.setHorizontalAlignment('center')
.setVerticalAlignment('middle');
}
// Пример использования
function demoFormat() {
const sheet = getActiveSheet();
formatHeaders(sheet);
Logger.log('Заголовки отформатированы.');
}Другие популярные методы форматирования:
setFontFamily(fontFamily)
setFontSize(size)
setFontLine('underline' | 'line-through' | 'none')
setNumberFormat(formatString)
setBorder(top, left, bottom, right, vertical, horizontal, color, style)
setWrap(isWrapEnabled)
Удаление и вставка строк/столбцов в диапазоне
insertCells(shiftDimension): Вставляет ячейки, сдвигая существующие (вправо или вниз).
deleteCells(shiftDimension): Удаляет ячейки, сдвигая существующие (влево или вверх).
insertRows(howMany), insertColumns(howMany): Вставляют строки/столбцы перед/слева от диапазона.
deleteRows(rowPosition, howMany), deleteColumns(columnPosition, howMany): Удаляют строки/столбцы, на которых находится диапазон.
/**
* Вставляет пустую строку перед строкой 5.
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
*/
function insertRowExample(sheet: GoogleAppsScript.Spreadsheet.Sheet): void {
sheet.insertRowsBefore(5, 1);
Logger.log('Строка вставлена перед 5-й строкой.');
}
/**
* Удаляет столбец C.
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
*/
function deleteColumnExample(sheet: GoogleAppsScript.Spreadsheet.Sheet): void {
// Столбец C имеет индекс 3
sheet.deleteColumn(3);
Logger.log('Столбец C удален.');
}Копирование и перемещение диапазонов
copyTo(destinationRange, options): Копирует данные и форматирование.
moveTo(targetRange): Перемещает диапазон (вырезать и вставить).
/**
* Копирует заголовки на другой лист 'Backup'.
* @param {GoogleAppsScript.Spreadsheet.Spreadsheet} ss - Таблица.
* @param {GoogleAppsScript.Spreadsheet.Sheet} sourceSheet - Исходный лист.
*/
function copyHeadersToBackup(ss: GoogleAppsScript.Spreadsheet.Spreadsheet, sourceSheet: GoogleAppsScript.Spreadsheet.Sheet): void {
let backupSheet = ss.getSheetByName('Backup');
if (!backupSheet) {
backupSheet = ss.insertSheet('Backup');
}
const lastCol = sourceSheet.getLastColumn();
if (lastCol === 0) return;
const sourceHeaders = sourceSheet.getRange(1, 1, 1, lastCol);
const destination = backupSheet.getRange('A1');
sourceHeaders.copyTo(destination);
Logger.log('Заголовки скопированы на лист Backup.');
}Очистка содержимого диапазона (clearContent()) и форматирования (clearFormat())
clearContent(): Удаляет только значения, оставляя форматирование.
clearFormat(): Удаляет только форматирование, оставляя значения.
clear(options): Позволяет гибко настроить очистку (содержимое, формат, примечания, проверки данных и т.д.).
/**
* Очищает данные о CTR (столбец E), но сохраняет формат.
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - Лист.
*/
function clearCtrData(sheet: GoogleAppsScript.Spreadsheet.Sheet): void {
const lastRow = sheet.getLastRow();
if (lastRow < 2) return;
const ctrRange = sheet.getRange(2, 5, lastRow - 1, 1); // E2:E
ctrRange.clearContent();
Logger.log('Данные CTR очищены.');
}Работа с диапазонами — фундаментальный навык при разработке скриптов для Google Таблиц. Понимание методов getRange, getValues, setValues и операций форматирования/структуры открывает широкие возможности для автоматизации анализа данных, отчетности и управления информацией.