Как создать кастомную функцию в Google Sheets

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

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

Когда нужны кастомные функции

• Если логика слишком ветвистая для одной формулы (много условий IF и SWITCH).
• Когда нужно интегрировать внешний API или выполнять операции, которых нет в стандартных функциях (например, геокодирование, сложные преобразования текста).
• Для повторного использования бизнес-логики в нескольких таблицах.

Важно: кастомные функции выполняются в среде Apps Script и имеют ограничения (квоты выполнения, запрещённые операции синхронного доступа к некоторым сервисам при вызове из ячейки). Ниже — раздел «Когда это НЕ работает».

Что вы получите из руководства

• Пошаговая инструкция: открыть редактор скриптов, создать, сохранить и использовать функцию.
• Рабочие примеры кода и JSDoc-комментарии.
• Шпаргалки, чек-листы для разных ролей и рекомендации по повторному использованию и публикации.
• Часто задаваемые вопросы с ответами.

Открытие редактора скриптов

1. Откройте Google Таблицу.
2. В меню выберите: Инструменты > Редактор скриптов.

Если вы увидите приглашение создать проект, дайте ему понятное имя — например, TaxHelpers или SheetUtils.

Пример функции: расчёт налога по штату

Ниже — минимальный пример функции для расчёта суммы налога по цене и локации. Вставьте код в редактор скриптов и сохраните проект.

/**
 * Вычисляет налог по цене и коду штата.
 * @param {number} price Цена товара
 * @param {string} state Код штата (например, 'PA' или 'CA')
 * @return {number} Сумма налога
 */
function tax(price, state) {
  var rate = 0;
  switch (String(state).toUpperCase()) {
    case 'PA':
      rate = 0.06;
      break;
    case 'CA':
      rate = 0.0625;
      break;
    default:
      rate = 0;
  }
  return price * rate;
}

Пояснение: функция принимает два аргумента — цену и код штата, возвращает числовую сумму налога. Это чистая (pure) функция: она не изменяет внешний мир и всегда возвращает одно и то же значение для одних и тех же входных данных.

Сохранение проекта

В редакторе выберите Файл > Сохранить, задайте название проекта и подтвердите.

Вызов функции в таблице

Вставьте в ячейку формулу так же, как встроенные функции:

=tax(B2, A2)

где B2 — цена, A2 — код штата.

Вы можете использовать автозаполнение (AutoFill), чтобы распространить формулу на другие строки:

Советы по написанию функций

• Держите функции короткими и однозначными — принцип единственной ответственности.
• Проверяйте типы входных данных: приводите к Number/String, чтобы избежать неожиданных NaN.
• Возвращайте простые типы (число, строка, массив) — таблицы лучше работают с простыми значениями.
• Документируйте с помощью JSDoc — подсказки появятся при вводе формулы.

JSDoc пример

/**
 * Конвертирует валюту по фиксированному курсу.
 * @param {number} amount Сумма в исходной валюте
 * @param {number} rate Курс конвертации
 * @return {number} Сумма в целевой валюте
 */
function convertCurrency(amount, rate) {
  return Number(amount) * Number(rate);
}

Повторное использование функций

Как сохранить и переиспользовать написанные функции:

1. Храните набор полезных функций в отдельной «мастер»-таблице (template) и копируйте этот файл.
2. Создайте библиотеку Apps Script и подключайте её к другим проектам (подробнее — в документации Google Apps Script).
3. Скопируйте код вручную между редакторами скриптов — работает, но не масштабируется.
4. Публикуйте скрипт как дополнение (Add-on) или используйте контейнерные/standalone проекты для распространения в организации.

Важно: публикация в галерее шаблонов может открыть доступ к документу другим пользователям. В рабочем аккаунте G Suite/Google Workspace можно ограничить доступ доменом.

Когда кастомные функции не подходят

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

Альтернативные подходы

• Google Apps Script — самый простой путь для встроенных функций.
• Google Cloud Functions / Cloud Run — для тяжёлых или приватных вычислений с HTTP API; возвращать результаты в таблицы через Apps Script или API.
• Add-ons (дополнения) — если нужно распространять функционал для многих пользователей через Marketplace.
• Табличные формулы и QUERY — когда задача описывается SQL-подобными запросами внутри таблицы.

Шпаргалка: часто используемые паттерны

• Обработка пустых значений:

if (price === '' || price === null || isNaN(Number(price))) return 0;

• Безопасное чтение строки состояния:

