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

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 |

### 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"

- Specific element

## 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.
- 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`

- split into byte chunks:

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

- Treat it as an escape character.
- If it is within a virtual array
- This is where things get
weird. Even if the value is held virtually, as the result of an operation that returns an array, Google Sheets will**really***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:

- This is where things get

- If it is a single leading mark, such as in the string
- 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*work! The fact that this would rarely ever show up in natural text makes it a great tool for escaping...**does**

- 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("=",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)))`