Google Apps Script — Custom Functions — Полезные Мелочи

Правильный канал о Таблицах (Google Sheets): @google_sheets

Чат канала: @google_spreadsheets_chat

Здравствуйте, товарищи!

В этой статье замечания про пользовательские функции, которые мне показались полезными/интересными/важными.

Для использования в формулах в Google Sheets доступны 500+ функций, но если вам чего-то всё равно не хватает, можно написать свою.

Пишутся они в Google Apps Script, называются пользовательскими функциями (custom functions).

Примеры

• Хочется импортировать JSON, а штатные функции не умеют? − ImportJSON().
• Можно достать данные о погоде с конкретного ресурса.
• Нужно достать из справочника не одно значение (для этого есть VLOOKUP()), а все, что соответствуют ключу? − товарищ Дамир Хафизов спешит на помощь с функцией VLOOKUPS().
• INDIRECT() не работает в массиве? − можно использовать решение из нашего канала INDIRECTS().
• Можно сгенерить все возможные пары из элементов двух множеств − декартово произведение.
• Недавно в чате товарищ Volond давал ссылку на любопытный сборник пользовательских функций.
• Часто спрашивают, как достать список названий листов. В одну строку делается (но мы для красоты напишем в 3):

function SHEETNAMES() {
  return SpreadsheetApp.getActiveSpreadsheet()
    .getSheets()
    .map((sheet) => [sheet.getName()]);
}

• Выводим все названия листов и диапазоны с данными на них.
• Сумма накопительным итогом. Способы через штатные функции (например, SUMIF() внутри ARRAYFORMULA()) работают крайне неэффективно, так как ходят N раз по всему массиву из N элементов, то есть совершают N² действий (пропорционально на самом деле, читайте подробнее про сложность алгоритмов, если интересно). Уже на 100 строках будет тормозить, а 1000 не посчитает. В пользовательской функции по массиву можно пройтись один раз − быстро отработает и на сотнях тысячах строк. Отдельно напишем про решение.

Разрешения

Пользователя не попросят предоставить никакие разрешения. Достаточно, чтобы был код функции и всё − можно пользоваться в таблице.

Но из-за этого есть ограничения.

Что можно использовать внутри

Из пользовательской функции доступ есть далеко не ко всем существующим функциям. Список того, что доступно есть в документации. Например, здорово, что можно делать UrlFetchApp.fetch(), есть доступ к Properties и Cache. Общее правило: с самой таблицей можно всякие get функции использовать, нельзя − set. Экспериментируйте.

Вот тут пример использования Properties и fetch().

Как использовать то, что использовать нельзя?

Один из способов − это опубликовать приложение на скрипте, которое будет обрабатывать GET или POST запросы, которое будет работать от имени пользователя, которому всё можно. А пользовательская функция через fetch() будет слать ему команды, что делать.

Аккуратнее с этим способом.

Ограничение времени выполнения

Время выполнения ограничено 30 секундами, как у функций, повешенных на simple trigger.

Ограничения на размер данных на входе и на выходе

Ограничения на размер входящих и исходящих данных есть, какие точно − неясно, но точно есть.

Например, ограничения на вход:

• Передаём столбик из единиц: 285714 штук − ок, 285715 − слишком много.

• Передаём столбик из пустот: 769229 − ок, 769230 − слишком много (первый скрин, ошибка про то, что возвращается, на вход не ругается)

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

Ограничения на выход:

• Можно вернуть массив из массивов по одному элементу с null: 525110 − ок, 525111 − слишком много.

function myFunction() {
  return Array(525111).fill([null]);
}

Понятно, если возвращать, непустые значения, ограничение в несколько раз меньше, а если в строку расположить, то наоборот − больше.

Тем не менее пользовательская функция для сумм нарастающим итогом на 285714 строчках отработала у меня за 30 сек (2 на выполнение функции, а остальные 28 на то, чтобы её запустить, передать ей данные, получить данные от неё и вставить их в строки), а известный прожорливый метод через SUMIF() в массиве так и не вернул ничего (я прождал час, не посчитало − прибил).

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

Нельзя заталкивать NOW(), RAND(), RANDBETWEEN(), RANDARRAY(), из-за того, что придётся бесконечно пересчитывать: RAND() и прочие обновляются при любом изменении в таблице, пересчёт пользовательской функции – это тоже изменение, и они друг друга будут обновлять.

