Private Google Sheets - Invalid URL Error only when Deploying

Community Cloud
1

Hi, I get the following error only when I deploy (works on local):

gsheetsdb.exceptions.InterfaceError: Invalid URL, must be a docs.google.com URL!

This is from the following lines:

qry = f'SELECT * FROM "{sheet_url}"'
rows = conn.execute(query, headers=1)

The code works on my local machine, however it does not work whenever I deploy it - therefore I belive this is a streamlit issue, not a link/authentication issue. I’ve also confirmed (via a st.info() on the app) that the query I use is:

SELECT * FROM "https://docs.google.com/spreadsheets/d/chars_here_in_real_url/edit?usp=sharing"

Folder structure is simple:

my_streamlit_app_folder >
  .streamlit > secrets.toml
   streamlit_app.py

Streamlit version is 1.8.1. I’ve followed the instructions step-by-step here: Connect Streamlit to a private Google Sheet - Streamlit Docs

Any ideas/suggestions for what might be going on? Been struggling with this for a few days, tried rebooting/deleting the app as well however no success so would really appreciate any thoughts - thanks!

Code is below for reference:

import streamlit as st
from google.oauth2 import service_account
from gsheetsdb import connect

# Create a connection object.
credentials = service_account.Credentials.from_service_account_info(
    st.secrets["gcp_service_account"],
    scopes=[
        "https://www.googleapis.com/auth/spreadsheets",
    ],
)
conn = connect(credentials=credentials)


@st.cache(ttl=600)
def run_query(query):
    rows = conn.execute(query, headers=1)
    rows = rows.fetchall()
    return rows


sheet_url = st.secrets["private_gsheets_url"]
qry = f'SELECT * FROM "{sheet_url}"'
st.info(qry)
rows = run_query(qry)

for row in rows:
    st.write(f"{row.name} has a :{row.pet}:")

And secrets.toml below - I’ve copy-pasted over my file from local to App settings > Secrets:

# .streamlit/secrets.toml

private_gsheets_url = "https://docs.google.com/spreadsheets/d/chars_here_in_real_url/edit?usp=sharing"

[gcp_service_account]
type = "service_account"
<<copied from json as per link above>>
2

Fixed! Solution for reference was to specify an older moz_sql_parser version in requirements.txt - I downgraded to

moz_sql_parser==3.125
3

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