Что такое Range в Google Sheets и Apps Script?
В Google Sheets, Range (диапазон) представляет собой группу из одной или нескольких ячеек. В Google Apps Script, Range — это объект, представляющий этот диапазон, позволяющий вам читать, записывать и манипулировать данными в таблицах. По сути, это интерфейс между вашим скриптом и данными на листе. Используя Range, можно получить доступ к отдельным ячейкам, строкам, столбцам или произвольным блокам ячеек.
Основные понятия: ячейки, строки, столбцы и диапазоны
Ячейка: Единичная единица хранения данных в таблице. Определяется номером строки и буквой столбца (например, A1, B2).
Строка: Горизонтальный набор ячеек. Нумеруется, начиная с 1.
Столбец: Вертикальный набор ячеек. Обозначается буквами, начиная с A.
Диапазон: Группа смежных ячеек. Может включать одну ячейку, строку, столбец или прямоугольную область ячеек. Описывается указанием верхней левой и нижней правой ячеек (например, A1:C5).
Получение доступа к листам и диапазонам в активной таблице
Прежде чем работать с диапазонами, необходимо получить доступ к таблице и листу. Вот как это можно сделать:
/**
* Получает активную таблицу и лист.
* @return {GoogleAppsScript.Spreadsheet.Range} Диапазон A1:C3 активного листа.
*/
function getRangeExample() {
// Получаем активную таблицу.
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Получаем активный лист.
const sheet = spreadsheet.getActiveSheet();
// Получаем диапазон A1:C3.
const range = sheet.getRange("A1:C3");
return range;
}Чтение данных из диапазонов
Метод `getValue()`: чтение значения из одной ячейки
Метод getValue() возвращает значение, содержащееся в указанной ячейке. Тип возвращаемого значения зависит от типа данных в ячейке (число, текст, дата, логическое значение).
/**
* Читает значение из ячейки A1.
* @return {any} Значение ячейки A1.
*/
function readSingleValue() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getActiveSheet();
const cellValue = sheet.getRange("A1").getValue();
Logger.log(cellValue);
return cellValue;
}Метод `getValues()`: чтение данных из диапазона в двумерный массив
Метод getValues() возвращает двумерный массив, содержащий значения всех ячеек в указанном диапазоне. Это оптимальный способ для чтения больших объемов данных, т.к. он минимизирует количество обращений к Google Sheets API.
/**
* Читает значения из диапазона A1:C5 в двумерный массив.
* @return {any[][]} Двумерный массив значений из диапазона A1:C5.
*/
function readRangeValues() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getActiveSheet();
const values = sheet.getRange("A1:C5").getValues();
// values - это массив массивов, где каждый внутренний массив представляет строку.
Logger.log(values);
return values;
}Перебор данных в диапазоне: примеры и лучшие практики
Для обработки данных, полученных с помощью getValues(), используйте циклы. Оптимально использовать for циклы вместо forEach для большей производительности.
/**
* Перебирает значения в двумерном массиве и выводит их в лог.
* @param {any[][]} data Двумерный массив данных.
*/
function processData(data) {
const numRows = data.length;
const numCols = data[0].length;
for (let i = 0; i < numRows; i++) {
for (let j = 0; j < numCols; j++) {
Logger.log(`Ячейка [${i}, ${j}]: ${data[i][j]}`);
}
}
}Обработка различных типов данных: числа, даты, текст
Google Apps Script автоматически преобразует типы данных при чтении из ячеек. Однако, важно помнить, что даты могут быть представлены в виде объектов Date. При необходимости форматирования дат, используйте класс Utilities.
Запись данных в диапазоны
Метод `setValue()`: запись значения в одну ячейку
Метод setValue() записывает указанное значение в одну ячейку. Тип данных автоматически преобразуется.
/**
* Записывает значение 'Hello' в ячейку A1.
*/
function writeSingleValue() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getActiveSheet();
sheet.getRange("A1").setValue("Hello");
}Метод `setValues()`: запись двумерного массива в диапазон
Метод setValues() записывает двумерный массив значений в диапазон. Размер массива должен соответствовать размеру диапазона. Как и getValues(), это наиболее эффективный способ записи больших объемов данных.
/**
* Записывает двумерный массив в диапазон A1:C3.
*/
function writeRangeValues() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getActiveSheet();
const data = [
["Значение 1", "Значение 2", "Значение 3"],
["Значение 4", "Значение 5", "Значение 6"],
["Значение 7", "Значение 8", "Значение 9"]
];
sheet.getRange("A1:C3").setValues(data);
}Автоматическое изменение размера диапазона под данные
Если размер записываемых данных не известен заранее, можно использовать метод getDataRegion() для определения границ данных и последующего изменения размера диапазона.
Форматирование данных при записи: примеры и опции
При записи данных можно применять форматирование, используя методы объекта Range, например, setFontWeight(), setBackground(), setNumberFormat().
Операции с диапазонами: изменение, удаление, добавление
Вставка строк и столбцов в диапазон
Методы insertRowBefore(), insertRowAfter(), insertColumnBefore(), insertColumnAfter() позволяют вставлять строки и столбцы относительно указанного диапазона.
Удаление строк и столбцов из диапазона
Методы deleteRow(), deleteColumn() удаляют строки и столбцы из указанного диапазона.
Метод `clearContent()`: очистка содержимого диапазона
Метод clearContent() очищает только значения ячеек в диапазоне, оставляя форматирование без изменений.
Копирование и перемещение диапазонов
Метод copyTo() позволяет копировать диапазон в другое место, а метод moveTo() – перемещать.
Продвинутые техники работы с диапазонами
Использование условного форматирования через Apps Script
Apps Script позволяет управлять правилами условного форматирования. Это может быть полезно, например, для выделения ячеек, соответствующих определенным критериям в маркетинговых отчетах.
Валидация данных в диапазонах
Вы можете установить правила валидации данных для диапазона, чтобы пользователи могли вводить только допустимые значения. Например, ограничить ввод email адресами или значениями из предопределенного списка для категории товара в интернет-магазине.
Работа с диапазонами в других листах и таблицах
Для работы с диапазонами в других листах и таблицах необходимо сначала получить доступ к этим листам и таблицам, используя соответствующие методы SpreadsheetApp.
Оптимизация кода при работе с большими объемами данных
При работе с большими объемами данных критически важна оптимизация. Избегайте многократных обращений к API, используйте getValues() и setValues() для пакетной обработки, а также минимизируйте количество итераций по данным.