Google Таблицы предлагают сотни встроенных функций, но иногда задача оказывается чуть сложнее, чем то, что они умеют. Нужно привести к единому виду несколько сотен телефонных номеров, вставить в отчет «5 дней» вместо безликого числа, или проверить список email-адресов перед рассылкой. Формулами это либо не решается вообще, либо превращается в многоэтажную конструкцию, которую невозможно потом читать. Для таких случаев и существуют пользовательские функции: вы один раз пишете код, а дальше используете его как обычную формулу – вводите =НАЗВАНИЕ() в ячейку и получаете результат.
В этой статье собраны семь готовых функций под задачи, с которыми рано или поздно сталкивается большинство пользователей Google Таблиц.
Где создаются пользовательские функции
Пользовательские функции пишутся в редакторе Apps Script – встроенном инструменте Google Таблиц. Чтобы открыть его, перейдите по пути «Расширения» – «Apps Script», и браузер откроет новую вкладку с редактором кода.
По умолчанию в редакторе уже есть пустая заготовка myFunction – ее можно удалить и вставить любой код из этой статьи. После этого нажмите на значок сохранения или Ctrl + S, вернитесь в таблицу, и функция сразу начнет работать в ячейках.
Все, что требуется, – аккаунт Google и любая таблица, Apps Script уже встроен в каждую из них.
TRANSLIT – транслитерация русского текста
Иногда нужно получить латинское представление русского текста: для формирования артикулов, логинов, URL-слагов или передачи данных в системы, которые не принимают кириллицу. Встроенной функции транслитерации в Google Таблицах нет, поэтому здесь и выручает пользовательская.
/**
* Транслитерирует русский текст в латиницу.
* @param {string} text Текст на русском языке.
* @return {string} Транслитерированный текст.
* @customfunction
*/
function TRANSLIT(text) {
var map = {
'а':'a','б':'b','в':'v','г':'g','д':'d','е':'e','ё':'yo','ж':'zh',
'з':'z','и':'i','й':'y','к':'k','л':'l','м':'m','н':'n','о':'o',
'п':'p','р':'r','с':'s','т':'t','у':'u','ф':'f','х':'kh','ц':'ts',
'ч':'ch','ш':'sh','щ':'sch','ъ':'','ы':'y','ь':'','э':'e','ю':'yu',
'я':'ya',
'А':'A','Б':'B','В':'V','Г':'G','Д':'D','Е':'E','Ё':'Yo','Ж':'Zh',
'З':'Z','И':'I','Й':'Y','К':'K','Л':'L','М':'M','Н':'N','О':'O',
'П':'P','Р':'R','С':'S','Т':'T','У':'U','Ф':'F','Х':'Kh','Ц':'Ts',
'Ч':'Ch','Ш':'Sh','Щ':'Sch','Ъ':'','Ы':'Y','Ь':'','Э':'E','Ю':'Yu',
'Я':'Ya'
};
return String(text).split('').map(function(char) {
return map[char] !== undefined ? map[char] : char;
}).join('');
}
Применение: =TRANSLIT(A1) – если в ячейке A1 написано «Иван Петров», функция вернет «Ivan Petrov».
Цифры, знаки препинания и пробелы передаются без изменений, а сама схема транслитерации основана на распространенном варианте для российских документов – при необходимости таблицу замены в коде легко подстроить под нужный стандарт.
PLURAL_RU – правильное склонение при числах
Русский язык требует разных форм слова в зависимости от числа: «1 день», «2 дня», «5 дней». В отчетах, счетах и автоматических уведомлениях такие подписи выглядят профессионально. Реализовывать это через ЕСЛИ нужно будет долго, к тому же подобный вариант в больших таблицах будет только создавать лишнюю нагрузку. Поэтому в подобном случае пользовательская функция сделает все за вас.
/**
* Возвращает число и правильную форму слова.
* @param {number} n Число.
* @param {string} form1 Форма для 1 (например, "день").
* @param {string} form2 Форма для 2-4 (например, "дня").
* @param {string} form5 Форма для 5 и больше (например, "дней").
* @return {string} Число и правильная форма слова.
* @customfunction
*/
function PLURAL_RU(n, form1, form2, form5) {
n = Math.abs(Math.floor(n));
var mod10 = n % 10;
var mod100 = n % 100;
var form;
if (mod10 === 1 && mod100 !== 11) {
form = form1;
} else if (mod10 >= 2 && mod10 <= 4 && (mod100 < 10 || mod100 >= 20)) {
form = form2;
} else {
form = form5;
}
return n + ' ' + form;
}
Применение: =PLURAL_RU(A1;"день";"дня";"дней") – при значении 21 вернет «21 день», при 13 – «13 дней», при 3 – «3 дня».
Отдельного внимания заслуживают числа от 11 до 19 – в русской грамматике они исключение, и алгоритм это учитывает: «11 дней», а не «11 дня». С отрицательными числами проблем тоже не возникнет. Три нужные формы можно передать для любого существительного – ограничений по тематике нет.
CLEAN_PHONE – очистка и форматирование телефонного номера
При импорте данных из CRM, форм или выгрузок телефоны приходят в самых разных видах: «+7(999)123-45-67», «89991234567», «8 999 123 45 67» и так далее. Приведение всего этого списка к единому стандарту руками займет много времени, а функция сделает это за секунды.
/**
* Приводит номер телефона к формату +7 (XXX) XXX-XX-XX.
* @param {string} phone Номер телефона в любом формате.
* @return {string} Отформатированный номер или сообщение об ошибке.
* @customfunction
*/
function CLEAN_PHONE(phone) {
var digits = String(phone).replace(/\D/g, '');
if (digits.length === 11 && (digits[0] === '7' || digits[0] === '8')) {
digits = digits.slice(1);
}
if (digits.length !== 10) {
return 'Неверный номер';
}
return '+7 (' + digits.slice(0, 3) + ') ' + digits.slice(3, 6) + '-' + digits.slice(6, 8) + '-' + digits.slice(8, 10);
}
Применение: =CLEAN_PHONE(A1) – любой из перечисленных выше вариантов записи превратится в «+7 (999) 123-45-67».
Если в ячейке окажется что-то совсем не похожее на номер, например слово или число с неподходящим количеством цифр, функция вернет «Неверный номер» вместо обычного кода ошибки. Формат вывода при необходимости легко изменить прямо в последней строке кода.
IS_EMAIL – проверка формата электронной почты
Встроенной валидации email в Google Таблицах нет, хотя проверка нужна регулярно: при подготовке базы для рассылки, при ведении клиентских данных, при проверке заполненности форм. Результатом пользовательской функции будет ИСТИНА или ЛОЖЬ, что позволяет в два клика отфильтровать проблемные строки.
/**
* Проверяет, является ли значение корректным адресом электронной почты.
* @param {string} email Адрес для проверки.
* @return {boolean} ИСТИНА, если адрес корректен.
* @customfunction
*/
function IS_EMAIL(email) {
var pattern = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
return pattern.test(String(email).trim());
}
Применение: =IS_EMAIL(A1) – вернет ИСТИНА для «user@example.com» и ЛОЖЬ для «user@» или просто «user».
По сути проверяется структура адреса: есть ли символ @, доменная часть и расширение. Убедиться, что адрес реально существует, таким способом нельзя – для этого потребовались бы запросы к почтовому серверу. Зато очевидные ошибки при вводе IS_EMAIL отловит надежно.
Читайте также в Комьюнити: Как проверить допустимые адреса электронной почты в Google Таблицах
CONVERT_CURRENCY – конвертация валюты по актуальному курсу
Пересчитывать суммы вручную по текущему курсу неудобно, а функция GOOGLEFINANCE требует изучения синтаксиса и иногда работает нестабильно. Поэтому можно создать пользовательскую функцию, которая обращается к открытому API и возвращает конвертированную сумму прямо в ячейку.
/**
* Конвертирует сумму из одной валюты в другую по текущему курсу.
* @param {number} amount Сумма для конвертации.
* @param {string} from Исходная валюта (например, "USD").
* @param {string} to Целевая валюта (например, "RUB").
* @return {number} Конвертированная сумма.
* @customfunction
*/
function CONVERT_CURRENCY(amount, from, to) {
var url = 'https://open.er-api.com/v6/latest/' + from.toString().toUpperCase();
var response = UrlFetchApp.fetch(url);
var data = JSON.parse(response.getContentText());
if (data.result !== 'success') {
return 'Ошибка получения курса';
}
var rate = data.rates[to.toString().toUpperCase()];
if (!rate) {
return 'Неизвестная валюта';
}
return amount * rate;
}
Применение: =CONVERT_CURRENCY(100;"USD";"RUB") – вернет сумму в рублях по актуальному курсу на момент вычисления.
Для работы используется бесплатный сервис без регистрации – лимит в 1500 запросов в месяц на IP-адрес для личного использования обычно более чем достаточно. Каждый вызов обращается к внешнему серверу, поэтому работает немного медленнее обычных формул. При массовом применении на сотни строк лучше рассчитать курс один раз в отдельной ячейке и умножать на него вручную.
Читайте также в Комьюнити: Как конвертировать валюту в Google Таблицах
WORD_COUNT – подсчет слов в ячейке
Встроенной функции для подсчета слов в Google Таблицах нет. Через LEN и SUBSTITUTE это реализуется, но формула получается неочевидной и ломается при двойных пробелах. Пользовательская функция справляется с этим корректно.
/**
* Подсчитывает количество слов в тексте.
* @param {string} text Текст для подсчета.
* @return {number} Количество слов.
* @customfunction
*/
function WORD_COUNT(text) {
var str = String(text).trim();
if (str === '') return 0;
return str.split(/\s+/).length;
}
Применение: =WORD_COUNT(A1) – подсчитывает слова в тексте ячейки A1, при этом двойные пробелы, табуляция и переносы строк на результат не влияют.
Разбивка идет по пробельным символам, а не по алфавиту, поэтому с любым языком результат будет корректным. Нужно обработать целый столбец – достаточно протянуть формулу вниз или подвести итог через СУММЕСЛИ.
Читайте также в Комьюнити: Как посчитать количество слов, символов и предложений в ячейке с формулами Google Таблиц
EXTRACT_DOMAIN – извлечение домена из email или URL
При работе с клиентской базой часто бывает нужно понять, сколько контактов приходится на корпоративные адреса, какие компании чаще всего встречаются в базе или сгруппировать строки по домену. Вытащить домен из email или ссылки вручную – долго, а формулами – неудобно, поэтому в таких случаях ничего не мешает воспользоваться пользовательской функцией.
/**
* Извлекает доменное имя из адреса электронной почты или URL.
* @param {string} input Email-адрес или ссылка.
* @return {string} Доменное имя.
* @customfunction
*/
function EXTRACT_DOMAIN(input) {
var str = String(input).trim().toLowerCase();
if (str.indexOf('@') !== -1) {
return str.split('@')[1];
}
return str.replace(/^https?:\/\//, '').replace(/^www\./, '').split('/')[0];
}
Применение: =EXTRACT_DOMAIN(A1) – из «user@company.ru» вернет «company.ru», из «https://www.company.ru/page» – тоже «company.ru».
Результат всегда возвращается в нижнем регистре, что упрощает последующую группировку и сравнение. Если передать строку, в которой нет ни @, ни ://, функция вернет исходный текст как есть.
Важные нюансы
Если захочется доработать приведенные функции или написать собственные, стоит заранее знать о нескольких особенностях Apps Script, которые иначе могут застать врасплох.
-
Функции привязаны к конкретному файлу. Добавленный в одну таблицу скрипт недоступен в других. Если нужна функция в новом документе, откройте его редактор Apps Script и вставьте код туда же. Либо создайте копию таблицы – скрипт скопируется вместе с ней.
-
Пользовательская функция не может изменять другие ячейки. Она только возвращает значение в ту ячейку, где вызвана. Для действий, меняющих данные в таблице, нужны макросы или триггеры – это другой инструмент.
-
Тег @customfunction в комментарии не обязателен, но полезен. Благодаря ему Google Таблицы начинают предлагать функцию в автодополнении прямо при вводе формулы – так же, как встроенные.
-
У пользовательских функций есть ограничение по времени выполнения – 30 секунд. Для тех, что обращаются к внешним сервисам (как CONVERT_CURRENCY), это особенно важно: если сервер ответит медленно, функция завершится с ошибкой. При таких проблемах стоит уменьшить количество одновременных вызовов.
Заключение
Пользовательские функции не заменяют встроенные возможности Google Таблиц, но хорошо дополняют их там, где стандартных инструментов не хватает. Семь функций из этой статьи охватывают разные задачи: одни пригодятся при работе с данными, другие – при составлении отчетов, третьи сэкономят время при ручной обработке информации. Главное – один раз вставить нужный код в редактор и дальше использовать его как обычную формулу, не думая о том, как именно он устроен внутри.
Комментарии