GoogleSheet: Simple, Remote Tabulation#

class my.apis.GoogleSheet.GoogleSheet#

A singleton wrapping the Google Sheets API v4 for usage in scripts and notebooks.

This code is written against Google’s v4 REST API for their Google Sheets product. They have multiple official python packages, but they all are fatally flawed in one way or another.

Important

These methods are only present if the optional google dependency is installed (pip install my-basis[google]). If you try to call them without it, an ImportError will be thrown.

Authentication is handled automatically via Google’s OAuth2.0 for Web Server Applications flow, which works by prompting the user to log in via an auto-launched browser window. From there, this class handles token caching and refresh, storing credentials in the creds_dir directory.

Caution

Credentials are stored unencrypted. Though they do expire quickly, please do not use this on an unsecure system without setting up some sort of encryption!

Data is seamlessly converted between Google Sheets’ list-of-lists format and pandas DataFrames. The class supports both single and batch operations for reading and writing worksheets, with intelligent handling of headers, indices, and cell ranges. Methods like read()/batch_read() and write()/batch_write() abstract away the complexity of the underlying API while preserving flexibility through A1-notation cell ranges.

The singleton pattern ensures a single authenticated connection is maintained throughout your application’s lifecycle, with explicit connect() and disconnect() methods for resource management.

Examples

Connect once, then move worksheets in and out as DataFrames (requires the google extra, plus a browser for the first OAuth run):

>>> from my.apis import GoogleSheet
>>> sheet = GoogleSheet()
>>> sheet.connect('1BxiM...your-sheet-id...upms')
>>> sheet.worksheets
['Roster', 'Scores']
>>> df = sheet.read('Roster')
>>> sheet.write('Scores', df)

I Initial Methods#

GoogleSheet.connect(uid: str) → None#

Connect to a Google Sheet via its sheet ID, loading its contents into local memory.

Fetches the sheet’s metadata, recording its display name and the ordered list of its worksheets. Triggers the OAuth flow on first use (see auth()).

Parameters:

uid – The Google Sheet ID (not URL).

GoogleSheet.disconnect() → None#

Disconnect from the current Google Sheet, clearing all cached data and freeing memory.

II Serialization Utilities#

static GoogleSheet.serialize_data(data: <MagicMock name = 'pd.DataFrame' id='140222154510688'>, header: bool = True, index: bool = False) → list[list[str]]#

Serialize a pandas DataFrame into a list of lists for Google Sheets API consumption.

Parameters:

  • data – The DataFrame to serialize.

  • header – If true, include column names as the first row.

  • index – If true, include the index as the first column.

Returns:

A list of lists representing the DataFrame.

Examples

Serialize a small frame, with and without its index:

>>> import pandas as pd
>>> from my.apis import GoogleSheet
>>> df = pd.DataFrame({'name': ['ada', 'bob'], 'score': [92, 85]})
>>> GoogleSheet.serialize_data(df)
[['name', 'score'], ['ada', '92'], ['bob', '85']]
>>> GoogleSheet.serialize_data(df, index=True)
[['index', 'name', 'score'], ['0', 'ada', '92'], ['1', 'bob', '85']]
static GoogleSheet.deserialize_data(values: list[list], header: int = 1, index: str = '') → <MagicMock name='pd.DataFrame' id='140222154510688'>#

Deserialize a list of lists from the Google Sheets API into a pandas DataFrame.

Parameters:

  • values – The list of lists to deserialize.

  • header – The 1-based row number to use as column names (rows before it are dropped), or 0 to number the columns instead. Short header rows are padded with Column N placeholders.

  • index – The column to use as the index, if any.

Returns:

A pandas DataFrame representing the data.

Examples

Rebuild a DataFrame from raw cell values:

>>> from my.apis import GoogleSheet
>>> GoogleSheet.deserialize_data([['name', 'score'], ['ada', '92'], ['bob', '85']])
  name score
0  ada    92
1  bob    85
static GoogleSheet.shape_to_range(shape: tuple[int, int], start: str = 'A1') → str#

Convert a shape (width, height) into an A1-style range string.

Parameters:

  • shape – The dimensions of the range, i.e. (num_columns, num_rows).

  • start – The cell to place the top-left corner of the block on (default A1).

Returns:

An A1-style range string.

Examples

Anchor a block at the origin or at an arbitrary cell:

>>> from my.apis import GoogleSheet
>>> GoogleSheet.shape_to_range((3, 5))
'A1:C5'
>>> GoogleSheet.shape_to_range((2, 2), start='B2')
'B2:C3'

III Primary Methods#

GoogleSheet.genexec(endpoint: str, **kwargs: Any) → dict[str, Any]#

Generic executor for Google Sheets API endpoints (relatively rare).

Parameters:

  • endpoint – The endpoint to call.

  • kwargs – Additional arguments to pass to the endpoint.

Returns:

The response from the API call.

