Google sheet connection

🎈 Using Streamlit
,
1

Hi!
I created a Streamlit app that pulls data from google sheet like so:
conn = st.connection(“gsheets”, type=GSheetsConnection)
data = conn.read(worksheet=‘Games’,usecols=list(range(6)))
st.dataframe(data)

Every time the sheet is updated, it does not reflect in the app.
I need to manually reboot the server.
Is there a way around that?
for data to be updated without the need of server reboot?
or for an automatic server reboot in Streamlit cloud?
Thank you all!

2 
conn = st.connection('gsheets', type=GSheetsConnection)
data = conn.read(worksheet='Games',usecols=list(range(6)))
st.dataframe(data)

The read() method has a ttl (time to live) parameter that is responsible in keeping the data in memory after the first reading from the source.

The concept is that if ttl is 60 seconds, the memory will be cleared after 60 seconds. If ttl is 3600 seconds (1 hour) the memory will be cleared after 1 hour. When we say memory is cleared that means the data is removed from memory and as a result the function will read again the data, right at the heart of the google sheet source and save the data in memory ready to be served if there are requests.

If we don’t specify a ttl as you did from your posted code, the default value is None. It is the same as.

data = conn.read(worksheet='Games', ttl=None, usecols=list(range(6)))

The ttl=None means that the data in memory will not be cleared. So even if the sheet from source has been updated, our app will not see it because the data served by the server was from memory the last time it reads from the sheet.

We may have 3 options to reasonably clear the cache.

  1. To force it to clear, you need to access the 3-dot menu located at the top right and press the clear cache.

image

  1. If that is not accessible you need to create a button in your app that forcefully clears the cache.
def clear_my_cache():
    st.cache_data.clear()

st.button('Clear memory', on_click=clear_my_cache)
  1. Modify the ttl value.
ttl_seconds = 10
data = conn.read(worksheet='Games', ttl=ttl_seconds, usecols=list(range(6)))

The memory will be cleared after every 10 seconds and data will be updated. Note sometimes this is annoying as you will see the interface displays some messages.

Setting ttl=1 or 1 second is overkill, google has a limit of request. Making requests at short time interval is not good for their hardware/resources of both google and streamlit community cloud servers. Your app might get warning messages, etc. As developers we try to avoid unnecessary excessive usage of resources as much as possible as these are just free.

Depending on how many seconds the google sheet receives updates, you have to set your ttl logically. My recommendation is to set ttl to something like 120 or 120 seconds or 2 minutes (ttl=120). Then add a button as in option 2. While interacting with the app, the message that pops during update will only happen every 2 minutes (You can increase that). Then add a button to clear the memory as in option 2, so that whenever you want to see right now, ahora mismo, the current content of the sheet, you just have to press that clear button.

References