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

Custom Functions in Google Sheets

Google Таблицы предлагают сотни встроенных функций, таких как СРЗНАЧ (AVERAGE), СУММ (SUM) и ВПР (VLOOKUP). Когда их недостаточно для ваших нужд, вы можете использовать Google Apps Script, чтобы написать собственную функцию, например, конвертирование метров в мили или получение данных непосредственно из Интернета, и использовать ее как обычную встроенную функцию.

Начало

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

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

function DOUBLE(input) {
  return input * 2;
}

Если вы не знаете, как использовать JavaScript, или у вас нет времени для изучения, посмотрите дополнения к Таблицам, возможно, там уже есть нужная вам функция.

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

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

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

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

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

Хранилище дополнений предлагает несколько пользовательских функций в виде дополнений к Google Таблицам. Чтобы использовать или найти дополнение:

  1. Создайте или откройте Google Таблицу.
  2. Выберите главное меню Дополнения > Установить дополнения.
  3. Откроется окно хранилища дополнений. В поле поиска введите "custom function" и нажмите ввод.
  4. Если вы нашли интересующее вас приложение, нажмите на кнопку "+БЕСПЛАТНО" для установки.
  5. Диалоговое окно может сообщить вам, что дополнение требует авторизации. Если это так, внимательно прочитайте сообщение и нажмите "Разрешить". После этого будет установлено дополнение.
  6. Дополнение установлено. Для использования его в других Таблицах откройте любую из них и выберите в главном меню Дополнения > [имя дополнения] > Используйте его в текущей Таблице.

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

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

  1. Нажмите на ячейку, которую собираетесь использовать для функции.
  2. Введите знак равенства (=), за которым следует имя функции с каким-либо параметром. Например, =DOUBLE(A1). Далее нажмите ввод.
  3. Ячейка какое-то мгновение будет отбражать "Loading...", а после этого выведет результат. The cell will momentarily display Loading..., then return the result.

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

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

Выбор имени

В дополенние к стантартным соглашениям о выборе имени JavaScript функции, обратите внимание на следующее:

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

Аргументы

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

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

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

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

  • Если функция возвращает одно значение, то оно отображается в ячейке, в которой была вызвана функция.
  • Если пользовательская функция возвращает двумерный массив значений, значения будут заполнять соседние клетки вправо и вниз до тех пор, как эти клетки остаются пустыми. Если же заполнение приведет к перезаписи существующих значений в соседних ячейках, пользовательская функция выдаст ошибку. Для примера, смотрите раздел по оптимизации пользовательских функций.
  • Пользовательская функция не может повлиять на другие, чем на те, в которые она возвращает значение, ячейки. Другими словами, пользовательская функция не может редактировать произвольные диапазоны, а только те, из которых она вызывается и соседние. Чтобы редактировать произвольные ячееки, рекомендуется использовать пользовательское меню для запуска функции.
  • Вызов пользовательской функции должн вернуть значение в течение 30 секунд. Если этого не произошло, то ячейка будет отображать сообщение об ошибке: "Внутренняя ошибка при выполнении пользовательской функции".

Типы данных

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

  • Времена и даты в Таблицах становится объектом Date в Apps Script. Если Таблица и сценарий используют различные часовые пояса (редкая проблема), пользовательская функция должна самостоятельно это компенсировать.
  • Значения Длительность в Таблицах также становятся объектами 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;
}

Дополнительно

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

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

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

Supported services Notes
Cache Работает, но не особенно полезно в пользовательских функциях
HTML Может генерировать HTML, но не может отобразить его (используется редко)
JDBC
Language
Lock Работает, но не особенно полезно в пользовательских функциях
Maps Можно рассчитать направления, но не отображать карты
Properties
Spreadsheet Только для чтения (можно использовать большинство get\*() методов, но не set\*().
Нельзя открыть другие Таблицы (SpreadsheetApp.openById() или SpreadsheetApp.openByUrl())
URL Fetch
Utilities
XML ...

Если ваша пользовательская функция выбрасывает сообщение об ошибке "У вас нет разрешения на вызов X службы.", значит служба требует авторизации пользователя и, таким образом, не может быть использована.

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

Общий доступ

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

  • Выберите в меню Инструменты > Редактор сценариев, чтобы открыть редактор сценариев, а затем скопировать текст сценария из исходной Таблицы и вставить его в редактор сценариев другой Таблицы.
  • Сделайте копию Таблицы, которая содержит пользовательскую функцию, выбрав в меню Файл > Создать копию. Когда Таблица копируется, любые скрипты, прикрепленные к ней копируются также. Любой, кто имеет доступ к Таблице может скопировать сценарий. (Соавторы, которые имеют доступ только на просмотр не могут открыть редактор скриптов в исходной Таблице. Тем не менее, когда они делают копию, они становятся владельцем копии и могут видеть скрипт).
  • Опубликуйте скрипт как дополнение к Google Таблицам.

Оптимизация

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

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

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

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value or range of cells to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  if (input.map) {            // Test whether input is an array.
    return input.map(DOUBLE); // Recurse over array if so.
  } else {
    return input * 2;
  }
}

Описанный выше подход использует метод map объекта Array из JavaScript, чтобы рекурсивно вызвать DOUBLE() для каждого значения в двумерном массиве ячеек. Она возвращает двумерный массив, содержащий результаты. Таким образом, вы можете вызвать DOUBLE только один раз, но вычислить его для большого числа клеток сразу же, как показано на рисунке ниже. (Вы можете сделать то же самое с вложенными if вместо вызова map). Пример пользовательской функции

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

/**
 * Show the title and date for the first page of posts on the Google Apps
 * Developer blog.
 *
 * @return Two columns of data representing posts on the Google Apps
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'http://googleappsdeveloper.blogspot.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;
}

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

results matching ""

    No results matching ""