Mito
Mito for Streamlit
  • Mito Documentation
  • Getting Started
    • Installing Mito
      • Fixing Common Install Errors
      • Installing Mito in a Docker Container
      • Installing Mito for Streamlit
      • Installing Mito for Dash
      • Installing Mito in a Jupyter Notebook Directly
      • Installing Mito in Vertex AI
      • Setting Up a Virtual Environment
  • Data Copilot
    • Data Copilot Core Concepts
    • Agent
    • Chat
    • Autocomplete
    • Smart Debugging
    • Configuration Options
    • Database Connectors
    • AI Data Usage FAQ
  • Apps (Beta)
    • Mito Apps
  • Mito Spreadsheet
    • Core Concepts
    • Creating a Mitosheet
      • Open Existing Virtual Environments
    • Importing Data
      • Importing CSV Files
      • Importing from Excel Files
      • Importing Dataframes
      • Importing from a remote drive
      • Import: Generated UI from any Python Function
      • Importing from other sources
    • Graphing
      • Graph Creation
      • Graph Styling
      • Graph Export
    • Pivoting/Group By
    • Filter
      • Filter By Condition
      • Filter By Value
    • Mito AI
    • Summary Statistics
    • Type Changes
    • Spreadsheet Formulas
      • Custom Spreadsheet Functions
      • Formula Reference
      • Using VLOOKUP
    • Editing Individual Cells
    • Combining Dataframes
      • Merge (horizontal)
      • Concatenate (horizontal)
      • Anti-merge (unique)
    • Sort Data
    • Split Text to Columns
    • Deleting Columns
    • Deleting Rows
    • Column Headers
      • Editing Column Headers
      • Promote Row to Header
    • Deduplicate
    • Fill NaN Values
    • Transpose
    • Reset Index
    • Unpivot a Dataframe (Melt)
    • Formatting
      • Column Formatting
      • Dataframe Colors
      • Conditional Formatting
    • Exporting Data
      • Download as CSV
      • Download as Excel
      • Generate code to create Excel and CSV reports
    • Using the Generated Code
      • Turn generated code into functions
    • Changing Imported Data
    • Code Snippets
    • Custom Editors: Autogenerate UI from Any Function
    • Find and Replace
    • Bulk column header edits
    • Code Options
    • Scheduling your Automation
    • Keyboard Shortcuts
    • Upgrading Mito
    • Enterprise Logging
  • Mito for Streamlit
    • Getting Started with Mito for Streamlit
    • Streamlit Overview
    • Create a Mito for Streamlit App
    • API Reference
      • Understanding import_folder
      • RunnableAnalysis class
      • Column Definitions
    • Streamlit App Gallery
    • Experienced Streamlit Users
    • Common Design Patterns
      • Deploying Mito for Streamlit in a Docker Image
      • Using Mito for Final Mile Data Cleaning
  • Mito for Dash
    • Getting Started
    • Dash Overview
    • Your First Dash App with Mito
    • Mito vs. Other Dash Components
    • API Reference
      • Understanding import_folder
    • Dash App Gallery
    • Common Design Patterns
      • Refresh Sheet Data Periodically
      • Change Sheet Data from a Select
      • Filter Other Elements to Data Selected in Mito
      • Graph New Data after Edits to Mito
      • Set Mito Spreadsheet Theme
  • Tutorials
    • Pass a dataframe into Mito
    • Create a line chart of time series data
    • Delete Columns with Missing Values
    • Split a column on delimiter
    • Rerun analysis on new data
    • Calculate the difference between rows
    • Calculate each cell's percent total of column
    • Import multiple tables from one Excel sheet
    • Share Mito Spreadsheets Across Users
  • Misc
    • Release Notes
      • May 28 - Just a Query Away
      • April 15 - Now Streaming (0.1.18)
      • March 21 - Smarter, Faster, Stronger Agents
      • February 25 - Agent Mode QoL Improvements
      • February 18 - Mito Agents
      • January 2nd - Inline Completions Arrive
      • December 6th - Smarter Workflow
      • November 27th - @ Mentions, Mito AI Server
      • November 4th, 2024 - Hello Mito AI
      • October 8, 2024 - JupyterLab 4
      • Aug 29th, 2024
      • June 12, 2024
      • March 19, 2024
      • March 13th, 2024
      • February 12th, 2024: Graphing Improvements
      • January 25th, 2024
      • January 5th, 2023: Keyboard Shortcuts
      • December 6, 2023: New Context Menu
      • November 28, 2023: Mito's New Toolbar
      • November 7, 2023: Multiplayer Dash
      • October 23, 2023: RunnableAnalysis class
      • October 16, 2023: Mito for Dash, Custom Editors
      • September 29, 2023: VLOOKUP and Find and Replace!
      • September 7, 2023
      • August 2, 2023: Mito for Streamlit!
      • July 10, 2023
      • May 31, 2023: Mito AI Recon
      • May 19, 2023: Mito AI Chat!
      • April 27, 2023: Generate Functions, Performance improvements, bulk column header transformations
      • April 18, 2023: Cell Editor Improvements, BYO Large Language Model, and more
      • April 10, 2023: AI Access, Excel-like Cell Editor, Performance Improvements
      • April 5, 2023: Range formulas, Pandas 2.0, Snowflake Views
      • March 29, 2023: Excel Range Import Improvements
      • March 14, 2023: Mito AI, Public Interface Versioning
      • February 28, 2023: In-place Pivot Errors
      • February 7, 2023: Excel-like Formulas, Snowflake Import
      • January 23, 2023: Excel range importing
      • January 8, 2023: Custom Code snippets
      • December 26, 2022: Code snippets and bug fixes
      • December 12, 2022: Group Dates in Pivot Tables, Reduced Dependencies
      • November 15, 2022: Filter in Pivot
      • November 9, 2022: Import and Enterprise Config
      • October 31, 2022: Replay Analysis Improvements
      • Old Release Notes
      • August 10, 2023: Export Formatting to Excel
    • Mito Enterprise Features
    • FAQ
    • Terms of Service
    • Privacy Policy
  • Mito
Powered by GitBook

© Mito

On this page
  • How to Write Custom Spreadsheet Functions
  • Custom functions must be fully capitalized
  • Custom functions must support the correct input types
  • Displaying syntax hints inside of Mito
  • How to add spreadsheet functions to the Mitosheet
  • Examples of Sheet Functions

Was this helpful?

  1. Mito Spreadsheet
  2. Spreadsheet Formulas

Custom Spreadsheet Functions

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

PreviousSpreadsheet FormulasNextFormula Reference

Last updated 1 year ago

Was this helpful?

This is a feature. 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])

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:

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

Mito Enterprise
Upgrade
spreadsheet component
https://github.com/mito-ds/monorepo/blob/0be44bedf7626baaa9eea6868ddb47c57d0a1d07/mitosheet/mitosheet/public/v3/sheet_functions/number_functions.py#L34
https://github.com/mito-ds/monorepo/blob/0be44bedf7626baaa9eea6868ddb47c57d0a1d07/mitosheet/mitosheet/public/v3/sheet_functions/string_functions.py#L86