Использование Apps Script в Google Sheets: Полное руководство

  •   
  •   16776
  •   9 мин
  •   171

Введение в Apps Script и Google Sheets

Что такое Apps Script и зачем он нужен в Google Sheets?

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

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

Преимущества использования Apps Script для автоматизации задач

Использование Apps Script в Google Sheets предоставляет ряд преимуществ:

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

Доступ к редактору Apps Script из Google Sheets

Чтобы получить доступ к редактору Apps Script из Google Sheets, выполните следующие действия:

  1. Откройте Google Sheets.
  2. Выберите «Инструменты» > «Редактор скриптов».

Откроется новое окно с редактором Apps Script, где можно писать и запускать скрипты.

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

Структура скрипта Apps Script: функции, переменные, объекты

Скрипт Apps Script состоит из функций, переменных и объектов.

  • Функции – это блоки кода, выполняющие определенные задачи. Они определяются с помощью ключевого слова function.
  • Переменные – это контейнеры для хранения данных. Они объявляются с помощью ключевых слов var, let или const.
  • Объекты – это коллекции свойств и методов. В Apps Script есть множество встроенных объектов для работы с Google Sheets, таких как SpreadsheetApp, Sheet, Range.

Пример структуры скрипта:

/**
 * Функция для получения имени активного листа.
 * @return {string} Имя активного листа.
 */
function getActiveSheetName(): string {
  // Получаем объект активной таблицы.
  const spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // Получаем объект активного листа.
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = spreadsheet.getActiveSheet();
  // Возвращаем имя листа.
  return sheet.getName();
}

Объект SpreadsheetApp: доступ к таблицам и листам

Объект SpreadsheetApp – это основной объект для работы с Google Sheets в Apps Script. Он предоставляет методы для доступа к таблицам, листам и ячейкам.

Основные методы SpreadsheetApp:

  • SpreadsheetApp.getActiveSpreadsheet(): Возвращает активную таблицу.
  • SpreadsheetApp.openById(id): Открывает таблицу по ее ID.
  • SpreadsheetApp.create(name): Создает новую таблицу.

Работа с листами (Sheets): получение, создание, удаление

Объект Sheet представляет лист в таблице.

Основные методы для работы с листами:

  • Spreadsheet.getSheetByName(name): Возвращает лист по имени.
  • Spreadsheet.getActiveSheet(): Возвращает активный лист.
  • Spreadsheet.insertSheet(name): Создает новый лист.
  • Sheet.deleteSheet(sheet): Удаляет лист.

Пример:

function createNewSheet() {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  ss.insertSheet('New Sheet');
}

Работа с диапазонами (Ranges): чтение и запись данных

Объект Range представляет диапазон ячеек на листе.

Основные методы для работы с диапазонами:

  • Sheet.getRange(row, column): Возвращает диапазон, состоящий из одной ячейки.
  • Sheet.getRange(row, column, numRows, numColumns): Возвращает диапазон, состоящий из нескольких ячеек.
  • Range.getValue(): Возвращает значение ячейки.
  • Range.getValues(): Возвращает значения диапазона в виде двумерного массива.
  • Range.setValue(value): Устанавливает значение ячейки.
  • Range.setValues(values): Устанавливает значения диапазона из двумерного массива.

Пример:

function readAndWriteData() {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  const range: GoogleAppsScript.Spreadsheet.Range = sheet.getRange(1, 1, 5, 5);
  const values: any[][] = range.getValues();

  // Обработка данных.
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      values[i][j] = values[i][j] + ' обработано';
    }
  }

  sheet.getRange(1, 6, 5, 5).setValues(values);
}

Обработка ошибок и отладка скриптов

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

Для отладки скриптов можно использовать встроенный отладчик в редакторе Apps Script. Он позволяет устанавливать точки останова, просматривать значения переменных и выполнять код построчно.

Чтение данных из Google Sheets с помощью Apps Script

Получение значений из ячеек, строк и столбцов

Для получения данных из Google Sheets можно использовать следующие методы:

  • Range.getValue(): Возвращает значение из одной ячейки.
  • Range.getValues(): Возвращает массив значений из диапазона.

Пример:

function getValueFromCell() {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  const value: any = sheet.getRange(1, 1).getValue();
  Logger.log(value);
}

Получение данных из диапазона ячеек (A1 нотация и индексы)

Для получения данных из диапазона ячеек можно использовать A1 нотацию (например, «A1:C5») или индексы строк и столбцов.

Пример:

