Error occured in connecting streamlit with public google sheets

🎈 Using Streamlit
1

I want to connect my streamlit app with public google sheets.

Therefore i had followed some pre-requisites Connect Streamlit to a public Google Sheet.

Following are some pre-requisite i have adhered:

  • Creation of .streamlit/secrets.toml file & pasted sheet url link inside it.

  • Added gsheetsdb==x.x.x version to requirements.txt file.

Following is public google sheet.

save
save1366×639 34.2 KB

Following is code:

import streamlit as st
from gsheetsdb import connect

# Create a connection object.
conn = connect()

# Perform SQL query on the Google Sheet.
# Uses st.cache to only rerun when the query changes or after 10 min.
@st.cache(ttl=600)
def run_query(query):
    rows = conn.execute(query, headers=1)
    rows = rows.fetchall()
    return rows

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

# Print results.
for row in rows:
    st.write(f" User: {row.Email} has password :{row.Password}:")

I am getting following errors:

ImportError: cannot import name 'Iterable' from 'collections' (C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\collections\__init__.py)
Traceback:
File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py", line 564, in _run_script
    exec(code, module.__dict__)
File "C:\Users\User\Desktop\Development\Minor Project\Sentiment Analysis Web APP\dbfetch.py", line 2, in <module>
    from gsheetsdb import connect
File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\gsheetsdb\__init__.py", line 1, in <module>
    from gsheetsdb.db import connect
File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\gsheetsdb\db.py", line 11, in <module>
    from gsheetsdb.query import execute
File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\gsheetsdb\query.py", line 11, in <module>
    from moz_sql_parser import parse as parse_sql
File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\moz_sql_parser\__init__.py", line 15, in <module>
    from moz_sql_parser.sql_parser import SQLParser, scrub_literal, scrub
File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\moz_sql_parser\sql_parser.py", line 12, in <module>
    from mo_parsing.engine import Engine
File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\mo_parsing\__init__.py", line 60, in <module>
    from mo_parsing.infix import LEFT_ASSOC, RIGHT_ASSOC, infixNotation
File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\mo_parsing\infix.py", line 4, in <module>
    from collections import Iterable

I am doing my graduation minor project using streamlit please help me out. Your help will really be appreciable. :pray:

2

Take a look at tha readme file of gsheetsdb.

1 Like
3

Thankyou so much @Goyo for replying.
After implementing below code:

import streamlit as st
#from gsheetsdb import connect
from shillelagh.backends.apsw.db import connect
conn = connect()
result = conn.execute("""
    SELECT
        *
    FROM
        "https://docs.google.com/spreadsheets/d/1Qrinki83wzM0TJrMNiACnInHAN9db1okjZwzdNiOX04/"
""", headers=1)
for row in result:
    print(row)

I am getting below error:

TypeError: connect() missing 1 required positional argument: 'path'
Traceback:
File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py", line 564, in _run_script
    exec(code, module.__dict__)
File "C:\Users\User\Desktop\Development\Minor Project\Sentiment Analysis Web APP\dbfetch.py", line 23, in <module>
    conn = connect()
           ^^^^^^^^^

Please help me rectify this one as well.
which path should mention inside connect() function

4

I guess ":memory:" should do.

5

I am having the same issue. after adding “::memory::”

I get the following error:

InterfaceError: Unable to load adapter datasetteapi

Traceback:

File "/usr/local/lib/python3.10/site-packages/streamlit/runtime/scriptrunner/script_runner.py", line 565, in _run_script
    exec(code, module.__dict__)File "/Users/asifk/Documents/github/portfolio/pages/7_✨_Makers.py", line 19, in <module>
    connection = connect(":memory:")File "/usr/local/lib/python3.10/site-packages/shillelagh/backends/apsw/db.py", line 521, in connect
    enabled_adapters = registry.load_all(adapters, safe)File "/usr/local/lib/python3.10/site-packages/shillelagh/adapters/registry.py", line 58, in load_all
    return self._load_all_safe(adapters) if safe else self._load_all(adapters)File "/usr/local/lib/python3.10/site-packages/shillelagh/adapters/registry.py", line 91, in _load_all
    return {File "/usr/local/lib/python3.10/site-packages/shillelagh/adapters/registry.py", line 92, in <dictcomp>
    name: self.load(name, safe=False)File "/usr/local/lib/python3.10/site-packages/shillelagh/adapters/registry.py", line 44, in load
    raise InterfaceError(f"Unable to load adapter {name}")
1 Like
6

I’m having same issue as khushnoodasis… let me know if you found a fix.

7

There is an issue with shillelagh that requires you to install a number of extra packages before it actually works with google sheets. pip install 'shillelagh[all]' should fix it, if you don’t mind extra packages.

However, there is a new simpler method simply using pandas in the just-updated tutorial Connect Streamlit to a public Google Sheet - Streamlit Docs

csv_url = sheets_url.replace("/edit#gid=", "/export?format=csv&gid=")
return pd.read_csv(csv_url)
1 Like
8

Installing shillelagh[gsheetsapi] is probably enough (didn’t test it myself).

9

It should be enough, but there seems to be a bug in shillelagh that assumes the url is a datasette url, so it fails unless datasette is installed also. Installing those two is sufficient, even though gsheetsapi should be sufficient, but [all] gets you everything you need for sure :slight_smile:

1 Like
10

How would this work for a private google sheet? E.g., how to pass the credentials? I would love to be able to load into a pandas dataframe like this instead of querying the sheet.

1 Like
11

@sudo_reboot You can see an example Connect Streamlit to a private Google Sheet - Streamlit Docs of how to set up the credentials, which should also work with shillelagh.