Writing Custom Functions with JavaScript
Main Docs: Apps Script  Custom Functions in Google Sheets
Custom Functions in JavaScript  How to handle input?
Don't assume the type or shape of input! It can be a single value, or an array, containing different JS primitives!
The basic rule to handle input is to check if it is an array first, and if so, return the result of applying recursion over it with map, otherwise, just process and return a single value.
Example:
function uppercase(input) {
if (Array.isArray(input)) {
return input.map(i => uppercase(i));
}
return typeof(input)==='string' ? input.toUpperCase() : input;
}
Custom Functions  More advanced coding
See my cheatsheet on developing with Google Apps Script
Custom Formatting
Apply custom formatting to an entire row based on cell

set formatting to "custom formula is" and then use something like:
=$E$2:$E$100="Yes"
 ... Where if the column "E" is "Yes", then the whole row will be formatted according to rule
Working with Arrays
ArrayFormula()
What exactly is ARRAYFORMULA()
, and how does it work? Hard question to answer!
A short answer is: ARRAYFORMULA lets you use a nonarray function with an array input.
 For example,
=ROUNDUP(A1:B5)
is not useable, but=ARRAYFORMULA(ROUNDUP(A1:B5))
is.
However, at least for me, that is not a suitable explanation. So let's try some other ways of thinking about them:
My favorite way to conceptualize ArrayFormula()
There are multiple ways to think about ArrayFormula, but one way that "clicked" for me is that it is similar to using Array.prototype.map()
to generate a new array based on a callback function applied to each element of the original array.
This can actually be demonstrated with a real code example! Pretend we want to uppercase string input. In JS, to accept an array as input and return an array with all elements uppercase, we could use map()
, like so:
const A1 = "alpha one";
const A2 = "alpha two";
const result = [A1, A2].map(str => str.toUpperCase());
// ["ALPHA ONE", "ALPHA TWO"]
In sheets, our input could be represented as:
Â  A 

1  alpha one 
2  alpha two 
And, using ARRAYFORMULA, we could generate an output with:
=ARRAYFORMULA(UPPER(A1:A2))
If we put that in cell B1, we get a nice output array automatically expanded. Final results:
Â  A  B 

1  alpha one  ALPHA ONE 
2  alpha two  ALPHA TWO 
Arrays  Assorted Tips

Return an array purely from a formula, not from a table
 Simply use brackets
={"Value A", "Value B"}

Access the "column index" inside an array formula (kind of like what is the "x" value in a for x < max loop)
 Not possible ðŸ˜¢ (to the best of my knowledge)

Index into a virtual array  good shortener so you don't have to repeat same thing multiple times in inner funcs

Specific element
=ARRAYFORMULA(INDEX({"FOO","BAR"},1,2))
=> returns "BAR"

Alternative to nested IFs (e.g. trying to create a switch statement)
You can use a trick with REPT()
(aka "repeat") combined with CONCATENATE()
:
CONCATENATE(REPT(val_if_true, test_expr), REPT(val_if_true, test_expr), ...)
This works because:
 The syntax for
