Writing Custom Functions with JavaScript
Main Docs: Apps Script  Custom Functions in Google Sheets
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;
}
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 
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
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

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)))
Using RegEx with CountIf()
This is extremely powerful; you can pattern match across thousands of rows to get matching counts
Basic formula
 Use SUM + ArrayFormula + REGEXMATCH
Example:
=SUM(ArrayFormula(IF(IFERROR(REGEXMATCH(Songs!$L$2:$L,"Happy Birthday to .*"),FALSE)=TRUE,1,0)))
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