FLATTEN 2: Other Undocumented Google Sheets Functions

My telegram channel on Google Sheets: @pro_google_sheets

Hello, comrades!

If you are anxious to get your hands on those functions, here's a link to a spreadsheet with the complete list of undocumented Google Sheets functions (descriptions, details, examples are there): go crazy!

Below I will describe where this list of functions came from.

Where did the first FLATTEN come from?

Among Google Sheets experts, an undocumented FLATTEN function was known. First time I heard about it was in April, 2020 on Stack Overflow. MattKing was the one who told me (and was telling everyone he could reach) about it. It seems that in March, 2020 this undocumented function was noticed for the first time. If I understand correctly, someone tried to make a custom function with the same name, but failed. People started to look into that failure and it turned out that there is already a function with that name. People studied it and began to use it.

The function turned out to be extremely useful, one might say fundamentally useful. It comes in handy for a variety of tasks, greatly simplifies and shortens formulas. Now Google has legalized it, it has gotten an article in the documentation.

Let's get the complete list of functions

In our little mutual help chat on Google Sheets (main language is Russian, but everybody is welcome, just ask anything in English - someone will help) I and Vitaliy P. discussed how search functionality works in Data Validation drop-down lists. I got the idea to look at the JavaScript implementation as it is executed locally, which means that the code is available. Although I didn’t really want to dive deep into it, and Vitaly also didn’t recommend it, since all the js there is obfuscated (not enough, heh, or maybe the developers didn’t do it at all) and minimized (it didn’t hamper at all), I nevertheless went into it to take a peek.

I decided to find some function to see its implementation. I chose ARRAY_CONSTRAIN (as it seemed to me, such a name is unlikely to be used outside the context of a spreadsheet function, unlike SUM, for example) and tried searching for it in all files, looked at the results.

One of the results was a massive string. I immediately recognized familiar names of other functions arranged alphabetically (almost, but it does not matter). That's all. That's how you get the complete list of functions available in Google Sheets.

Plain and simple process of finding the functions list.

[Of course, that is not a fully correct statement for the list being "complete". There maybe some other functions whose names are actually obfuscated and are absent in that string.]

Undocumented Google Sheets Functions

Keeping in mind the story with FLATTEN, I immediately got the idea to check for mismatches in two lists of functions: the list from the documentation and the list from a string.

I performed this check right in Google Sheets (will try it on the spot in case I find something...).

Let's get the list from the official documentation with the formula:

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

List of Google Sheets functions from official documentation

We transform our massive string (I placed it in C2 cell on a separate sheet), copied from js, into the list of function names with another formula:

=TRANSPOSE(SPLIT (C2, " "))

List of Google Sheets function names from js file

Now we'll compare two lists of names. Let's keep just the names from the second list which are absent in the first one. For this we use the formula:

=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))
)

That's not the MATCH function actually on the screenshot :)

Actually MATCH skips the * function (asterisk, multiplication operator), since it is a wildcard for the search. Therefore MATCH was replaced with another function (on the screen).

Studying new functions

I studied them by hand, walked through all of them (at some I just took a glance, others I tried using). I tried to describe everything that I managed to understand (if I was not mistaken anywhere) and how I studied them. Wrote some examples.

Everything is in the shared spreadsheet (this is the same link as at the beginning of this article): Undocumented Google Sheets Functions

I recommend that you first look at the functions highlighted in green - in my opinion, these are the most useful. However, all are of interest, although some are only of aesthetic interest.

The studying process is not complete. There are questions about the functions highlighted in yellow.

We (all the humanity) will be very grateful if you have something to add to the results in this table. Write your comments directly in the table or join the chat and tell. Use cases will be extremely valuable. Share information with your friends!

Examples

Number in words (UPD: not recommended)

Formula (dirty hack, but still):

=REGEXREPLACE(LOWER(GOOGLETRANSLATE(BAHTTEXT(123456789), "th", "en")), "\.|,|\s+thousand\s+baht\s*",)

Result:

one hundred and twenty three million four hundred and fifty-six thousand seven hundred and eighty nine

This particular dirty hack with GOOGLETRANSLATE(BAHTTEXT(...)) is not recommended to use, since GOOGLETRANSLATE() sometimes returns some kind of nonsense:

Instead of "three" there is "three thousand"

How often an item occurs in the range (percentage)

Formula (units will be the percentage):

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

Result:

78%

What does the fox say? 😄

Formula:

=WHATTHEFOXSAY()

Result (random string from a fixed set):

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

If for some reason you missed out on this, then it is the time to get to make yourself familiar with the masterpiece.

WHATTHEFOXSAY function looks like a joke, but could be used for quite serious tasks (credits to player0 who did the research):

Final note

This article is called FLATTEN 2. Among the functions, there is really one that works like FLATTEN (I'm not completely sure what it is for): CONDITION_FLATTEN. If FLATTEN had not been found in March, 2020, it would have been found now.

I am, of course, pleased that I fixed to do it all myself (albeit a clumsy method, everything is primitive and on the surface). However, I don't think I was the first one (the developers were the first, hey guys!). I'm sure someone has already done this before me, but maybe not published it (I tried to search for a couple of useful functions with complicated names on Google and found nothing). So, I do not claim any scientific novelty or any completeness in the study of the issue. No need for a Nobel Prize, I will refuse.

But hey, write me if anything. :)

My Google Sheets channel on Telegram: @pro_google_sheets (join and ask any questions)