Query Worksheets in a Google Spreadsheet

Using Streamlit
,
1

Hello all,

I wanted to know if there is a way to find all worksheets that exist within a spreadsheet. I don’t see anything mentioned in the .connection documentation. I found an earlier post (linked below), but I don’t think the method mentioned is currently available. Any help would be greatly appreciated. Thank you!

2

There’s not an obvious way to do it, but you can if you get the underlying connection object like this:

import streamlit as st

from streamlit_gsheets import GSheetsConnection

conn = st.connection("gsheets", type=GSheetsConnection)

sh = conn._instance._open_spreadsheet()

worksheets = sh.worksheets()

for worksheet in worksheets:
    st.write(worksheet.title)

Note that this depends on having a connection called gsheets set up in .streamlit/secrets.toml, including the spreadsheet url.

[connections.gsheets]
spreadsheet = "https://docs.google.com/spreadsheets/d/..."
type = "service_account"
project_id = "..."
private_key_id = "..."
private_key = "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n"
client_email = "..."
client_id = "..."
auth_uri = "https://accounts.google.com/o/oauth2/auth"
token_uri = "https://oauth2.googleapis.com/token"
auth_provider_x509_cert_url = "https://www.googleapis.com/oauth2/v1/certs"
client_x509_cert_url = "..."
3

@blackary Awesome, thank you so much for the quick reply!

4

@blackary One other question, is it possible to set the size of the newly created workbook (i.e., number of columns and rows) through gsheets_connection?

5

The only way I know how to do it is by creating a worksheet from a dataframe – then the worksheet will have the same number of rows and columns as the dataframe

    df = conn.create(
        worksheet="Example 1",
        data=df,
    )
6

Sweet! Thanks again.