Google Apps Script: Как работать с SpreadsheetApp? Полное руководство

16.09.2025
52444

12 мин
111

Что такое SpreadsheetApp и его назначение

SpreadsheetApp – это сервис в Google Apps Script, предоставляющий API для взаимодействия с Google Sheets. Он позволяет автоматизировать создание, чтение, изменение и управление электронными таблицами Google. Это ключевой инструмент для автоматизации рутинных задач, связанных с обработкой данных, генерацией отчетов и интеграцией таблиц с другими сервисами Google.

Преимущества использования SpreadsheetApp для автоматизации Google Sheets

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

Интеграция: Интеграция с другими сервисами Google (Gmail, Calendar, Drive) и внешними API значительно расширяет возможности обработки и анализа данных.

Централизованное управление: Управление таблицами Google Sheets из единого скрипта позволяет стандартизировать процессы и снизить риск ошибок.

Расширяемость: Пользовательские функции (UDF) позволяют создавать собственные формулы и расширять функциональность Google Sheets.

Как получить доступ к SpreadsheetApp в Google Apps Script

Для доступа к SpreadsheetApp не требуется никаких дополнительных установок или авторизаций. Сервис доступен глобально в любом скрипте Google Apps Script. Просто используйте его методы для взаимодействия с таблицами.

Основные операции с электронными таблицами

Открытие и получение активной электронной таблицы (getActiveSpreadsheet, openById, openByName)

/**
 * Открывает электронную таблицу по ID.
 * @param {string} spreadsheetId ID электронной таблицы.
 * @return {GoogleAppsScript.Spreadsheet.Spreadsheet} Открытая электронная таблица.
 */
function openSpreadsheetById(spreadsheetId: string): GoogleAppsScript.Spreadsheet.Spreadsheet {
  return SpreadsheetApp.openById(spreadsheetId);
}

/**
 * Открывает электронную таблицу по имени.
 * @param {string} spreadsheetName Имя электронной таблицы.
 * @return {GoogleAppsScript.Spreadsheet.Spreadsheet} Открытая электронная таблица.
 */
function openSpreadsheetByName(spreadsheetName: string): GoogleAppsScript.Spreadsheet.Spreadsheet {
  return SpreadsheetApp.openByName(spreadsheetName);
}

/**
 * Возвращает активную электронную таблицу.
 * @return {GoogleAppsScript.Spreadsheet.Spreadsheet} Активная электронная таблица.
 */
function getActiveSpreadsheet(): GoogleAppsScript.Spreadsheet.Spreadsheet {
  return SpreadsheetApp.getActiveSpreadsheet();
}

getActiveSpreadsheet() возвращает таблицу, открытую в текущем контексте выполнения скрипта (например, таблицу, к которой привязан скрипт или таблицу, открытую в редакторе скриптов). openById() и openByName() позволяют получить доступ к таблицам по их уникальному идентификатору или имени соответственно.

Создание новой электронной таблицы (create)

/**
 * Создает новую электронную таблицу.
 * @param {string} spreadsheetName Имя новой электронной таблицы.
 * @return {GoogleAppsScript.Spreadsheet.Spreadsheet} Созданная электронная таблица.
 */
function createSpreadsheet(spreadsheetName: string): GoogleAppsScript.Spreadsheet.Spreadsheet {
  return SpreadsheetApp.create(spreadsheetName);
}

// Пример использования:
let newSpreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = createSpreadsheet("Новая таблица для отчетов");
Logger.log('ID новой таблицы: ' + newSpreadsheet.getId());

Функция create() создаёт новую электронную таблицу в корневой папке вашего Google Drive.

Сохранение и защита электронных таблиц

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

Работа с метаданными электронной таблицы (название, URL и др.)

/**
 * Выводит метаданные электронной таблицы.
 * @param {GoogleAppsScript.Spreadsheet.Spreadsheet} spreadsheet Электронная таблица.
 */
function logSpreadsheetMetadata(spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet): void {
  Logger.log('Название таблицы: ' + spreadsheet.getName());
  Logger.log('URL таблицы: ' + spreadsheet.getUrl());
  Logger.log('ID таблицы: ' + spreadsheet.getId());
  Logger.log('Часовой пояс таблицы: ' + spreadsheet.getSpreadsheetTimeZone());
}

// Пример использования:
let activeSpreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
logSpreadsheetMetadata(activeSpreadsheet);

Работа с листами (Sheets)

Получение доступа к листам (getSheetByName, getSheets, getActiveSheet)

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

