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!


This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.