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

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

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

cd project-name
pip install streamlit

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())

streamlit run app.py

Related Posts

Top 10 React Libraries/Frameworks for 2025

Feb 20, 2025  0

Women's Day

Mar 8, 2025  0

React Suspense and Concurrent Rendering: The Future of ...

Mar 17, 2025  0