/**
 * Получает все листы электронной таблицы.
 * @return {GoogleAppsScript.Spreadsheet.Sheet[]} Массив листов.
 */
function getAllSheets(): GoogleAppsScript.Spreadsheet.Sheet[] {
  let spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  return spreadsheet.getSheets();
}

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

getSheetByName() – возвращает лист по его имени. Важно помнить, что имена листов чувствительны к регистру. getSheets() – возвращает массив всех листов в таблице. getActiveSheet() – возвращает лист, который в данный момент активен в интерфейсе пользователя.

Создание, удаление и переименование листов (insertSheet, deleteSheet, setName)

/**
 * Создает новый лист.
 * @param {string} sheetName Имя нового листа.
 * @return {GoogleAppsScript.Spreadsheet.Sheet} Созданный лист.
 */
function insertNewSheet(sheetName: string): GoogleAppsScript.Spreadsheet.Sheet {
  let spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  return spreadsheet.insertSheet(sheetName);
}

/**
 * Удаляет лист.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet Лист для удаления.
 */
function deleteExistingSheet(sheet: GoogleAppsScript.Spreadsheet.Sheet): void {
  let spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.deleteSheet(sheet);
}

/**
 * Переименовывает лист.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet Лист для переименования.
 * @param {string} newName Новое имя листа.
 */
function renameSheet(sheet: GoogleAppsScript.Spreadsheet.Sheet, newName: string): void {
  sheet.setName(newName);
}

// Пример использования:
let newSheet: GoogleAppsScript.Spreadsheet.Sheet = insertNewSheet("Новый лист с данными");
renameSheet(newSheet, "Данные за май");
// deleteExistingSheet(newSheet); // Закомментировано, чтобы лист не удалялся при каждом запуске.

insertSheet() создаёт новый лист с указанным именем. Можно указать индекс, чтобы вставить лист в определенное место. deleteSheet() удаляет существующий лист. setName() позволяет переименовать лист.

Работа с видимостью и порядком листов

/**
 * Скрывает/отображает лист.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet Лист.
 * @param {boolean} hidden true, чтобы скрыть, false, чтобы отобразить.
 */
function setSheetVisibility(sheet: GoogleAppsScript.Spreadsheet.Sheet, hidden: boolean): void {
  sheet.hideSheet().setVisible(!hidden);
}

/**
 * Перемещает лист на указанную позицию.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet Лист.
 * @param {number} index Индекс, куда нужно переместить лист (начиная с 1).
 */
function moveSheetToIndex(sheet: GoogleAppsScript.Spreadsheet.Sheet, index: number): void {
  let spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.moveActiveSheet(index);
  spreadsheet.setActiveSheet(sheet);
}

// Пример использования:
let sheetToHide: GoogleAppsScript.Spreadsheet.Sheet | null = getSheetByName("Скрытый лист");
if (sheetToHide) {
    setSheetVisibility(sheetToHide, true); // Скрыть лист

    let sheetToMove: GoogleAppsScript.Spreadsheet.Sheet | null = getSheetByName("Данные за май");
    if(sheetToMove) {
      moveSheetToIndex(sheetToMove, 1); // Переместить на первую позицию
    }
}

hideSheet() и showSheet() управляют видимостью листа. moveActiveSheet() – перемещает активный лист на указанную позицию.

Работа с диапазонами (Ranges)

Получение диапазонов (getRange, getDataRange)

/**
 * Получает диапазон по A1 нотации.
 * @param {string} rangeString A1 нотация диапазона (например, "A1:B10").
 * @return {GoogleAppsScript.Spreadsheet.Range} Диапазон.
 */
function getRangeByA1Notation(rangeString: string): GoogleAppsScript.Spreadsheet.Range {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  return sheet.getRange(rangeString);
}

/**
 * Получает диапазон по координатам и размерам.
 * @param {number} row Номер строки начала диапазона (начиная с 1).
 * @param {number} column Номер столбца начала диапазона (начиная с 1).
 * @param {number} numRows Количество строк в диапазоне.
 * @param {number} numColumns Количество столбцов в диапазоне.
 * @return {GoogleAppsScript.Spreadsheet.Range} Диапазон.
 */
function getRangeByCoordinates(row: number, column: number, numRows: number, numColumns: number): GoogleAppsScript.Spreadsheet.Range {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  return sheet.getRange(row, column, numRows, numColumns);
}

/**
 * Получает диапазон, содержащий данные.
 * @return {GoogleAppsScript.Spreadsheet.Range} Диапазон данных.
 */
