Google Sheets - various Named Functions for utility purposes

named_functions.js

// ------------------------------------------------------------------------------------
// These are some handy Named Functions I came up with to help with my
// Google Sheet I use for tracking roommate expenses.
//
// This gist uses JSDoc syntax but translates pretty easily into the form fields
// in Google Sheets Named Function wizard.
// ------------------------------------------------------------------------------------

/**
 * @function
 * @name SETTLE_UP
 * @description
 * Performs a SplitWise style settle up between multiple people
 * given a row of costs and a row of payments
 *
 * @param {A1:C1} costs row of costs 
 * @param {A2:C2} payments row of payments
 *
 * @note rows must be the same size
 *
 * @returns a matrix of payments required to settle up
 *
 * @example
 * given
 *   people A, B, C
 *  `costs    = { 400 , 200 , 400 }`
 *  `payments = { 500 , 400 , 100 }`
 *
 * this will return
 *             A     B     C
 * Owes A ` {   0 ,   0 , 100 }`
 * Owes B ` {   0 ,   0 , 200 }`
 * Owes C ` {   0 ,   0 ,   0 }`
 * 
 * Indicating that C owes A $100 and C owes B $200
 *
 * @note instead of using raw costs and payments, this will also work
 * using the amounts each person owes and is owed respectively.
 * 
 * @example
 * given
 *   people A, B, C
 *  `owes    = {   0 ,   0 , 300 }`
 *  `is owed = { 100 , 200 ,   0 }`
 *
 * this will return
 *             A     B     C
 * Owes A ` {   0 ,   0 , 100 }`
 * Owes B ` {   0 ,   0 , 200 }`
 * Owes C ` {   0 ,   0 ,   0 }`
 */
// =
arrayformula(
 map(
  arrayformula(costs-payments),
  lambda(x, max(x,0))
 )
 *
 MAP_WITH_ARRAY(
  transpose(
   map(
    arrayformula(costs-payments),
    lambda(x, -1 * min(x,0))
   )
  ),
  lambda(
   owed,
   oweds,
   iferror(
    owed/sum(oweds),
    0
   )
  )
 )
)

/**
 * @function
 * @name MAP_WITH_ARRAY
 * @descroption
 * Acts like the builtin `MAP` function but also passes
 * the original array into the provided lambda.
 *
 * @param {A1:C1} array
 *        - range (a row, column, or grid) of values to map over
 * @param {lambda(v, array)} lambda_with_array
 *        - a lambda that maps over each value in the given array
 *          and takes two arguments: `v` (each value) and `array` (the entire array being iterated over)
 */ 
// =
map(
 array,
 lambda(v, lambda_with_array(v, array))
)


/**
 * @function
 * @name REGEX_MATCH_AND_MAP
 * @description
 * Performs a regex match for each search key onto the target.
 * Match hits use their corresponding hit value, match misses use their corresponding miss value.
 * Default to miss determines which set of values to use when all matches fail.
 *
 * @param {A1} target target string to look through
 * @param {B2:B4} search_keys keys to look for in the target
 * @param {C2:C4} hit_values values to use when a value is found
 * @param {D2:D4} miss_values values to use when a value isn't found
 * @param {true|false} default_to_miss_values set of values to return when no values are found
 * 
 * @note ranges of keys and values must all be the same size
 * 
 * @return mapped values
 * 
 * @example
 * ```
 * REGEX_MATCH_AND_MAP(
 *   "Groceries for John and Jane",
 *   { "John", "Jane", "Sam" },
 *   { 15, 10, 15 },
 *   { 0, 0, 0 },
 *   false,
 * )
 * // yields { 15, 10, 0 }
 * ```
 * 
 */
// =
lambda(
  matches,
  if(
    not(or(matches)),
    if(
      default_to_miss_values,
      miss_values,
      hit_values
    ),
    map(
      matches,
      hit_values,
      miss_values,
      lambda(
        matched,
        hit_value,
        miss_value,
        if(
          matched,
          hit_value,
          miss_value
        )
      )
    )
  )
)(
  map(
    search_keys,
    lambda(
      key,
      regexmatch(
        lower(target),
        lower(key)
      )
    )
  )
)


/**
 * @function
 * @name SHARE_TOTAL
 * @description
 * Maps a given set of shares to their share of a given total
 *
 * @param {number} total The total amount to split amongst shareholders
 * @param {B2:B4} range_of_shares The amount of shares each holder has
 *
 * @return range of shared totals
 */
// =
lambda(
  total_shares,
  arrayformula(
    range_of_shares / total_shares * total
  )
)(
  sum(range_of_shares)
)


