Gspread together with streamlit is extremely slow

🎈 Using Streamlit
1

Summary

As a part of a larger project I’m using gspread to read and write data to a google sheet. It has worked perfectly before but lately it has been extremely slow, everything between 30 mins to several hours just to fetch a couple of lines of text. I’ve tried setting up different environments, trying python 3.10 as well as 3.9 together with Streamlit 1.21 and 1.22 but always the same issue. The strange thing is that the same code works fine in Jupyter, using the same environment, which makes me think that this is some kind of network issue. I can see in the Google api dashboard that the calls only go through sometimes. How do I troubleshoot this and has anyone experienced similar issues?

Steps to reproduce

import gspread
gc = gspread.service_account()
def getCell(sheetName='sheet_projects'):
    try:
        sh = gc.open("sheet_projects").sheet1.get('A1')
        st.write(sh)
        
    except Exception as e:
        st.write(e)

getCell()

Expected behavior:

This code is a minimal version of the larger project just to test that the sheet connection works. The code works fine in Jupyter, using the same environment as Streamlit, and gives me the contents of the cell in a few seconds. On Streamlit it just loads forever and doesn’t give me an error.

Debug info

  • Streamlit version: 1.22
  • Python version: 3.11.3
  • gspread version: 5.7.1 (have also tried 5.8.0 in another environment but same issue)
  • miniforge 23.1.0-1, using conda to set up environments
  • OS version: macOS 13.3.1
  • Browser version: Tested both Safari 16.4 and Chrome 113.0.5672.63.
2

Hi @Niklas

Thanks for your question.

  1. Have you tried adding st.cache_data in a preceding line to the getCell function.
  2. Instead of gspread, can you try the implementation described in the Streamlit Docs for private/public Google Sheets:

Hope this helps!

Best regards,
Chanin

3

Hi @dataprofessor, thank you for the quick reply!

I tried setting st.cache_data before the function but it behaved the same, except for the spinner.

I need the code to be independent from Streamlit, I only use it to display some things with some controls, so using a Streamlit specific solution for google sheets wouldn’t work unfortunately.

However, since I thought it might be some network issue I tried switching to a mobile connection instead and then it worked immediately! The wifi otherwise works fine and is fast, and it’s only gspread that is affected by the slow speed, I’m using pymongo in other parts of the code and that works fine regardless if I use wifi or something else. I found this thread where they seem to have the same or similar issue https://discuss.streamlit.io/t/i-am-not-able-to-access-my-app-when-using-wifi-network/38033

What could I do?

Edit:
I just tried using a VPN and that makes it work as well. Do I need to change some network settings to make it work on regular wifi?

4

Definitely is something related to your connection because for me gspread is working fine at the moment even for large databases, with streamlit.

5

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