TODAY() – можно передать в пользовательскую функцию.

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

Когда вызывается?

Вызывается при любом изменении входящих данных.

Если на входе что-то долгосчитающееся, что сначала вернёт Loading..., то функция вызовется два раза: сначала получит Loading..., увидит это как строку #ERROR!, потом уже реальные данные.

Как в обычную js функцию, можно передать больше параметров, чем требуется. Они будут просто проигнорированы. Можно повесить на галочку и провоцировать выполнение функции.

Что с триггерами?

Устанавливаемый триггер onChange() срабатывает (постарается) на каждый пересчёт каждого экземпляра функции. Делайте поправку на то, что триггеры могут некоторые события проморгать.

В массиве работают?

Не работает в массиве, то есть не будет она много раз вызываться (по разу для каждого элемента массива), но может работать с массивом (в неё можно передать и диапазон).

Понятно, ARRAYFORMULA() может работать с тем, что вернёт функция.

Ошибки

Можно выкинуть ошибку со своим текстом, например, поругать пользователя за плохие данные, которые он передал.

Достаточно сделать так:

throw Error('Текст ошибки, туда-сюда.');

Подсказки

Пользователя можно не только ругать за тупость, но и помочь ему не тупить. Тем более, что обычно ты − это он и есть.

Можно добавить JSDoc комментарий, чтобы была справка по функции в таблице (простая функция, умножает то, что ей дали, на 2):

/**
 * 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;
}

В комментариях на любом языке можно писать. Напишете на русском – подсказка будет на русском.

Как понять, в какой ячейке используется функция

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

• SpreadsheetApp.getActiveSpreadsheet() — в какой таблице работает
• SpreadsheetApp.getActiveSheet() — на каком листе работает
• SpreadsheetApp.getActiveRange() — в какой ячейке листа расположена формула

Тривиальный вариант использования – для логов. Что-то сложнее – например, передавать данные с помощью UrlFetchApp.fetch() с дополнительной информацией об источнике этих данных.

Что за диапазон передали в пользовательскую формулу?

Нельзя понять, что за диапазон (именованный или нет, виртуальный или реальный) был передан. Передаются именно данные. Если надо понять, что и откуда было передано, а потом это использовать, то можно передавать строку, а внутри пользовательской функции делать getRange() или getNamedRange().

Хотя есть кое-какой способ, можно раздербанить текст формулы:

function myFunction() {
  // Get the formula this custom function is used in
  const formula = SpreadsheetApp.getActiveRange().getFormula();

  // Simple regex to get custom function's arguments
  const rePtrn = /(?<=myFunction\().*?(?=\))/ig;

  // MatchAll
  const result = [...formula.matchAll(rePtrn)];

  return result;
}

Это минимальный пример, и даже в нём многое не учитывается (переносы, вложенные скобки и пр.).

Так как в регулярках js нет рекурсии, легко достать содержимое скобок можно только в простом случае (выше). Иначе придётся строить синтаксическое дерево (гуглить про abstract syntax tree, concrete syntax tree, добавлять в строку поиска "Excel"). Скорее всего, того не стоит. Толк будет только в случае, если передаются реальные ячейки (A1), диапазоны ($A$1:B12) или именованные диапазоны. Если передаётся виртуальное значение, то есть такое, которое не является ссылкой на реальный диапазон таблицы, а получено с помощью вычислений (например, результат VLOOKUP(...) или 1 + 1), то что толку такое понять?

На этом всё. Спасибо за внимание.

Ссылки

Google Sheets

• Все доступные функции Google Sheets
• Секретные функции, тоже можно использовать напрямую, но на них нет документации.

Google Apps Script − Custom Functions

• Основная документация

Готовые решения

• ImportJSON()
• VLOOKUPS()
• INDIRECTS()
• Данные о погоде
• Декартово произведение
• Выводим все названия листов и диапазоны с данными на них.
• Пример использования Properties и fetch()
• Любопытный сборник пользовательских функций
• Куча всяких разных проскакивали в чате − ищите, спрашивайте (именно в таком порядке).

Правильный канал о Таблицах (Google Sheets): @google_sheets

Чат канала: @google_spreadsheets_chat