St.secrets & Google Sheets


I’m seeking to deploy an app to Streamlit Cloud that makes API calls to Google Sheets. While the Streamlit guide for Google Sheets is helpful, my script uses a different method to call Google Sheets, in this case pygsheets (link) instead of gsheetsdb. For authorizing access to Google Sheets, pygsheets requires the Google API credentials in json format. I attempted to implement this in my app’s secret.toml file in the format recommended in the TOML guidelines:

type = 'service_account'
project_id = 'name'
private_key_id = 'private_key_id'
private_key = '''private_key'''
client_email = 'email'
client_id = 'id'
auth_uri = ''
token_uri = ''
auth_provider_x509_cert_url = ''
client_x509_cert_url = 'url'

My app script for initializing the authorization to Google Sheets is:

import streamlit as st
import pygsheets
credentials = st.secrets["gcp_service_account"]
gc = pygsheets.authorize(custom_credentials=credentials)
sh0 ='users') # opens Google Sheet 'users'
wks0 = sh0[0] # opens first worksheet in spreadsheet

When loading the app, the following error is thrown, with the callback below: AttributeError: st.secrets has no attribute "before_request".

File "/home/appuser/venv/lib/python3.7/site-packages/streamlit/scriptrunner/", line 443, in _run_script
    exec(code, module.__dict__)
File "/app/bacon_bot/", line 26, in <module>
    sh0 ='users')
File "/home/appuser/venv/lib/python3.7/site-packages/pygsheets/", line 120, in open
    spreadsheet = list(filter(lambda x: x['name'] == title,[0]
File "/home/appuser/venv/lib/python3.7/site-packages/pygsheets/", line 127, in spreadsheet_metadata
    q=query, pageSize=500, orderBy='recency')
File "/home/appuser/venv/lib/python3.7/site-packages/pygsheets/", line 85, in list
    response = self._execute_request(self.service.files().list(**kwargs))
File "/home/appuser/venv/lib/python3.7/site-packages/pygsheets/", line 364, in _execute_request
    return request.execute(num_retries=self.retries)
File "/home/appuser/venv/lib/python3.7/site-packages/googleapiclient/", line 131, in positional_wrapper
    return wrapped(*args, **kwargs)
File "/home/appuser/venv/lib/python3.7/site-packages/googleapiclient/", line 931, in execute
File "/home/appuser/venv/lib/python3.7/site-packages/googleapiclient/", line 190, in _retry_request
    resp, content = http.request(uri, method, *args, **kwargs)
File "/home/appuser/venv/lib/python3.7/site-packages/", line 209, in request
    self.credentials.before_request(self._request, method, uri, request_headers)
File "/home/appuser/venv/lib/python3.7/site-packages/streamlit/", line 68, in __getattr__
    raise AttributeError(_missing_attr_error_message(attr_name))

Any suggestions on how to approach this?

Hi @Daniel_Hutchinson ,

Here is quick video I made related to Streamlit-GoogleSheet automation. You can refer to the code section and Notion Pages for more details. However, I used gspread python module.

Hope this helps.

1 Like


Tremendous thanks for the assistance! Your code and video were very helpful, and I’ve got the app up and running! Thanks again!

1 Like

I’m glad that it helped you ,Daniel :balloon::hugs: