Зачем работать с несколькими таблицами?
В Google Apps Script часто возникает необходимость работы с данными, хранящимися в разных Google Sheets таблицах. Причины могут быть разными: разделение данных по отделам, хранение данных за разные периоды времени, или просто архитектурные решения, требующие распределения информации по разным файлам. Работа с несколькими таблицами позволяет автоматизировать задачи, требующие консолидации, анализа и переноса данных между ними, например, для создания дашбордов, отчетов или для синхронизации информации.
Основные понятия: Spreadsheet, Sheet, Range
Прежде чем погрузиться в код, важно понимать основные термины:
Spreadsheet: Это, собственно, и есть Google Sheets таблица, файл с данными.
Sheet: Это лист внутри таблицы. Одна таблица может содержать несколько листов.
Range: Это диапазон ячеек на листе (например, A1:B10).
Получение доступа к активной таблице и листам
Начнем с получения доступа к текущей, активной таблице и ее листам. Этот код обычно используется как отправная точка:
/**
* Получает активную таблицу и её листы.
*/
function getActiveSpreadsheetAndSheets() {
// Получаем активную таблицу.
const spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Получаем все листы в таблице.
const sheets: GoogleAppsScript.Spreadsheet.Sheet[] = spreadsheet.getSheets();
Logger.log('Активная таблица: ' + spreadsheet.getName());
sheets.forEach(sheet => Logger.log('Лист: ' + sheet.getName()));
return {spreadsheet, sheets};
}Получение доступа к нескольким таблицам
Открытие таблиц по ID
Самый надежный способ получить доступ к определенной таблице – использовать её ID. ID можно найти в URL таблицы (часть между /d/ и /edit):
/**
* Открывает таблицу по её ID.
* @param {string} spreadsheetId ID таблицы.
* @return {GoogleAppsScript.Spreadsheet.Spreadsheet} Открытая таблица.
*/
function openSpreadsheetById(spreadsheetId: string): GoogleAppsScript.Spreadsheet.Spreadsheet {
try {
const spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.openById(spreadsheetId);
Logger.log('Таблица открыта: ' + spreadsheet.getName());
return spreadsheet;
} catch (e) {
Logger.log('Ошибка при открытии таблицы: ' + e);
return null;
}
}
// Пример использования:
const spreadsheetId = 'YOUR_SPREADSHEET_ID'; // Замените на реальный ID
const mySpreadsheet = openSpreadsheetById(spreadsheetId);Открытие таблиц по имени
Открытие таблицы по имени – менее надежный способ, так как имена могут совпадать. Этот метод предполагает, что имя таблицы уникально в пределах вашего Google Drive.
/**
* Открывает таблицу по её имени.
* @param {string} spreadsheetName Имя таблицы.
* @return {GoogleAppsScript.Spreadsheet.Spreadsheet | null} Открытая таблица или null, если не найдена.
*/
function openSpreadsheetByName(spreadsheetName: string): GoogleAppsScript.Spreadsheet.Spreadsheet | null {
const files: GoogleAppsScript.Drive.FileIterator = DriveApp.getFilesByName(spreadsheetName);
while (files.hasNext()) {
const file: GoogleAppsScript.Drive.File = files.next();
if (file.getMimeType() === MimeType.GOOGLE_SHEETS) {
const spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.open(file);
Logger.log('Таблица открыта: ' + spreadsheet.getName());
return spreadsheet;
}
}
Logger.log('Таблица с именем ' + spreadsheetName + ' не найдена.');
return null;
}
// Пример использования:
const spreadsheetName = 'My Spreadsheet';
const mySpreadsheet = openSpreadsheetByName(spreadsheetName);Работа с таблицами из Google Drive
Можно получить доступ к таблицам через Google Drive API, что дает больше контроля над процессом, включая фильтрацию по папкам и другим критериям. Этот подход полезен, когда нужно найти таблицу, соответствующую определенным условиям, например, находящуюся в определенной папке.
/**
* Находит таблицы в указанной папке Google Drive.
* @param {string} folderId ID папки Google Drive.
* @return {GoogleAppsScript.Drive.FileIterator} Итератор файлов таблиц.
*/
function findSpreadsheetsInFolder(folderId: string): GoogleAppsScript.Drive.FileIterator {
const folder: GoogleAppsScript.Drive.Folder = DriveApp.getFolderById(folderId);
const files: GoogleAppsScript.Drive.FileIterator = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
return files;
}
// Пример использования:
const folderId = 'YOUR_FOLDER_ID'; // Замените на реальный ID папки
const spreadsheets = findSpreadsheetsInFolder(folderId);
while (spreadsheets.hasNext()) {
const file: GoogleAppsScript.Drive.File = spreadsheets.next();
Logger.log('Найдена таблица: ' + file.getName());
}Чтение данных из нескольких таблиц
Использование `getValue()` и `getValues()` для чтения данных
Для чтения данных используются методы getValue() (для одной ячейки) и getValues() (для диапазона). getValues() возвращает двумерный массив, представляющий данные в диапазоне.
/**
* Читает данные из указанного диапазона в таблице.
* @param {GoogleAppsScript.Spreadsheet.Spreadsheet} spreadsheet Таблица.
* @param {string} sheetName Имя листа.
* @param {string} range Диапазон (например, 'A1:B10').
* @return {any[][]} Двумерный массив с данными.
*/
function readDataFromSpreadsheet(spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet, sheetName: string, range: string): any[][] {
const sheet: GoogleAppsScript.Spreadsheet.Sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
Logger.log('Лист ' + sheetName + ' не найден.');
return null;
}
const dataRange: GoogleAppsScript.Spreadsheet.Range = sheet.getRange(range);
const values: any[][] = dataRange.getValues();
return values;
}
// Пример использования:
const mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // Или откройте по ID/имени
const data = readDataFromSpreadsheet(mySpreadsheet, 'Sheet1', 'A1:C5');
if (data) {
Logger.log(data);
}Чтение данных из определенных диапазонов в разных таблицах
Чтобы прочитать данные из нескольких таблиц, нужно просто повторить процесс для каждой из них, получив доступ к таблице и используя getValues():
// Предположим, у вас есть массив с ID таблиц:
const spreadsheetIds: string[] = ['ID_TABLE_1', 'ID_TABLE_2'];
spreadsheetIds.forEach(spreadsheetId => {
const spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = openSpreadsheetById(spreadsheetId);
if (spreadsheet) {
const data = readDataFromSpreadsheet(spreadsheet, 'Sheet1', 'A1:C5');
if (data) {
Logger.log('Данные из таблицы ' + spreadsheet.getName() + ': ' + data);
}
}
});Обработка ошибок при чтении данных
Важно обрабатывать возможные ошибки, например, отсутствие листа или неправильный диапазон. Используйте try...catch блоки для перехвата исключений и логирования ошибок.
try {
const data = readDataFromSpreadsheet(mySpreadsheet, 'NonExistentSheet', 'A1:C5');
} catch (e) {
Logger.log('Ошибка при чтении данных: ' + e);
}Запись данных в несколько таблиц
Использование `setValue()` и `setValues()` для записи данных
Для записи данных используются методы setValue() (для одной ячейки) и setValues() (для диапазона). setValues() принимает двумерный массив данных для записи.
/**
* Записывает данные в указанный диапазон таблицы.
* @param {GoogleAppsScript.Spreadsheet.Spreadsheet} spreadsheet Таблица.
* @param {string} sheetName Имя листа.
* @param {string} range Диапазон (например, 'A1:B10').
* @param {any[][]} data Двумерный массив с данными для записи.
*/
function writeDataToSpreadsheet(spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet, sheetName: string, range: string, data: any[][]) {
const sheet: GoogleAppsScript.Spreadsheet.Sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
Logger.log('Лист ' + sheetName + ' не найден.');
return;
}
const dataRange: GoogleAppsScript.Spreadsheet.Range = sheet.getRange(range);
dataRange.setValues(data);
Logger.log('Данные записаны в ' + sheetName + '!' + range);
}
// Пример использования:
const mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // Или откройте по ID/имени
const newData = [ [1, 2, 3], [4, 5, 6] ];
writeDataToSpreadsheet(mySpreadsheet, 'Sheet1', 'A1:C2', newData);Запись данных в разные листы и диапазоны
Запись в разные листы и диапазоны выполняется аналогично чтению – просто меняйте параметры sheetName и range.
writeDataToSpreadsheet(mySpreadsheet, 'Sheet2', 'D1:F2', newData);Форматирование данных при записи
При записи данных можно применять форматирование, например, изменять цвет фона, шрифт и т.д. Для этого используются методы объекта Range:
const dataRange: GoogleAppsScript.Spreadsheet.Range = mySpreadsheet.getSheetByName('Sheet1').getRange('A1:C2');
dataRange.setBackground('red');
dataRange.setFontWeight('bold');Примеры практического применения
Сбор данных из нескольких таблиц в одну сводную таблицу
Допустим, у вас есть несколько таблиц с данными по продажам за разные месяцы, и вы хотите собрать все данные в одну сводную таблицу. Для этого нужно прочитать данные из каждой таблицы и записать их в сводную таблицу.
Автоматическое обновление данных между таблицами
Можно настроить скрипт, который будет автоматически копировать данные из одной таблицы в другую при изменении исходных данных. Для этого можно использовать триггеры, срабатывающие при изменении таблицы (onChange).
Создание отчетов на основе данных из нескольких таблиц
Наиболее распространенный пример. Можно использовать данные из нескольких таблиц для создания красивых и информативных отчетов, используя библиотеки для работы с графиками и диаграммами.