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.



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:

@AvratanuBiswas Dear Avratan,
I followed your video. But I ended up facing this error:

File “C:\Users\s.kian\AppData\Local\Programs\Python\Python310\lib\site-packages\streamlit\runtime\scriptrunner\”, line 556, in _run_script
exec(code, module.dict)
File “C:\Users\s.kian\OneDrive - Intrum Law\Desktop\tt\”, line 11, in
credentials = service_account.Credentials.from_service_account_info(
File “C:\Users\s.kian\AppData\Local\Programs\Python\Python310\lib\site-packages\google\oauth2\”, line 221, in from_service_account_info
signer = _service_account_info.from_dict(
File “C:\Users\s.kian\AppData\Local\Programs\Python\Python310\lib\site-packages\google\”, line 58, in from_dict
signer = crypt.RSASigner.from_service_account_info(data)
File “C:\Users\s.kian\AppData\Local\Programs\Python\Python310\lib\site-packages\google\auth\crypt\”, line 113, in from_service_account_info
return cls.from_string(
File “C:\Users\s.kian\AppData\Local\Programs\Python\Python310\lib\site-packages\google\auth\”, line 154, in from_string
marker_id, key_bytes = pem.readPemBlocksFromFile(
File “C:\Users\s.kian\AppData\Local\Programs\Python\Python310\lib\site-packages\pyasn1_modules\”, line 44, in readPemBlocksFromFile
substrate = ‘’.encode().join([base64.b64decode(x.encode()) for x in certLines])
File “C:\Users\s.kian\AppData\Local\Programs\Python\Python310\lib\site-packages\pyasn1_modules\”, line 44, in
substrate = ‘’.encode().join([base64.b64decode(x.encode()) for x in certLines])
File “C:\Users\s.kian\AppData\Local\Programs\Python\Python310\lib\”, line 87, in b64decode
return binascii.a2b_base64(s)
binascii.Error: Incorrect padding