Experiment: Using data from an external Google Sheet

I set up this page to experiment with getting and using external data from a Google Sheet. The Google Sheet exists here, though you likely don't have access - sorry 🙃

The data loader is fairly simplistic:

1. Set up authentication via a Google service account (which is mounted via the build/publish step for hosting via Github Pages)
2. Pull the data via the gspread library

import os, sys, json
from google.oauth2 import service_account
import gspread
import pandas as pd

# import secrets (for use below)
secrets = json.load(open("env/secrets.json"))

# initialize google sheets access
creds_scope = ["https://www.googleapis.com/auth/spreadsheets.readonly"]
creds_file = None
for f in [secrets["gsheet-sa-local-path"], "gsheet-sa.json"]:
    if os.path.isfile(f):
        creds_file = f
assert os.path.isfile(creds_file), "No credentials file found"
creds = service_account.Credentials.from_service_account_file(creds_file, scopes = creds_scope)
gc = gspread.authorize(creds)

# get data from gsheet
wkb = gc.open_by_key("1wediAtmyRAZgCCaB4Bj9VCvWcOA6Ep8_7jjhJrR51HI")
wks = wkb.worksheet("data")
df = pd.DataFrame(wks.get_all_records())

# export data
df.to_csv(sys.stdout, index = False)

Now that we have the data, here are a few very trivial representations of the data!

Here's the code to make the columns with sparkbars within the table.

function sparkbar(max) {
  return (x) => htl.html`<div style="
    background: var(--theme-blue);
    color: black;
    font: 10px/1.6 var(--sans-serif);
    width: ${100 * x / max}%;
    float: right;
    padding-right: 3px;
    box-sizing: border-box;
    overflow: visible;
    display: flex;
    justify-content: end;">${x.toLocaleString("en-US")}`
}

Here's a chart of the data using Observable Plot!