Пользовательские функции в Google Таблицах

Google Sheets предлагает сотни встроенных функций, таких как AVERAGE , SUM и VLOOKUP . Если этого недостаточно для ваших нужд, вы можете использовать Apps Script для написания пользовательских функций, а затем использовать их в Sheets так же, как и встроенные функции.

Примеры пользовательских функций можно найти в следующих руководствах:

• Рассчитайте продажную цену товаров со скидкой (быстрый старт)
• Рассчитайте скидку в зависимости от уровня ценообразования.
• Рассчитайте расстояние по дороге и переведите метры в мили.
• Свести воедино данные из нескольких листов.
• Проверка фактов с помощью агента ADK AI и модели Gemini.

Начиная

Пользовательские функции создаются с использованием стандартного JavaScript. Если вы новичок в JavaScript, Codecademy предлагает курс для начинающих . Этот курс не был разработан Google и не связан с Google.

Вот пользовательская функция с именем DOUBLE , которая умножает входное значение на 2:

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
function DOUBLE(input) {
  return input * 2;
}

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

Создайте пользовательскую функцию

Чтобы написать собственную функцию:

1. Создайте или откройте электронную таблицу в Google Sheets.
2. Выберите пункт меню «Расширения» > «Apps Script» .
3. Удалите весь код из редактора скриптов. Для функции DOUBLE , показанной ранее, скопируйте и вставьте код в редактор скриптов.
4. Вверху нажмите «Сохранить .

Теперь вы можете использовать пользовательскую функцию .

Получите пользовательскую функцию из Google Workspace Marketplace.

В Google Workspace Marketplace представлен ряд пользовательских функций в виде надстроек Google Workspace для Google Sheets . Чтобы использовать или изучить эти надстройки:

1. Создайте или откройте электронную таблицу в Google Sheets.
2. Вверху нажмите «Дополнения» > «Получить дополнения» .
3. После открытия Google Workspace Marketplace нажмите на поле поиска в правом верхнем углу.
4. Введите "пользовательская функция" и нажмите Enter.
5. Если вы нашли интересующее вас дополнение с пользовательскими функциями, нажмите «Установить» , чтобы установить его.
6. В диалоговом окне может появиться сообщение о том, что для работы дополнения требуется авторизация. В этом случае внимательно прочитайте уведомление, а затем нажмите «Разрешить» .
7. Надстройка становится доступной в электронной таблице. Чтобы использовать надстройку в другой электронной таблице, откройте эту таблицу и вверху нажмите «Надстройки» > «Управление надстройками» . Найдите нужную надстройку и нажмите > «Использовать в этом документе» .

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

После того как вы написали собственную функцию или установили её из Google Workspace Marketplace, она используется так же, как и встроенная функция:

1. Щелкните по ячейке, в которой хотите использовать функцию.
2. Введите знак равенства ( = ), затем имя функции и любое входное значение — например, =DOUBLE(A1) — и нажмите Enter.
3. В ячейке на мгновение отображается Loading... , после чего возвращается результат.

Рекомендации по настройке пользовательских функций

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

именование функций

Помимо стандартных соглашений об именовании функций JavaScript, следует учитывать следующее:

• Название пользовательской функции должно отличаться от названий встроенных функций, таких как SUM() .
• Название пользовательской функции не может заканчиваться символом подчеркивания ( _ ), который в Apps Script обозначает приватную функцию.
• Имя пользовательской функции должно быть объявлено с использованием синтаксиса ` function myFunction() , а не ` var myFunction = new Function() .
• Регистр букв не имеет значения, хотя названия функций электронных таблиц традиционно пишутся с заглавной буквы.

Аргументы

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

• Если вы вызываете функцию, передавая в качестве аргумента ссылку на отдельную ячейку (например =DOUBLE(A1) ), то аргументом будет значение этой ячейки.

• Если вы вызываете функцию, передавая в качестве аргумента ссылку на диапазон ячеек (например, =DOUBLE(A1:B10) ), аргументом является двумерный массив значений ячеек. Например, на следующем скриншоте аргументы в =DOUBLE(A1:B2) интерпретируются Apps Script как double([[1,3],[2,4]]) . Обратите внимание, что описанный ранее пример кода для DOUBLE необходимо изменить, чтобы он принимал массив в качестве входных данных .

  
  

• Аргументы пользовательских функций должны быть детерминированными . То есть, встроенные функции электронных таблиц, которые возвращают разные результаты при каждом вычислении — такие как NOW() или RAND() — не допускаются в качестве аргументов для пользовательской функции. Если пользовательская функция попытается вернуть значение, основанное на одной из этих нестабильных встроенных функций, она будет бесконечно отображать Loading... .

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

Возвращаемые значения

Каждая пользовательская функция должна возвращать значение для отображения, а именно:

