SheetSupport – Make spreadsheets readable with custom formulas

README.md

SheetSupport – Make spreadsheets readable with custom formulas

SheetSupport is a library of Google Sheets custom formulas, inspired by Ruby's Enumerable module and Rails' ActiveSupport

What / Why?

These functions are meant to make your spreadsheets more readable, better understood, and more easily maintained.

Here's an example of a formula that:

• Looks up a value
• If it's an N/A error, just returns an empty space (not quite a true blank value)
• Otherwise, it returns the lookup value and tries to (naively) remove extra whitespace
• Then gets the last 4 characters

=IF(
  ISNA(VLOOKUP(A3, 'Next Tab'!A2:D6, 3, FALSE)),
  "",
  LEFT(REGEXREPLACE(VLOOKUP(A3, 'Next Tab'!A2:D6, 3, FALSE),,1), "  ", " "), 4)
)

With SheetSupport, we can rewrite this in a way we can almost read aloud. We CATCH potential N/A errors and return BLANKs, otherwise we SQUISH the result and get the LAST4.

=LET(
  lookup, VLOOKUP(value, range, 3, FALSE),
  CATCH_IF(
    LAST4(SQUISH(lookup)),
    "N/A",
    BLANK()
  )
)

Go back and try reading each of these formulas out loud. You'll see quickly that one is easier to read and speak about, and therefore easier to reason about, train others on, and maintain.

How to use

If you're in 18F, TTS, or GSA, go to your Google Drive, and select "+ New" > "Google Sheets >" (click the right arrow) > "From a template", and select the 18F Template.

If you're outside GSA, you'll have to manually import the functions. (Google Sheets doesn't offer export/import of functions as of Sept 2023).

1. Download functions.csv and upload to your Google Drive as a spreadsheet.
2. In the top menu, select Data > Named functions.
3. In the sidebar that opens, at the bottom, click "Add new function".
4. Set the function name, function description, and formula definition (adding a = to the front) from the values in the spreadsheet.
5. Use the below guide to input the argument placeholders.
6. Click Next.
7. Input the argument descriptions and examples into this page.
8. Click Save (or Update if you're editing).

Guide to arguments

There's a lot of information about each argument — each value passed to the function — so let's look at how we store that information and how you can manually enter it.

As an example, let's look at the arguments for the custom function ALL.

array:
  type: range (1D)
  description: A list whose elements will all be run against the condition
  example: {2, 4, 6, 8}
block:
  type: lambda
  description: A lambda containing the criterion
  example: lambda(x, IS_EVEN(x))

This block defines two arguments, array and block. If you're entering this formula manually, make sure to add argument placeholders for both of these, in order, top to bottom. So, in Argument placeholders, type "array", Enter, then "block", then Enter.

When you click Next, you'll be prompted to add a description and example for each argument. Copy and paste the corresponding descriptions and examples. You can ignore the type line — this is just helpful information for you, the reader, to know what type of argument is expected. A list of types are available in Type Definitions.md.

If you see a block like the one below, it means that while there are named arguments (in this case, 5), the author did not provide documentation. Fortunately, descriptions and examples are optional, you can skip them while entering this formula.

index_int:
size:
input:
output:
block:

functions.csv

Type Definitions.md

Type Definitions

These are the descriptions of the data types you'll see in the type section of each function's arguments.

any: A value or expression of any type, except lambdas.
character: A single letter, for example "X"
date: A date or datetime
expression: Any expression or formula. Another way to say "any", but implying that it'll be a formula or function.
function invocation: A function that's being called with ending parentheses, for example IS_DEFINED()
integer: A number that is not a decimal
lambda: A lambda function, which is an anonymous function. A lambda that multiplies x2 could be written as lambda(x, x * 2)
number: Any number, either integer or decimal
range: A range, either by reference (A1:C2) or an array literal ({1,2;3,4;5,6})
range (1D): A range, but only a single row or a single column, either by reference (A1:A5 or A1:E1) or array literal ({1,2,3,4,5})
text: Any string of text, surrounded by double quotes, like "hello world"

a type (more information): Some functions expect input in a certain range, like integers only 1–26.
a type | a second type: Multiple types separated by pipe | characters mean that either of the listed types are acceptable