Google Sheets Functions

README.md

Named Functions

COLUMNADDRESS

Column reference as a string.

Arguments:

Name	Description	Example
column_number	The column number (not name) of the cell reference. 'A' is column number '1'.	1
absolute_relative_mode	An indicator of whether the reference is column absolute. '1' is column absolute (e.g. $A), '2' is column relative (e.g. A).	1

Definition:

=SUBSTITUTE(ADDRESS(1,column_number,MIN({MAX({absolute_relative_mode,1}),2})),"$1","")

REMAININGCOLUMN

Range starting from a cell until the end of the column. This will stop at the current cell if the cell is below the cell_reference to avoid reference errors.

Arguments:

Name	Description	Example
cell_reference	Cell at the start of the range.	A1

Definition:

=INDIRECT(
  CELL("address",cell_reference) &
  ":" &
  IF(
    AND(COLUMN()=COLUMN(cell_reference),ROW()>ROW(cell_reference)),
    ADDRESS(ROW()-1,COLUMN()),
    COLUMNADDRESS(COLUMN(cell_reference),)
  )
)

FIRSTBLANKROWNUM

Returns the row number of the first cell in the first column of the given range. If no empty cell is found then returns the row number for the cell after the end of the column.

Arguments:

Name	Description	Example
column_reference	Column to search in.	A1:A

Definition:

=QUERY(
  {
    QUERY(column_reference,"select Col1",0),
    ARRAYFORMULA(ROW(column_reference))
    ;
    "",
    ARRAYFORMULA(MAX(ROW(column_reference))+1)
  },
  "select Col2 where (Col1 = '') limit 1",
  0
) - 1

CONTIGUOUSCOLUMN

Creates a range starting at a cell that continuous until the last non-blank cell in that column.

Arguments:

Name	Description	Example
cell_reference	Cell at the start of the range.	A1

Definition:

=LET(
  column_reference,
  REMAININGCOLUMN(cell_reference),
  INDIRECT(
    CELL("address", column_reference) &
    ":" &
    ADDRESS(
      FIRSTBLANKROWNUM(column_reference),
      COLUMN(column_reference)
    )
  )
)