• Если пользовательская функция возвращает значение, это значение отображается в ячейке, из которой была вызвана функция.
• Если пользовательская функция возвращает двумерный массив значений, значения будут переполнять соседние ячейки, пока эти ячейки пусты. Если это приведет к перезаписи содержимого существующих ячеек, пользовательская функция вместо этого выдаст ошибку. Пример см. в разделе об оптимизации пользовательских функций .
• Пользовательская функция не может влиять на ячейки, кроме тех, которым она возвращает значение. Другими словами, пользовательская функция не может редактировать произвольные ячейки, только те, из которых она вызывается, и соседние с ними ячейки. Для редактирования произвольных ячеек используйте пользовательское меню для запуска функции.
• Вызов пользовательской функции должен завершиться в течение 30 секунд. В противном случае в ячейке отобразится #ERROR! и примечание к ячейке: Exceeded maximum execution time (line 0).

Типы данных

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

• В Apps Script время и даты в таблицах преобразуются в объекты типа Date . Если электронная таблица и скрипт используют разные часовые пояса (что встречается редко), пользовательская функция должна это компенсировать.
• Значения продолжительности в таблицах также преобразуются в объекты Date , но работа с ними может быть сложной .
• В Apps Script значения в процентах преобразуются в десятичные числа в таблице Sheets. Например, ячейка со значением 10% в Apps Script преобразуется 0.1 .

Автозаполнение

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

Пользовательские функции отображаются в этом списке, если их скрипт содержит тег JSDoc @customfunction , как в примере с функцией DOUBLE() .

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

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return {number} The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

Передовой

В этом разделе рассматриваются расширенные возможности настройки пользовательских функций.

Используйте сервисы Google Apps Script.

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

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

Поддерживаемые услуги	Примечания
Кэш	Работает, но не особенно полезно в пользовательских функциях.
HTML	Может генерировать HTML-код, но не может его отображать (что редко бывает полезно).
JDBC
Язык
Замок	Работает, но не особенно полезно в пользовательских функциях.
Карты	Может рассчитывать маршрут, но не отображает карты.
Характеристики	getUserProperties() получает только свойства владельца электронной таблицы. Редакторы электронных таблиц не могут задавать свойства пользователя в пользовательской функции.
Электронная таблица	Доступно только для чтения (можно использовать большинство методов get*() , но не set*() ). Не удается открыть другие электронные таблицы ( SpreadsheetApp.openById() или SpreadsheetApp.openByUrl() ).
Получение URL-адреса	Получайте доступ к ресурсам в интернете, загружая URL-адреса.
Коммунальные услуги
XML

Если ваша пользовательская функция выдает сообщение об ошибке You do not have permission to call X service. означает, что для работы службы требуется авторизация пользователя, и поэтому ее нельзя использовать в пользовательской функции.

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

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

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

• Чтобы открыть редактор скриптов, нажмите «Расширения» > «Скрипты приложений» , затем скопируйте текст скрипта из исходной электронной таблицы и вставьте его в редактор скриптов другой электронной таблицы.
• Создайте копию электронной таблицы, содержащей пользовательскую функцию, нажав «Файл» > «Создать копию» . При копировании электронной таблицы копируются и все прикрепленные к ней скрипты. Любой, кто имеет доступ к электронной таблице, может скопировать скрипт. (Участники, имеющие только доступ на просмотр, не могут открыть редактор скриптов в исходной электронной таблице. Однако, создав копию, они становятся ее владельцами и могут видеть скрипт.)
• Опубликуйте скрипт как надстройку для редактора таблиц.

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

Оптимизация

Каждый раз, когда в электронной таблице используется пользовательская функция, Sheets выполняет отдельный вызов к серверу Apps Script. Если ваша электронная таблица содержит десятки (или сотни, или тысячи!) вызовов пользовательских функций, этот процесс может быть медленным. В некоторых проектах с большим количеством сложных пользовательских функций может наблюдаться временная задержка в выполнении.

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

Например, показанную ранее функцию DOUBLE() можно переписать таким образом, чтобы она принимала одну ячейку или диапазон ячеек:

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

Этот подход использует метод `map` объекта ` Array в JavaScript для получения каждой строки из двумерного массива ячеек, а затем для каждой строки снова использует map для возврата значения каждой ячейки, равного 2. В результате возвращается двумерный массив, содержащий результаты. Таким образом, вы можете вызвать DOUBLE всего один раз, но при этом он будет выполнять вычисления для большого количества ячеек одновременно, как показано на следующем скриншоте. Аналогичного результата можно добиться с помощью вложенных операторов if вместо вызова map .

Аналогично, следующая пользовательская функция эффективно получает контент в режиме реального времени из Интернета и использует двумерный массив для отображения двух столбцов результатов всего одним вызовом функции. Если бы для каждой ячейки требовался отдельный вызов функции, операция заняла бы значительно больше времени, поскольку серверу Apps Script пришлось бы каждый раз загружать и анализировать XML-поток.

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

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