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 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:

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:

Virtual Arrays / Creating Arrays

You can return an array purely from a formula, without a table as the starting point. Use curly braces / brackets to do so:

={"Value A", "Value B"}

Use , (commas) to separate columns (values within a row), and ; (semicolons) to separate rows.

You can also use TRANSPOSE to quickly rotate an array, which is helpful for functions that expect arrays of a certain orientation and/or matching dimensions.

Dynamically Creating an Array of Fixed Length

Here is the use case: Imagine you have a column of Y-values, but no X-values, and you want to dynamically create an array of x-values to correspond.

You can create an array of fixed length, filled with custom values, a few different ways:

Wrap any of these with TRANSPOSE() if you want a vertical array instead of horizontal

• =SPLIT(REPT(CONCAT(FILL_VALUE, CHAR(127)), 4),CHAR(127))
• Answers to this StackOverflow question

Reshaping Arrays

One way to slice an array is using ARRAY_CONSTRAIN(INPUT_ARR, NUM_ROWS, NUM_COLS).

Some array formula reshaping functions, like ARRAY_CONSTRAIN, require that both dimensions be specified. If you need to use a placeholder for "don't limit in x direction", you can use something like 1e99.

Example:

=ARRAY_CONSTRAIN(QUERY(Songs!A:O,"select K, COUNT(K) group by K order by COUNT(K) desc",0),1E+99,1)

Arrays - Assorted Tips

• 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

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

RegEx in Google Sheets

WARNING: Google Sheets uses a special regular expression library / interpreter, re2, which is more obscure than what you might be used to.

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:

Multiplication:

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!!!
• Google Sheets does not always preserve, or even accept non-printable 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 non-empty.
  • The only characters below 32 that work for me are 9 (horizontal tab), 10 (lf), and 13 (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...

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:

1. First split text into single chars
   • =SPLIT(REGEXREPLACE(A2&"","(.{1})","~$1"),"~")
2. Convert into binary (base2)
   • =ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(A2&"","(.{1})","~$1"),"~")),2,8))
3. Join together
   • =JOIN("",ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(A2&"","(.{1})","~$1"),"~")),2,8)))

Using Cell Values as Range References

Use the indirect() function

Misc

• Joining strings
  • Use CONCAT, CONCATENATE, JOIN, or &
• How to use regex flags, like case-insensitive?
  • Use (?FLAGS) at start of pattern
• How to select just subset of range that has values (not empty)? E.g., so JOIN("|", 'My Page'!A:A) doesn't result in a|b|c||||||...
  • Easiest: FILTER(MY_RANGE, LEN(MY_RANGE))