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

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

Начиная

Пользовательские функции создаются с использованием стандартного 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 Sheets.
  2. Выберите пункт меню Расширения > Скрипт приложений .
  3. Удалите любой код в редакторе скриптов. Для приведенной выше функции DOUBLE просто скопируйте и вставьте код в редактор скриптов.
  4. Вверху нажмите .

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

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

Google Workspace Marketplace предлагает несколько пользовательских функций в качестве дополнений к 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... на неопределенный срок.

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

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

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

Типы данных

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

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

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

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

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

Поддерживаемые услуги Примечания
Кэш Работает, но не особенно полезно в пользовательских функциях.
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 Таблиц.

Оптимизация

Каждый раз, когда в электронной таблице используется пользовательская функция, Google 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;
}

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

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

,

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

Начиная

Пользовательские функции создаются с использованием стандартного 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 Sheets.
  2. Выберите пункт меню Расширения > Скрипт приложений .
  3. Удалите любой код в редакторе скриптов. Для приведенной выше функции DOUBLE просто скопируйте и вставьте код в редактор скриптов.
  4. Вверху нажмите .

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

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

Google Workspace Marketplace предлагает несколько пользовательских функций в качестве дополнений к 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... на неопределенный срок.

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

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

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

Типы данных

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

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

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

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

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

Поддерживаемые услуги Примечания
Кэш Работает, но не особенно полезно в пользовательских функциях.
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 Таблиц.

Оптимизация

Каждый раз, когда в электронной таблице используется пользовательская функция, Google 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;
}

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

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