/**
 * @function
 * @name ROW_LOOKUP
 * @description
 * Searches down a given column for a key and returns the matching row in the provided range.
 *
 * @param {A1} search_key The value to search for
 * @param {B1:B4} match_column The column of cells to match against
 * @param {C1:E4} rows The range of possible rows to return
 *
 * @return The matching row
 */
// =
arrayformula(
  vlookup(
    search_key,
    { match_column , rows },
    sequence(1, columns(rows), 2),
    false
  )
)

/**
 * @function
 * @name CATEGORIZE
 * @description
 * Categorizes target text by regex matching it against each categories row of possible match criteria.
 * Categories higher up take precedence.
 *
 * @param {A1} target The string to match against
 * @param {B1:B4} category_column The column of possible categories
 * @param {B1:B4} match_criteria The rows of search keys to match against for each category
 * @param {C1:E4} default The default value to return if no matches are found
 *
 * @return The matched category of the given input
 * 
 * @example
 * ```
 * CATEGORIZE(
 *   "Costco Dining Chairs",
 *   {
 *     "House" ;
 *     "Groceries" ;
 *   },
 *   {
 *     "living", "dining" ;
 *     "costco", "qfc", "groceries" ;
 *   },
 *   "Other"
 * )
 * // returns "House"
 * ```
 */
// =
if(
  isblank(target),
  "",
  lambda(
    matches,
    iferror(
      index(
        filter(
          matches,
          matches <> ""
        ),
        1,
        1
      ),
      default
    )
  )
  (
    map(
      category_column,
      lambda(
        category,
        lambda(
          row_criteria,
          iferror(
            if(
              columns(
                filter(
                  row_criteria,
                  not(isblank(row_criteria)),
                  regexmatch(
                    lower(target),
                    lower(row_criteria)
                  )
                )
              ) > 0,
              category,
              ""
            ),
            ""
          )
        )(
          filter(
            match_criteria,
            row(match_criteria)
              = row(category)
          )
        )
      )
    )
  )
)

/**
 * @function
 * @name SUM_MATCHING_CELLS
 * @description
 * Sums certain cells in a specified column of a range
 *
 * @param {A1:D4} range The 2D range of cells whose first column will be matched against
 * @param {C1 & "|" & D1} column_match_expression The regular expression to match against the top most cell the columns to sum
 * @param {"^Expense"} row_match_expression The regular expression to match against the left-most column of each row to be summed
 *
 * @return The sum of all cells in the matching column that satisfy the row match expression
 * 
 * @example
 * ```
 * SUM_MATCHING_CELLS(
 *   {
 *     "Type",             "Item",    "Amount for John", "Amount for Jane" ;
 *     "Expense - Living", "Food",    20,                20 ;
 *     "Income - Venmo",   "Drinks",  15,                0 ;
 *     "Expense - Fun",    "Concert", 30,                30 ;
 *   },
 *   "^Amount",
 *   "^Expense",
 * )
 * // returns 100
 * ```
 */
// =
sum(
  lambda(
    filtered_rows,
    filter(
      filtered_rows,
      regexmatch(
        index(
          range,
          1,
          0
        ),
        "(?i)" & column_match_expression
      )
    )
  )(
    iferror(
      filter(
        range,
        regexmatch(
          index(
            range,
            0,
            1
          ),
          "(?i)" & row_match_expression
        )
      ),
      index(
        range,
        1,
        0
      )
    )
  )
)

/**
 * @function
 * @name MAP_ROWS
 * @description
 * Like MAP but maps over rows of a range instead of each individual cell
 *
 * @param {A1:C4} range A 2D range
 * @param {lambda(row)} row_lambda A lambda that will be applied to each row in the range
 *
 * @return The transformed 2D range
 */
// =
map(
  sequence(
    rows(range),
    1
  ),
  lambda(
    row_index,
    row_lambda(
      index(
        range,
        row_index,
        0
      )
    )
  )
)

/**
 * @function
 * @name MAP_COLUMNS
 * @description
 * Like MAP but maps over columns of a range instead of each individual cell
 *
 * @param {A1:C4} range A 2D range
 * @param {lambda(column)} column_lambda A lambda that will be applied to each column in the range
 *
 * @return The transformed 2D range
 */
// =
map(
  sequence(
    1,
    columns(range)
  ),
  lambda(
    column_index,
    column_lambda(
      index(
        range,
        0,
        column_index
      )
    )
  )
)

/**
 * @function
 * @name VLOOKUP_NEAREST
 * @description
 * Performs a vertical lookup like VLOOKUP but based on proximity to a specified anchor cell instead of by a particular value.
 * 
 * @note blank cells will be skipped
 * 
 * @param {B4} anchor_cell The cell from which proximity will be determined (likely the current cell using this formula)
 * @param {A1:A5} search_column The column of values to search through
 * @param {boolean} ascend Whether look upward (ascend = true) or downward (ascend = false) for the nearest cell
 *
 * @return The value contained inside the nearest non empty cell in the search columm
 */
