Google Apps Script: Как получить данные с листа?

Что такое Google Apps Script и его возможности

Google Apps Script (GAS) – это облачная среда разработки, позволяющая автоматизировать задачи и расширять функциональность приложений Google Workspace, таких как Google Sheets, Docs, Forms и других. С помощью GAS можно создавать пользовательские функции, автоматизировать обработку данных, интегрировать Google-сервисы с внешними API и многое другое. В контексте Google Sheets, GAS предоставляет мощные инструменты для чтения, записи и обработки данных.

Обзор сервиса Spreadsheet Service

Spreadsheet Service – это основной сервис в GAS, который обеспечивает доступ к Google Sheets. Он предоставляет классы и методы для работы с книгами (Spreadsheet), листами (Sheet), диапазонами (Range) и ячейками. Через Spreadsheet Service мы можем получать доступ к существующим таблицам, создавать новые, а также манипулировать данными внутри листов.

Подключение к Google Sheets из Apps Script

Прежде чем начать работать с данными, необходимо получить доступ к нужной Google Sheets. Это можно сделать несколькими способами, например, по ID таблицы или по её имени. Вот примеры кода:

/**
 * Получает активную таблицу.
 * @return {Spreadsheet} Активная таблица.
 */
function getActiveSpreadsheet(): Spreadsheet {
  return SpreadsheetApp.getActiveSpreadsheet();
}

/**
 * Получает таблицу по ID.
 * @param {string} spreadsheetId ID таблицы.
 * @return {Spreadsheet} Таблица с указанным ID, или null, если не найдена.
 */
function getSpreadsheetById(spreadsheetId: string): Spreadsheet {
  try {
    return SpreadsheetApp.openById(spreadsheetId);
  } catch (e) {
    Logger.log(`Ошибка при открытии таблицы с ID ${spreadsheetId}: ${e}`);
    return null;
  }
}

Основные методы для получения данных из листа

Получение активного листа (getActiveSheet())

Самый простой способ получить лист, с которым работает пользователь в данный момент, – использовать метод getActiveSheet(). Это вернет объект Sheet, представляющий текущий активный лист.

/**
 * Получает активный лист в активной таблице.
 * @return {Sheet} Активный лист.
 */
function getActiveSheet(): Sheet {
  const spreadsheet: Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  return spreadsheet.getActiveSheet();
}

Получение листа по имени (getSheetByName())

Если необходимо получить доступ к листу по его имени, можно использовать метод getSheetByName(). Это особенно полезно, если скрипт должен работать с конкретным листом, независимо от того, какой лист активен.

/**
 * Получает лист по его имени.
 * @param {string} sheetName Имя листа.
 * @return {Sheet} Лист с указанным именем, или null, если не найден.
 */
function getSheetByName(sheetName: string): Sheet {
  const spreadsheet: Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  return spreadsheet.getSheetByName(sheetName);
}

Получение листа по индексу (getSheet())

Листы в Google Sheets упорядочены по индексу (начиная с 0). Для получения листа по его индексу используется метод getSheet(). Этот метод менее распространен, чем предыдущие два, но может быть полезен в определенных сценариях.

/**
 * Получает лист по его индексу.
 * @param {number} index Индекс листа (начиная с 0).
 * @return {Sheet} Лист с указанным индексом, или null, если индекс некорректен.
 */
function getSheetByIndex(index: number): Sheet {
  const spreadsheet: Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheets: Sheet[] = spreadsheet.getSheets();
  if (index >= 0 && index < sheets.length) {
    return sheets[index];
  } else {
    Logger.log(`Лист с индексом ${index} не найден.`);
    return null;
  }
}

Получение значений ячеек и диапазонов

Метод getValue() для получения значения одной ячейки

Для получения значения из одной конкретной ячейки используется метод getValue(). Этот метод возвращает значение ячейки в виде строки, числа, даты или логического значения, в зависимости от формата ячейки.

/**
 * Получает значение из ячейки. 
 * @param {Sheet} sheet Лист, из которого нужно получить значение.
 * @param {number} row Номер строки (начиная с 1).
 * @param {number} column Номер столбца (начиная с 1).
 * @return {any} Значение ячейки.
 */
function getCellValue(sheet: Sheet, row: number, column: number): any {
  return sheet.getRange(row, column).getValue();
}

Метод getValues() для получения значений диапазона ячеек

Для получения значений из диапазона ячеек используется метод getValues(). Этот метод возвращает двумерный массив, где каждая строка массива представляет строку диапазона, а каждый элемент строки – значение ячейки.

