I’m building a learning page prototype for my physics students using chatgpt on streamlit.
It guided me to pulling course data from google sheets in real time.
Weirdly, works well on the streamlit community cloud with <1 second delay, but has exactly 900 seconds(plus1-2 seconds) time delay locally. Debug results attached as images
. This has made any iteration locally painful.
The main difference is in authentication method. Using environment variable locally (which works flawlessly with a python script locally). And streamlit secrets on cloud.
Here is the debug code:
import os
import time
import json
import gspread
import pandas as pd
from google.oauth2 import service_account
import streamlit as st
from pathlib import Path
def get_credentials():
"""Get credentials based on environment"""
try:
scope = [
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/drive",
]
# Check for local secrets.toml
secrets_path = Path('.streamlit/secrets.toml')
if secrets_path.exists():
st.write("🌩️ Testing Cloud Authentication...")
credentials_info = dict(st.secrets["GOOGLE_APPLICATION_CREDENTIALS"])
credentials = service_account.Credentials.from_service_account_info(
credentials_info,
scopes=scope
)
else:
st.write("💻 Testing Local Authentication...")
credentials_path = os.getenv("GOOGLE_APPLICATION_CREDENTIALS")
if not credentials_path:
st.error("❌ GOOGLE_APPLICATION_CREDENTIALS not set")
return None
credentials = service_account.Credentials.from_service_account_file(
credentials_path,
scopes=scope
)
st.success("✅ Credentials loaded successfully")
return credentials
except Exception as e:
st.error(f"❌ Authentication error: {str(e)}")
return None
@st.cache_resource
def get_client(_credentials):
"""Get cached client connection"""
try:
with st.spinner("🔄 Testing Google API connection..."):
start = time.time()
client = gspread.authorize(_credentials)
auth_time = time.time() - start
st.success(f"✅ Connected in {auth_time:.2f}s")
return client
except Exception as e:
st.error(f"❌ Connection error: {str(e)}")
return None
@st.cache_data(ttl=3600)
def get_sheet_data(_client):
"""Get cached sheet data"""
try:
with st.spinner("🔄 Testing sheet access..."):
start = time.time()
sheet = _client.open("LMS").sheet1
values = sheet.get_values()
fetch_time = time.time() - start
st.success(f"✅ Data fetched in {fetch_time:.2f}s")
return values, fetch_time
except Exception as e:
st.error(f"❌ Sheet access error: {str(e)}")
return None, None
# Main debug interface
st.title("🔍 Google Sheets Integration Debug")
if st.button("Run Debug Tests", type="primary"):
# Step 1: Test credentials
credentials = get_credentials()
if credentials:
# Step 2: Test client connection
client = get_client(credentials)
if client:
# Step 3: Test data fetch
values, fetch_time = get_sheet_data(client)
if values:
# Data preview
df = pd.DataFrame(values[1:], columns=values[0])
st.write(f"📊 Found {len(df)} rows and {len(df.columns)} columns")
st.dataframe(df.head())
# Performance metrics
st.info(f"⏱️ Total fetch time: {fetch_time:.2f}s")
# Debug information
with st.expander("🔧 Debug Information"):
st.write(f"- Environment: {'Cloud' if Path('.streamlit/secrets.toml').exists() else 'Local'}")
st.write(f"- Working Directory: {os.getcwd()}")
st.write(f"- Python Version: {pd.__version__}")
st.write(f"- Streamlit Version: {st.__version__}")
Checked locally with running this python script locally as suggested by GPT, it got the sheet data in <1 second.
import time
import gspread
from google.oauth2 import service_account
import pandas as pd
credentials = service_account.Credentials.from_service_account_file(
"/Users/atmandeshmane/Documents/physics-foundation-streamlit-5282814e8533.json",
scopes=["https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/drive"]
)
client = gspread.authorize(credentials)
sheet = client.open("LMS").sheet1
start = time.time()
data = sheet.get_all_records()
end = time.time()
print(f"✅ Data fetched in {end - start:.2f} seconds")
df = pd.DataFrame(data)
print(df.head()) # Print first few rows
So it seems that there’s no problem with google APIs. The problem occurs only when I try to get the data from sheets using streamlit, and only locally. With the same exact code.
Here is the github link: GitHub - Atman-Deshmane/Streamlit-LMS: This is me first time building a streamlit LMS for my students.