// =
lambda(
  filtered_search_column,
  index(
    filtered_search_column,
    if(
      ascend,
      rows(filtered_search_column),
      1
    ),
    0
  )
)(
  filter(
    search_column,
    if(
      ascend,
      row(search_column)
      <
      row(anchor_cell),
      row(search_column)
      >
      row(anchor_cell)
    ),
    not(isblank(search_column))
  )
)

/**
 * @function
 * @name MAP_WITH_ROWS_AND_COLUMNS
 * @description
 * Acts like the builtin `MAP` function but also passes
 * the current row and column into the provided lambda.
 *
 * @param {A1:C3} array
 *        - range (a row, column, or grid) of values to map over
 * @param {lambda(v, row, column)} lambda_with_row_and_column
 *        - a lambda that maps over each value in the given array
 *          and takes 3 arguments:
 *          1. `v` (each value)
 *          2. `row` (the entire row being iterated over)
 *          3. `column` (the entire column being iterated over)
 */ 
// =
lambda(
  row_offset,
  column_offset,
  map(
   array,
   lambda(
    v,
    lambda_with_row_and_column(
     v,
     index(array, row(v) - row_offset, 0),
     index(array, 0, column(v) - column_offset)
    )
   )
  )
 )(
  row(array) - 1,
  column(array) - 1
 )
 
 /**
  * @function
  * @name FILTER_ROWS
  * @description
  * Filters out rows a range using a syntax similar to the builtin
  * `MAP` function. The provided LAMBDA takes in each row and should
  * output a single boolean value indicating whether or not
  * to include that row.
  *
  * @param {A1:C3} array
  *        - range (a row, column, or grid) whose rows will be filtered
  * @param {lambda(row)} row_condition
  *        - a lambda that maps over each row in the given array
  *
  * @note depends on custom named function MAP_ROWS
  *
  * @example
  * ```
  * // filters out empty rows in a range
  * FILTER_ROWS(
  *   A1:C3,
  *   lambda(row, concatenate(row) <> "")
  * )
  * ```
  */ 
 // =
 filter(
  array,
  map_rows(
   array,
   lambda(
    row,
    row_condition(row)
   )
  )
 )
 
 lambda(
  row_indeces,
  map(
   row_indeces,
   lambda(
    row_index,
    row_lambda(
     index(array, row_index, 0)
    )
   )
  )
 )(
  sequence(
   rows(array),
   1
  )
 )


 /**
  * @function
  * @name WEEKDAYS
  * @description
  * Returns a unique list of all the dates provided,
  * filtering out any dates that aren't weekdays
  *
  * @param {date} start_date
  * @param {date} end_date
  */ 
// =
lambda(
 weekdays,
 count(
  filter(
   weekdays,
   weekdays <> 1,
   weekdays <> 7
  )
 )
)(
 map(
  sequence(
   days(end_date, start_date),
   1,
   0
  ),
  lambda(
   offset,
   weekday(
    date(
     year(start_date),
     month(start_date),
     day(start_date) + offset
    )
   )
  )
 )
)

/**
 * @function
 * @name CALCULATE_BURN
 * @description
 * Calculates how much a worker is potentially being burned out based on
 * a given list of metrics, the industry expected value, the workers preferred value,
 * and custom defined coefficients for scaling how differences affect burn out.
 *
 * @param {range} values list of values to calculate worker burn for
 * @param {number} optimal worker's preferred value
 * @param {number} standard industry standard value
 * @param {number} coefficient a scalar coefficient to apply to the burn result
 * @param {number} exponent an exponential coefficient to apply to the burn result
 */ 
// =
map(
 values,
 lambda(
  value,
  switch(
   true,
   value = "",
   "",
   lambda(
    diff,
    max(
     0,
     (1 + 0.2 * diff * coefficient)
     * pow(
      max(0, 1 + 0.2 * diff),
      exponent - 1
     )
    )
   )(
    (value / optimal) - (standard / optimal)
    )
  )
 )
)

/**
 * @function
 * @name ROW_VALUES_ARE_EQUAL
 * @description
 * Returns whether or not all cells in a given row are equal
 *
 * @param {range} values row of values
 */ 
// =
index(
 reduce(
  {index(values, 1, 1), true},
  {index(values, 1, 0)},
  lambda(acc, val,
   if(
     index(acc,1,1)=val,
     acc,
     {index(acc,1,1), false}
   )
  )
 ),
 1,
 2
)

