Sending Data to Private Google Sheet, authentication & st.secrets

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.

According to the docs you need st.secrets["gcp_service_account"]["type"] instead of st.secrets["type"].

Ah, there it is! Once I had that, I had to finagle slightly with shillelagh, but it worked from there. Thank you!