Что такое класс Spreadsheet в Google Apps Script?
Класс Spreadsheet в Google Apps Script представляет собой основной интерфейс для взаимодействия с Google Таблицами. Он является центральным объектом, позволяющим управлять файлами Google Sheets, включая получение доступа к листам (Sheets), чтение и запись данных, форматирование и выполнение других операций. Фактически, это объект, представляющий конкретную электронную таблицу.
Обзор основных методов и свойств класса
Класс Spreadsheet предоставляет широкий набор методов и свойств для манипулирования таблицами. Вот некоторые из наиболее важных:
getId(): Возвращает ID таблицы.
getName(): Возвращает имя таблицы.
getSheets(): Возвращает массив всех листов в таблице.
getSheetByName(name): Возвращает лист с указанным именем.
insertSheet(): Добавляет новый лист.
deleteSheet(sheet): Удаляет указанный лист.
getRange(a1Notation): Возвращает диапазон ячеек.
getDataRange(): Возвращает диапазон, содержащий все данные в таблице.
copy(destination): Создаёт копию таблицы.
Как получить доступ к активной таблице (SpreadsheetApp.getActiveSpreadsheet())
Для доступа к текущей открытой таблице используется метод SpreadsheetApp.getActiveSpreadsheet(). Этот метод возвращает объект Spreadsheet, представляющий таблицу, в которой запущен скрипт.
/**
* Получает активную таблицу.
* @return {Spreadsheet} Активная таблица.
*/
function getActiveSpreadsheet(): Spreadsheet {
const ss: Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
return ss;
}Как получить таблицу по ID или имени (SpreadsheetApp.openById(), SpreadsheetApp.openByName())
Для доступа к таблице по её ID или имени используются методы SpreadsheetApp.openById(id) и SpreadsheetApp.openByName(name) соответственно. openById предпочтительнее, так как ID является уникальным, в то время как имена таблиц могут повторяться.
/**
* Получает таблицу по ID.
* @param {string} spreadsheetId ID таблицы.
* @return {Spreadsheet} Таблица.
*/
function getSpreadsheetById(spreadsheetId: string): Spreadsheet {
const ss: Spreadsheet = SpreadsheetApp.openById(spreadsheetId);
return ss;
}
/**
* Получает таблицу по имени (если имя уникально!).
* @param {string} spreadsheetName Имя таблицы.
* @return {Spreadsheet} Таблица.
*/
function getSpreadsheetByName(spreadsheetName: string): Spreadsheet {
const ss: Spreadsheet = SpreadsheetApp.openByName(spreadsheetName);
return ss;
}Работа с листами (Sheets) внутри Spreadsheet
Получение листов (getSheetByName, getSheets, getActiveSheet)
getSheetByName(name: string): Получает лист по его имени. Возвращает null, если лист с таким именем не найден.
getSheets(): Получает массив всех листов в таблице. Порядок листов в массиве соответствует их порядку в интерфейсе Google Sheets.
getActiveSheet(): Получает текущий активный лист (тот, который отображается в интерфейсе).
/**
* Получает лист по имени.
* @param {Spreadsheet} spreadsheet Таблица.
* @param {string} sheetName Имя листа.
* @return {Sheet | null} Лист или null, если не найден.
*/
function getSheetByNameFromSpreadsheet(spreadsheet: Spreadsheet, sheetName: string): Sheet | null {
const sheet: Sheet | null = spreadsheet.getSheetByName(sheetName);
return sheet;
}Создание новых листов (insertSheet, duplicateSheet)
insertSheet(): Создает новый лист. Можно указать индекс (позицию) нового листа, имя и другие параметры.
duplicateSheet(): Создает копию существующего листа. Можно указать имя для новой копии.
/**
* Вставляет новый лист.
* @param {Spreadsheet} spreadsheet Таблица.
* @param {string} sheetName Имя нового листа.
* @return {Sheet} Новый лист.
*/
function insertNewSheet(spreadsheet: Spreadsheet, sheetName: string): Sheet {
const newSheet: Sheet = spreadsheet.insertSheet(sheetName);
return newSheet;
}Удаление листов (deleteSheet)
deleteSheet(sheet): Удаляет указанный лист.
/**
* Удаляет лист.
* @param {Sheet} sheet Лист для удаления.
*/
function deleteExistingSheet(sheet: Sheet): void {
const ss: Spreadsheet = sheet.getParent();
ss.deleteSheet(sheet);
}Переименование листов (setName)
setName(name: string): Переименовывает лист.
/**
* Переименовывает лист.
* @param {Sheet} sheet Лист для переименования.
* @param {string} newName Новое имя листа.
*/
function renameSheet(sheet: Sheet, newName: string): void {
sheet.setName(newName);
}Основные операции с данными в Spreadsheet
Получение диапазонов (getRange)
getRange(a1Notation): Возвращает объект Range, представляющий диапазон ячеек. Можно указать диапазон в формате A1 (например, "A1:C5") или использовать числовые индексы строк и столбцов (например, getRange(1, 1, 5, 3) для диапазона A1:C5).
/**
* Получает диапазон ячеек.
* @param {Sheet} sheet Лист.
* @param {string} a1Notation Диапазон в формате A1.
* @return {Range} Диапазон.
*/
function getRangeFromSheet(sheet: Sheet, a1Notation: string): Range {
const range: Range = sheet.getRange(a1Notation);
return range;
}Чтение данных из ячеек (getValue, getValues)
getValue(): Возвращает значение одной ячейки. Тип возвращаемого значения зависит от типа данных в ячейке (число, строка, дата и т.д.).
getValues(): Возвращает массив значений из диапазона. Возвращает двумерный массив, где каждая строка представляет строку диапазона, а каждый элемент строки — значение ячейки.
/**
* Читает значения из диапазона.
* @param {Range} range Диапазон.
* @return {any[][]} Двумерный массив значений.
*/
function readValuesFromRange(range: Range): any[][] {
const values: any[][] = range.getValues();
return values;
}Запись данных в ячейки (setValue, setValues)
setValue(value): Записывает значение в одну ячейку. Тип значения должен соответствовать типу ячейки.
setValues(values): Записывает массив значений в диапазон. Принимает двумерный массив, структура которого должна соответствовать структуре диапазона.
/**
* Записывает значения в диапазон.
* @param {Range} range Диапазон.
* @param {any[][]} values Двумерный массив значений.
*/
function writeValuesToRange(range: Range, values: any[][]): void {
range.setValues(values);
}Форматирование данных (setNumberFormat, setBackground, setFontColor и др.)
Класс Range предоставляет множество методов для форматирования ячеек. Некоторые из них:
setNumberFormat(numberFormat: string): Устанавливает формат чисел.
setBackground(color: string): Устанавливает цвет фона.
setFontColor(color: string): Устанавливает цвет шрифта.
setFontWeight(fontWeight: string): Устанавливает толщину шрифта (‘bold’, ‘normal’).
setHorizontalAlignment(alignment: string): Устанавливает горизонтальное выравнивание (‘left’, ‘center’, ‘right’).
/**
* Форматирует диапазон.
* @param {Range} range Диапазон.
* @param {string} backgroundColor Цвет фона.
* @param {string} fontColor Цвет шрифта.
*/
function formatRange(range: Range, backgroundColor: string, fontColor: string): void {
range.setBackground(backgroundColor);
range.setFontColor(fontColor);
}Расширенные возможности класса Spreadsheet
Работа с защитой листов и диапазонов (защита от редактирования)
Можно защитить листы и диапазоны от случайного редактирования. Используйте методы Sheet.protect() и Range.protect() для создания объектов Protection. Затем можно настроить, кто имеет право редактировать защищенные области.
Использование условного форматирования (условное форматирование на основе правил)
Условное форматирование позволяет автоматически форматировать ячейки на основе определенных правил. Например, можно выделить ячейки, содержащие значения больше определенного числа.
Работа с меню (создание пользовательских меню)
Можно создавать пользовательские меню в интерфейсе Google Sheets для запуска скриптов. Это позволяет пользователям легко взаимодействовать со скриптами, не открывая редактор кода.
Работа с триггерами (автоматическое выполнение скриптов)
Триггеры позволяют автоматически запускать скрипты при определенных событиях, таких как открытие таблицы, редактирование ячейки или наступление определенного времени. Это позволяет автоматизировать рутинные задачи.
Примеры использования класса Spreadsheet
Автоматизация сбора данных из разных листов в один
Допустим, у вас есть таблица с несколькими листами, каждый из которых содержит данные за определенный период. Можно написать скрипт, который будет автоматически собирать данные из всех листов и записывать их в один сводный лист.
Создание отчетов на основе данных из таблицы
Можно использовать Google Apps Script для создания отчетов на основе данных, хранящихся в таблице. Например, можно сгенерировать отчет о продажах, отчет о посещаемости сайта или отчет о рекламных кампаниях.
Реализация поиска и фильтрации данных
Можно реализовать функции поиска и фильтрации данных непосредственно в таблице. Например, можно создать пользовательский интерфейс для поиска клиентов по имени, адресу или другим критериям.
Импорт и экспорт данных из внешних источников
Google Apps Script позволяет импортировать и экспортировать данные из различных внешних источников, таких как базы данных, API или другие файлы Google Sheets. Например, можно автоматически импортировать данные о кликах из Google Ads в таблицу для дальнейшего анализа.