Google Sheet Connection when I have more than one page

Using Streamlit
1

How can I connect a google sheet when I have more than one page with data?

I have the same link between different pages? How can I do?

streamlit_app.py

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)
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"{row.name} has a :{row.pet}:")

Thank you in advance

2

Hi @Rodolfo_PENA,

Happy to help! :grinning_face_with_smiling_eyes: Here’s an illustrative example:

From the Connect Streamlit to a public Google Sheet tutorial, we see the gsheets URL that is added to .streamlit/secrets.toml looks like the following:

https://docs.google.com/spreadsheets/d/xxxxxxx/edit#gid=0

For this example, let’s use this spreadsheet I created that contains two pages / sheets: Sheet1 and Sheet2

  • Sheet1: https://docs.google.com/spreadsheets/d/1w1hk9YHtpKUGxTgoGBmOSH_jh4NA-Y06cb4KmFEvYrk/edit#gid=0
  • Sheet2: https://docs.google.com/spreadsheets/d/1w1hk9YHtpKUGxTgoGBmOSH_jh4NA-Y06cb4KmFEvYrk/edit#gid=1802976462

Notice that only difference between the page URLs is the value of the gid=xxxxx query parameter. To query both pages, include the respective URLs in your .streamlit/secrets.toml file and call them in your script.

For example, here’s a secrets file with both URLs and an example app that queries both of them:

# .streamlit/secrets.toml

[gsheets]
Sheet1 = "https://docs.google.com/spreadsheets/d/1w1hk9YHtpKUGxTgoGBmOSH_jh4NA-Y06cb4KmFEvYrk/edit#gid=0"
Sheet2 = "https://docs.google.com/spreadsheets/d/1w1hk9YHtpKUGxTgoGBmOSH_jh4NA-Y06cb4KmFEvYrk/edit#gid=1802976462"

Streamlit app:

# streamlit_app.py
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)
    return rows

# Query first sheet
sheet1_url = st.secrets["gsheets"]["Sheet1"]
rows = run_query(f'SELECT * FROM "{sheet1_url}"')

st.subheader('Sheet 1:')
# Print results.
for row in rows:
    st.write(f"{row.name} has a :{row.pet}:")

# Query second sheet
sheet2_url = st.secrets["gsheets"]["Sheet2"]
rows = run_query(f'SELECT * FROM "{sheet2_url}"')

st.subheader('Sheet 2:')
# Print results.
for row in rows:
    st.write(f"{row.name} has a :{row.pet}:")

Output:

image
image1180×822 28.5 KB

Hope this helps :slightly_smiling_face:

Happy Streamlit’ing! :balloon:
Snehan