DEV Community: hayato onodera

How to Build a Internal Data App with Google Sheets Backend Using Streamlit

hayato onodera — Wed, 02 Apr 2025 08:00:00 +0000

In this article, we’ll walk you through the process of building an internal-use data app using Streamlit with a spreadsheet as the backend. We’ll also explore scenarios where Streamlit is a particularly good fit.

In our previous article, we introduced several ways to build data apps using spreadsheets as the backend.

This time, we’ll dive deeper into one of the code-based frameworks we mentioned — Streamlit — and explain how to build a data app that’s suitable for internal operations.

You can check out the previous article here:https://dev.to/morph-data/how-to-build-an-internal-data-application-using-google-sheets-as-a-data-source-ddo

Step-by-Step Guide

Google Cloud / Google Sheets Configuration

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

2. Generate a Service Account and Download the JSON Key
Go to APIs & Services > Credentials and create a new service account.Under the Keys tab of the service account, create a new key and download it in JSON format.

3. Grant Access to the Service Account on the Target Spreadsheet
Click the Share button on the Google Spreadsheet and add the service account. This allows the service account to access the spreadsheet data.

Setting Up and Building with Streamlit

In this section, we’ll build a Streamlit app using the directory structure shown below.

project-name/
│
├── app.py               
└── service_key.json

1.Move to your project directory and install the Streamlit framework.

cd project-name
pip install streamlit

2.Place the JSON file you obtained in Step-by-Step Guide 2 as service_key.json.

3.Write your code in app.py to build the Streamlit application.

import streamlit as st
from google.oauth2 import service_account
import gspread
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime

# Add a title to the app
st.title('Dashboard')

# Google Sheets Authentication
scopes = [ 'https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive'
]
credentials = service_account.Credentials.from_service_account_file( "./service_key.json", scopes=scopes
)
gc = gspread.authorize(credentials)

# Get data from Google Sheets
SP_SHEET_KEY = '{Sheet ID}' # sheet id (which can be found in the URL of the Google Sheet, after https://docs.google.com/spreadsheets/d/)
sh = gc.open_by_key(SP_SHEET_KEY)
SP_SHEET = 'test' # Sheet name
worksheet = sh.worksheet(SP_SHEET)
data = worksheet.get_all_values() # Get all data from the sheet
df = pd.DataFrame(data[1:], columns=data[0]) # Convert data to DataFrame

# Display original DataFrame (limited to 100 rows with scrolling)
st.subheader('Original Data')
st.dataframe(df.head(100), height=400)  # height parameter adds scrolling capability

# Check if 'Created Date' column exists
if 'Created Date' in df.columns:
    # Convert to datetime format (adjust format as needed)
    df['Created Date'] = pd.to_datetime(df['Created Date'])

    # Group by date and count records
    count_by_date = df.groupby(df['Created Date'].dt.date).size().reset_index()
    count_by_date.columns = ['Created Date', 'Count']

    # Sort by Created Date in descending order
    count_by_date = count_by_date.sort_values('Created Date', ascending=False)

    # Create two columns for side-by-side display
    st.subheader('Record Count by Date')
    col1, col2 = st.columns([3, 2])  # Adjust the ratio to make the first column wider

    # Display grouped DataFrame in the first column
    with col1:
        st.dataframe(count_by_date.head(100), height=400)

    # Create line chart in the second column
    with col2:
        # For the chart, we need to sort by date in ascending order
        chart_data = count_by_date.sort_values('Created Date', ascending=True)
        st.line_chart(chart_data.set_index('Created Date'))
else:
    st.error("'Created Date' column not found in the DataFrame. Please check the column name.")
    st.write("Available columns:", df.columns.tolist())

4.Open the app in your browser to verify that it works.

streamlit run app.py

Adding Authentication for Internal Use

When running Streamlit apps internally, authentication becomes essential.Here, we’ll cover three methods for implementing authentication.
If you’re only deploying a single app, the first method might be sufficient.However, if you have multiple apps or plan to scale in the future, we recommend using either the second or third approach.

1.Add Authentication to Each App Individually

As of version 1.42.0, Streamlit supports OpenID Connect (OIDC) for user authentication.You can use this built-in feature to add flexible authentication to your app.

Pros: Highly flexible authentication
Cons: Requires implementation for each app, which increases development effort

Official documentation: https://docs.streamlit.io/develop/concepts/connections/authentication

2.Use Snowflake in Streamlit

Snowflake in Streamlit allows you to build and host Streamlit apps directly on the Snowflake platform.Since authentication to Snowflake also applies to your Streamlit app, no additional authentication implementation is required.

Pros: No need to implement authentication individually for each app
Cons: Requires a Snowflake account / Limited to fewer components compared to open-source Streamlit

