Custom Spreadsheet Functions

Create your own spreadsheet functions and share them with your entire organization.

This is a Mito Enterprise feature. Upgrade to extend your spreadsheet with additional spreadsheet functions.

The Mito spreadsheet is built to be extensible to your teams use case. Mito users or infrastructure administrators can the data analysis capabilities of their team by incorporating custom Python functions directly into the sheet.

Bringing your own functions enables organizations to maintain their proprietary algorithms, calculations, and domain-specific knowledge right within Mito Spreadsheet. Stop your internal Python code from rotting on the shelf, and get it to end users in the most intuitive interface -- a spreadsheet.

How to Write Custom Spreadsheet Functions

Let's say that we want to use a custom spreadsheet function called ADD_ONE that adds 1 to the input number.

def ADD_ONE(series):
    return series + 1

Custom spreadsheet functions must:

1. Have a fully capitalized function name

2. Support the correct input types

Optionally, spreadsheet functions can use a a docstring to configure spreadsheet syntax hints.

Custom functions must be fully capitalized

Custom spreadsheet functions are required to have fully upercase names.

If you have my_function you wish to add, simply wrap it in a wrapper to use it in the Mitosheet.

def MY_FUNCTION(*args, **kwargs):
    return my_functions(*args, **kwargs)

Custom functions must support the correct input types

When writing formulas in a Mito spreadsheet, there are a few ways to reference other pieces of data in the spreadsheet. Let's consider each reference type, and the data type you receive in return.

Imagine we're writing a formula on the dataframe df, and we write formulas in example formula. The passed type to the function will be:

Reference Type	Example Formula	Passed Type
Single cell reference, same row	B1 = MY_FORMULA(A1)	df['A']
Single cell reference, different row	B2 = MY_FORMULA(A1)	df['A'].shift(1)
Entire column reference	C1 = MY_FORMULA(A:B)	df.loc[:, 'A':'B']
Range reference	B3 = MY_FORMULA(A1:A2)	RollingRange(df[['A']], 2, -2)

For the first two references types of single-cell references, the passed through type will be a pd.Series. For the entire column reference, a pd.DataFrame will be passed. Finially, the RollingRange is a Mito-specific object that has the following interface.

Rolling Range Object

A rolling range is a helper object that is passed to sheet functions when the user references a range within the sheet. For example, consider how the following references transpile:

- A0 = SUM(B0:B0) => SUM(RollingRange(df[['B']], 1, 0))
- A0 = SUM(B0:B1) => SUM(RollingRange(df[['B']], 2, 0))
- A1 = SUM(B0:B1) => SUM(RollingRange(df[['B']], 2, -1))
- A1 = SUM(B0:B2) => SUM(RollingRange(df[['B']], 3, -1))

To support range references in your custom functions, you can use the RollingRange.apply function, which has the following type:

"""
Calls the passed func with each of the ranges defined all the way down
the series, and returns a series with the same index as the original
dataframe. 
"""
rolling_range.apply(
    func: Callable[[pd.DataFrame], Union[str, float, int, bool, datetime, timedelta]], 
    default_value: Union[str, float, int, bool, datetime, timedelta]=0
) -> pd.Series:

Displaying syntax hints inside of Mito

Displaying syntax support to the user inside of the Mito spreadsheet makes it easy for them to use your custom functions.

If you want to add syntax support for your custom functions, you can do so by writing a docstring for your function that adheres to the following structure. Mito will automatically detect the docstring and convert it into useful syntax support.

def ADD_ONE(series):    
    """
    {
        "function": "ADD_ONE",
        "description": "Returns the series with 1 added to each value.",
        "search_terms": ["my_function"],
        "examples": [
            "ADD_ONE(A)",
            "ADD_ONE(4)"
        ],
        "syntax": "ADD_ONE(value)",
        "syntax_elements": [{
                "element": "value",
                "description": "The number or series to add one to"
            }
        ]
    }
    """
    return series + 1

This will display the syntax support:

How to add spreadsheet functions to the Mitosheet

There are two ways to make the custom functions accessible in Mito:

Passing the spreadsheet functions as a parameters

In Jupyter, you can make spreadsheet functions accessible using the following code:

mitosheet.sheet(sheet_functions=[MY_FUNCTION])

If you're using Streamlit, you can instead pass the spreadsheet functions as a paramter to the spreadsheet component.

Setting an environment variable

If your spreadsheet functions are importable from a .py file, you can set the following environment variables, to make those spreadsheet functions accessible in Mito.

MITO_CONFIG_VERSION = '2'
MITO_CONFIG_CUSTOM_SHEET_FUNCTIONS_PATH = 'path/to/custom_sheet_functions.py'

Examples of Sheet Functions

To see how sheet functions are implemented in practice, you can view example sheet functions here:

1. https://github.com/mito-ds/monorepo/blob/0be44bedf7626baaa9eea6868ddb47c57d0a1d07/mitosheet/mitosheet/public/v3/sheet_functions/number_functions.py#L34
2. https://github.com/mito-ds/monorepo/blob/0be44bedf7626baaa9eea6868ddb47c57d0a1d07/mitosheet/mitosheet/public/v3/sheet_functions/string_functions.py#L86