Formulas API Reference
The Formulas API uses the mc namespace (e.g. mc.cell(), mc.cr(), mc.col(), etc.). The exception is if you're using the Quick Formula command. Then, you can use cell(), cr(), col(), etc.
The Formulas API is different from the Scripts API since it's much narrower in scope.
This document covers the following topics:
Index Types
Modern CSV has three index systems:
display: The row/column numbers or letters shown in the table.visual: The current visible order after sorting, filtering, hiding, and moving data.physical: The file order on disk.
Absolute numeric indexes use display by default in formulas. Relative offsets use visual indexes.
Getters
Getters that return rows, columns, or ranges return NumPy arrays with one of three data types: int, float, or string. Blank values in otherwise numeric arrays become nan; non-numeric text makes the array a string array.
Multiple-cell getters read to the end of the data, not the visible buffer. If a requested range extends past the end of the data, blank cells are padded. Indexes below the data raise IndexError.
Absolute numeric indexes use Display indexes by default. Pass absolute_index_type="visual" to use Visual indexes. Relative offsets (dr, dc) use Visual indexes.
If a column argument such as c is a string, its meaning depends on Represent Columns as Letters:
- Disabled: the string is a column title.
- Enabled: the string is a column letter such as
"A","B", or"AA".
Use c_title, c0_title, or c1_title to refer to a column title regardless of that setting. Do not pass both c and c_title.
If an absolute index and a relative offset are both provided, the offset is applied to the absolute location. If the absolute index is None, the current cell is used.
Cells
In the rc, RC, rC, Rc, cr, CR, cR, and Cr names, lowercase letters are relative and uppercase letters are absolute. The rc family takes row first, then column. The cr family takes column first, then row.
cell(r: int | None = None, c: int | str | None = None, c_title: str | None = None, dr: int = 0, dc: int = 0, absolute_index_type: str = "display"): A cell value.cell_rel(dr: int = 0, dc: int = 0): A cell value relative to the current cell.rc(dr: int = 0, dc: int = 0): Relative row, relative column.cr(dc: int = 0, dr: int = 0): Relative column, relative row.RC(r: int | None = None, c: int | str | None = None, c_title: str | None = None, absolute_index_type: str = "display"): Absolute row, absolute column.CR(c: int | str | None = None, r: int | None = None, c_title: str | None = None, absolute_index_type: str = "display"): Absolute column, absolute row.rC(dr: int = 0, c: int | str | None = None, c_title: str | None = None, absolute_index_type: str = "display"): Relative row, absolute column.Cr(c: int | str | None = None, dr: int = 0, c_title: str | None = None, absolute_index_type: str = "display"): Absolute column, relative row.cR(dc: int = 0, r: int | None = None, absolute_index_type: str = "display"): Relative column, absolute row.Rc(r: int | None = None, dc: int = 0, absolute_index_type: str = "display"): Absolute row, relative column.
mc.rc(0, -1) # Cell one column to the left
mc.cr(-1, 0) # Same cell, column first
mc.RC(5, "Revenue") # Row 5, column titled "Revenue"
mc.cell(r=3, c="B", dc=1) # Row 3, one column to the right of B
Rows and Columns
include_headers includes the header rows or columns.
If num_rows or num_cols is defined, the result has that many cells. If only an end index is otherwise defined, the start is inferred from the count. Otherwise, the result starts at the normal start index and extends by the count.
Use any two of start, end, and count. Passing all three is over-specified.
row(r: int | None = None, dr: int = 0, c_start: int | str | None = None, c_end: int | str | None = None, c_start_title: str | None = None, c_end_title: str | None = None, num_cols: int | None = None, include_headers: bool = False, absolute_index_type: str = "display"): A row as a 1D NumPy array.col(c: int | str | None = None, c_title: str | None = None, dc: int = 0, r_start: int | None = None, r_end: int | None = None, num_rows: int | None = None, include_headers: bool = False, absolute_index_type: str = "display"): A column as a 1D NumPy array.row_rel(dr: int = 0, c_start: int | str | None = None, c_end: int | str | None = None, c_start_title: str | None = None, c_end_title: str | None = None, num_cols: int | None = None, include_headers: bool = False, absolute_index_type: str = "display"): A relative row as a 1D NumPy array.col_rel(dc: int = 0, r_start: int | None = None, r_end: int | None = None, num_rows: int | None = None, include_headers: bool = False, absolute_index_type: str = "display"): A relative column as a 1D NumPy array.
mc.row() # Current row
mc.col(c="Price") # Column titled "Price"
mc.col(c="B", r_start=2, r_end=10) # Column B, rows 2 through 10
mc.row(c_start="Name", c_end="Email") # Current row, Name through Email
Ranges
Ranges return a 2D NumPy array from two corners. Corner 0 is usually the top-left and corner 1 is usually the bottom-right, but this is not required.
num_rows can replace one corner's row definition. num_cols can replace one corner's column definition.
mc.range(r0: int | None = None, c0: int | str | None = None, r1: int | None = None, c1: int | str | None = None, c0_title: str | None = None, c1_title: str | None = None, dr0: int | None = None, dc0: int | None = None, dr1: int | None = None, dc1: int | None = None, num_rows: int | None = None, num_cols: int | None = None, absolute_index_type: str = "display"): A 2D range.range_rel(dr0: int | None = None, dc0: int | None = None, dr1: int | None = None, dc1: int | None = None, num_rows: int | None = None, num_cols: int | None = None): A relative 2D range.
mc.range(r0=1, c0="A", r1=10, c1="D")
mc.range(r0=1, c0="Name", num_rows=5, num_cols=3)
mc.range_rel(dr0=-3, dc0=-1, dr1=0, dc1=1)
Table Attributes
dimensions(include_headers: bool = False): Named tuple(num_rows, num_cols).num_rows(include_headers: bool = False): Number of rows.num_cols(include_headers: bool = False): Number of columns.first_row_index(include_headers: bool = False, index_type: str = "display"): First row index.first_col_index(include_headers: bool = False, index_type: str = "display"): First column index.num_header_rows(): Number of header rows.num_header_cols(): Number of header columns.last_row_index(index_type: str = "display"): Last data row index.last_col_index(index_type: str = "display"): Last data column index.
Indexes
ri(index_type: str = "display"): Current cell's row index.ci(index_type: str = "display"): Current cell's column index.index(index_type: str = "display"): Named tuple(row, col).
r, c = mc.index()
if mc.ri() == mc.first_row_index():
return "First row"
Type Checking
These functions work on scalars and arrays.
dtype(val): Returns"int","float", or"string".is_numeric(val):Truefor numeric scalars or numeric arrays.is_text(val):Truefor text scalars or string arrays.is_int(val):Truefor integer scalars or integer arrays.is_float(val):Truefor float scalars or float arrays.
price = mc.rc(0, -1)
if mc.is_numeric(price):
return price * 1.1
return "N/A"
Setter
The Formulas API has one setter function:
set_cell(r, c, val, absolute_index_type="display")
It writes a value to a cell. This is usually used in single-run mode.
r: Row index.c: Column index, title, or letter.val:int,float,str, list, or NumPy array.absolute_index_type:"display"or"visual".
If val is a 1D list or array, values are written downward from (r, c). If val is a 2D list or array, values fill a block with (r, c) as the top-left cell. Lists and arrays may contain int, float, or str. More than two dimensions raises ValueError.
mc.set_cell(1, "A", "Hello")
mc.set_cell(1, "B", [10, 20, 30])
mc.set_cell(1, "C", [[1, 2], [3, 4]])
Analysis Tools
Formulas and Scripts share analysis helpers. To avoid collisions with script-editing functions such as sort(), these helpers live under mc.analysis.
mc.analysis.mean(mc.col(c="Score"))
mc.analysis.round(mc.rc(0, -1) * 1.08, 2)
See the Analysis Tools API Reference.
Error Handling
Invalid arguments raise standard Python exceptions:
IndexError: Invalid row or column indexes, invalid column letter labels, or relative offsets that go below the data.KeyError: Column title strings that do not match a header.TypeError: Wrong argument types.ValueError: Conflicting arguments such as bothcandc_title, invalidabsolute_index_typeorindex_type, negative counts, under-specified ranges, or over-specified ranges such as start, end, and count all being defined.