Google Apps Script для Google Sheets: как автоматизировать задачи?

Что такое Google Apps Script и его преимущества

Google Apps Script (GAS) — это облачный язык сценариев, основанный на JavaScript, который позволяет автоматизировать задачи и расширять функциональность приложений Google Workspace, включая Google Sheets. Его ключевые преимущества:

• Автоматизация рутинных операций: GAS позволяет автоматизировать повторяющиеся задачи, такие как форматирование данных, отправка электронных писем и создание отчетов.
• Интеграция с другими сервисами Google: GAS тесно интегрирован с другими сервисами Google Workspace (Drive, Calendar, Gmail и т.д.), что позволяет создавать комплексные решения.
• Расширение функциональности Google Sheets: Можно создавать собственные функции, меню и диалоговые окна, адаптируя Google Sheets под конкретные нужды.
• Облачная платформа: Скрипты выполняются в облаке, что обеспечивает их доступность и независимость от локального компьютера.

Связь Google Apps Script с Google Sheets: возможности автоматизации

Google Apps Script предоставляет мощные инструменты для автоматизации задач в Google Sheets. Можно автоматизировать:

• Обновление данных в реальном времени из внешних источников.
• Форматирование и валидацию данных.
• Рассылку отчетов по расписанию.
• Создание интерактивных дашбордов.
• Интеграцию с CRM и другими системами.

Начало работы: открытие редактора Apps Script из Google Sheets

Чтобы открыть редактор Apps Script, в Google Sheets выберите Инструменты > Редактор скриптов. Откроется новое окно с интегрированной средой разработки (IDE), где можно писать и запускать скрипты.

Основы работы с Google Apps Script в Google Sheets

Объектная модель Google Sheets: Spreadsheet, Sheet, Range

Для работы с Google Sheets через Apps Script необходимо понимать объектную модель:

• Spreadsheet: Представляет собой всю таблицу Google Sheets. SpreadsheetApp.getActiveSpreadsheet() возвращает активную таблицу.
• Sheet: Представляет собой отдельный лист в таблице. spreadsheet.getSheetByName('ИмяЛиста') позволяет получить лист по имени.
• Range: Представляет собой диапазон ячеек. sheet.getRange('A1:B10') выбирает диапазон ячеек.

Чтение и запись данных в ячейки: методы getValue(), setValue(), getValues(), setValues()

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

/**
 * Читает значение из ячейки A1 и записывает его в ячейку B1.
 */
function copyValue() {
  // Get the active spreadsheet.
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // Get the active sheet.
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  // Read the value from cell A1.
  const value: any = sheet.getRange('A1').getValue();
  // Write the value to cell B1.
  sheet.getRange('B1').setValue(value);
}

getValue() и setValue() работают с одной ячейкой, а getValues() и setValues() — с диапазонами. getValues() возвращает двумерный массив.

Работа с диапазонами: выбор, изменение размеров, форматирование

Можно выбирать диапазоны, изменять их размеры и форматировать:

/**
 * Изменяет цвет фона диапазона A1:C10 на серый.
 */
function formatRange() {
  // Get the active spreadsheet.
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // Get the active sheet.
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  // Get the range A1:C10.
  const range: GoogleAppsScript.Spreadsheet.Range = sheet.getRange('A1:C10');
  // Set the background color to gray.
  range.setBackground('#cccccc');
}

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

Циклы for и while используются для итерации по диапазонам данных:

/**
 * Суммирует значения в столбце A и записывает результат в ячейку B1.
 */
function sumColumn() {
  // Get the active spreadsheet.
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // Get the active sheet.
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  // Get the last row in column A.
  const lastRow: number = sheet.getLastRow();
  // Initialize the sum.
  let sum: number = 0;
  // Iterate over the cells in column A.
  for (let i: number = 1; i <= lastRow; i++) {
    // Read the value from the cell.
    const value: any = sheet.getRange(i, 1).getValue();
    // Check if the value is a number.
    if (typeof value === 'number') {
      // Add the value to the sum.
      sum += value;
    }
  }
  // Write the sum to cell B1.
  sheet.getRange('B1').setValue(sum);
}

