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

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

Начиная

Пользовательские функции создаются с использованием стандартного JavaScript. Если вы новичок в JavaScript, Codecademy предлагает отличный курс для начинающих . (Примечание: этот курс не был разработан 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 и у вас нет времени на обучение, проверьте магазин дополнений, чтобы узнать, не создал ли кто-то уже нужную вам пользовательскую функцию.

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

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

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

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

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

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

1. Создайте или откройте электронную таблицу в Google Таблицах.
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... бесконечно.

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

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

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

Типы данных

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

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

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

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

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

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

Передовой

Использование сервисов Google Apps Script

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

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

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

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

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

Поделиться

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

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

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

Оптимизация

Каждый раз, когда пользовательская функция используется в таблице, Google Таблицы отправляют отдельный вызов серверу 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 объекта JavaScript Array для двумерного массива ячеек для получения каждой строки, а затем для каждой строки снова вызывает map , чтобы вернуть удвоенное значение каждой ячейки. Возвращается двумерный массив, содержащий результаты. Таким образом, можно вызвать 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;
}

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