Building a Dashboard Web App Using Google Sheets and Markdown

Spreadsheets are familiar and flexible. In addition to the ease of inputting, processing, and visualizing data, modern spreadsheets also offer features such as real-time collaborative editing. These advantages make them a popular foundation for managing and analyzing massive amounts of data in many environments the world over.

But how to integrate them into a modern workflow? In this article, we focus on these spreadsheet utilities and explain the detailed process of converting them into a practical dashboard web app using the Morph framework.

The app built in this tutorial is shown below:

Morph is a framework for building web applications in Python and markdown.

Documentation: https://docs.morph-data.io/

Service site: https://www.morph-data.io/

Setting Up the Google Sheets API

1. Enable the Google Sheets API

Log in to the Google Developers Console, create a new project, and enable the Google Sheets API.

2. Create a Service Account and Download the JSON File

Navigate to API & Services > Credentials and create a new service account.

Then, under the service account’s Keys tab, create a new key and download it in JSON format.

3. Invite the Service Account to the Target Spreadsheet

From the spreadsheet’s Share button, add the service account. This will allow the service account to access the data in that spreadsheet.

Initializing the Project

We will use Morph as the application framework. To use Morph, you must have Python 3.9 or later and Node.js 18 or later installed.

For detailed setup instructions, please refer to the documentation.

Installing the Package

pip install morph-data

Initializing the Project

morph new sheet-app

Installing Additional Packages

pip install gspread oauth2client

Calling the Google Sheets API from Python

1. Converting Spreadsheet Data to a DataFrame

import pandas as pd
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import os
import morph
from morph import MorphGlobalContext

# Load authentication credentials and set them in the environment variables
auth = os.path.join(os.path.dirname( __file__ ), "../credential.json")
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = auth

# Set the required scopes
scope = ['https://spreadsheets.google.com/feeds']

# Initialize the API client
credentials = ServiceAccountCredentials.from_json_keyfile_name(auth, scope)
client = gspread.authorize(credentials)

@morph.func
def get_sales_from_sheet(context: MorphGlobalContext):
    spread_sheet = client.open_by_key("SHEET_ID") # Retrieve the entire spreadsheet
    raw_data = spread_sheet.worksheet("SHEET_NAME") # Get the specific worksheet
    all_data = raw_data.get_all_values()
    # Convert to a DataFrame.
    df = pd.DataFrame(all_data[1:], columns=all_data[0])
    return df

2. Creating a Chart with Plotly from the DataFrame

import plotly.express as px
import pandas as pd
import morph
from morph import MorphGlobalContext

@morph.func
@morph.load_data("get_sales_from_sheet") # Load the result from the specified function
def sales_chart(context: MorphGlobalContext):
    # Retrieve the result from get_sales_from_sheet
    df = context.data["get_sales_from_sheet"]
    # Convert the 'Total_Sales' column to numeric
    df['Total_Sales'] = pd.to_numeric(df['Total_Sales'], errors='coerce')
    # Aggregate the data
    df = context.data["get_sales_spreadsheet"].groupby("Product").sum(["Total_Sales"]).reset_index()
    # Create the chart
    fig = px.bar(df, x="Product", y="Total_Sales")
    return fig

One of the key features of Morph is that by using the @morph.load_data annotation, you can load results from other functions. What’s the difference from simply calling a Python function? With Morph, you can handle other file formats — such as SQL and Markdown — using the same mechanism.

3. Building the UI

# Sales Analysis Cheat Sheet

<Grid cols="2"><div>    
    ## Table Display
    <DataTable loadData="get_sales_spreadsheet" height={300} />
  </div>

  <div>
    ## Chart Display
    <Embed loadData="chart_sales_data" height={300} />
  </div>
</Grid>

Deploying to Morph Cloud

If you want to publish your locally built application to the web, you can deploy it to Morph Cloud instead of self-hosting. To deploy to Morph Cloud, simply configure the morph_project.yml file and run morph deploy. For more details, please refer to the documentation below.

Documentation: https://docs.morph-data.io/docs/en/deploy/deploy

By following these steps, you can successfully publish a web dashboard that visualizes your spreadsheet data. The dashboard reflects real-time changes to the spreadsheet without risking accidental modifications to the original data, allowing you to share the dashboard safely. Give it a try!

---