Автоматизация распространенных задач в Google Sheets с помощью Apps Script

Автоматическая отправка email-уведомлений на основе изменений в таблице

Можно отправлять email-уведомления при изменении данных в таблице:

/**
 * Sends an email notification when a cell in column A is edited.
 *
 * @param {GoogleAppsScript.Events.SheetsOnEditEvent} e The event object.
 */
function onEdit(e: GoogleAppsScript.Events.SheetsOnEditEvent) {
  // Get the edited range.
  const range: GoogleAppsScript.Spreadsheet.Range = e.range;
  // Get the column of the edited range.
  const column: number = range.getColumn();
  // Check if the edited column is column A.
  if (column === 1) {
    // Get the edited value.
    const value: any = range.getValue();
    // Get the email address.
    const email: string = 'example@example.com';
    // Get the subject.
    const subject: string = 'Изменение в таблице';
    // Get the body.
    const body: string = `В ячейке ${range.getA1Notation()} изменено значение на ${value}.`;
    // Send the email.
    MailApp.sendEmail(email, subject, body);
  }
}

Создание пользовательских функций (custom functions) для Google Sheets

Можно создавать собственные функции, которые можно использовать непосредственно в ячейках Google Sheets:

/**
 * Возвращает курс доллара к рублю.
 *
 * @customfunction
 */
function GET_DOLLAR_RATE(): number {
  // Get the JSON data from the API.
  const response: GoogleAppsScript.URL_Fetch.HTTPResponse = UrlFetchApp.fetch('https://www.cbr-xml-daily.ru/daily_json.js');
  // Parse the JSON data.
  const json: any = JSON.parse(response.getContentText());
  // Get the dollar rate.
  const rate: number = json.Valute.USD.Value;
  // Return the dollar rate.
  return rate;
}

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

Apps Script позволяет автоматизировать фильтрацию, сортировку и агрегацию данных в Google Sheets. Например, можно отфильтровать строки по определенному условию и скопировать их на другой лист.

Импорт данных из внешних источников (например, API) в Google Sheets

Apps Script позволяет импортировать данные из внешних источников, таких как API, в Google Sheets. Например, можно получать данные о рекламных кампаниях из Google Ads API и записывать их в таблицу.

Триггеры в Google Apps Script: автоматический запуск скриптов

Типы триггеров: по времени, при открытии, при изменении, при отправке формы

Триггеры позволяют автоматически запускать скрипты при определенных событиях:

• По времени: Запуск скрипта по расписанию (например, каждый час или каждый день).
• При открытии: Запуск скрипта при открытии таблицы.
• При изменении: Запуск скрипта при изменении данных в таблице.
• При отправке формы: Запуск скрипта при отправке Google Forms.

Создание и настройка триггеров программно и через интерфейс редактора

Триггеры можно создавать как программно (через Apps Script), так и через интерфейс редактора (Редактор скриптов > Триггеры).

Ограничения и best practices при работе с триггерами

При работе с триггерами следует учитывать ограничения на время выполнения скрипта и количество запросов к сервисам Google. Рекомендуется оптимизировать код и использовать пакетную обработку данных.

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

Создание системы учета задач с автоматическим оповещением ответственных

Можно создать систему учета задач, где при изменении статуса задачи ответственному автоматически отправляется email-уведомление.

Автоматическое формирование отчетов на основе данных из нескольких листов

Можно автоматически формировать отчеты, агрегируя данные из нескольких листов и отправляя их по расписанию.

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

Можно интегрировать Google Sheets с Google Calendar для создания событий на основе данных в таблице или с Google Drive для автоматического создания и сохранения файлов.