function getDataFromRange() {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  // A1 notation.
  const valuesA1: any[][] = sheet.getRange('A1:C5').getValues();
  // Indices.
  const valuesIndices: any[][] = sheet.getRange(1, 1, 5, 3).getValues();
  Logger.log(valuesA1);
  Logger.log(valuesIndices);
}

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

Для обработки данных в таблице можно использовать циклы for, while или forEach.

Пример:

function processDataInTable() {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  const lastRow: number = sheet.getLastRow();
  const values: any[][] = sheet.getRange(1, 1, lastRow, 1).getValues();

  for (let i = 0; i < values.length; i++) {
    Logger.log(values[i][0]);
  }
}

Фильтрация данных при чтении

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

Пример:

function filterData() {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  const lastRow: number = sheet.getLastRow();
  const values: any[][] = sheet.getRange(1, 1, lastRow, 2).getValues();

  const filteredValues: any[][] = values.filter(function(row) {
    return row[1] > 10; // Фильтруем строки, где значение во втором столбце больше 10.
  });

  Logger.log(filteredValues);
}

Запись данных в Google Sheets с помощью Apps Script

Запись значений в ячейки, строки и столбцы

Для записи данных в Google Sheets можно использовать следующие методы:

  • Range.setValue(value): Устанавливает значение в ячейке.
  • Range.setValues(values): Устанавливает значения в диапазоне ячеек.

Пример:

function writeValueToCell() {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  sheet.getRange(1, 1).setValue('Hello, Apps Script!');
}

Запись данных в диапазон ячеек

Для записи данных в диапазон ячеек необходимо передать двумерный массив значений методу Range.setValues().

Пример:

function writeDataToRange() {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  const data: any[][] = [
    ['Name', 'Age', 'City'],
    ['John', 30, 'New York'],
    ['Jane', 25, 'London']
  ];
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

Добавление новых строк и столбцов

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

  • Sheet.insertRowBefore(rowPosition): Вставляет новую строку перед указанной строкой.
  • Sheet.insertColumnBefore(columnPosition): Вставляет новый столбец перед указанным столбцом.
  • Sheet.appendRow(content): Добавляет новую строку в конец листа.

Пример:

function addNewRow() {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  sheet.appendRow(['New Value 1', 'New Value 2']);
}

Форматирование данных при записи (цвет, шрифт, выравнивание)

При записи данных можно задавать различные параметры форматирования, такие как цвет, шрифт, выравнивание.

Основные методы для форматирования:

  • Range.setBackground(color): Устанавливает цвет фона ячейки.
  • Range.setFontColor(color): Устанавливает цвет шрифта ячейки.
  • Range.setFontSize(size): Устанавливает размер шрифта ячейки.
  • Range.setFontWeight(weight): Устанавливает жирность шрифта ячейки.
  • Range.setHorizontalAlignment(alignment): Устанавливает горизонтальное выравнивание ячейки.

Пример:

function formatData() {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  const range: GoogleAppsScript.Spreadsheet.Range = sheet.getRange(1, 1);
  range.setBackground('red');
  range.setFontColor('white');
  range.setFontSize(14);
  range.setFontWeight('bold');
  range.setHorizontalAlignment('center');
}

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

Создание пользовательских функций (Custom Functions)

Apps Script позволяет создавать пользовательские функции, которые можно использовать в формулах Google Sheets. Пользовательские функции должны начинаться с аннотации @customfunction.

Пример:

/**
 * Складывает два числа.
 * @customfunction
 */
function MY_SUM(a: number, b: number): number {
  return a + b;
}

Триггеры: автоматический запуск скриптов при определенных событиях (onOpen, onEdit, onChange, time-driven)

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

  • onOpen: Запускается при открытии таблицы.
  • onEdit: Запускается при редактировании ячейки.
  • onChange: Запускается при изменении структуры таблицы.
  • Time-driven: Запускается по расписанию.

Триггеры можно создавать вручную в редакторе Apps Script или программно с помощью сервиса ScriptApp.

Примеры автоматизации: отправка email уведомлений, создание отчетов, синхронизация данных с другими сервисами

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

  • Отправка email уведомлений: Автоматическая отправка уведомлений о событиях в таблице (например, при изменении статуса заказа).
  • Создание отчетов: Автоматическое создание еженедельных или ежемесячных отчетов на основе данных в таблице.
  • Синхронизация данных с другими сервисами: Автоматическая синхронизация данных между Google Sheets и другими сервисами, такими как CRM, базы данных, системы аналитики.

Примеры использования Apps Script в Google Sheets

Пример 1: Автоматическая отправка email-рассылки из списка контактов в таблице

function sendEmailNewsletter() {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  const lastRow: number = sheet.getLastRow();
  const data: any[][] = sheet.getRange(2, 1, lastRow - 1, 2).getValues(); // Предполагаем, что в первом столбце email, во втором - имя.

  for (let i = 0; i < data.length; i++) {
    const email: string = data[i][0];
    const name: string = data[i][1];
    const subject: string = 'Newsletter';
    const body: string = `Dear ${name},

This is a newsletter.

Regards,
Your Company`;

    MailApp.sendEmail(email, subject, body);
  }
}

Пример 2: Создание автоматического еженедельного отчета с графиками

function createWeeklyReport() {
  // TODO: implement.
}

Пример 3: Интеграция с Google Calendar для автоматического создания событий

function createCalendarEvents() {
  // TODO: implement.
}

Пример 4: Импорт данных из внешнего API в Google Sheets

function importDataFromAPI() {
  const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
  const url: string = 'https://api.example.com/data'; // Замените на URL вашего API.

  const response: GoogleAppsScript.URL_Fetch.HTTPResponse = UrlFetchApp.fetch(url);
  const json: any = JSON.parse(response.getContentText());

  // Преобразуем JSON в двумерный массив для записи в таблицу.
  const data: any[][] = json.map(function(item: any) {
    return [item.field1, item.field2, item.field3]; // Замените на поля вашего API.
  });

  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

Продвинутые техники Apps Script для Google Sheets

Использование библиотек Apps Script для повторного использования кода

Библиотеки Apps Script позволяют повторно использовать код в разных проектах. Для создания библиотеки необходимо опубликовать скрипт как библиотеку и добавить ее в другие проекты.

Работа с пользовательским интерфейсом (UI Service): диалоговые окна, меню

UI Service позволяет создавать пользовательский интерфейс для скриптов Apps Script, включая диалоговые окна, меню и боковые панели. Это позволяет сделать скрипты более интерактивными и удобными в использовании.

Использование Cache Service для повышения производительности

Cache Service позволяет кэшировать данные в Apps Script, что может значительно повысить производительность скриптов, особенно при работе с большими объемами данных или внешними API.

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

При работе с большими объемами данных важно оптимизировать скрипты, чтобы избежать превышения лимитов выполнения и обеспечить приемлемую производительность. Это можно сделать, например, путем использования пакетной обработки данных, кэширования и оптимизации алгоритмов.

Рекомендации и лучшие практики при работе с Apps Script и Google Sheets

Безопасность скриптов: ограничение доступа, проверка разрешений

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

Организация кода: комментарии, отступы, именование переменных

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

Тестирование скриптов: проверка корректности работы

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

Поиск и устранение проблем: ресурсы и сообщества

При возникновении проблем при работе со скриптами Apps Script можно обратиться к различным ресурсам и сообществам, таким как документация Google, форумы Stack Overflow и Google Groups.

Заключение

Краткое повторение основных моментов

В этой статье мы рассмотрели основы работы с Apps Script в Google Sheets, включая чтение и запись данных, автоматизацию задач, создание пользовательских функций и продвинутые техники. Apps Script предоставляет мощные возможности для расширения функциональности Google Sheets и автоматизации рутинных операций.

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

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

Дополнительные ресурсы для изучения Apps Script

Метки:

Добавить комментарий

Как узнать длину строки в Google Apps Script?
Что такое Google Apps Script и где он применяется Google Apps Script (GAS) — это облачная платформа разработки, позволяющая автоматизировать задачи и расширять функциональность Google Workspace, включая Google Sheets, Docs, Forms и другие приложения.
  •  1
  •   2025
Google Apps Script: Как удалить строку?
Обзор Google Apps Script и его возможностей Google Apps Script (GAS) — это облачная платформа разработки, позволяющая автоматизировать задачи и расширять функциональность Google Workspace, включая Google Sheets.
  •  12
  •   2025
Google Apps Script: Как добавить столбец в таблицу?
Введение в добавление столбцов в Google Таблицах с помощью Apps Script Что такое Google Apps Script и зачем он нужен для работы с таблицами? Google Apps Script — это облачный язык сценариев, который позволяет автоматизировать задачи в Google Workspace, включая Google Таблицы.
  •  13
  •   2025
Google Apps Script: Где найти примеры и руководство для новичков?
Введение в Google Apps Script для начинающих Что такое Google Apps Script и зачем он нужен? Google Apps Script (GAS) — это облачный язык сценариев, разработанный Google для автоматизации задач и расширения функциональности Google Workspace (ранее G Suite).
  •  16
  •   2025