/**
 * @function
 * @name EXTRACT_ROWS
 * @description
 * Returns a subset of the given array whos rows are between (inclusive)
 * the provided row offsets. This is similar to INDEX with multiple rows
 * instead of the content of just one row.
 *
 * @param {A1:C9} array
 *        - The array of cells to be offset into.
 * @param {2} start_offset
 *        - The offset of the first row to be included.
 * @param {2} end_offset
 *        - The offset of the last row to be included.
 *          0 indicates the rest of the array.
 *          Negatives count backward from the end of the array.
 *
 * @example
 * ```
 * EXTRACT_ROWS(A10:C20, 2, -1)
 * // Returns A11:C19
 * ```
 */ 
// =
lambda(indeces, istart, iend,
 filter(array,
  indeces>=istart,
  indeces<=iend
 )
)(
 sequence(rows(array), 1, 1, 1),
 start_offset,
 if(end_offset<1,
  end_offset+rows(array),
  end_offset
 )
)

/**
 * @function
 * @name MAP_RANGE
 * @description
 * ...
 *
 * @param {A1:C5} range
 *        - The range of cells
 * @param {2} transform_lambda
 *        - The transformation lambda that takes (in order)
 *          the cell value, its row index, its column index
 *
 * @example
 * ```
 * MAP_RANGE(A1:C5, lambda(value, row, column, "(" & row & "," & column & ") = " & value))
 * ```
 */ 
// =
lambda(
  row_indeces,
  column_indeces,
  map(row_indeces,
    lambda(row,
      map(column_indeces,
        lambda(column,
          transform_lambda(
            index(
              range,
              row,
              column
            ),
            row,
            column
          )
        )
      )
    )
  )
)(
  sequence(rows(range), 1),
  sequence(1, columns(range))
)

/**
 * @function
 * @name TRANSFORM_SHAPES
 * @description
 * ...
 *
 * @param {A2:E8} table
 *        - The range of cells
 *
 * @example
 * ```
 * TRANSFORM_SHAPES(filter(Sheet1!A2:E8, Sheet1!A2:A8<>""))
 * ```
 */ 
// =
MAP_RANGE(sequence(rows(table), 12), lambda(v, r, c,
  switch(c,
   1, index(table, r, 1),
   2, switch(
    index(table, r, 2),
    "Question", "Decision",
    "State", "Terminator",
    "Circle"
   ),
   3, "Flowchart Shapes",
   4, 0,
   5, "",
   6, "",
   7, "",
   8, "",
   9, "",
   10, index(table, r, 3),
   11, "",
   12 , "",
   "???"
  )
))

/**
 * @function
 * @name TRANSFORM_LINES
 * @description
 * ...
 *
 * @param {A2:E8} table
 *        - The range of cells
 * @param {10} id_start
 *        - The id of the first line, fromt which to increment
 * @param {4} text_col
 *        - The column index of the arrow text
 * @param {5} dest_col
 *        - The column index of the arrow desination
 *
 * @example
 * ```
 * TRANSFORM_LINES(filter(Sheet1!A2:E8, Sheet1!A2:A8<>""))
 * ```
 */ 
// =
MAP_RANGE(sequence(rows(table), 12), lambda(v, r, c,
  switch(c,
  1, id_start + r,
  2, "Line",
  3, "",
  4, 0,
  5, "",
  6, index(table, r, 1),
  7, index(table, r, dest_col),
  8, "None",
  9, "Arrow",
  10, " "&index(table, r, text_col)&" ",
  11, "",
  12 , "",
  "???"
  )
))


/**
 * @function
 * @name TRANSFORM_DECISION_TABLE
 * @description
 * ...
 *
 * @param {A2:E8} table
 *        - The range of cells
 *
 * @example
 * ```
 * TRANSFORM_DECISION_TABLE(Sheet1!A2:E8)
 * ```
 */ 
// =
lambda(
  shapes_table,
  lines_table1,
  lines_table2,
  lines_table3,
  lines_table4,
  {
    TRANSFORM_SHAPES(
      shapes_table
    )
    ;
    TRANSFORM_LINES(
      lines_table1,
      rows(shapes_table),
      4,
      5
    )
    ;
    TRANSFORM_LINES(
      lines_table2,
      rows(shapes_table)
      + rows(lines_table1),
      6,
      7
    )
    ;
    TRANSFORM_LINES(
      lines_table3,
      rows(shapes_table)
      + rows(lines_table1)
      + rows(lines_table2),
      8,
      9
    )
    ;
    TRANSFORM_LINES(
      lines_table4,
      rows(shapes_table)
      + rows(lines_table1)
      + rows(lines_table2)
      + rows(lines_table3),
      10,
      11
    )
  }
)(
  filter(table,
    index(table, 0, 1)<>""
  ),
  filter(table,
    index(table, 0, 5)<>""
  ),
  filter(table,
    index(table, 0, 7)<>""
  ),
  filter(table,
    index(table, 0, 9)<>""
  ),
  filter(table,
    index(table, 0, 11)<>""
  )
)