function getDataRange(): GoogleAppsScript.Spreadsheet.Range {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  return sheet.getDataRange();
}

// Пример использования:
let rangeA1: GoogleAppsScript.Spreadsheet.Range = getRangeByA1Notation("A1:C5");
let rangeCoordinates: GoogleAppsScript.Spreadsheet.Range = getRangeByCoordinates(1, 1, 5, 3);
let dataRange: GoogleAppsScript.Spreadsheet.Range = getDataRange();

getRange() можно использовать с A1 нотацией (например, "A1:B10") или с указанием номера строки, столбца, количества строк и столбцов. getDataRange() возвращает диапазон, содержащий все данные на листе.

Чтение и запись данных в диапазоны (getValue, getValues, setValue, setValues)

/**
 * Читает одно значение из диапазона.
 * @param {string} rangeString A1 нотация диапазона (например, "A1").
 * @return {any} Значение ячейки.
 */
function getCellValue(rangeString: string): any {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  return sheet.getRange(rangeString).getValue();
}

/**
 * Читает значения из диапазона.
 * @param {string} rangeString A1 нотация диапазона (например, "A1:B10").
 * @return {any[][]} Двумерный массив значений.
 */
function getRangeValues(rangeString: string): any[][] {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  return sheet.getRange(rangeString).getValues();
}

/**
 * Записывает одно значение в ячейку.
 * @param {string} rangeString A1 нотация ячейки (например, "A1").
 * @param {any} value Значение для записи.
 */
function setCellValue(rangeString: string, value: any): void {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(rangeString).setValue(value);
}

/**
 * Записывает значения в диапазон.
 * @param {string} rangeString A1 нотация диапазона (например, "A1:B10").
 * @param {any[][]} values Двумерный массив значений для записи.
 */
function setRangeValues(rangeString: string, values: any[][]): void {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(rangeString).setValues(values);
}

// Пример использования:
let value: any = getCellValue("A1");
Logger.log("Значение A1: " + value);

let data: any[][] = [["Имя", "Возраст"], ["Иван", 30], ["Мария", 25]];
setRangeValues("A2:B4", data);

setCellValue("C1", "Статус");

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

Форматирование диапазонов (setFontSize, setBackground, setNumberFormat и др.)

/**
 * Устанавливает размер шрифта для диапазона.
 * @param {string} rangeString A1 нотация диапазона.
 * @param {number} fontSize Размер шрифта.
 */
function setRangeFontSize(rangeString: string, fontSize: number): void {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(rangeString).setFontSize(fontSize);
}

/**
 * Устанавливает цвет фона для диапазона.
 * @param {string} rangeString A1 нотация диапазона.
 * @param {string} color Цвет в формате HEX (например, "#FF0000").
 */
function setRangeBackground(rangeString: string, color: string): void {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(rangeString).setBackground(color);
}

/**
 * Устанавливает формат числа для диапазона.
 * @param {string} rangeString A1 нотация диапазона.
 * @param {string} numberFormat Формат числа (например, "0.00", "#,##0.00").
 */
function setRangeNumberFormat(rangeString: string, numberFormat: string): void {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(rangeString).setNumberFormat(numberFormat);
}

// Пример использования:
setRangeFontSize("A1:B1", 12);
setRangeBackground("A2:B4", "#FFFF00");
setRangeNumberFormat("B2:B4", "0");

Методы setFontSize(), setBackground(), setNumberFormat() позволяют изменять внешний вид ячеек. Доступны и другие методы для форматирования, такие как установка шрифта, выравнивания, границ и т.д.

Поиск и замена данных в диапазонах (find, replace)

/**
 * Ищет значение в диапазоне.
 * @param {string} rangeString A1 нотация диапазона.
 * @param {string} searchText Текст для поиска.
 * @return {GoogleAppsScript.Spreadsheet.Range | null} Найденный диапазон или null, если ничего не найдено.
 */
function findValueInRange(rangeString: string, searchText: string): GoogleAppsScript.Spreadsheet.Range | null {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  return sheet.getRange(rangeString).find(searchText);
}

/**
 * Заменяет значение в диапазоне.
 * @param {string} rangeString A1 нотация диапазона.
 * @param {string} searchText Текст для поиска.
 * @param {string} replacementText Текст для замены.
 */
function replaceValueInRange(rangeString: string, searchText: string, replacementText: string): void {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(rangeString).replace(searchText, replacementText);
}

