tha-google-runner 0.1.0

A Tabular Helper API library that wraps Google Sheets with a typed, consistent interface built on gspread.

tha-google-runner

A Tabular Helper API library that wraps Google Sheets with a typed, consistent interface built on gspread.

Install

pip install tha-google-runner

Authentication setup

tha-google-runner uses your personal Google account — not a service account. There are two ways to authenticate. Option 1 is recommended if you have the Google Cloud SDK installed.

Cost note: This package is free and open source. The Google APIs it uses (Google Sheets API, Google Drive API) are also free for normal scripting workloads — Google provides a generous free tier (300 reads/min, 60 writes/min) that the vast majority of users will never exceed. Google Cloud Console may ask for a credit card when you first create a project to verify your identity, but Google does not charge you for the APIs used here. Any billing questions are between you and Google — not this package.

Option 1 — Application Default Credentials (ADC)

This is the zero-config path. Run once in your terminal:

gcloud auth application-default login

A browser window opens, you sign in with your Google account, and credentials are saved to your machine. After that, ThaSheets() works with no arguments.

Don't have gcloud? Install the Google Cloud SDK — it's a standalone CLI tool, roughly similar in spirit to the AWS CLI or the Azure CLI. It is not heavy and not venv-specific; install it once at the system level and every Python project on your machine can use ADC. Or skip it entirely and use Option 2.

Option 2 — OAuth2 client secrets

Use this if you don't have gcloud or prefer not to install it.

Step 1 — Create a Google Cloud project

1. Go to console.cloud.google.com
2. Click the project dropdown → New Project → give it any name → Create

Step 2 — Enable the required APIs

In your new project, go to APIs & Services → Enable APIs and Services and enable both:

• Google Sheets API
• Google Drive API

Step 3 — Create OAuth2 credentials

1. Go to APIs & Services → Credentials → Create Credentials → OAuth 2.0 Client ID
2. If prompted, configure the OAuth consent screen first:
   • User type: External → fill in app name and your email → save
3. Application type: Desktop app → give it a name → Create
4. Click Download JSON and save the file (e.g., client_secrets.json)

Step 4 — Use the credentials file

sheets = ThaSheets(credentials_file="client_secrets.json")

On the first run, a browser window opens for you to grant access. After that, the token is cached at ~/.config/tha-google-runner/token.json and no browser is needed.

---

Quick start

from tha_google_runner import ThaSheets

sheets = ThaSheets()  # uses ADC; or pass credentials_file="client_secrets.json"

# Read all rows (first row is headers)
rows = sheets.read(spreadsheet_id="your-spreadsheet-id")

# Append new rows (writes headers automatically if the sheet is empty)
sheets.append_rows(
    [{"name": "Alice", "score": 95}, {"name": "Bob", "score": 82}],
    spreadsheet_id="your-spreadsheet-id",
)

# Append using raw lists — header row auto-detected and dropped if it matches the sheet
sheets.append_rows(
    [["name", "score"], ["Alice", 95]],
    spreadsheet_id="your-spreadsheet-id",
)

# Overwrite the entire sheet
sheets.update_rows(
    [{"name": "Alice", "score": 95}],
    spreadsheet_id="your-spreadsheet-id",
)

# Upsert by key — inserts new rows, updates existing ones
sheets.upsert_rows(
    [{"id": "1", "name": "Alice", "score": 99}],
    key="id",
    spreadsheet_id="your-spreadsheet-id",
)

# Create a new spreadsheet and get its ID
spreadsheet_id = sheets.create("My Report", rows=[{"col": "val"}])

# Clear a sheet
sheets.clear(spreadsheet_id="your-spreadsheet-id")

Finding your spreadsheet ID: It's the long string in the URL between /d/ and /edit. https://docs.google.com/spreadsheets/d/<spreadsheet-id>/edit

You can also pass url= instead of spreadsheet_id= to any method and the ID will be extracted automatically.

---

Row input formats

All write methods (append_rows, update_rows, upsert_rows, create, add_sheet) accept either format:

list[dict] — keys are column headers:

[{"name": "Alice", "score": 95}, {"name": "Bob", "score": 82}]

list[list] — raw rows with automatic header detection:

[["name", "score"], ["Alice", 95], ["Bob", 82]]

Header detection for list[list] input:

Sheet state	First row matches existing headers?	Result
Has data	Yes	Header row dropped, rest appended as data
Has data	No	All rows treated as data
Empty / being replaced	—	First row always becomes headers

