How to Build a Business Automation Platform using Mito and Streamlit

A Business Automation Platform is a general purpose platform for automating recurring reports. The goal of the platform isn't to automate any single business process, instead, it's goal is provide end users with the tools they need to automate their own repetitive workflows.

For business users, automation platforms empower them to automate their own business workflows without waiting in long IT queues.

For IT teams, automation platforms allow them to focus on building shared infrastructure used by a large set of important reports instead of trying to understand the specific business edge cases for each of them. And it allows them to capture the business logic as Python code so they can audit it, manage it with GitHub, and schedule them to run.

The structure of a business automation platform is the following:

1. Analyst uses Mito to create a new automation using the same Excel transformations that they are used to. Mito generates the equivalent Python code for every edit the user makes.
2. The app saves the Mito-generated Python code
3. When the analyst wants to rerun their automation, they select it from the list of saved automations and select a new data set to run the automation on.
4. The app automatically uses the saved Python code to rebuild the report on the new dataset.

Creating a New Automation

The only way that automation platforms are useful is if analysts can create their own automations without relying on IT to do it for them. That's why the Mito Spreadsheet is so useful – it allows business analysts to rebuild their report using the same spreadsheet formulas, filters, and pivot tables that they are used to in Excel, and converts each edit into Python code. In that sense, you can think of Mito as a Python macro recorder.

# Create a New Automation
new_automation_name = st.text_input("Automation Name", value="")

# Create a new Mito Spreadsheet for the analyst to build their automation
analysis: RunnableAnalysis = spreadsheet(
    import_folder='./Data',
    return_type='analysis',
    sheet_functions=[GET_EMAIL],
    importers=[get_performance_data],
)

There are two important callouts from the above code:

1. When the return type of Mito is set to 'analysis', the Mito spreadsheet returns a RunnableAnalysis object, which is designed to make it easy to save and rerun an automation on new datasets.
2. Custom sheet functions and custom importers allow app developers to integrate Mito with their enterprise infrastructure. In this example, a custom sheet function called GET_EMAIL allows analysts to make a call to the internal phonebook API. For the analyst, using the GET_EMAIL function is exactly the same as using a native spreadsheet function, like VLOOKUP, in Mito. These integration points are available in Mito Enterprise.

Saving the Automation

When the analyst is finished building their automation, save the Mito generated code as a new automation.

if st.button("Save automation"):
        # When the user clicks the button, save the generated_code returned by Mito to a .py file in the /scripts directory.
        file_path = os.path.join(os.getcwd(), 'scripts', new_automation_name + '.py')
        with open(file_path, 'w') as f:
            f.write(analysis.to_json())

In this automation app, the automation python script is just saved to disk, but more commonly the script is saved to GitHub.

Using an Automation to Create a Report

Once a report automation is created, the next time the user wants to recreate the report on a new dataset, they can select the automation from the list of saved automations and select new input datasets.

In this simple business automation platform, all of the automation scripts are stored in the scripts folder, so finding the available automations for the user to select from is as simple as reading the file names from the scripts folder, but in a more mature application, the automations might be stored in GitHub and metadata like an automation description, creation date, and point of contact might be stored as well.

# Read the file names from the scripts folder
file_names = os.listdir(os.path.join(os.getcwd(), 'scripts'))

# Remove the .py from the end
file_names = [file_name.split('.')[0] for file_name in file_names]

# Create a dropdown for the user to 
# select which  automation they want to use
file_name = st.selectbox("Select an automation", file_names)

if file_name is None:
    st.stop()

# Read the Python automation from the path
path = os.path.join(os.getcwd(), 'scripts', file_name + '.py')
with open(path, 'r') as f:
    json_string = f.read()

# Recreate the RunnableAnalysis object from the stored data
analysis = RunnableAnalysis.from_json(json_string)

Once the user selects the automation, the RunnableAnalysis object makes it easy to determine which data sources the user should be prompted to select. The get_param_metadata function returns an object with all of the analysis parameters that the user might want to overwrite for their new report.

The code below prompts the user to upload a new Excel or CSV file for each input to the automation. In a production app, however, the automation might automatically pull the most up to date data from a database instead of asking for input files.

