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 non-array 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() is REPT("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)+1-ROW(INDIRECT("1:"&LEN(A2))),1),(2^(ROW(INDIRECT("1:"&LEN(A2)))-1)))

Piece-by-piece:

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.
1. split into byte chunks:
  - 11100001 10010011 10000100
2. Same chunks in decimal
  - 225 147 132
3. 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("=",6-MOD(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)+(6-1))/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&"")+(6-1))/6)*6)-LEN(A2&""))),"(.{6})","/$1"),"/"),"000000"))

Text to binary

Formula:

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

Piece-by-piece:

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