GSheets Connection - "Spreadsheet must be specified"

Deployment
1

Hi Community,

I have encountered some issues with my private expense tracker app (maybe I will further improve the project and set it to public).
The app is connected to a private google sheet using streamlit_gsheets (GSheetsConnection). When I run the app locally, everything runs fine with the connection to google sheets and quering the data.
Originally the deployment on the streamlit cloud worked as well, but after about one week of usage I ran into this error message:

File "/home/adminuser/venv/lib/python3.9/site-packages/streamlit/runtime/scriptrunner/script_runner.py", line 535, in _run_script
    exec(code, module.__dict__)File "/mount/src/schmeckels-tracker/Schmeckels.py", line 67, in <module>
    data = conn.read(worksheet = "Datasheet", usecols= list(range(6)), ttl = 5)File "/home/adminuser/venv/lib/python3.9/site-packages/streamlit_gsheets/gsheets_connection.py", line 570, in read
    return self.client.read(File "/home/adminuser/venv/lib/python3.9/site-packages/streamlit_gsheets/gsheets_connection.py", line 419, in read
    raise ValueError("Spreadsheet must be specified")

The code block in questions is as follows:

“”"

—Google Sheets Connection —

conn = st.connection(“gsheets”, type=GSheetsConnection)

#read dataframe (already as pandas df)

data = conn.read(worksheet = “Datasheet”, usecols= list(range(6)), ttl = 5)
“”"
This should work according to all the tutorials out there and as mentioned above it already did run successfully on the cloud and runs perfectly locally.

Any help would be much appreciated!

Kind Regards,
Nicholas

2

Hi @NicholasA0712

Could it be that after a certain period of time, the Google API quote has been exceeded. Could you try putting the line data = conn.read() to be behind an if st.button() statement so that it does not consistently run every time the app is refreshed.

See example from gsheets-connection/examples/pages/Service_Account_Example.py at main · streamlit/gsheets-connection · GitHub

Here’s an excerpt from the above mentioned example:

   # click button to update worksheet
    # This is behind a button to avoid exceeding Google API Quota
    if st.button("Create new worksheet"):
        df = conn.create(
            worksheet="Example 1",
            data=df,
        )
        st.cache_data.clear()
        st.experimental_rerun()

    # Display our Spreadsheet as st.dataframe
    st.dataframe(df.head(10))

Hope this helps!

3

Hi @dataprofessor,

thank you for the fast reply. Unfortunately, that didn’t solve my problem.

Since the app (including sheet connection) works perfekly fine when I host it locally I assume the problem is somehow connected to the cloud deployment.
As before the app still starts when deployed to the cloud but the same error message keeps coming up.
What I do not understand is the spreadsheet is defined according to the documentation using "worksheet = " which already worked on the cloud and still does locally.

I am thankful for any possible solutions you might have.

Kind Regards,
Nicholas

4

Can you make sure you have pinned a specific version of your dependencies locally and on cloud, using requirements.txt? What you’re describing sounds like you might have a dependency that got auto-updated on cloud, and didn’t get updated on local, so you’re running two different versions of some dependency.

5

Note that spreadsheet and worksheet are different – spreadsheet specifies a Google Sheet, and worksheet specifies which tab of that sheet should be used. Can you also make sure that your secrets.toml locally and your secrets on cloud are the same?

6

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