Введение в работу с Apps Script в Google Таблицах

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

Что такое Apps Script

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

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

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

Для начала работы с Apps Script необходимо открыть редактор скриптов через меню «Расширения» – «Apps Script». После этого вы попадете в интерфейс редактора, где можно создавать и редактировать скрипты. Каждый скрипт начинается с объявления функции, которая будет выполнять определенные действия в таблице.

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

Синтаксис и основные конструкции

В Apps Script используются основные конструкции JavaScript, но с некоторыми особенностями. Каждая функция должна начинаться с ключевого слова function, за которым следует имя функции и круглые скобки. Внутри скобок могут указываться параметры, если они необходимы для работы функции.

При работе с таблицами часто используются следующие конструкции:

• SpreadsheetApp.getActiveSpreadsheet() – получение активной таблицы;

• getActiveSheet() – получение активного листа;

• getRange() – получение диапазона ячеек;

• getValue() и setValue() – чтение и запись значений;

• getValues() и setValues() – работа с массивами данных.

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

Google Apps Script Samples on GitHub

Примеры практического применения

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

Пример 1: Создание пользовательской функции для форматирования текста

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

function ADD_PREFIX(text, prefix) {

  if (text === undefined || text === null) return "";

  return prefix + " " + text.toString().toUpperCase();

}

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

Использовав эту функцию в таблице, вы сможете быстро форматировать текст в ячейках. Например, формула =ADD_PREFIX(A1; "ВАЖНО") преобразует текст «совещание в 15:00» в «ВАЖНО СОВЕЩАНИЕ В 15:00». Это особенно полезно при работе с большими объемами данных, которые требуют единообразного форматирования.

Пример 2: Автоматическое создание оглавления

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

function CREATE_CONTENTS() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var contentsSheet = spreadsheet.getSheetByName("Contents") || spreadsheet.insertSheet("Contents");

  var sheets = spreadsheet.getSheets();

  

  var contentsData = [["Sheet Name", "Row Count", "Last Update"]];

  

  for (var i = 0; i < sheets.length; i++) {

    if (sheets[i].getName() !== "Contents") {

      contentsData.push([

        sheets[i].getName(),

        sheets[i].getLastRow(),

        new Date().toLocaleString()

      ]);

    }

  }

  

  contentsSheet.getRange(1, 1, contentsData.length, 3).setValues(contentsData);

  contentsSheet.setFrozenRows(1);

}

В этом примере мы создаем функцию, которая перебирает все листы в таблице и собирает информацию о каждом из них. После запуска выполнения, как показано на скриншоте выше, скрипт создает новый лист «Contents» (или использует существующий) и заполняет его данными о названиях листов, количестве заполненных строк и времени последнего обновления.

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

Пример 3: Автоматизация отправки уведомлений

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

function SEND_NOTIFICATIONS() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = spreadsheet.getSheetByName("Projects");

  var data = sheet.getDataRange().getValues();

  

  for (var i = 1; i < data.length; i++) {

    var deadlineDate = new Date(data[i][2]);

    var today = new Date();

    var daysDiff = Math.floor((deadlineDate - today) / (1000 * 60 * 60 * 24));

    

    if (daysDiff === 3 && data[i][3] !== "Completed") {

      var recipient = data[i][4];

      var projectTheme = data[i][1];

      

      MailApp.sendEmail({

        to: recipient,

        subject: "Deadline Reminder: " + projectTheme,

        body: "Project '" + projectTheme + "' deadline is in 3 days. Please check task status."

      });

    }

  }

}

Этот скрипт анализирует данные в таблице проектов, проверяя даты дедлайнов и статусы выполнения. Когда до дедлайна остается три дня, скрипт автоматически отправляет уведомление ответственному лицу. В примере используются встроенные возможности Apps Script для работы с электронной почтой через сервис MailApp. Убедитесь, что в вашей таблице есть лист с именем Projects и соответствующие данные. Скрипт ожидает такую структуру, которая показана на следующем изображении.

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

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

1. Находясь на вкладке редактора, перейдите в раздел «Триггеры», нажав по соответствующему значку, затем нажмите кнопку «Добавление триггера».

2. Выберите заранее сохраненный скрипт с отправкой уведомлений, тип развертывания оставьте основным, за‎‎‎оем установите триггер по времени и тип триггера «По дням», после чего укажите удобный часовой промежуток, например, который будет совпадать с началом рабочего дня. В завершение убедитесь, что скрипт будет срабатывать ежедневно, после чего сохраните его.

3. Теперь триггер добавлен в список и будет выполняться автоматически. Соответственно, если условия отправки уведомлений совпадут с теми, которые указаны в скрипте, пользователь, чья почта указана в таблице под конкретной задачей, получит сообщение на почту.‎

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

Пример 4: Создание автоматических резервных копий

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

Такой скрипт особенно полезен в командах, работающих с большими объемами данных или проектами, где регулярно происходят изменения. Например, он может пригодиться для ведения финансовой отчетности, управления проектами, планирования задач или работы с базами данных. В случае необходимости отката к предыдущей версии таблицы резервные копии позволяют легко восстановить утраченные данные. Кроме того, уведомление редакторов о создании копии повышает прозрачность работы в команде и обеспечивает всех участников актуальной информацией. Благодаря автоматизации и централизованному ведению журнала вам не нужно вручную отслеживать процесс создания резервных копий – система делает это за вас, минимизируя человеческий фактор.