Official documentation: https://docs.snowflake.com/en/developer-guide/streamlit/about-streamlit

3.Use Squadbase

Morph is a cloud platform that supports Streamlit deployment.By deploying your Streamlit app to Morph, you can leverage Morph’s built-in authentication, eliminating the need to implement it manually.

Pros: No need for individual authentication implementation / Centralized app management
Cons: Involves using an external service (Squadbase)

Official documentation: https://www.squadbase.dev/en

Conclusion

Streamlit is a great tool for data scientists and analysts who are comfortable with Python and want to build data applications powered by machine learning or AI.

It’s especially well-suited for internal tools like:

Workflow automation apps using LLMs
Demand forecasting tools
Internal chatbots that reference company documents

There are endless ways to apply Streamlit — so we encourage you to give it a try!

How to Build a Data App with Google Sheets Backend Using Evidence

hayato onodera — Tue, 18 Mar 2025 12:08:52 +0000

In this article, we will introduce the process of building a data app with a spreadsheet backend using Evidence. We will also explore the use cases where Evidence is particularly well-suited.

In the previous article, we introduced multiple methods for building a data app with a spreadsheet backend. This time, we will focus on one of the code-based frameworks mentioned earlier, Evidence, providing a detailed introduction and step-by-step guide.

Here is the previous article:https://dev.to/morph-data/how-to-build-an-internal-data-application-using-google-sheets-as-a-data-source-ddo

What is Evidence?

Evidence is an open-source framework primarily built on SvelteKit, a JavaScript framework. It requires a Node.js environment to operate.

One of the key features of Evidence is that it allows users to build dashboard-like data applications using SQL and Markdown syntax. Unlike traditional BI tools, which require complex GUI operations, Evidence enables users to create and manage interactive dashboards entirely in a code-based manner.

Step-by-Step Guide

Google Cloud / Google Sheets Configuration

1. Enable the Google Sheets API

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

2. Generate a Service Account and Download the JSON Key

Go to APIs & Services > Credentials and create a new service account. Under the Keys tab of the service account, create a new key and download it in JSON format.

3. Grant Access to the Service Account on the Target Spreadsheet

Click the Share button on the Google Spreadsheet and add the service account. This allows the service account to access the spreadsheet data.

Setting Up and Configuring Evidence

1.Install the Evidence framework and start the development server.

npx degit evidence-dev/template project-name
cd project-name
npm install
npm run sources
npm run dev

2.Install the Google Sheets plugin by running:

npm install @evidence-dev/connector-gsheets

3.After installation, add the following to the end of evidence.config.yaml:

"@evidence-dev/connector-gsheets": { }

4.Run npm run dev to launch the Evidence page in the browser. Click on the "..." at the top and navigate to the Settings page.

5.Click +New Sources. Select gsheets as the Source Type, enter a directory name, and click Next.

6.Upload the JSON file obtained in step 2 of the Google Cloud / Google Sheets Configuration section by clicking Choose file. Once the upload is complete, click Test Configuration to verify the connection. If there are no issues, click Save to store the connection settings.

7.Once the connection settings are saved, two files, connection.options.yml and connection.yml, will be generated in the directory created in step 5. Modify connection.yml as follows:

