Google Таблицы предлагают сотни встроенных функций, таких как AVERAGE , SUM и VLOOKUP . Если этого недостаточно для ваших нужд, вы можете использовать скрипт Google Apps для написания пользовательских функций — например, для преобразования метров в мили или извлечения живого контента из Интернета — а затем использовать их в 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 и у вас нет времени учиться, загляните в магазин дополнений , чтобы узнать, не создал ли кто-то еще нужную вам пользовательскую функцию.
Создание пользовательской функции
Чтобы написать пользовательскую функцию:
- Создайте или откройте электронную таблицу в Google Sheets.
- Выберите пункт меню «Расширения» > «Скрипт приложений» .
- Удалите любой код в редакторе скриптов. Для приведенной выше функции
DOUBLEпросто скопируйте и вставьте код в редактор скриптов. - Вверху нажмите Сохранить .
Теперь вы можете использовать пользовательскую функцию .
Получение пользовательской функции из Google Workspace Marketplace
Google Workspace Marketplace предлагает несколько пользовательских функций в качестве надстроек для Google Таблиц . Чтобы использовать или исследовать эти надстройки:
- Создайте или откройте электронную таблицу в Google Sheets.
- Вверху нажмите «Дополнения» > «Получить дополнения» .
- Когда откроется Google Workspace Marketplace , щелкните поле поиска в правом верхнем углу.
- Введите «пользовательская функция» и нажмите Enter.
- Если вы найдете интересующую вас дополнительную функцию, нажмите «Установить» , чтобы установить ее.
- В диалоговом окне может быть указано, что дополнение требует авторизации. Если это так, внимательно прочитайте уведомление и нажмите «Разрешить» .
- Дополнение становится доступным в электронной таблице. Чтобы использовать надстройку в другой электронной таблице, откройте другую таблицу и вверху щелкните надстройки > Управление надстройками . Найдите надстройку, которую хотите использовать, и щелкните Параметры > Использовать в этом документе .
Использование пользовательской функции
После того, как вы написали пользовательскую функцию или установили ее изGoogle Workspace Marketplace, использовать ее так же просто, как и встроенную функцию:
- Щелкните ячейку, в которой вы хотите использовать функцию.
- Введите знак равенства (
=), за которым следует имя функции и любое входное значение — например,=DOUBLE(A1)— и нажмите Enter. - Ячейка на мгновение отобразит
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 секунд. Если это не так, в ячейке будет отображаться ошибка:
Internal error executing the custom function.
Типы данных
Google Таблицы хранят данные в разных форматах в зависимости от характера данных. Когда эти значения используются в пользовательских функциях, Apps Script обрабатывает их как соответствующий тип данных в JavaScript . Вот наиболее распространенные области путаницы:
- Время и даты в Таблицах становятся объектами Date в Apps Script. Если таблица и сценарий используют разные часовые пояса (редкая проблема), пользовательская функция должна компенсировать это.
- Значения длительности в Sheets также становятся объектами
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
Пользовательские функции могут вызывать определенные службы 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.
Обмен
Пользовательские функции начинаются с привязки к электронной таблице, в которой они были созданы. Это означает, что пользовательскую функцию, написанную в одной электронной таблице, нельзя использовать в других электронных таблицах, если вы не используете один из следующих методов:
- Нажмите «Расширения» > «Сценарий приложений» , чтобы открыть редактор сценариев, затем скопируйте текст сценария из исходной электронной таблицы и вставьте его в редактор сценариев другой таблицы.
- Сделайте копию электронной таблицы, содержащей пользовательскую функцию, щелкнув Файл > Создать копию . При копировании электронной таблицы все прикрепленные к ней скрипты также копируются. Любой, у кого есть доступ к электронной таблице, может скопировать сценарий. (Соавторы, у которых есть доступ только для просмотра, не могут открыть редактор сценариев в исходной электронной таблице. Однако, когда они делают копию, они становятся владельцем копии и могут видеть сценарий.)
- Опубликуйте скрипт как дополнение к редактору Google Таблиц.
Оптимизация
Каждый раз, когда пользовательская функция используется в электронной таблице, Google Sheets выполняет отдельный вызов на сервер сценариев приложений. Если ваша электронная таблица содержит десятки (или сотни, или тысячи!) вызовов пользовательских функций, этот процесс может быть довольно медленным.
Следовательно, если вы планируете использовать пользовательскую функцию несколько раз для большого диапазона данных, рассмотрите возможность изменения функции, чтобы она принимала диапазон в качестве входных данных в форме двумерного массива, а затем возвращала двумерный массив, который может перетекают в соответствующие ячейки.
Например, показанную выше функцию 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 для рекурсивного вызова DOUBLE для каждого значения в двумерном массиве ячеек. Он возвращает двумерный массив, содержащий результаты. Таким образом, вы можете вызвать DOUBLE только один раз, но при этом рассчитать сразу большое количество ячеек, как показано на снимке экрана ниже. (То же самое можно было бы сделать с помощью вложенных операторов if вместо вызова map .)
Точно так же приведенная ниже пользовательская функция эффективно извлекает живой контент из Интернета и использует двумерный массив для отображения двух столбцов результатов всего одним вызовом функции. Если бы для каждой ячейки требовался вызов собственной функции, операция заняла бы значительно больше времени, поскольку серверу сценариев приложений приходилось бы каждый раз загружать и анализировать 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;
}
Эти методы можно применять практически к любой пользовательской функции, которая многократно используется в электронной таблице, хотя детали реализации будут различаться в зависимости от поведения функции.