function CREATE_BACKUP() {

  var sourceSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var backupDate = new Date().toISOString().slice(0, 10);

  var backupName = sourceSpreadsheet.getName() + " - Backup " + backupDate;

  

  // Создание копии в той же папке

  var backup = DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy(backupName);

  

  // Получение списка email-адресов редакторов исходного документа

  var editors = DriveApp.getFileById(sourceSpreadsheet.getId()).getEditors();

  var editorEmails = editors.map(function(editor) {

    return editor.getEmail();

  });

  

  // Запись в лист "Backup Log"

  var logSheetName = "Backup Log";

  var logSheet = sourceSpreadsheet.getSheetByName(logSheetName);

  if (!logSheet) {

    logSheet = sourceSpreadsheet.insertSheet(logSheetName);

    logSheet.appendRow(["Дата", "Имя резервной копии", "ID копии", "Уведомленные редакторы"]);

  }

  logSheet.appendRow([backupDate, backupName, backup.getId(), editorEmails.join(",")]);

  

  // Отправка уведомления о создании копии

  if (editorEmails.length > 0) {

    MailApp.sendEmail({

      to: editorEmails.join(","),

      subject: "Spreadsheet backup created",

      body: "A backup copy of document '" + sourceSpreadsheet.getName() + 

            "' has been created.\nBackup name: " + backupName + 

            "\nCreation date: " + backupDate

    });

  }

  

  return backup.getId();

}

После изменения скрипта, он автоматически создает лист для ведения журнала, если такого еще нет. Этот лист называется «Backup Log» и используется для записи всех резервных копий, которые создаются с помощью скрипта. В процессе выполнения скрипт проверяет, существует ли лист с таким названием. Если его нет, он создает новый лист и добавляет в первую строку заголовки: «Дата», «Имя резервной копии», «ID копии», «Уведомленные редакторы». Эти заголовки служат ориентирами для последующего внесения информации о каждом созданном бэкапе. Такой подход позволяет не только централизовать данные, но и автоматизировать процесс ведения отчета без необходимости ручного создания таблицы.

Когда резервная копия создается, в лист «Backup Log» добавляется новая строка с подробной информацией. Каждое новое выполнение скрипта дополняет журнал: записывается дата создания копии, ее имя, уникальный ID (по которому можно найти файл на Google Диске), а также email-адреса всех редакторов, которые были уведомлены о создании бэкапа. 

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

Пример 5: Валидация данных

Этот скрипт предназначен для автоматической проверки данных в таблице Google Sheets на наличие ошибок в записях, таких как некорректные email-адреса, номера телефонов или суммы. Он выделяет проблемные строки цветом и формирует список ошибок, которые отображаются в виде уведомления. Такой подход удобен для команд, работающих с большими массивами данных, где важно обеспечить их корректность перед использованием – например, в финансовых отчетах, базах клиентов или при подготовке списков для рассылки. Скрипт исключает необходимость ручной проверки данных и минимизирует вероятность пропуска ошибок. Перед показом всего кода уточню формат листа, который имеется для данного примера. Его видно на следующем изображении, поэтому учитывайте, что скрипт работает именно на таком примере.

Сам скрипт в текущем его виде содержит указанные ниже строчки кода. В будущем, когда вы разберетесь с простыми примерами, сможете модернизировать и этот, а также понимать все его составляющие без сопроводительных комментариев.

function VALIDATE_DATA() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var range = sheet.getDataRange();

  var data = range.getValues();

  var errors = [];

  

  for (var i = 1; i < data.length; i++) {

    var row = i + 1;

    var email = data[i][2] ? data[i][2].toString().trim() : ""; // Убираем лишние пробелы

    var phone = data[i][3] ? data[i][3].toString().trim() : ""; // Преобразуем в строку и очищаем

    var amount = parseFloat(data[i][4]); // Преобразуем в число

    

    // Проверка email

    if (email && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) {

      errors.push("Row " + row + ": invalid email");

      sheet.getRange(row, 3).setBackground("#ffcdd2");

    }

    

    // Проверка телефона

    if (phone && !/^\+?[\d\s-()]{10,}$/.test(phone)) {

      errors.push("Row " + row + ": invalid phone");

      sheet.getRange(row, 4).setBackground("#ffcdd2");

    }

    

    // Проверка суммы

    if (isNaN(amount) || amount <= 0) {

      errors.push("Row " + row + ": invalid amount");

      sheet.getRange(row, 5).setBackground("#ffcdd2");

    }

  }

  

  if (errors.length > 0) {

    SpreadsheetApp.getUi().alert("Found errors:\n" + errors.join("\n"));

  } else {

    SpreadsheetApp.getUi().alert("Validation completed successfully");

  }

}

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

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

Полезные советы по работе с Apps Script

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

1. Всегда добавляйте проверку входных данных, чтобы избежать ошибок при выполнении скрипта. Используйте условные конструкции для обработки пустых значений и некорректных данных.

2. Старайтесь структурировать код, разбивая сложные функции на более простые подфункции. Это упрощает понимание кода и его последующую модификацию.

3. Используйте комментарии для объяснения логики работы скрипта. Это особенно важно, если над документом работает несколько человек или вы планируете возвращаться к коду через некоторое время.

4. При работе с большими объемами данных старайтесь минимизировать количество обращений к таблице. Вместо многократного чтения или записи отдельных ячеек лучше использовать операции с массивами данных.

5. Регулярно тестируйте скрипты на небольших наборах данных перед применением их к реальным документам.

Заключение

Apps Script – сложный, но полезный инструмент, который позволяет существенно расширить возможности Google Таблиц и автоматизировать многие рутинные операции. Даже без глубоких знаний программирования, понимание основных принципов работы с Apps Script открывает широкие возможности для оптимизации работы с данными.

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