var st = (state || '').toString().trim().toUpperCase();

• Поддержка массивов (чтобы функция работала при подаче диапазона):

function sumArray(values) {
  if (Array.isArray(values)) {
    return values.flat().reduce(function(acc, v) {
      return acc + Number(v || 0);
    }, 0);
  }
  return Number(values || 0);
}

Чек-листы по ролям

Разработчик:

• Писать тесты для функций (локально через модульный тест в Apps Script).
• Добавлять JSDoc и примеры использования.
• Обрабатывать ошибки и не выбрасывать необработанные исключения в ячейках.

Аналитик/бизнес-пользователь:

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

Администратор/IT:

• Рассмотреть публикацию библиотек для домена.
• Контролировать доступ и аудит скриптов.
• Настроить ограничения на публикацию по внутренним правилам безопасности.

Модель принятия решений — когда писать функцию

1. Сможете ли решить задачу формулой? Если да — используйте формулу.
2. Нужны ли повторные вызовы или централизованная логика? Если да — пишите функцию.
3. Нужен ли интеграционный доступ к внешним API? Если да — рассмотрите Apps Script или Cloud Functions.

Безопасность и приватность

• Скрипт выполняется от имени пользователя, сохраняющего проект. Убедитесь, что конфиденциальные данные не передаются в публичные дополнения.
• При интеграции внешних API избегайте хранения секретов в коде. Используйте PropertiesService или секретное хранилище и управляйте доступом через IAM.
• Для обработки персональных данных соблюдайте требования GDPR и локального законодательства: минимизируйте набор обрабатываемых данных и документируйте цели обработки.

Риски и способы их снижения

• Таймауты: используйте пакетную обработку и триггеры времени.
• Непредвиденные ошибки: добавляйте в код валидацию входных данных и понятные сообщения об ошибках.
• Нарушение доступа: не публикуйте скрипты с секретами и проверяйте разрешения перед публикацией.

Примеры расширений и улучшений

• Поддержка множества налоговых правил: храните правила в листе “rates” и читайте таблицу в скрипте.
• Кэширование: используйте CacheService для уменьшения числа запросов к внешним сервисам.
• Логирование и мониторинг: используйте Stackdriver (Logging) или записывайте события в отдельный лист для аудита.

Пример: чтение таблицы ставок

/**
 * Находит ставку налога в листе Rates по коду штата.
 */
function getRateFromSheet(state) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Rates');
  if (!sheet) return 0;
  var data = sheet.getDataRange().getValues(); // [[state, rate], ...]
  for (var i = 0; i < data.length; i++) {
    if (String(data[i][0]).toUpperCase() === String(state).toUpperCase()) {
      return Number(data[i][1]) || 0;
    }
  }
  return 0;
}

Документирование и пример использования

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

Часто задаваемые вопросы

Как сохранить функцию и использовать её в другой таблице?

Скопируйте проект Apps Script в новую таблицу через меню редактора или создайте библиотеку и подключите её в другом проекте.

Нужно ли публиковать скрипт, чтобы другие могли использовать мои функции?

Не обязательно. Коллеги, у которых есть доступ к таблице, смогут вызывать функции из скрипта, встроенного в эту таблицу. Для массового распространения используйте Add-on или библиотеку.

Что делать, если функция возвращает ERR: Время выполнения истекло?

Оптимизируйте код, уменьшите количество внешних запросов, используйте пакетную обработку и триггеры по расписанию.

Как тестировать функции перед использованием?

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

Короткое резюме

• Кастомные функции расширяют возможности Google Sheets и позволяют инкапсулировать сложную логику.
• Пишите простые, детализированные и документированные функции.
• Для повторного использования выбирайте библиотеки или шаблоны; для масштабируемости — облачные решения.

Важное: перед публикацией скрипта проверьте права доступа и избегайте хранения секретов в коде.

Шаблоны и ресурсы (чек-лист для быстрой настройки)

• Открыл редактор скриптов.
• Назвал проект и сохранил.
• Написал функцию с JSDoc и проверил её на тестовых данных.
• Добавил обработку ошибок и проверку типов.
• Решил способ распространения (копирование, библиотека, аддон).

FAQ (короткий)

Q: Можно ли вызвать внешнее API из кастомной функции в ячейке? A: Часто да, но это может приводить к таймаутам; для тяжёлых интеграций лучше использовать триггеры или серверные функции.

Q: Где хранить общие функции для всей команды? A: В библиотеке Apps Script или в шаблонной таблице, доступной команде.