/**
 * Получает значения из диапазона ячеек.
 * @param {Sheet} sheet Лист, из которого нужно получить значения.
 * @param {number} startRow Номер первой строки (начиная с 1).
 * @param {number} startColumn Номер первого столбца (начиная с 1).
 * @param {number} numRows Количество строк.
 * @param {number} numColumns Количество столбцов.
 * @return {any[][]} Двумерный массив значений.
 */
function getRangeValues(sheet: Sheet, startRow: number, startColumn: number, numRows: number, numColumns: number): any[][] {
  return sheet.getRange(startRow, startColumn, numRows, numColumns).getValues();
}

Работа с диапазонами: A1 нотация и числовые координаты

В GAS для указания диапазонов можно использовать два основных способа:

A1 нотация: Например, "A1:C5" – это диапазон от ячейки A1 до ячейки C5.

Числовые координаты: Указываются номер строки и номер столбца, начиная с 1. Например, getRange(1, 1, 5, 3) эквивалентно "A1:C5" в A1 нотации.

Чтение данных из всего листа

Чтобы прочитать все данные из листа, можно использовать метод getDataRange().getValues(). Это вернет двумерный массив, содержащий все значения из листа, включая пустые ячейки.

/**
 * Получает все данные из листа.
 * @param {Sheet} sheet Лист, из которого нужно получить данные.
 * @return {any[][]} Двумерный массив всех данных листа.
 */
function getAllData(sheet: Sheet): any[][] {
  return sheet.getDataRange().getValues();
}

Обработка полученных данных

Преобразование полученных данных: типы данных в Apps Script

При получении данных из Google Sheets, важно учитывать, что GAS автоматически преобразует данные в соответствующие типы: строки, числа, даты, логические значения. Пустые ячейки возвращаются как пустые строки ("").

Использование циклов для обработки данных из диапазона

Для обработки данных, полученных из диапазона (двумерного массива), обычно используются циклы for. Это позволяет итерировать по каждой строке и каждой ячейке в строке.

/**
 * Выводит в лог каждое значение из двумерного массива.
 * @param {any[][]} data Двумерный массив данных.
 */
function processData(data: any[][]) {
  for (let i = 0; i < data.length; i++) {
    for (let j = 0; j < data[i].length; j++) {
      Logger.log(`Ячейка [${i + 1}, ${j + 1}]: ${data[i][j]}`);
    }
  }
}

Примеры обработки данных: фильтрация, сортировка, агрегация

Рассмотрим пример фильтрации данных, полученных из Google Sheets, для отображения только тех строк, где значение в определенном столбце соответствует заданному критерию. Представим, что у нас есть таблица с данными о рекламных кампаниях: Название кампании, CTR, CPC, бюджет.

/**
 * Фильтрует данные рекламных кампаний по CTR.
 * @param {any[][]} campaignData Данные о рекламных кампаниях (Название, CTR, CPC, Бюджет).
 * @param {number} minCTR Минимальный CTR для фильтрации.
 * @return {any[][]} Отфильтрованные данные кампаний.
 */
function filterCampaignsByCTR(campaignData: any[][], minCTR: number): any[][] {
  const filteredCampaigns: any[][] = [];
  // Пропускаем строку заголовков
  for (let i = 1; i = minCTR) {
      filteredCampaigns.push(campaign);
    }
  }
  return filteredCampaigns;
}

Продвинутые техники и оптимизация

Оптимизация скорости чтения данных

При работе с большими объемами данных, скорость чтения может стать узким местом. Чтобы оптимизировать скорость, рекомендуется:

Избегать чтения отдельных ячеек в цикле (использовать getValues() для диапазонов).

Минимизировать количество обращений к Spreadsheet Service.

Использовать batch-операции, где это возможно.

Использование SpreadsheetApp.flush()

Метод SpreadsheetApp.flush() не имеет прямого отношения к чтению данных, он применяется при записи данных в Google Sheets. Однако, важно понимать, что GAS выполняет операции пакетно. SpreadsheetApp.flush() принудительно отправляет все накопившиеся изменения в Google Sheets немедленно. Обычно, этот метод не требуется при чтении данных, но может быть полезен при комбинированных операциях чтения и записи для обеспечения актуальности данных.

Обработка ошибок при чтении данных

При чтении данных всегда следует учитывать возможность ошибок. Например, лист может не существовать, или диапазон может быть некорректным. Для обработки ошибок используйте блоки try...catch.

function readDataSafely(sheetName: string): any[][] | null {
  try {
    const sheet: Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    if (!sheet) {
      Logger.log(`Лист с именем ${sheetName} не найден.`);
      return null;
    }
    return sheet.getDataRange().getValues();
  } catch (e) {
    Logger.log(`Ошибка при чтении данных с листа ${sheetName}: ${e}`);
    return null;
  }
}