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(
        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}")'

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"

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 = ""
token_uri = ""
auth_provider_x509_cert_url = ""
client_x509_cert_url = ""

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!


Can you maybe tell me the finagle slightly with shillelagh? I keep getting stuck with getting an error message after all changes.

Hey Wim040, I had the same issue, and you will hate to know what the bug is.

If you read your first field of ‘adapter_kwargs’, it should be “gsheetsapi” , not “gsheetaspi” .
I noticed that typo must originate from shillelagh’s doc because the same typo exists there.

Let me know if this solves your issue!