Google Apps Script: Как сделать запрос к таблице?

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

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

Зачем использовать Apps Script для работы с Google Sheets

Apps Script предоставляет мощные инструменты для работы с Google Sheets, превосходящие стандартные возможности таблиц. С его помощью можно:

Автоматизировать рутинные задачи (например, сбор и обработка данных).

Создавать сложные отчеты и аналитику.

Интегрировать Google Sheets с другими сервисами.

Реализовать пользовательские функции и инструменты.

Необходимые условия: доступ к Google Sheets и базовые знания JavaScript

Для работы с Apps Script необходимы:

Аккаунт Google с доступом к Google Sheets.

Базовые знания JavaScript (переменные, типы данных, операторы, функции, циклы).

Представление о структуре Google Sheets (таблицы, листы, ячейки).

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

Получение активной таблицы и листа

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

/**
 * @return {GoogleAppsScript.Spreadsheet.Sheet} Активный лист.
 */
function getActiveSheet(): GoogleAppsScript.Spreadsheet.Sheet {
  const spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = spreadsheet.getActiveSheet();
  return sheet;
}

/**
 * @return {GoogleAppsScript.Spreadsheet.Spreadsheet} Активная таблица.
 */
function getActiveSpreadsheet(): GoogleAppsScript.Spreadsheet.Spreadsheet {
  return SpreadsheetApp.getActiveSpreadsheet();
}

Чтение данных из ячеек: getValue(), getValues()

Для чтения данных из ячеек используются методы getValue() (для одной ячейки) и getValues() (для диапазона ячеек):

/**
 * Читает значение из указанной ячейки.
 * @param {number} row Номер строки (начиная с 1).
 * @param {number} column Номер столбца (начиная с 1).
 * @return {any} Значение ячейки.
 */
function readCellValue(row: number, column: number): any {
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = getActiveSheet();
  return sheet.getRange(row, column).getValue();
}

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

Определение размеров таблицы: getLastRow(), getLastColumn()

Чтобы узнать размеры таблицы (количество строк и столбцов), используются методы getLastRow() и getLastColumn():

/**
 * @return {number} Номер последней строки с данными.
 */
function getLastRow(): number {
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = getActiveSheet();
  return sheet.getLastRow();
}

/**
 * @return {number} Номер последнего столбца с данными.
 */
function getLastColumn(): number {
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = getActiveSheet();
  return sheet.getLastColumn();
}

Перебор строк и столбцов таблицы

Для обработки всех данных в таблице необходимо перебрать строки и столбцы. Пример:

/**
 *  Пример перебора строк и столбцов таблицы
 */
function processTableData() {
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = getActiveSheet();
  const lastRow: number = sheet.getLastRow();
  const lastColumn: number = sheet.getLastColumn();
  
  for (let i: number = 1; i <= lastRow; i++) {
    for (let j: number = 1; j <= lastColumn; j++) {
      const cellValue: any = sheet.getRange(i, j).getValue();
      // Обработка значения ячейки
      Logger.log(`Row: ${i}, Column: ${j}, Value: ${cellValue}`);
    }
  }
}

Создание и выполнение запросов к таблице с использованием SQL-подобного синтаксиса

Функция QUERY в Google Sheets: обзор и возможности

Функция QUERY в Google Sheets позволяет выполнять SQL-подобные запросы к данным в таблице. Она предоставляет возможности фильтрации, сортировки, выборки и агрегации данных. Ключевые возможности:

SELECT: Выбор столбцов для отображения.

WHERE: Фильтрация строк по условию.

ORDER BY: Сортировка результатов.

LIMIT: Ограничение количества возвращаемых строк.

Использование QUERY в Apps Script: создание запроса

В Apps Script функция QUERY вызывается через метод QUERY объекта SpreadsheetApp. Пример создания запроса:

/**
 * Выполняет запрос QUERY к данным таблицы.
 * @param {string} query Строка запроса.
 * @return {any[][]} Результат запроса.
 */
