FLATTEN 2: другие недокументированные функции Google Sheets

Мой канал о Гугл Таблицах: @pro_google_sheets

English speaking guys and gals! Here's the same article in English, and here's the same table in English.

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

Кому не терпится, вот ссылка на таблицу с полным списком недокументированных функций Google Sheets (описания, детали, примеры прилагаются): go crazy!

А ниже я опишу, откуда этот список функций взялся.

Откуда взялась первая FLATTEN

Среди экспертов по Google Sheets была известна недокументированная функция FLATTEN. Я первый раз встретил её на Stack Overflow в апреле 2020-го. Рассказал мне (и рассказывал всем, до кого мог добраться) о ней MattKing. Вроде бы в марте эту недокументированную функцию заметили первый раз. Если я правильно понял, то кто-то пытался сделать пользовательскую функцию с таким же именем, но не получилось. Начали разбираться - оказалось, есть такая функция. Её изучили и начали использовать.

Функция оказалась крайне полезная, можно сказать, фундаментально полезная. Пригождается во множестве задач, значительно упрощает и сокращает формулы. Сейчас Google её легализовал, у неё появилась статья в документации.

Достаём полный список функций

Обсуждали мы с Vitaliy P. в одном небезызвестном чате по Google Таблицам кривую работу поиска в выпадающем списке Data Validation. Возникла мысль посмотреть на реализацию в javascript, ведь он выполняется локально, а значит, код доступен. Хотя решать эту задачу мне не очень хотелось, да и Виталий не советовал, так как весь js там обфусцирован (недостаточно, хехе, а может, и не пытались разработчики) и минимизирован (никак не помешало), тем не менее полез одним глазком посмотреть.

Решил найти какую-нибудь функцию, чтоб посмотреть её реализацию. Выбрал ARRAY_CONSTRAIN (как мне показалось, такое имя вряд ли будут использовать вне контекста табличной функции, в отличие от SUM, например), запустил поиск по всем файлам, посмотрел на результаты.

В одном из результатов была здоровенная строка. Сразу увидел знакомые названия других функций, расположенные по алфавиту (почти, но это не важно). Вот и всё. Вот мы и нашли полный список функций доступных в Google Sheets.

Вот так нехитро выглядит поиск списка функций

[Конечно, это не очень корректное заявление про "полный список". Может, есть ещё какие-нибудь функции, чьи имена всё же обфусцированы и отсутствуют в этой строке.]

Недокументированные функции Google Sheets

Помня про историю с FLATTEN, тут же возникла мысль проверить на несовпадения два списка функций: список из документации и список из строки.

Проверять стал в Google Sheets (там же и попробую, если что-то найдётся...).

Достаём список из официальной документации формулой:

=IMPORTHTML("https://support.google.com/docs/table/25273?hl=en"; "table"; 1)

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

Преобразовываем нашу строку (поместил в C2 на отдельном листе), скопированную из js, в список имён функций другой формулой:

=TRANSPOSE(SPLIT(C2; " "))

Список имён функций Google Sheets из js-файла

Теперь сравним два списка имён. Оставим из второго только те, что отсутствуют в первом. Для этого используем формулу:

=FILTER(
  '🤔 Functions from JS'!A:A;
  '🤔 Functions from JS'!A:A <> "";
  ISNA(MATCH('🤔 Functions from JS'!A:A; '📚 Google Sheets function list'!B:B; 0))
)

На самом деле MATCH пропускает функцию * (звёздочка, оператор умножения), так как это спецсимвол (wildcard). Поэтому MATCH был заменён на другую функцию (на скрине).

Изучение новых функций

Изучал я их вручную, прошёлся по всем (по некоторым только глазами, другие и попробовал). Постарался описать всё, что удалось понять (если нигде не ошибся) и то, как изучал. Местами написал примеры.

Всё в таблице по ссылке (это та же ссылка, что и в начале статьи): Недокументированные функции Google Sheets

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

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

Будем (всё человечество) очень признательны, если вам есть, чем дополнить результаты в этой таблице. Пишите комментарии прямо в таблице или заходите в чат и рассказывайте. Крайне ценными будут примеры использования. Делитесь информацией с товарищами!

Примеры

Число прописью (UPD: не рекомендуется использовать)

Формула (костыль, но всё же):

=LOWER(REGEXREPLACE(GOOGLETRANSLATE(BAHTTEXT(123456789); "th"; "ru"); "\.|,|\s+тыс\s+бат\s*";))

Результат:

сто двадцать три миллиона четыреста пятьдесят шесть тысяч семьсот восемьдесят девять

Именно такой костыль GOOGLETRANSLATE(BAHTTEXT(...)) не рекомендуется использовать, так как GOOGLETRANSLATE() иногда возвращает какую-то дичь:

Как часто элемент встречается в диапазоне (в процентах)

Формула (единицы - это сколько процентов?):

=PERCENTIF({1;2;3;1;1;1;1;1;1}; 1)

Результат:

78%

Как говорит лисичка? 😄

Формула:

=WHATTHEFOXSAY()

Результат (случайная строка из фиксированного набора):

Joff-tchoff-tchoff-tchoffo-tchoffo-tchoff!

Если эта песня по какой-то причине прошла мимо вас, сейчас самое время с ней познакомиться

Функция WHATTHEFOXSAY, может, и выглядит смешно, но с её помощью решаются некоторые серьёзные задачи (лучи добра player0, кто провёл и опубликовал эти исследования) (ENG):

Замечание

Статья называется FLATTEN 2. Среди функций, действительно, есть работающая так же как FLATTEN (я до конца не уверен, для чего она нужна): CONDITION_FLATTEN. Если бы в марте 2020-го FLATTEN не нашли, то её нашли бы сейчас.

Мне, конечно, приятно, что я сам догадался это всё проделать (хоть и топорный метод, примитивно всё и на поверхности). Тем не менее я не думаю, что был первым (первыми были разработчики, привет, ребята!). Уверен, кто-нибудь это уже проделывал до меня, но, может, не опубликовал (я попытался поискать пару полезных функций со сложными названиями в Google и ничего не нашёл). Так что не претендую на научную новизну или хоть какую полноту в изучении вопроса. Нобелевку не надо, откажусь.

Но если что, пишите. :)