your_workbook_name: The name of the Google Spreadsheet file
your_sheet_id: The Google Spreadsheet ID (the string after https://docs.google.com/spreadsheets/d/ in the URL)

name: [your_source_name]
type: gsheets
options: {}
sheets:
[your_workbook_name]: [your_sheet_id]

8.Create a new file named spreadsheet.md under the pages directory to build the app.

## Check data connection
``sql gsheets
select * from googlesheets.test_test
``

## Count group by date
``sql count_groupby_date
select "Created Date" as date , 
        count(*) as count 
from googlesheets.test_test
     group by "Created Date"
     order by "Created Date" DESC
``

## Dashboard
<Grid cols=2>
    <DataTable data={count_groupby_date}/>

    <LineChart 
        data = {count_groupby_date}    
        y = count 
        title = 'chart' 
    />
</Grid>

9.Open the developed app in your browser to verify that everything is working correctly.

Summary

Evidence is ideal for data teams that primarily work with SQL and need to build data apps for analysis and reporting tasks.

Use cases where Evidence excels include KPI tracking, sharing business metrics (such as financial reports, sales performance dashboards, and marketing analytics reports), and creating regularly updated dashboards managed by data analyst teams.

Traditionally, data aggregation (using SQL) and visualization (using GUI-based BI tools or Python) were separate processes. However, with Evidence, dashboards can be created and managed entirely with SQL, making it a powerful tool for the right use cases.

We encourage you to give it a try!

How to Build an Internal Data Application Using Google Sheets as a Data Source

hayato onodera — Thu, 13 Mar 2025 17:25:35 +0000

This article introduces multiple approaches to building an internal data application using google sheets as the backend. In addition to summarizing the advantages and disadvantages of each approach, we will also discuss the differences from the perspective of AI functionality, which has been gaining increasing demand.

Many teams manage their daily data using Google Sheets due to its high convenience. But when you decide to build a data application using google sheets as the backend, what are your options?

Code-Based / Low-Code / No-Code

The methods for building an internal data application can be categorized into three approaches: code-based development, low-code tools, and no-code tools. Below are the descriptions and representative tools for each approach.

Code-Based

This approach involves building a data application with code. While it requires coding, the frameworks introduced below provide pre-built components that enable rapid development. Additionally, since they are open-source, you can choose your deployment environment.

[Representative Tools]

Low-Code

This approach combines coding and GUI-based development. For simple applications with minimal functionality/UI, it is possible to build using only the GUI, while some parts can be customized with code. Compared to no-code, low-code allows for more flexibility, but it still has limitations compared to full code-based development.

[Representative Tools]

Appsmith: https://www.appsmith.com/
Retool: https://retool.com/
OutSystems：https://www.outsystems.com/?sc_lang=en

No-Code

This approach allows users to build data applications entirely through GUI-based tools, making it accessible to non-engineers. However, it has significant limitations in terms of handling complex processing and UI customization. Additionally, the development skill is heavily dependent on familiarity with the specific tool, making knowledge transfer a challenge.

[Representative Tools]

AppSheet: https://about.appsheet.com/home/
Glide: https://www.glideapps.com/
Softr: https://www.softr.io/

Compatibility with AI Features

One of the most significant emerging needs is AI integration. Let’s examine how well each category supports AI functionality.

Code-Based

With a code-based approach, you can leverage open-source AI frameworks such as Langchain and CrewAI, allowing for the highest level of flexibility in AI development. This approach supports model switching, fine-tuning with custom datasets, and other advanced AI functionalities. Since internal business workflows vary widely between companies and often involve complex logic, this approach is the most recommended for building AI-powered applications tailored to your business needs.

Low-Code

In general, AI functionality in low-code platforms relies on pre-integrated external APIs. While parameters and prompts can be tuned, you cannot perform additional training with new datasets or freely select AI models. This makes low-code a suitable choice for single-task AI use cases but limits its flexibility. Therefore, the AI compatibility of low-code is considered moderate.

No-Code

With a no-code approach, you can only use the AI features provided by the platform. While these features are easy to use, they lack customization options, making them useful only for specific use cases. No-code is ideal for Proof of Concept (PoC) and quick experiments, but for building production-ready applications, AI compatibility is low.

Comparison of Code-Based Frameworks

Next, let’s focus on code-based frameworks and try building a simple application using google sheets as the backend. For this example, we will create a dashboard to manage user inquiry data stored in a spreadsheet.

Streamlit

First, we will build the application with Streamlit. Streamlit allows you to complete the development using a single Python file.

import streamlit as st
from google.oauth2 import service_account
import gspread
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime
# Add a title to the app
st.title('Dashboard')
# Google Sheets Authentication
scopes = [ 'https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive'
]
json_file_path = os.path.join(os.path.dirname( __file__ ), "../credential.json")
credentials = service_account.Credentials.from_service_account_file(json_file_path, scopes=scopes)
gc = gspread.authorize(credentials)
# Get data from Google Sheets
SP_SHEET_KEY = '{Sheet ID}' # sheet id (which can be found in the URL of the Google Sheet, after https://docs.google.com/spreadsheets/d/)
sh = gc.open_by_key(SP_SHEET_KEY)
SP_SHEET = 'test' # Sheet name
worksheet = sh.worksheet(SP_SHEET)
data = worksheet.get_all_values() # Get all data from the sheet
df = pd.DataFrame(data[1:], columns=data[0]) # Convert data to DataFrame
# Display original DataFrame (limited to 100 rows with scrolling)
st.subheader('Original Data')
st.dataframe(df.head(100), height=400) # height parameter adds scrolling capability
# Check if 'Created Date' column exists
if 'Created Date' in df.columns:
 # Convert to datetime format (adjust format as needed)
 df['Created Date'] = pd.to_datetime(df['Created Date'])

 # Group by date and count records
 count_by_date = df.groupby(df['Created Date'].dt.date).size().reset_index()
 count_by_date.columns = ['Created Date', 'Count']

 # Sort by Created Date in descending order
 count_by_date = count_by_date.sort_values('Created Date', ascending=False)

 # Create two columns for side-by-side display
 st.subheader('Record Count by Date')
 col1, col2 = st.columns([3, 2]) # Adjust the ratio to make the first column wider

 # Display grouped DataFrame in the first column
 with col1:
 st.dataframe(count_by_date.head(100), height=400)

 # Create line chart in the second column
 with col2:
 # For the chart, we need to sort by date in ascending order
 chart_data = count_by_date.sort_values('Created Date', ascending=True)
 st.line_chart(chart_data.set_index('Created Date'))
else:
 st.error("'Created Date' column not found in the DataFrame. Please check the column name.")
 st.write("Available columns:", df.columns.tolist())

Evidence

Next, let’s try Evidence. Evidence is built with the SvelteKit JavaScript framework, so you need a Node.js environment to use it.

## Check data connection
``sql gsheets
select * from googlesheets.test_test
``

## Count group by date
``sql count_groupby_date
select "Created Date" as date , 
        count(*) as count 
from googlesheets.test_test
     group by "Created Date"
     order by "Created Date" DESC
``

## Dashboard
<Grid cols=2>
    <DataTable data={count_groupby_date}/>

    <LineChart 
        data = {count_groupby_date}    
        y = count 
        title = 'chart' 
    />
</Grid>

The app will look like this. One nice touch is that the SQL source code is displayed alongside it.

Morph

Finally, we will use the Morph framework. Morph is unique in that it allows you to structure your application using separate Python and Markdown files. Additionally, Python functions are written in a standard function format, making it easier to learn.

# Get spreadsheet data

import pandas as pd
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import os
import morph
from morph import MorphGlobalContext
@morph.func
def get_gsheets(context: MorphGlobalContext):
    # Configure authentication information
    json_path = os.path.join(os.path.dirname( __file__ ), "../credential.json")
    scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
    # get certification information
    credentials = ServiceAccountCredentials.from_json_keyfile_name(json_path, scope)
    client = gspread.authorize(credentials)
    # Open Spreadsheet
    spreadsheet = client.open_by_key("Sheet ID")
    worksheet = spreadsheet.worksheet("Sheet name")
    # Get data and convert to a DataFrame
    data = worksheet.get_all_values()
    df = pd.DataFrame(data[1:], columns=data[0])
    return df

## Display of aggregated table

import pandas as pd
import morph
from morph import MorphGlobalContext
@morph.func()
@morph.load_data("get_gsheets")
def group_by_date(context: MorphGlobalContext):
    df = context.data["get_gsheets"]
    # Group by Created Date and count records, then sort by date in descending order
    grouped_df = df.groupby('Created Date').size().reset_index(name='count')
    grouped_df = grouped_df.sort_values('Created Date', ascending=False)
    return grouped_df

# Display of Chart

import plotly.express as px
import pandas as pd
import morph
from morph import MorphGlobalContext
@morph.func()
@morph.load_data("group_by_date")
def group_by_date_chart(context: MorphGlobalContext):
    df = context.data["group_by_date"]
    df['Created Date'] = pd.to_datetime(df['Created Date'])
    df['count'] = pd.to_numeric(df['count'], errors='coerce')
    # Create a line chart using plotly
    fig = px.line(df, x='Created Date', y='count', title='Record Count by Created Date')
    return fig.to_html()

{/* Frontend */}
## Dashboard
<DataTable
  loadData="get_gsheets"
  height={300}
 />
<Grid cols="2">
    <div>
        ## Table
        <DataTable
            loadData="group_by_date"
            height={300}
        />
    </div>
    <div>
        ## Chart
        <Embed
            loadData="group_by_date_chart"
            height={300}
            width={800}
        />
    </div>
</Grid>

Summary of Comparisons

If you are familiar with Python, Streamlit and Morph will feel the most intuitive. If you are a data analyst proficient in SQL but not experienced with Python, you might prefer Evidence.

For dashboard applications, Evidence is a strong option because it provides many pre-built chart components, reducing the amount of code needed. If you need additional AI functionalities or features beyond dashboards, Streamlit or Morph would be better choices.

If you want maximum flexibility in UI customization, Morph stands out because it allows you to create custom React components and apply styling flexibly using MDX files.

Pros, Cons, and Recommended Use Cases

Conclusion

When comparing no-code, low-code, and code-based approaches for developing data applications with google sheets as the backend, there is a trade-off between flexibility, speed, and engineering requirements. For simple single-function applications or PoC use cases, no-code and low-code are viable options due to their speed. However, for building enterprise-grade internal data applications that meet specific operational needs, code-based approaches are more advantageous.

Considering the increasing demand for AI integration, Python frameworks such as Streamlit and Morph are strong contenders for future-proof data applications. Give them a try and see which one fits your needs!

Building a Dashboard Web App Using Google Sheets and Markdown

hayato onodera — Tue, 18 Feb 2025 07:53:35 +0000

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

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 Squadbase

If you want to publish your locally built application to the web, you can deploy it to Squadbase instead of self-hosting. For more details, please refer to the documentation below.

Documentation: https://www.squadbase.dev/en/docs

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!