Solved issue of pulling private google sheet into a streamlit app using gspread instead of gsheetsdb

[sudo_reboot]

So I tried using this documentation (Connect Streamlit to a private Google Sheet - Streamlit Docs) to pull data from a private google worksheet of financial data, but gsheetsdb was causing issues (and I think it’s deprecated). Also, I wanted to pull the data as a pandas DataFrame instead of using sql commands. Here is the solution I found, while still using the secrets.toml approach in the main documentation.

First step is to follow about 90% of the guide Connect Streamlit to a private Google Sheet - Streamlit Docs except, in particular, about setting up the google cloud env and adding the shared email to your private sheet. Just ignore the last bit of code at the end. Instead do this:

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

scopes = [
    "https://www.googleapis.com/auth/spreadsheets",
]

skey = st.secrets["gcp_service_account"]
credentials = Credentials.from_service_account_info(
    skey,
    scopes=scopes,
)
client = gspread.authorize(credentials)


# Perform SQL query on the Google Sheet.
# Uses st.cache_data to only rerun when the query changes or after 10 min.
@st.cache_data(ttl=600)
def load_data(url, sheet_name="Transactions"):
    sh = client.open_by_url(url)
    df = pd.DataFrame(sh.worksheet(sheet_name).get_all_records())
    return df

Now just call load_data(…) and it will return a pandas dataframe. As usual, make sure to update your requirements.txt with gspread. One caveat is that the .get_all_records function didn’t like when the sheet had empty column in between non empty ones. So I deleted those and everything seemed to work. Hope this helps someone.

[Caroline]

Just wanted to add that you can also use Shillelagh for this – I have some example snippets I can share as well

[sudo_reboot]

Could you share? That would be fantastic. Which is preferable?

[Caroline]

import streamlit as st
from google.oauth2 import service_account
from shillelagh.backends.apsw.db import connect

sheet_url = st.secrets["private_gsheets_url"]

def create_connection():
     if 'email' in st.session_state:
          credentials = service_account.Credentials.from_service_account_info(
            st.secrets["gcp_service_account"], 
            scopes=["https://www.googleapis.com/auth/spreadsheets",],)
          connection = connect(":memory:", adapter_kwargs={
            "gsheetsapi" : { 
            "service_account_info" : {
                "type" : st.secrets["gcp_service_account"]["type"],
                "project_id" : st.secrets["gcp_service_account"]["project_id"],
                "private_key_id" : st.secrets["gcp_service_account"]["private_key_id"],
                "private_key" : st.secrets["gcp_service_account"]["private_key"],
                "client_email" : st.secrets["gcp_service_account"]["client_email"],
                "client_id" : st.secrets["gcp_service_account"]["client_id"],
                "auth_uri" : st.secrets["gcp_service_account"]["auth_uri"],
                "token_uri" : st.secrets["gcp_service_account"]["token_uri"],
                "auth_provider_x509_cert_url" : st.secrets["gcp_service_account"]["auth_provider_x509_cert_url"],
                "client_x509_cert_url" : st.secrets["gcp_service_account"]["client_x509_cert_url"],
                }
            },
        })
     return connection.cursor()

def execute_query(query):
     cursor = create_connection()
     rows = cursor.execute(query)
     rows = rows.fetchall()

execute_query("your query")

[wagnerbrito]

@sudo_reboot

I

Hi! After that, how can I check if the code is reading my google spreadsheet correctly? I’m a beginner in streamlit and your method seemed easier to me. I haven’t deployed yet, I’m testing locally before publishing.

pardon my english, it’s not my native language