// Пример использования:
let foundRange: GoogleAppsScript.Spreadsheet.Range | null = findValueInRange("A1:C10", "Иван");
if (foundRange) {
  Logger.log("Найдено в ячейке: " + foundRange.getA1Notation());
  replaceValueInRange("A1:C10", "Иван", "Петр");
}

find() ищет указанный текст в диапазоне и возвращает первый найденный диапазон. replace() заменяет все вхождения текста в диапазоне.

Расширенные возможности SpreadsheetApp

Работа с формулами (setFormula, setFormulas)

/**
 * Устанавливает формулу в ячейку.
 * @param {string} rangeString A1 нотация ячейки.
 * @param {string} formula Формула (например, "=SUM(A1:A10)").
 */
function setCellFormula(rangeString: string, formula: string): void {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(rangeString).setFormula(formula);
}

/**
 * Устанавливает формулы в диапазон.
 * @param {string} rangeString A1 нотация диапазона.
 * @param {string[][]} formulas Двумерный массив формул.
 */
function setRangeFormulas(rangeString: string, formulas: string[][]): void {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(rangeString).setFormulas(formulas);
}

// Пример использования:
setCellFormula("D1", "=SUM(B2:B4)");

let formulas: string[][] = [["=A2*B2"], ["=A3*B3"], ["=A4*B4"]];
setRangeFormulas("C2:C4", formulas);

setFormula() устанавливает формулу в одну ячейку. setFormulas() устанавливает формулы в диапазон ячеек.

Использование условного форматирования

SpreadsheetApp позволяет создавать правила условного форматирования для автоматического изменения внешнего вида ячеек в зависимости от их значений. Пример:

/**
 * Создает правило условного форматирования.
 */
function createConditionalFormatRule(): void {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let range: GoogleAppsScript.Spreadsheet.Range = sheet.getRange("A1:C10");

  let rule = SpreadsheetApp.newConditionalFormatRule()
      .whenNumberGreaterThan(100)
      .setBackground("#FF0000")
      .setRanges([range])
      .build();

  let rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

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

Создание и управление выпадающими списками (Data Validation)

/**
 * Создает выпадающий список.
 * @param {string} rangeString A1 нотация ячейки.
 * @param {string[]} values Список значений для выпадающего списка.
 */
function createDropdownList(rangeString: string, values: string[]): void {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let range: GoogleAppsScript.Spreadsheet.Range = sheet.getRange(rangeString);

  let rule = SpreadsheetApp.newDataValidation()
      .requireValueInList(values)
      .setAllowInvalid(false) // Запретить ввод значений, отсутствующих в списке
      .build();

  range.setDataValidation(rule);
}

// Пример использования:
createDropdownList("E1", ["Да", "Нет", "Возможно"]);

Этот код создаст выпадающий список в ячейке E1, предлагающий варианты "Да", "Нет" и "Возможно".

Работа с защищенными диапазонами и листами ( защиты от изменений )

/**
 * Защищает диапазон от редактирования.
 * @param {string} rangeString A1 нотация диапазона.
 * @param {string[]} editors Список email адресов пользователей, которым разрешено редактирование.
 */
function protectRange(rangeString: string, editors: string[]): void {
  let sheet: GoogleAppsScript.Spreadsheet.Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let range: GoogleAppsScript.Spreadsheet.Range = sheet.getRange(rangeString);
  let protection = range.protect().setDescription('Защищенный диапазон');

  let me = Session.getActiveUser().getEmail();
  protection.addEditor(me);

  if (editors && editors.length > 0) {
    protection.removeEditors(protection.getEditors()); // Удаляем всех текущих редакторов, кроме текущего пользователя
    editors.forEach(editor => protection.addEditor(editor));
  }
  protection.removeEditor(me); //Убираем себя из редакторов

  protection.setWarningOnly(true); //Показывать только предупреждение


}

// Пример использования:
// protectRange("A1:C10", ["user1@example.com", "user2@example.com"]);

protect() позволяет защитить диапазон или лист от редактирования, разрешив доступ только определенным пользователям. Метод setWarningOnly(true) позволяет только показывать предупреждение при попытке редактирования защищенного диапазона, но не запрещает редактирование.

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

Google Apps Script: Как добавить элемент в массив?

Введение в массивы в Google Apps Script Что такое массив и зачем он нужен? Массив – это упорядоченная коллекция элементов данных, каждый из которых идентифицируется индексом.

88
2025

Google Apps Script: Как получить столбец из таблицы?

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

88
2025

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

Что такое диапазон данных и почему он важен? Диапазон данных в Google Sheets – это прямоугольная область ячеек на листе.

88
2025