Skip to main content

Streamlit Connection for Google Sheets.

Project description

Streamlit GSheetsConnection

Connect to public or private Google Sheets from your Streamlit app. Powered by st.experimental_connection() and gspread.

GSheets Connection works in two modes:

  • in Read Only mode, using publicly shared Spreadsheet URLs (Read Only mode)
  • CRUD operations support mode, with Authentication using Service Account. In order to use Service Account mode you need to enable Google Drive and Google Sheets API in Google Developers Console. Follow Initial setup for CRUD mode section in order to authenticate your Streamlit app first.

Streamlit App

Install

pip install st-gsheets-connection

Minimal example: publicly shared spreadsheet (read-only)

# example/st_app.py

import streamlit as st
from streamlit_gsheets import GSheetsConnection

url = "https://docs.google.com/spreadsheets/d/1JDy9md2VZPz4JbYtRPJLs81_3jUK47nx6GYQjgU8qNY/edit?usp=sharing"

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

data = conn.read(spreadsheet=url, usecols=[0, 1])
st.dataframe(data)

Service account / CRUD example

Initial setup for private spreadsheet and/or CRUD mode

  1. Setup .streamlit/secrets.toml inside your Streamlit app root directory, check out Secret management documentation for references.
  2. Enable API Access for a Project
    • Head to Google Developers Console and create a new project (or select the one you already have).
    • In the box labeled “Search for APIs and Services”, search for “Google Drive API” and enable it.
    • In the box labeled “Search for APIs and Services”, search for “Google Sheets API” and enable it.
  3. Using Service Account
    • Enable API Access for a Project if you haven’t done it yet.
    • Go to “APIs & Services > Credentials” and choose “Create credentials > Service account key”.
    • Fill out the form
    • Click “Create” and “Done”.
    • Press “Manage service accounts” above Service Accounts.
    • Press on ⋮ near recently created service account and select “Manage keys” and then click on “ADD KEY > Create new key”.
    • Select JSON key type and press “Create”.

You will automatically download a JSON file with credentials. It may look like this:

{
    "type": "service_account",
    "project_id": "api-project-XXX",
    "private_key_id": "2cd … ba4",
    "private_key": "-----BEGIN PRIVATE KEY-----\nNrDyLw … jINQh/9\n-----END PRIVATE KEY-----\n",
    "client_email": "473000000000-yoursisdifferent@developer.gserviceaccount.com",
    "client_id": "473 … hd.apps.googleusercontent.com",
    ...
}

Remember the path to the downloaded credentials file. Also, in the next step you’ll need the value of client_email from this file.

  • :red[Very important!] Go to your spreadsheet and share it with a client_email from the step above. Just like you do with any other Google account. If you don’t do this, you’ll get a gspread.exceptions.SpreadsheetNotFound exception when trying to access this spreadsheet from your application or a script.
  1. Inside streamlit/secrets.toml place service_account configuration from downloaded JSON file, in the following format (where gsheets is your st.connection name):
# .streamlit/secrets.toml

[connections.gsheets]
spreadsheet = "<spreadsheet-name-or-url>"
worksheet = "<worksheet-gid-or-folder-id>"  # worksheet GID is used when using Public Spreadsheet URL, when usign service_account it will be picked as folder_id
type = ""  # leave empty when using Public Spreadsheet URL, when using service_account -> type = "service_account"
project_id = ""
private_key_id = ""
private_key = ""
client_email = ""
client_id = ""
auth_uri = ""
token_uri = ""
auth_provider_x509_cert_url = ""
client_x509_cert_url = ""

Code

# example/st_app_gsheets_using_service_account.py

import streamlit as st
from streamlit_gsheets import GSheetsConnection

st.title("Read Google Sheet as DataFrame")

conn = st.experimental_connection("gsheets", type=GSheetsConnection)
df = conn.read(worksheet="Example 1")

st.dataframe(df)
# .streamlit/secrets.toml

[connections.gsheets]
spreadsheet = "<spreadsheet-name-or-url>"
worksheet = "<worksheet-gid-or-folder-id>"  # worksheet GID is used when using Public Spreadsheet URL, when usign service_account it will be picked as folder_id
type = ""  # leave empty when using Public Spreadsheet URL, when using service_account -> type = "service_account"
project_id = ""
private_key_id = ""
private_key = ""
client_email = ""
client_id = ""
auth_uri = ""
token_uri = ""
auth_provider_x509_cert_url = ""
client_x509_cert_url = ""
# requirements.txt

streamlit==1.22
git+https://github.com/streamlit/gsheets-connection
pandasql  # this is for example/st_app.py only

Full example

Check gsheets_connection/example directory for full example of the usage.

Q&A

  • Does this work with a public spreadsheet without the authentication details? Or only a private spreadsheet?

    GSheets Connection works in two modes:

    • in Read Only mode, using publicly shared Spreadsheet URLs (Read Only mode)
    • CRUD operations support mode, with Authentication using Service Account. In order to use Service Account mode you need to enable Google Drive and Google Sheets API in Google Developers Console. Follow Initial setup for CRUD mode section in order to authenticate your Streamlit app first.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

st-gsheets-connection-0.0.4.tar.gz (9.1 kB view details)

Uploaded Source

Built Distribution

st_gsheets_connection-0.0.4-py3-none-any.whl (8.9 kB view details)

Uploaded Python 3

File details

Details for the file st-gsheets-connection-0.0.4.tar.gz.

File metadata

  • Download URL: st-gsheets-connection-0.0.4.tar.gz
  • Upload date:
  • Size: 9.1 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? Yes
  • Uploaded via: twine/4.0.2 CPython/3.11.7

File hashes

Hashes for st-gsheets-connection-0.0.4.tar.gz
Algorithm Hash digest
SHA256 2caca6d934b5f6a955ef6406598f573473005f3817be2bc8dd29d7da6e0dfa95
MD5 ecf7524d8750d5211ce234011c41d72a
BLAKE2b-256 4fe09a262955fe063050339967b70e98d26ae3181264c9ea7387d07097592b31

See more details on using hashes here.

Provenance

File details

Details for the file st_gsheets_connection-0.0.4-py3-none-any.whl.

File metadata

File hashes

Hashes for st_gsheets_connection-0.0.4-py3-none-any.whl
Algorithm Hash digest
SHA256 34547989bc5aafb23d445317d7d5cbeded6dca2e1929368af9eefcbf73ab5756
MD5 26f4ed4a8b0b9cf592fe0c851e16b9cb
BLAKE2b-256 f2e8f018382d4250dc2ebfa3674d03a88177921092631a240ed6dc66ea11fa1e

See more details on using hashes here.

Provenance

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page