Что такое пользовательские функции и зачем они нужны?
Пользовательские функции в Google Apps Script позволяют значительно расширить возможности Google Sheets, добавляя логику и функциональность, недоступную во встроенных функциях. Они позволяют автоматизировать сложные вычисления, манипулировать данными, интегрироваться с другими сервисами Google (такими как Gmail, Calendar, Drive) и даже сторонними API. В сущности, вы определяете собственные формулы, которые можно использовать непосредственно в ячейках таблицы.
Преимущества использования пользовательских функций в Google Sheets
Автоматизация рутинных задач: Выполняйте сложные операции над данными одним вызовом функции.
Расширение функциональности: Добавляйте специфичные для вашего бизнеса или проекта функции.
Повышение читаемости формул: Сложные формулы можно разбить на несколько пользовательских функций, упрощая их понимание и отладку.
Централизованное управление логикой: Изменяйте логику вычислений в одном месте (в скрипте) вместо множества формул в таблице.
Интеграция с другими сервисами: Получайте данные из внешних источников и используйте их в своих таблицах.
Необходимые условия: доступ к Google Apps Script и базовые знания JavaScript
Для создания и использования пользовательских функций вам потребуется:
Учетная запись Google.
Базовые знания JavaScript. Понимание переменных, функций, циклов и условий будет полезно.
Доступ к редактору Google Apps Script, который можно открыть непосредственно из Google Sheets.
Создание простой пользовательской функции
Открытие редактора Google Apps Script из Google Sheets
Откройте Google Sheets.
Выберите Инструменты > Редактор скриптов.
Откроется новая вкладка с редактором Google Apps Script.
Написание кода первой функции: пример и объяснение
Давайте создадим простую функцию, которая умножает число на 2:
/**
* Умножает число на 2.
* @param {number} число, которое нужно умножить.
* @return {number} Результат умножения.
* @customfunction
*/
function doubleValue(number: number): number {
return number * 2;
}Объяснение:
/** ... */: Это многострочный комментарий, описывающий функцию. Включает параметры (@param), возвращаемое значение (@return) и тег @customfunction, необходимый для распознавания функции в Google Sheets.
function doubleValue(number: number): number: Определение функции с именем doubleValue. number: number — указывает, что параметр number должен быть числом, а : number после скобок указывает, что функция должна возвращать число.
return number * 2;: Возвращает результат умножения входного числа на 2.
Сохранение и запуск функции: как проверить ее работоспособность в таблице
В редакторе скриптов нажмите значок дискеты, чтобы сохранить скрипт (например, под именем "МоиФункции").
В Google Sheets введите в ячейку =doubleValue(5). Если все сделано правильно, в ячейке отобразится результат 10.
Основные правила именования и синтаксиса функций
Имена функций должны начинаться с буквы и могут содержать буквы, цифры и знаки подчеркивания.
Регистр имеет значение: myFunction и MyFunction — это разные функции.
Аргументы функции перечисляются в скобках через запятую.
Функция должна возвращать значение с помощью оператора return.
Обязательно используйте @customfunction в комментарии для пользовательской функции.
Аргументы и возвращаемые значения
Передача аргументов в пользовательскую функцию
Функции могут принимать входные данные через аргументы. Аргументы позволяют делать функцию более гибкой и универсальной.
Использование нескольких аргументов
/**
* Складывает два числа.
* @param {number} a Первое число.
* @param {number} b Второе число.
* @return {number} Сумма чисел.
* @customfunction
*/
function sum(a: number, b: number): number {
return a + b;
}В Google Sheets: =sum(2, 3) вернет 5.
Возврат значений различных типов (числа, строки, массивы)
Функции могут возвращать значения различных типов:
/**
* Возвращает приветствие.
* @param {string} name Имя пользователя.
* @return {string} Приветствие.
* @customfunction
*/
function greet(name: string): string {
return "Привет, " + name + "!";
}
/**
* Возвращает массив чисел.
* @return {Array} Массив чисел.
* @customfunction
*/
function getNumbers(): number[] {
return [1, 2, 3, 4, 5];
}=greet("Иван") вернет "Привет, Иван!".
=getNumbers() вернет {1,2,3,4,5} в таблице (в виде горизонтального массива). Чтобы получить вертикальный массив, используйте TRANSPOSE(getNumbers()).
Обработка ошибок и возвращение сообщений об ошибках
Важно обрабатывать возможные ошибки в функции и возвращать информативные сообщения.
/**
* Делит одно число на другое.
* @param {number} numerator Числитель.
* @param {number} denominator Знаменатель.
* @return {number|string} Результат деления или сообщение об ошибке.
* @customfunction
*/
function divide(numerator: number, denominator: number): any {
if (denominator === 0) {
return "Ошибка: Деление на ноль!";
}
return numerator / denominator;
}=divide(10, 2) вернет 5.
=divide(10, 0) вернет "Ошибка: Деление на ноль!".
Продвинутые техники и примеры
Использование встроенных функций Google Sheets в пользовательских функциях
В пользовательских функциях можно использовать другие встроенные функции Google Sheets, например SUM, AVERAGE, VLOOKUP и т.д.
/**
* Вычисляет среднее значение диапазона.
* @param {Array<Array>} range Диапазон ячеек.
* @return {number} Среднее значение.
* @customfunction
*/
function averageRange(range: number[][]): number {
let sum = 0;
let count = 0;
for (let i = 0; i < range.length; i++) {
for (let j = 0; j < range[i].length; j++) {
if (typeof range[i][j] === 'number') { // Проверяем, является ли значение числом
sum += range[i][j];
count++;
}
}
}
if (count === 0) {
return 0; // Или другое значение по умолчанию, или сообщение об ошибке
}
return sum / count;
}В Google Sheets: =averageRange(A1:B10) вернет среднее значение чисел в диапазоне A1:B10.
Работа с диапазонами ячеек в качестве аргументов
При передаче диапазона ячеек в функцию, Google Apps Script автоматически преобразует его в двумерный массив.
Создание пользовательских функций для обработки текста, дат и чисел
Рассмотрим пример функции для форматирования даты:
/**
* Форматирует дату в заданный формат.
* @param {Date} date Дата.
* @param {string} format Формат даты (например, 'dd.MM.yyyy').
* @return {string} Отформатированная дата.
* @customfunction
*/
function formatDate(date: Date, format: string): string {
return Utilities.formatDate(date, Session.getScriptTimeZone(), format);
}=formatDate(TODAY(), "dd.MM.yyyy") вернет текущую дату в формате "дд.мм.гггг".
Оптимизация производительности пользовательских функций
Избегайте частого обращения к ячейкам Google Sheets внутри цикла. Лучше считать данные в массив и обрабатывать его.
Используйте встроенные функции Google Apps Script, если они доступны.
Ограничивайте сложность вычислений в одной функции.
Ограничения и лучшие практики
Ограничения на использование пользовательских функций в Google Sheets (время выполнения, частота вызовов)
Время выполнения одной функции ограничено 30 секундами.
Существуют ограничения на количество вызовов сервисов Google в день. Смотрите документацию Google Apps Script для получения актуальной информации.
Функции не должны изменять состояние Google Sheets (например, форматирование ячеек). Они предназначены только для вычислений и возврата значений.
Рекомендации по написанию читаемого и поддерживаемого кода
Используйте понятные имена переменных и функций.
Добавляйте комментарии для объяснения логики кода.
Разбивайте сложные функции на более мелкие.
Используйте отступы для улучшения читаемости кода.
Отладка и тестирование пользовательских функций
Используйте Logger.log() для вывода информации в консоль редактора скриптов.
Используйте отладчик Google Apps Script для пошагового выполнения кода.
Создавайте тестовые случаи для проверки правильности работы функции.
Полезные ресурсы и дальнейшее изучение Google Apps Script
Официальная документация Google Apps Script
Stack Overflow (форум для вопросов и ответов по программированию)
Блоги и статьи по Google Apps Script.