I am trying to create a Streamlit app which will take data input by a user (mostly via selectbox) and then when that user has provided all of the data, they submit the data and a service account adds that data as a line in a Google Sheet. I’ve never made an app like this before, just some general Python data analysis scripts, so I’m really struggling and right now I seem to be stuck on the issue of authentication and using st.secrets.
I’ve tried following the example in the docs for connecting a Streamlit app to a GoogleSheet, but that example only provides an example for reading data from a sheet, not for adding data to the sheet (which is an idea for improving it). Additionally, that example uses gsheetsdb which has been deprecated in favor of shillelagh, but as a newbie, I’m finding the documentation for that package to be inadequate.
Here’s a shortened version of what I have so far:
from google.oauth2 import service_account
from shillelagh.backends.apsw.db import connect
import streamlit as st
with st.form("contest_entry_form"):
name = st.text_input("Your name")
email = st.text_input("Your email address")
accepted_answers = ["aaa", "bbb", "ccc"]
q1a1 = st.selectbox("Question 1, Answer 1", ["PASS"] + accepted_answers)
q1a2 = st.selectbox("Question 1, Answer 1", ["PASS"] + accepted_answers)
q1a3 = st.selectbox("Question 1, Answer 1", ["PASS"] + accepted_answers)
submitted = st.form_submit_button("Submit your entry!")
if submitted:
# Here's where it falls apart, I've kludged this together from the streamlit docs and shillelagh docs
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={
"gsheetaspi" : {
"service_account_info" : {
"type" : st.secrets["type"],
"project_id" : st.secrets["project_id"],
"private_key_id" : st.secrets["private_key_id"],
"private_key" : st.secrets["private_key"],
"client_email" : st.secrets["client_email"],
"client_id" : st.secrets["client_id"],
"auth_uri" : st.secrets["auth_uri"],
"token_uri" : st.secrets["token_uri"],
"auth_provider_x509_cert_url" : st.secrets["auth_provider_x509_cert_url"],
"client_x509_cert_url" : st.secrets["client_x509_cert_url"],
}
},
})
cursor = connection.cursor()
sheet_url = st.secrets["private_gsheets_url"]
query = f'INSERT INTO "{sheet_url}" VALUES ("{name}", "{email}", "{q1a1}", "{q1a2}", "{q1a3}")'
cursor.execute(query)
At present, when I run my app, I get this: “KeyError: 'st.secrets has no key “type”. Did you forget to add it to secrets.toml or the app settings on Streamlit Cloud?” I have created my secrets.toml file locally, and I’ve copy/pasted the secrets in to my Secrets in the settings for my app, as directed. Here is what my secrets page looks like, here in the app:
private_gsheets_url = "ThisIsMyURLForTheGoogleSheet"
[gcp_service_account]
type = "service_account"
project_id = "ThisIsMyGoogleCloudProjectID"
private_key_id = "ThisIsMyPrivateKeyID"
private_key = "-----BEGIN PRIVATE KEY-----\nThisIsMyPrivateKey\n-----END PRIVATE KEY-----\n"
client_email = "ThisIsMyServiceAccountEmailAddress"
client_id = "ThisIsABunchOfNumbers"
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 = "https://www.googleapis.com/SecretsGoHere"
I feel like whatever I’m doing wrong is inevitably going to be a small and silly thing, but I’m at my wits end and have spent all day trying to figure this out. If anyone could give me a hand here with what I’m doing wrong, I’d very much appreciate it.