function executeQuery(query: string): any[][] {
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = getActiveSheet();
  const range: GoogleAppsScript.Spreadsheet.Range = sheet.getDataRange();
  const data: any[][] = range.getValues();

  // Создаем временный лист для выполнения запроса
  const newSheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
  newSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
  
  // Выполняем запрос QUERY
  const queryRange: GoogleAppsScript.Spreadsheet.Range = newSheet.getRange("A1:" + String.fromCharCode(64 + sheet.getLastColumn()) + sheet.getLastRow()); // Динамическое определение диапазона
  const resultRange: GoogleAppsScript.Spreadsheet.Range = newSheet.getRange("Z1"); // Временная ячейка для результата
  
  resultRange.setFormula('=QUERY(' + queryRange.getA1Notation() + ', "' + query + '", 1)');

  // Читаем результаты запроса
  const result: any[][] = newSheet.getDataRange().getValues();  // Изменяем на чтение всего листа
  
  SpreadsheetApp.getActiveSpreadsheet().deleteSheet(newSheet);
  // Возвращаем все данные, начиная со второй строки (пропускаем заголовки)
  return result.slice(1);
}

Синтаксис запросов: SELECT, WHERE, ORDER BY, LIMIT

SELECT column1, column2, ...: Выбор определенных столбцов.

WHERE condition: Фильтрация строк по условию (например, WHERE A > 10).

ORDER BY column ASC|DESC: Сортировка по столбцу в возрастающем (ASC) или убывающем (DESC) порядке.

LIMIT number: Ограничение количества возвращаемых строк.

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

Выбор всех данных, где значение в столбце A больше 10: SELECT * WHERE A > 10

Выбор столбцов A и C, отсортированных по столбцу B в убывающем порядке: SELECT A, C ORDER BY B DESC

Выбор первых 5 строк, где значение в столбце C равно "active": SELECT * WHERE C = 'active' LIMIT 5

Продвинутые техники работы с данными

Динамические запросы: передача параметров из Apps Script в запрос

Для создания динамических запросов можно использовать конкатенацию строк и передавать параметры из Apps Script в строку запроса:

/**
 * Пример динамического запроса с передачей параметра.
 * @param {string} status Значение статуса для фильтрации.
 * @return {any[][]} Результат запроса.
 */
function getOrdersByStatus(status: string): any[][] {
  const query: string = `SELECT * WHERE C = '${status}'`;
  return executeQuery(query);
}

Обработка ошибок при выполнении запросов

При выполнении запросов могут возникать ошибки (например, неверный синтаксис). Рекомендуется использовать блоки try...catch для обработки ошибок:

try {
  const result: any[][] = executeQuery("SELECT * WHERE A > 'abc'");
  Logger.log(result);
} catch (e) {
  Logger.log("Ошибка при выполнении запроса: " + e);
}

Работа с датами и числами в запросах

При работе с датами и числами в запросах необходимо учитывать их формат. Для дат рекомендуется использовать функцию DATE в запросе. Числа обычно распознаются автоматически.

Использование нескольких условий в WHERE

В секции WHERE можно использовать несколько условий, объединенных операторами AND и OR:

SELECT * WHERE A > 10 AND B < 20

SELECT * WHERE C = 'active' OR D = 'pending'

Примеры практического применения запросов Apps Script к Google Sheets

Автоматическое создание отчетов на основе данных таблицы

Можно создавать отчеты, извлекая данные из таблицы с помощью запросов и форматируя их в Google Docs или Slides.

Фильтрация и обработка данных для отправки уведомлений

Можно фильтровать данные по определенным критериям (например, просроченные задачи) и отправлять уведомления по электронной почте.

Интеграция с другими сервисами Google (Docs, Calendar)

Можно интегрировать Google Sheets с другими сервисами Google, например, создавать события в Calendar на основе данных из таблицы или генерировать документы в Docs.