Connect Streamlit to a public Google Sheet

Introduction

This guide explains how to securely access a public Google Sheet from Streamlit. It uses st.connection, Streamlit GSheetsConnection, and Streamlit's Secrets management.

This method requires you to enable link sharing for your Google Sheet. While the sharing link will not appear in your code (and actually acts as sort of a password!), someone with the link can get all the data in the Sheet. If you don't want this, follow the (more complicated) guide to Connect Streamlit to a private Google Sheet.

Prerequisites

This tutorial requires streamlit>=1.28 and st-gsheets-connection in your Python environment.

Create a Google Sheet and turn on link sharing

If you already have a Sheet that you want to access, you can skip to the next step. See Google's documentation on how to share spreadsheets for more information.

Create a spreadsheet with this example data and create a share link. The link should have "Anyone with the link" set as a "Viewer."

name	pet
Mary	dog
John	cat
Robert	bird

Add the Sheets URL to your local app secrets

Your local Streamlit app will read secrets from a file .streamlit/secrets.toml in your app's root directory. Create this file if it doesn't exist yet and add the share link of your Google Sheet to it as shown below:

# .streamlit/secrets.toml
[connections.gsheets]
spreadsheet = "https://docs.google.com/spreadsheets/d/xxxxxxx/edit#gid=0"

priority_high

Important

Add this file to .gitignore and don't commit it to your GitHub repo!

Write your Streamlit app

Copy the code below to your Streamlit app and run it.

# streamlit_app.py

import streamlit as st
from streamlit_gsheets import GSheetsConnection

# Create a connection object.
conn = st.connection("gsheets", type=GSheetsConnection)

df = conn.read()

# Print results.
for row in df.itertuples():
    st.write(f"{row.name} has a :{row.pet}:")

See st.connection above? This handles secrets retrieval, setup, query caching and retries. By default, .read() results are cached without expiring. You can pass optional parameters to .read() to customize your connection. For example, you can specify the name of a worksheet, cache expiration time, or pass-through parameters for pandas.read_csv like this:

df = conn.read(
    worksheet="Sheet1",
    ttl="10m",
    usecols=[0, 1],
    nrows=3,
)

In this case, we set ttl="10m" to ensure the query result is cached for no longer than 10 minutes. You can also set ttl=0 to disable caching. Learn more in Caching. We've declared optional parameters usecols=[0,1] and nrows=3 for pandas to use under the hood.

If everything worked out (and you used the example table we created above), your app should look like this:

Connecting to a Google Sheet from Community Cloud

This tutorial assumes a local Streamlit app, however you can also connect to Google Sheets from apps hosted in Community Cloud. The main additional steps are:

Include information about dependencies using a requirements.txt file with st-gsheets-connection and any other dependencies.
Add your secrets to your Community Cloud app.

Previous: Private Google Sheet Next: Snowflake

forum

Still have questions?

Our forums are full of helpful information and Streamlit experts.