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
- Go to Google Cloud Console.
- Create a project → Enable “Google Sheets API” and “Google Drive API”.
- Create a Service Account → download the JSON key.
- 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
- Build an admin panel backed by Sheets.
- Add auth so only team members edit.
- Combine with real-time dashboard — Sheets as the data source.
- Migrate to Supabase when you outgrow Sheets.
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).