Введение в Google Apps Script и работу с листами
Что такое Google Apps Script и его преимущества
Google Apps Script – это облачный язык сценариев, основанный на JavaScript, который позволяет автоматизировать задачи и расширять функциональность Google Workspace (Sheets, Docs, Forms, Gmail и др.). Преимущества Apps Script включают интеграцию с сервисами Google, простоту использования, отсутствие необходимости в установке дополнительного ПО и возможность создания веб-приложений.
Основные понятия: Spreadsheet, Sheet, Range
- Spreadsheet (таблица) – это основной объект, представляющий собой файл Google Sheets.
- Sheet (лист) – это отдельная страница внутри таблицы.
- Range (диапазон) – это группа ячеек на листе. Диапазон может быть одной ячейкой, строкой, столбцом или блоком ячеек.
Получение доступа к Google Sheets через Apps Script
Для работы с Google Sheets в Apps Script используется объект SpreadsheetApp. Этот объект предоставляет методы для открытия таблиц, получения листов и доступа к данным.
Получение данных с другого листа в той же таблице
Метод SpreadsheetApp.getActiveSpreadsheet(): получение текущей таблицы
Этот метод возвращает объект Spreadsheet, представляющий таблицу, в которой выполняется скрипт.
/**
* Получает текущую таблицу.
* @return {Spreadsheet} Текущая таблица.
*/
function getActiveSpreadsheet() {
return SpreadsheetApp.getActiveSpreadsheet();
}
Метод getSheetByName(sheetName): получение листа по имени
Этот метод позволяет получить объект Sheet по его имени. Важно помнить, что имя листа должно быть указано точно.
/**
* Получает лист по имени.
* @param {string} sheetName Имя листа.
* @return {Sheet} Лист с указанным именем.
*/
function getSheetByName(sheetName) {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
return spreadsheet.getSheetByName(sheetName);
}
Методы getDataRange(), getLastRow(), getLastColumn(): определение диапазона данных
getDataRange(): возвращает диапазон, содержащий все данные на листе.getLastRow(): возвращает номер последней строки, содержащей данные.getLastColumn(): возвращает номер последнего столбца, содержащего данные.
/**
* Получает диапазон данных на листе.
* @param {Sheet} sheet Лист.
* @return {Range} Диапазон данных.
*/
function getDataRange(sheet) {
return sheet.getDataRange();
}
/**
* Получает номер последней строки с данными.
* @param {Sheet} sheet Лист.
* @return {number} Номер последней строки.
*/
function getLastRow(sheet) {
return sheet.getLastRow();
}
/**
* Получает номер последнего столбца с данными.
* @param {Sheet} sheet Лист.
* @return {number} Номер последнего столбца.
*/
function getLastColumn(sheet) {
return sheet.getLastColumn();
}
Метод getRange(row, column, numRows, numColumns): получение конкретного диапазона
Этот метод позволяет получить объект Range, представляющий собой конкретный диапазон ячеек, заданный начальной строкой и столбцом, а также количеством строк и столбцов.
/**
* Получает диапазон ячеек.
* @param {Sheet} sheet Лист.
* @param {number} row Начальная строка.
* @param {number} column Начальный столбец.
* @param {number} numRows Количество строк.
* @param {number} numColumns Количество столбцов.
* @return {Range} Диапазон ячеек.
*/
function getRange(sheet, row, column, numRows, numColumns) {
return sheet.getRange(row, column, numRows, numColumns);
}
Метод getValues(): получение данных из диапазона в виде массива
Этот метод возвращает двумерный массив, содержащий значения ячеек из указанного диапазона.
/**
* Получает значения из диапазона в виде массива.
* @param {Range} range Диапазон.
* @return {any[][]} Массив значений.
*/
function getValues(range) {
return range.getValues();
}
Пример: Копирование данных с листа ‘Sheet1’ на лист ‘Sheet2’
/**
* Копирует данные с листа 'Sheet1' на лист 'Sheet2'.
*/
function copyDataBetweenSheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheetName: string = 'Sheet1';
const targetSheetName: string = 'Sheet2';
const sourceSheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getSheetByName(sourceSheetName);
const targetSheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getSheetByName(targetSheetName);
if (!sourceSheet || !targetSheet) {
Logger.log("One or both sheets not found.");
return;
}
const lastRow: number = sourceSheet.getLastRow();
const lastColumn: number = sourceSheet.getLastColumn();
if (lastRow === 0 || lastColumn === 0) {
Logger.log("Source sheet is empty.");
return;
}
const dataRange: GoogleAppsScript.Spreadsheet.Range = sourceSheet.getDataRange();
const values: any[][] = dataRange.getValues();
targetSheet.getRange(1, 1, lastRow, lastColumn).setValues(values);
Logger.log("Data copied successfully.");
}
Получение данных с другого листа в другой таблице
Метод SpreadsheetApp.openById(id): открытие таблицы по ID
Этот метод открывает таблицу по ее ID. ID таблицы можно найти в URL таблицы.
/**
* Открывает таблицу по ID.
* @param {string} id ID таблицы.
* @return {Spreadsheet} Таблица.
*/
function openSpreadsheetById(id) {
return SpreadsheetApp.openById(id);
}
Метод SpreadsheetApp.openByUrl(url): открытие таблицы по URL
Этот метод открывает таблицу по ее URL.
/**
* Открывает таблицу по URL.
* @param {string} url URL таблицы.
* @return {Spreadsheet} Таблица.
*/
function openSpreadsheetByUrl(url) {
return SpreadsheetApp.openByUrl(url);
}
Получение листа и диапазона данных (аналогично предыдущему разделу)
После открытия таблицы, получение листа и диапазона данных выполняется аналогично тому, как это делалось в предыдущем разделе.
Пример: Импорт данных из одной таблицы в другую
/**
* Импортирует данные из одной таблицы в другую.
*/
function importDataFromAnotherSpreadsheet() {
const sourceSpreadsheetId: string = 'YOUR_SOURCE_SPREADSHEET_ID';
const sourceSheetName: string = 'SourceSheet';
const targetSheetName: string = 'TargetSheet';
const targetSpreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sourceSpreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
const sourceSheet: GoogleAppsScript.Spreadsheet.Sheet = sourceSpreadsheet.getSheetByName(sourceSheetName);
const targetSheet: GoogleAppsScript.Spreadsheet.Sheet = targetSpreadsheet.getSheetByName(targetSheetName);
if (!sourceSheet || !targetSheet) {
Logger.log("One or both sheets not found.");
return;
}
const lastRow: number = sourceSheet.getLastRow();
const lastColumn: number = sourceSheet.getLastColumn();
if (lastRow === 0 || lastColumn === 0) {
Logger.log("Source sheet is empty.");
return;
}
const values: any[][] = sourceSheet.getDataRange().getValues();
targetSheet.getRange(1, 1, lastRow, lastColumn).setValues(values);
Logger.log("Data imported successfully.");
}
Безопасность: Разрешения и доступ к таблицам
Для доступа к другой таблице необходимо, чтобы у пользователя, выполняющего скрипт, были права на чтение этой таблицы. При первом запуске скрипта, обращающегося к другой таблице, Google запросит разрешение на доступ к этой таблице.
Обработка полученных данных
Итерация по массиву данных
Для обработки данных, полученных из таблицы, необходимо итерировать по массиву, возвращенному методом getValues(). Можно использовать циклы for или методы массивов, такие как forEach() и map().
Фильтрация данных на основе условий
Для фильтрации данных можно использовать метод filter() массива. Этот метод принимает функцию, определяющую условие, которому должны соответствовать элементы массива, чтобы быть включенными в результирующий массив.
Например, если у вас есть данные о рекламных кампаниях и вы хотите оставить только кампании с CTR больше 2%, код может выглядеть так:
const campaignsData = sheet.getDataRange().getValues();
const filteredCampaigns = campaignsData.filter(campaign => campaign[5] > 0.02); // Предполагаем, что CTR в 6м столбце (индекс 5)
Преобразование данных (например, изменение формата дат)
Для преобразования данных можно использовать метод map() массива. Этот метод принимает функцию, которая применяется к каждому элементу массива и возвращает новый элемент.
Например, для приведения дат к определенному формату можно использовать:
const formattedDates = dates.map(date => Utilities.formatDate(date, Session.getTimeZone(), "yyyy-MM-dd"));
Запись обработанных данных на другой лист
После обработки данных их можно записать на другой лист с помощью метода setValues(). Важно, чтобы размер массива данных соответствовал размеру диапазона, в который записываются данные.
Оптимизация и обработка ошибок
Оптимизация скорости выполнения скрипта при работе с большими объемами данных
- Используйте пакетную обработку: Вместо того, чтобы записывать данные по одной ячейке, записывайте их блоками (например, используя
setValues()для записи сразу нескольких строк). - Избегайте циклов внутри циклов: По возможности, старайтесь оптимизировать алгоритмы, чтобы избежать вложенных циклов.
- Используйте кеширование: Если данные не изменяются часто, можно закешировать их, чтобы не обращаться к таблице каждый раз.
Обработка ошибок: проверка существования листа, таблицы, диапазона
Всегда проверяйте существование листа, таблицы и диапазона перед работой с ними. Это поможет избежать ошибок и сделать скрипт более надежным.
if (sheet) {
// Работаем с листом
} else {
Logger.log("Sheet not found.");
}
Логирование и отладка скрипта
Используйте Logger.log() для логирования информации о работе скрипта. Это поможет отследить ошибки и понять, что происходит в скрипте.
Также можно использовать встроенный отладчик Apps Script для пошаговой отладки скрипта.
Примеры практического применения
Автоматическое обновление данных из мастер-таблицы
Можно создать скрипт, который будет автоматически копировать данные из мастер-таблицы в другие таблицы, например, для распространения данных среди сотрудников.
Сбор статистики из нескольких листов в сводную таблицу
Можно создать скрипт, который будет собирать статистику из нескольких листов и формировать сводную таблицу, например, для анализа данных о продажах.
Создание отчетов на основе данных из других листов
Например, можно создать отчет об эффективности рекламных кампаний, собирая данные о расходах, показах и конверсиях из разных листов.
Заключение
Краткое резюме основных моментов
В этой статье мы рассмотрели, как получать данные с другого листа в Google Apps Script. Мы научились открывать таблицы по ID и URL, получать листы, диапазоны и данные. Также мы рассмотрели вопросы оптимизации, обработки ошибок и примеры практического применения.