Что такое Google Apps Script и его возможности
Google Apps Script – это облачный язык сценариев, позволяющий автоматизировать задачи в Google Workspace (Sheets, Docs, Forms, Calendar и т.д.). С его помощью можно создавать собственные функции, расширять функциональность приложений Google и интегрировать их с другими сервисами. Например, Apps Script может использоваться для автоматической отправки email-рассылок, создания пользовательских отчетов или синхронизации данных между различными приложениями.
Подключение Google Apps Script к Google Sheets
Для начала работы с Google Apps Script и Google Sheets, откройте нужную таблицу в Google Sheets. Затем выберите Инструменты > Редактор скриптов. Откроется новое окно редактора, в котором вы будете писать код Apps Script. Этот скрипт будет непосредственно связан с текущей таблицей.
Основные объекты Google Sheets: Spreadsheet, Sheet, Range
В Google Apps Script для работы с таблицами используются следующие основные объекты:
Spreadsheet: Представляет собой всю таблицу Google Sheets.
Sheet: Представляет собой отдельный лист в таблице.
Range: Представляет собой диапазон ячеек, включая отдельные ячейки и целые строки или столбцы.
Выбор строки в таблице по номеру
Получение активного листа
Прежде чем выбирать строку, необходимо получить доступ к нужному листу таблицы. Самый простой способ – получить активный лист, то есть тот, который открыт в данный момент. Это можно сделать с помощью следующего кода:
/**
* Получает активный лист таблицы.
* @return {GoogleAppsScript.Spreadsheet.Sheet} Активный лист.
*/
function getActiveSheet(): GoogleAppsScript.Spreadsheet.Sheet {
const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
return sheet;
}Использование `getSheetByName()` для получения листа по имени
Если вам нужен конкретный лист по имени, используйте метод getSheetByName():
/**
* Получает лист таблицы по имени.
* @param {string} sheetName Имя листа.
* @return {GoogleAppsScript.Spreadsheet.Sheet} Лист таблицы.
*/
function getSheetByName(sheetName: string): GoogleAppsScript.Spreadsheet.Sheet {
const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet: GoogleAppsScript.Spreadsheet.Sheet | null = ss.getSheetByName(sheetName);
if (!sheet) {
throw new Error(`Sheet with name '${sheetName}' not found.`);
}
return sheet;
}Метод `getRange(row, column)` для выбора ячейки в строке
Для доступа к ячейке в строке используется метод getRange(row, column), который возвращает объект Range. Например, чтобы получить ячейку в первой строке и первом столбце (A1), используйте getRange(1, 1).
Чтобы выбрать всю строку, укажите только номер строки в методе getRange(row, column, numRows, numColumns). Например, для выбора первой строки (строка 1), покрывающей все столбцы с данными, можно использовать следующий код:
/**
* Получает всю строку по номеру.
* @param {number} rowNumber Номер строки.
* @return {GoogleAppsScript.Spreadsheet.Range} Выбранная строка.
*/
function getRow(rowNumber: number): GoogleAppsScript.Spreadsheet.Range {
const sheet: GoogleAppsScript.Spreadsheet.Sheet = getActiveSheet();
const lastColumn: number = sheet.getLastColumn();
const rowRange: GoogleAppsScript.Spreadsheet.Range = sheet.getRange(rowNumber, 1, 1, lastColumn);
return rowRange;
}Метод `getRow()` для получения номера строки активной ячейки.
Метод getRow() позволяет получить номер строки, в которой находится активная ячейка:
/**
* Получает номер строки активной ячейки.
* @return {number} Номер строки.
*/
function getActiveRowNumber(): number {
const sheet: GoogleAppsScript.Spreadsheet.Sheet = getActiveSheet();
const activeCell: GoogleAppsScript.Spreadsheet.Range = sheet.getActiveCell();
return activeCell.getRow();
}Выбор строки на основе условия
Перебор строк таблицы с помощью цикла `for`
Для поиска строки, удовлетворяющей определенному условию, необходимо перебрать все строки таблицы. Для этого используется цикл for. Необходимо знать общее количество строк или использовать getLastRow() для определения последней строки с данными.
Чтение значения ячейки в каждой строке
Внутри цикла необходимо прочитать значение ячейки в каждой строке, чтобы проверить, соответствует ли оно условию. Используйте метод getValue() объекта Range для получения значения ячейки.
Применение условного оператора `if` для проверки условия
После получения значения ячейки используйте условный оператор if для проверки, соответствует ли значение заданному условию.
Сохранение номера строки, соответствующей условию
Если условие выполнено, сохраните номер строки в переменную, чтобы использовать его позже. Если нужно найти все строки, соответствующие условию, можно использовать массив для хранения номеров строк.
/**
* Находит строки в таблице, соответствующие заданному условию.
* @param {string} columnName Имя столбца для поиска.
* @param {string} searchValue Значение для поиска.
* @return {number[]} Массив номеров строк, соответствующих условию.
*/
function findRowsByValue(columnName: string, searchValue: string): number[] {
const sheet: GoogleAppsScript.Spreadsheet.Sheet = getSheetByName('Лист1');
const lastRow: number = sheet.getLastRow();
const column: number = findColumnIndexByName(sheet, columnName);
const matchingRows: number[] = [];
if (column === -1) {
Logger.log(`Column '${columnName}' not found.`);
return [];
}
for (let i: number = 2; i <= lastRow; i++) { // Начинаем со второй строки, предполагая наличие заголовка
const cellValue: string = sheet.getRange(i, column).getValue().toString();
if (cellValue === searchValue) {
matchingRows.push(i);
}
}
return matchingRows;
}
/**
* Вспомогательная функция для поиска индекса столбца по имени.
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet Лист таблицы.
* @param {string} columnName Имя столбца.
* @return {number} Индекс столбца (начиная с 1) или -1, если столбец не найден.
*/
function findColumnIndexByName(sheet: GoogleAppsScript.Spreadsheet.Sheet, columnName: string): number {
const lastColumn: number = sheet.getLastColumn();
const headerRange: GoogleAppsScript.Spreadsheet.Range = sheet.getRange(1, 1, 1, lastColumn);
const headerValues: string[][] = headerRange.getValues() as string[][];
for (let i: number = 0; i < lastColumn; i++) {
if (headerValues[0][i] === columnName) {
return i + 1; // Индексы в Google Apps Script начинаются с 1
}
}
return -1; // Столбец не найден
}Примеры практического применения выбора строк
Выделение строки, содержащей определенное значение
После нахождения строки, содержащей определенное значение, можно выделить ее цветом, чтобы визуально отметить ее. Используйте метод setBackground() объекта Range для изменения цвета фона строки.
Копирование строки в другой лист
Можно скопировать строку, соответствующую определенному условию, в другой лист таблицы. Для этого получите значения ячеек в строке с помощью метода getValues() и затем вставьте их в другой лист с помощью метода setValues().
Удаление строки, не соответствующей условию
Можно удалить строки, не соответствующие определенному условию. Будьте осторожны при удалении строк, так как это действие необратимо! Используйте метод deleteRow() объекта Sheet для удаления строки.
Заключение и лучшие практики
Рекомендации по оптимизации кода при работе с большими таблицами
При работе с большими таблицами важно оптимизировать код, чтобы избежать замедления работы скрипта.
Используйте пакетную обработку данных: вместо чтения и записи каждой ячейки по отдельности, считывайте и записывайте диапазоны ячеек.
Избегайте лишних вызовов API: старайтесь минимизировать количество обращений к сервисам Google Apps Script.
Используйте кэширование: если данные не изменяются часто, кэшируйте их, чтобы избежать повторных запросов.
Обзор распространенных ошибок и способов их устранения
Ошибка "Service invoked too many times for one day": Ограничение на количество вызовов API. Разбивайте задачи на более мелкие части и выполняйте их постепенно.
Ошибка "TypeError: Cannot read property ‘…’ of null": Неправильная обработка null значений. Проверяйте значения на null перед их использованием.
Неправильный индекс строки или столбца: Убедитесь, что индексы строк и столбцов начинаются с 1.