Tech With Galvan
Back to Articles
DevelopmentDec 6, 2026 • 5 min read

Streamlit + Google Sheets: Use Sheets as a Database

Connect Streamlit to Google Sheets for data storage and retrieval — gspread, service accounts, CRUD operations, and a real app example.

Galvan
Galvan

Founder & Creator

Introduction

Google Sheets is the world’s most popular “database” — everyone knows how to use it, it’s free, and it has a real-time UI. For small Streamlit apps, Sheets as a backend is surprisingly practical: no DB setup, no SQL, and your team can edit data directly in the sheet.

This post connects Streamlit to Google Sheets with gspread.

Setup

pip install gspread google-auth streamlit

Create a service account

  1. Go to Google Cloud Console.
  2. Create a project → Enable “Google Sheets API” and “Google Drive API”.
  3. Create a Service Account → download the JSON key.
  4. Share your Google Sheet with the service account email (Editor access).

Store the JSON key in Streamlit secrets:

# .streamlit/secrets.toml
[gcp_service_account]
type = "service_account"
project_id = "your-project"
private_key_id = "..."
private_key = "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n"
client_email = "[email protected]"
client_id = "..."
auth_uri = "https://accounts.google.com/o/oauth2/auth"
token_uri = "https://oauth2.googleapis.com/token"

Step 1: Connect

import streamlit as st
import gspread
from google.oauth2.service_account import Credentials

scopes = ["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"]
creds = Credentials.from_service_account_info(st.secrets["gcp_service_account"], scopes=scopes)
gc = gspread.authorize(creds)

# Open by name or URL
sh = gc.open("My Data Sheet")
worksheet = sh.sheet1

Step 2: Read data

# Get all records as list of dicts
records = worksheet.get_all_records()
import pandas as pd
df = pd.DataFrame(records)
st.dataframe(df, use_container_width=True)

# Get a specific cell
val = worksheet.acell("A1").value

# Get a range
values = worksheet.get("A1:D10")

Step 3: Write data

# Append a row
worksheet.append_row(["Alice", "[email protected]", "admin"])

# Update a cell
worksheet.update_acell("B2", "[email protected]")

# Update a range
worksheet.update("A2:C2", [["Bob", "[email protected]", "user"]])

# Clear
worksheet.clear()

Step 4: Full CRUD app

import streamlit as st
import gspread, pandas as pd
from google.oauth2.service_account import Credentials

@st.cache_resource
def get_sheet():
    creds = Credentials.from_service_account_info(st.secrets["gcp_service_account"],
        scopes=["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"])
    gc = gspread.authorize(creds)
    return gc.open("Contacts").sheet1

ws = get_sheet()

st.title("📇 Contacts (Google Sheets)")

# Read
df = pd.DataFrame(ws.get_all_records())
st.dataframe(df, use_container_width=True)

# Add
with st.form("add", clear_on_submit=True):
    name = st.text_input("Name")
    email = st.text_input("Email")
    if st.form_submit_button("Add") and name and email:
        ws.append_row([name, email])
        st.success("Added!")
        st.rerun()

# Delete (by row index)
if not df.empty:
    to_delete = st.selectbox("Delete contact", df["Name"].tolist())
    if st.button("Delete"):
        idx = df[df["Name"] == to_delete].index[0] + 2  # +2 for header + 1-indexed
        ws.delete_rows(idx)
        st.rerun()

Step 5: Caching (important)

Google Sheets API has rate limits (60 requests/min). Cache reads:

@st.cache_data(ttl=30)   # refresh every 30s
def load_sheet():
    return pd.DataFrame(ws.get_all_records())

df = load_sheet()

Write operations should clear the cache: load_sheet.clear() after writes.

Step 6: Real-time sync

Sheets updates in real-time. To see changes from other users:

if st.button("🔄 Refresh"):
    load_sheet.clear()
    st.rerun()

Or auto-refresh with st.fragment(run_every=30).

Google Sheets vs Supabase vs SQLite

Google Sheets Supabase SQLite
Setup Share sheet with SA Create project Zero
UI for editing ✅ (Sheets UI) Dashboard No
Rate limits 60 req/min Generous None (local)
Persistence Cloud Cloud Local (ephemeral on Cloud)
Best for Small teams, non-devs Production apps Prototypes
Cost Free Free tier Free

Common mistakes

  • Not sharing the sheet — the service account email must have Editor access.
  • Rate limiting — batch reads/writes; cache aggressively.
  • Storing the service account JSON in code — use st.secrets.
  • No error handling — API calls fail; wrap in try/except.
  • Using Sheets for large data — Sheets maxes at ~10M cells. For big data, use a real DB.

Key Concepts

  • gspread = Python client for Google Sheets
  • Service account = headless auth (no user login)
  • st.secrets = secure credential storage
  • @st.cache_data(ttl=...) = avoid rate limits
  • CRUD = create, read, update, delete rows

What to Try Next

FAQ

Google Sheets vs Airtable?

Airtable has a better API and more features (views, automations, linked records). Sheets is simpler and free. For complex data, Airtable. For simple lists, Sheets.

Can multiple users edit simultaneously?

Yes — Sheets handles concurrent edits. But Streamlit’s cache may show stale data; use short TTLs (10-30s) and a refresh button.

Is Google Sheets secure enough for production?

For internal tools with non-sensitive data, yes. For customer data or PII, use a real database (Supabase, Postgres) with proper access controls. Sheets’ permissions are coarse (viewer/editor).