REPT()
isREPT("value to repeat", # of times to repeat)
 You can use an equality check for the second argument, and if it evaluates to false, it will be equivalent to
REPT("string", 0)
 which means it will output nothing
REPT as Nested IF Alternative  Example:
Replace letter input with NATO phonetic alphabet
=CONCATENATE(REPT("Alpha",A2="A"),REPT("Bravo",A2="B"),REPT("Charlie",A2="C")...)
BIN2DEC  large binary workaround
BIN2DEC limits input to 10 characters (you will get an error if you exceed it). For a really large binary number, we need a different approach. We can chunk the binary into smaller chunks and then add them together.
Formula (based on an approach in Excel):
=SUMPRODUCT(MID(A2,LEN(A2)+1ROW(INDIRECT("1:"&LEN(A2))),1),(2^(ROW(INDIRECT("1:"&LEN(A2)))1)))
Piecebypiece:

The formula for adding binary chunks, represented as decimal, is:
(chunkDecimal * (2^distance)) + (chunkDecimal * (2^distance)) + ...

Example: 111000011001001110000100 in binary, should convert to a final decimal value of 14783364.

split into byte chunks:
11100001 10010011 10000100

Same chunks in decimal
225 147 132

Formula applied
(225 * (2^16)) + (147 * (2^8)) + (132 * (2^0)) = 14783364

Counting with RegEx
This is extremely powerful; you can pattern match across thousands of rows to get matching counts
RegEx and ArrayFormula Sum  Basic formula
 Use SUM + ArrayFormula + REGEXMATCH
Example:
=SUM(ArrayFormula(IF(IFERROR(REGEXMATCH(Songs!$L$2:$L,"Happy Birthday to .*"),FALSE)=TRUE,1,0)))
RegEx and ArrayFormula Sum  With multiple conditions
=SUM(ArrayFormula(
IF(
(
(IFERROR(REGEXMATCH(Songs!$L$2:$L,H22),FALSE))
*
(IFERROR(REGEXMATCH(Songs!$D$2:$D,"[Yy]es"),FALSE))
)
=1,1,0)
))
Note that you can multiple "truth" values to get zero or 1. I'm using that here, because you can't use AND() with arrayformula
Eval in Google Sheets
An interesting question when it comes to spreadsheet is the idea of an equivalent to how eval
works in programming languages; if we have a formulae stored as a string in one cell, can we get the results in another?
Most results when you search online for answers will tell you that the only way to get anywhere close to an equivalent in Google Sheets, is to use some custom Google Apps Script code. (S/O, Google Forums). However, if you know the structure of the stored formula in advance, it is possible to actually construct a native solution with pure Google Sheets functions.
Eval Example: Simple Math
Here is an example, which shows that if you know the structure of the input beforehand, it is possible to construct a formulae that will parse and evaluate the input, without requiring the use of custom code.
These solutions all rely on splitting up the input by the symbol used as the mathematical operator, and then applying the appropriate mathematical function to the resulting array.
Addition:
A1 (string)  B1  Result of B1 

25 + 2 
=SUM(SPLIT(A1, "+")) 
27 
1 + 4 + 6 
=SUM(SPLIT(A1, "+")) 
11 
Hello 
=SUM(SPLIT(A1, "+")) 
0 
Multiplication:
A1 (string)  B1  Result of B1 

2 * 3 
=PRODUCT(SPLIT(A1, "*")) 
6 
2 * 3 * 4 
=PRODUCT(SPLIT(A1, "*")) 
24 
String Issues in Google Sheets
There are some things to note about how Google Sheets handles strings, especially when combined with RegEx.

Google sheets treats single quotation marks really strangely

If it is a single leading mark, such as in the string
'my string
, Google Sheets will:
Treat it as an escape character.
 This means you can use it to escape and display a formula, instead of executing said formula
 For example:
'=UPPER(A2)
will display as=UPPER(A2)
instead of the results of said formula  Here is an example of how this break things: Try using
=UNICODE(A2)
where A2 only contains'
. It won't work, and will say the input cell is empty.
 Hide the actual quotation mark from rendering in the cell (unless you click into it)
 Remove the character when copying and pasting from the cell (unless you click into it, select all text manually, and copy from that)


If it is within a virtual array
 This is where things get really weird. Even if the value is held virtually, as the result of an operation that returns an array, Google Sheets will still treat it as an escape character.

Here is an example:
 Formula:
=ARRAYFORMULA(LEN(SPLIT(REGEXREPLACE(A2&"","(.{1})","$1~"),"~")))
 Input:
A'B
 Expected output:
1  1  1

Actual output:
1  0  1
 ðŸ‘† Notice how it thinks that the middle character has a length of ZERO!!!
 Formula:


Google Sheets does not always preserve, or even accept nonprintable characters in strings (e.g. many ASCII below
32
) For example,
=SPLIT("A"&CHAR(31)&"B",CHAR(31))
will throw the error:Function SPLIT parameter 2 value should be nonempty.
 The only characters below
32
that work for me are9
(horizontal tab),10
(lf), and13
(cr). These make somewhat sense. Interestingly,127
(DEL
, Delete) also does work! The fact that this would rarely ever show up in natural text makes it a great tool for escaping...
 For example,
Miscellaneous Formulae
RIGHT Pad (e.g. add trailing characters to pad to length)
Example : Pad to 6 characters, pad right with "="
=IF(MOD(LEN(A2),6),CONCATENATE(A2,REPT("=",6MOD(LEN(A2),6))),A2)
// "foo" becomes "foo==="
Same thing, but without IF statement (Doesn't work if A2 <> 0)
=CONCATENATE(A2,REPT("=",(FLOOR((LEN(A2)+(61))/6)*6)LEN(A2)))
Split text by a specific number of characters
Without padding  example: split into 6 character chunks
=SPLIT(REGEXREPLACE(A2&"","(.{6})","$1~"),"~")
With right pad  example: split into 6 character chunks, right pad with zeros if chunk is less than 6 characters
=ARRAYFORMULA(TEXT(SPLIT(REGEXREPLACE(CONCATENATE(A2&"",REPT("0",(FLOOR((LEN(A2&"")+(61))/6)*6)LEN(A2&""))),"(.{6})","/$1"),"/"),"000000"))
Text to binary
Formula:
=JOIN("",ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(A2&"","(.{1})","~$1"),"~")),2,8)))
Piecebypiece:

First split text into single chars
=SPLIT(REGEXREPLACE(A2&"","(.{1})","~$1"),"~")

Convert into binary (base2)
=ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(A2&"","(.{1})","~$1"),"~")),2,8))

Join together
=JOIN("",ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(A2&"","(.{1})","~$1"),"~")),2,8)))