---

API

ThaSheets(*, credentials_file=None, token_file=None)

ThaSheets(
    credentials_file: str | None = None,  # path to client_secrets.json; None uses ADC
    token_file: str | None = None,         # override token cache path (OAuth2 only)
)

The Google client is built lazily on first use and cached for the lifetime of the instance. After any write, sheets.rows is set to the data rows that were written (as list[dict]).

---

read(*, spreadsheet_id=None, url=None, sheet_name=None) -> list[dict]

Read all rows. The first row is treated as headers; each subsequent row becomes a dict.

rows = sheets.read(spreadsheet_id="spreadsheet-id")
rows = sheets.read(url="https://docs.google.com/spreadsheets/d/.../edit")
rows = sheets.read(spreadsheet_id="spreadsheet-id", sheet_name="Q1 Data")

---

append_rows(rows, *, spreadsheet_id=None, url=None, sheet_name=None) -> int

Append rows to an existing sheet. Returns the number of rows appended.

• If the sheet is empty, the headers are written first.
• Missing keys in a row are filled with "".

count = sheets.append_rows(
    [{"name": "Alice", "score": 95}],
    spreadsheet_id="spreadsheet-id",
)

---

update_rows(rows, *, spreadsheet_id=None, url=None, sheet_name=None) -> int

Overwrite all data in a sheet. Clears the sheet first, then writes headers + rows. Returns the number of rows written. Passing an empty list clears the sheet and returns 0.

count = sheets.update_rows(
    [{"name": "Alice", "score": 95}],
    spreadsheet_id="spreadsheet-id",
)

---

upsert_rows(rows, *, key, spreadsheet_id=None, url=None, sheet_name=None, on_conflict="update_all") -> int

Insert new rows and update existing ones matched by key. Returns the number of rows upserted.

• key — column name (str) or list of column names for composite keys
• New columns in incoming rows are appended to the sheet automatically
• on_conflict controls what happens when multiple existing rows match the same key:
  • "update_all" (default) — update every matching row
  • "update_first" — update only the first match
  • "update_last" — update only the last match
  • "skip" — leave duplicates untouched
  • "raise" — raise GoogleError

count = sheets.upsert_rows(
    [{"id": "1", "name": "Alice", "score": 99}],
    key="id",
    spreadsheet_id="spreadsheet-id",
)

# Composite key
count = sheets.upsert_rows(rows, key=["year", "month"], spreadsheet_id="spreadsheet-id")

---

create(title, *, rows=None, sheet_name="Sheet1") -> str

Create a new spreadsheet. Returns the new spreadsheet's ID.

sid = sheets.create("My Report")
sid = sheets.create("My Report", rows=[{"col": "val"}], sheet_name="Data")

---

delete(*, spreadsheet_id=None, url=None) -> None

Permanently delete a spreadsheet.

sheets.delete(spreadsheet_id="spreadsheet-id")

---

list_sheets(*, spreadsheet_id=None, url=None) -> list[str]

Return the names of all worksheets in a spreadsheet.

names = sheets.list_sheets(spreadsheet_id="spreadsheet-id")
# ["Sheet1", "Q1 Data", "Archive"]

---

add_sheet(sheet_name, *, spreadsheet_id=None, url=None, rows=None) -> None

Add a new worksheet to an existing spreadsheet. Optionally write initial rows.

sheets.add_sheet("Q2 Data", spreadsheet_id="spreadsheet-id")
sheets.add_sheet("Q2 Data", spreadsheet_id="spreadsheet-id", rows=[{"col": "val"}])

---

delete_sheet(sheet_name, *, spreadsheet_id=None, url=None) -> None

Delete a worksheet from a spreadsheet.

sheets.delete_sheet("Archive", spreadsheet_id="spreadsheet-id")

---

share(email, *, spreadsheet_id=None, url=None, role="reader") -> None

Share a spreadsheet with a user. role can be "reader", "writer", or "owner".

sheets.share("[e-mail]", spreadsheet_id="spreadsheet-id", role="writer")

---

clear(*, spreadsheet_id=None, url=None, sheet_name=None) -> None

Clear all data in a sheet. Resets sheets.rows to [].

sheets.clear(spreadsheet_id="spreadsheet-id")
sheets.clear(spreadsheet_id="spreadsheet-id", sheet_name="Archive")

---

License

MIT