# Create an object to store the new values for the parameters
updated_metadata = {}

# Loop through the parameters in the analysis to display imports
for param in analysis.get_param_metadata():
    new_param = None
        
    # For imports that are file imports, display a file uploader
    if param['subtype'] in ['file_name_import_excel', 'file_name_import_csv']:
        new_param = st.file_uploader(f"Select a new file to replace: **{param['original_value'].split('/')[-1]}**")
    
    if new_param is not None:
        updated_metadata[param['name']] = new_param

Rebuild the Report on the New Dataset

Once the analyst has updated the inputs required to rebuild the report, the platform automatically recreates the report. That's the magic! The analyst did not need to update formulas, re-apply conditional formatting, etc – the Python code does it all automatically.

To rebuild the report, the app again utilizes the RunnableAnalysis class. This time it uses the run function to rerun the analysis on new data.

if len(updated_metadata) == len(analysis.get_param_metadata()):
  
  st.success("Successful recreated the report")

  # Rebuild the report on the new datasets 
  result = analysis.run(**updated_metadata)

  # Display the automation in a new Mito Spreadsheet 
  spreadsheet(*result)

The Full Code

import json
import streamlit as st
import pandas as pd 
import os
from mitosheet.streamlit.v1 import spreadsheet, RunnableAnalysis

# Set the streamlit page to wide so you can see the whole spreadsheet
st.set_page_config(layout="wide")

st.title("Automation Builder")
st.markdown("""Want to automate your repetitive spreadsheet workflows? This app makes it easy for you to automate your own work without relying on IT.

If you're creating a new automation, click `Start new automation` button below and build your report. 

If you've already created an automation, click `Use Existing Automation` and upload a new dataset to build the report with. 
""")

create_tab, consume_tab = st.tabs(["Start New Automation", "Use Existing Automation"])

with create_tab:

    new_automation_name = st.text_input("Automation Name", value="")

    # Create an empty spreadsheet
    analysis: RunnableAnalysis = spreadsheet(
        import_folder='./Data',
        return_type='analysis',
    )

    if st.button("Save automation"):
        # When the user clicks the button, save the generated_code returned by the spreadsheet 
        # component to a .py file in the /scripts directory.
        file_path = os.path.join(os.getcwd(), 'scripts', new_automation_name + '.py')
        with open(file_path, 'w') as f:
            f.write(analysis.to_json())
            st.success(f"""
                Automation successfully saved as {new_automation_name} 
            """)
            with st.expander("View Generated Python Code", expanded=False):
                st.code(analysis.fully_parameterized_function)

with consume_tab:

    # Read the file names from the scripts folder
    file_names = os.listdir(os.path.join(os.getcwd(), 'scripts'))

    # Remove the .py from the end
    file_names = [file_name.split('.')[0] for file_name in file_names]

    # Create a dropdown to select the file name
    file_name = st.selectbox("Select an automation", file_names)

    if file_name is None:
        st.stop()

    path = os.path.join(os.getcwd(), 'scripts', file_name + '.py')
    # Read the contents of the path
    with open(path, 'r') as f:
        json_string = f.read()

    analysis = RunnableAnalysis.from_json(json_string)

    st.markdown("### Upload the new Data files")

    # Create an object to store the new values for the parameters
    updated_metadata = {}

    # Loop through the parameters in the analysis to display imports
    for param in analysis.get_param_metadata():
        new_param = None

        # For imports that are exports, display a text input
        if param['subtype'] in ['file_name_export_excel', 'file_name_export_csv']:
            new_param = st.text_input(param['original_value'], value=param['initial_value'])
            
        # For imports that are file imports, display a file uploader
        elif param['subtype'] in ['file_name_import_excel', 'file_name_import_csv']:
            new_param = st.file_uploader(f"Select a new file to replace: **{param['original_value'].split('/')[-1]}**")
        
        if new_param is not None:
            updated_metadata[param['name']] = new_param

    # Show a button to trigger re-running the analysis with the updated_metadata

    if len(updated_metadata) == len(analysis.get_param_metadata()):
        st.success("Ran the automation")
        result = analysis.run(**updated_metadata)
        spreadsheet(
            *result,
            import_folder='./Data',
            return_type='analysis',
        )