GoogleSheet.exec(endpoint: str, **kwargs: Any) → dict[str, Any]#

Generic executor for Google Sheets API values endpoints (most data operations).

Parameters:

  • endpoint – The endpoint to call.

  • kwargs – Additional arguments to pass to the endpoint.

Returns:

The response from the API call.

GoogleSheet.auth() → None#

Authenticate with Google APIs, caching tokens locally in creds_dir.

If the user already has credentials stored on disk, those are read by this method and no unnecessary work is performed. The credentials are automatically refreshed if necessary & possible.

See Google’s authentication flow documentation for further guidance: googleapis.dev/python/google-auth/latest/reference/google.oauth2.credentials.html

IV Public Methods#

property GoogleSheet.is_connected: bool#

Check if currently connected to a Google Sheet.

property GoogleSheet.creds_dir: Path#

Get the directory where credentials are stored, creating it if it doesn’t exist.

property GoogleSheet.cache_dir: Path#

Get the directory where cache files are stored, creating it if it doesn’t exist.

property GoogleSheet.sheets_api: Any#

Build and return the Google Sheets API client.

property GoogleSheet.files_api: Any#

Build and return the Google Drive API client for file-level operations.

property GoogleSheet.values: Any#

Return the Google Sheets API values resource.

GoogleSheet.mtime() → datetime | None#

Get the last modified time of the connected Google Sheet, or None if not connected.

GoogleSheet.read(worksheet: str, cells: str = 'A1:Z', header: int = 1, index: str = '') → <MagicMock name='pd.DataFrame' id='140222154510688'>#

Load a single worksheet from the given google sheet via the Google Sheets API.

Parameters:

  • worksheet – The name (NOT ID) of the worksheet to load.

  • cells – The range of cells to load.

  • header – The index of the row to use as column names; rows before it are ignored.

  • index – The index of the column to use as the row names (i.e. the ‘index’), if any.

Returns:

A pandas DataFrame with the worksheet data.

Examples

Load a range, optionally naming the header and index (requires a connection):

>>> df = sheet.read(
...     'Roster', cells='A1:C10', header=1, index='name')
GoogleSheet.batch_read(*args: str, **kwargs: dict[str, Any]) → dict[str, <MagicMock name='pd.DataFrame' id='140222154510688'>]#

Load multiple worksheets/ranges from the given google sheet via the Google Sheets API.

Parameters:

  • *args – The worksheet names or ranges to load.

  • **kwargs – Additional options for each range. Each value is a dictionary of options to pass to deserialize_data.

Returns:

A map from range strings to DataFrames, with keys simplified to bare worksheet names whenever those are unique.

Examples

Load several worksheets in one API call (requires a connection):

>>> frames = sheet.batch_read('Roster', 'Scores!A1:D20')
GoogleSheet.clear(worksheet: list[str] | str, cells: list[str] | str = '') → None#

Clear the given range(s) from the Google Sheet.

If a single cell is provided, it applies to all given worksheets. If a list is provided, it must match the length of worksheet, identifying cells for each.

Parameters:

  • worksheet – The worksheet name(s) to clear.

  • cells – The cell range(s) to clear.

Examples

Clear one range, or several worksheets at once (requires a connection):

>>> sheet.clear('Roster', 'A2:C100')
>>> sheet.clear(['Roster', 'Scores'])
GoogleSheet.write(worksheet: str, data: <MagicMock name = 'pd.DataFrame' id='140222154510688'>, cells: str = 'A1:Z', **kwargs: Any) → None#

Write data to a single worksheet in the Google Sheet.

Parameters:

  • worksheet – The name of the worksheet to write to.

  • data – The DataFrame to write.

  • cells – The range of cells to write to.

  • **kwargs – Additional arguments to pass to serialize_data.

Examples

Write a DataFrame into a worksheet, headers included (requires a connection):

>>> sheet.write('Scores', df, cells='A1:Z')
GoogleSheet.batch_write(**kwargs: <MagicMock name = 'pd.DataFrame' id='140222154510688'>) → None#

Write multiple DataFrames to the Google Sheet in a single batch operation.

Bare worksheet names have a range (and headers/index) computed from each DataFrame’s shape; explicit Sheet!A1:... targets are written as-is.

Parameters:

**kwargs – A mapping from worksheet names or cell ranges to DataFrame objects.

Examples

Write two worksheets in one API call (requires a connection):

>>> sheet.batch_write(Roster=roster_df, Scores=scores_df)
GoogleSheet.add_worksheets(*args: str, **kwargs: Any) → None#

Add new worksheets to the Google Sheet.

Parameters:

  • *args – The names of the worksheets to add with default properties.

  • **kwargs – A map of properties to set for each worksheet.

Examples

Add plain and customized worksheets together (requires a connection):

>>> sheet.add_worksheets(
...     'Notes', Wide=dict(gridProperties=dict(columnCount=40)))