St.secrets & Google Sheets

Greetings,

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:

[[gcp_service_account]]
type = 'service_account'
project_id = 'name'
private_key_id = 'private_key_id'
private_key = '''private_key'''
client_email = 'email'
client_id = 'id'
auth_uri = 'https://accounts.google.com/o/oauth2/auth'
token_uri = 'https://oauth2.googleapis.com/token'
auth_provider_x509_cert_url = 'https://www.googleapis.com/oauth2/v1/certs'
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 = gc.open('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/script_runner.py", line 443, in _run_script
    exec(code, module.__dict__)
File "/app/bacon_bot/Baconbot_1_6_7.py", line 26, in <module>
    sh0 = gc.open('users')
File "/home/appuser/venv/lib/python3.7/site-packages/pygsheets/client.py", line 120, in open
    spreadsheet = list(filter(lambda x: x['name'] == title, self.drive.spreadsheet_metadata()))[0]
File "/home/appuser/venv/lib/python3.7/site-packages/pygsheets/drive.py", line 127, in spreadsheet_metadata
    q=query, pageSize=500, orderBy='recency')
File "/home/appuser/venv/lib/python3.7/site-packages/pygsheets/drive.py", line 85, in list
    response = self._execute_request(self.service.files().list(**kwargs))
File "/home/appuser/venv/lib/python3.7/site-packages/pygsheets/drive.py", line 364, in _execute_request
    return request.execute(num_retries=self.retries)
File "/home/appuser/venv/lib/python3.7/site-packages/googleapiclient/_helpers.py", line 131, in positional_wrapper
    return wrapped(*args, **kwargs)
File "/home/appuser/venv/lib/python3.7/site-packages/googleapiclient/http.py", line 931, in execute
    headers=self.headers,
File "/home/appuser/venv/lib/python3.7/site-packages/googleapiclient/http.py", line 190, in _retry_request
    resp, content = http.request(uri, method, *args, **kwargs)
File "/home/appuser/venv/lib/python3.7/site-packages/google_auth_httplib2.py", line 209, in request
    self.credentials.before_request(self._request, method, uri, request_headers)
File "/home/appuser/venv/lib/python3.7/site-packages/streamlit/secrets.py", 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.
Best
Avra

2 Likes

Avra,

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\script_runner.py”, line 556, in _run_script
exec(code, module.dict)
File “C:\Users\s.kian\OneDrive - Intrum Law\Desktop\tt\gsh.py”, 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\service_account.py”, 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\auth_service_account_info.py”, 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\base.py”, 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\crypt_python_rsa.py”, 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\pem.py”, 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\pem.py”, 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\base64.py”, line 87, in b64decode
return binascii.a2b_base64(s)
binascii.Error: Incorrect padding