AB
1
Google Sheets Telegram Channel (RUS)

Our mutual help chat on Google Sheets in Telegram (main language is Russian, but you are welcome to write in English - someone will surely answer your question)

Made by		@google_sheets


@google_spreadsheets_chat






@Smirnovme
2
3
Plain and simple description of the finding and some history on undocumented functions in Google Sheets:
https://telegra.ph/FLATTEN-2-Other-Undocumented-Google-Sheets-Functions-02-14
4
5
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!
6
7
SheetDescription
8
📚 Google Sheets function listGoogle Sheets function list imported from the official documentation page.
9
🤔 Functions from JSFunction list that was found in one of JavaScript strings. That is from the code that is actually Google Sheets functionality implementation and which is executed locally, so it is available.
10
😎 Undocumented functionsA list of functions from the 2nd sheet (they are in the code, so they exist) which are absent from the first sheet (they have no corresponding documentation). Along with description, comments and sometimes examples. All to the extent of author's humble understanding (and now with priceless additions from the community). The functions are marked with colors, which meanings are described below.
11
✨ Some ExamplesSome usage examples that do not fin in a single cell. Those that do fit are on the 3rd sheet next to a function description.
12
😞 GOOGLETRANSLATE(BAHTTEXT(...))Example of GOOGLETRANSLATE returning nonsense sometimes, showing that the dirty trick of getting numbers as words with BAHTTEXT appears to be not that usable.
13
14
Function ColorDescription (keep in mind that's all just author's opinion)
15
That's a good, useful function.
16
Most likely a useful function, it's better be studied in detail.
17
Examination is not yet complete (or has not taken place at all). Unclear, needed to be studied.
18
Does not work. Either a placeholder (hope it will be implemented someday) or a deprecated function.
19
Most likely a function is of no interest.
20
White background (no background). Everything looks clear, but a function is not interesting or not useful. But there might be surprises, like it was with WHATTHEFOXSAY().
21
22
Notes
23
24
To check the number of arguments accepted by a function it needs to be called without arguments.

It returns an error? Place mouse pointer above the error and read about the correct number of arguments.

No error means the function could be called without any arguments. Then one should try giving it one argument as an input, then two arguments. If there's an error it will state that the function accepts 0 (or another number) arguments. If there is no error, then it means the function accepts any number of arguments.
25
26
For every function first the number of arguments is checked to be correct, only afterwards it is actually ran. That is the reason why for some functions we have information about accepted number of arguments despite the fact it is not actually implemented: an error is returned with correct number of arguments passed to it.

1st type of error (not implemented): Unknown function: 'XLOOKUP'.
2nd type of error (not implemented fully?): This function is not fully supported in Sheets. Please see the help center for more information.
3rd type of error (deprecated): The NOEXPAND function has been replaced with the ARRAY_CONSTRAIN function.
27
28
Often one can understand what a function does just by its name. It is worth to compare that guess with the actual results of the function.
29
30
It is useful to try different sets of arguments: different number of arguments, empty arguments, empty strings, numbers (positive, negative, whole numbers, fractions), ranges (real and virtual), errors and so on.
31
32
Presumably ".LEGACY" suffix in the name of a function is a mark of the previous implementation. Maybe it is used inside current implementation somehow. Mostly it is documented functions that have this suffix. Seems they are not of any interest.
33
34
At the meantime it is unclear what ".INEXACT" suffix means. There is a function with and without such suffix, the latter (with the suffix) accepts any number of arguments, and conducts a search among all of them. But looks like this characteristic is not common to every function with that suffix. Further exploration is needed.
35
36
For some of the functions there are alternatives: solutions that provide the same behavior using only documented functions. Some are compact, others are quite heavy.
37