eftoolkit.gsheets

Google Sheets client with automatic batching and dashboard orchestration.

Classes

Spreadsheet

Spreadsheet

Spreadsheet(
    credentials: dict | None = None,
    spreadsheet_name: str = '',
    *,
    max_retries: int = 5,
    base_delay: float = 2.0,
    local_preview: bool = False,
    preview_dir: str | Path = 'gsheets_preview',
)

Google Spreadsheet client for managing worksheets.

Represents the entire spreadsheet document. Use worksheet() to get individual tabs for read/write operations.

Can be used as a context manager to automatically flush all accessed worksheets on exit. In local_preview mode, previews open in browser:

with Spreadsheet(credentials, 'My Sheet') as ss:
    ws1 = ss.worksheet('Tab1')
    ws1.write_dataframe(df1)
    ws2 = ss.worksheet('Tab2')
    ws2.write_dataframe(df2)
# Both ws1 and ws2 are flushed here
# In local_preview mode, browser tabs open automatically

Initialize Spreadsheet client.

Parameters:

Name	Type	Description	Default
credentials	dict | None	Service account credentials dict. Required unless local_preview=True.	None
spreadsheet_name	str	Name of the spreadsheet to open.	''
max_retries	int	Max retry attempts for API errors (429, 5xx).	5
base_delay	float	Base delay for exponential backoff.	2.0
local_preview	bool	If True, skip API calls and render to local HTML.	False
preview_dir	str | Path	Directory for HTML preview files (only used if local_preview=True).	'gsheets_preview'

Source code in eftoolkit/gsheets/sheet.py

def __init__(
    self,
    credentials: dict | None = None,
    spreadsheet_name: str = '',
    *,
    max_retries: int = 5,
    base_delay: float = 2.0,
    local_preview: bool = False,
    preview_dir: str | Path = 'gsheets_preview',
) -> None:
    """Initialize Spreadsheet client.

    Args:
        credentials: Service account credentials dict. Required unless local_preview=True.
        spreadsheet_name: Name of the spreadsheet to open.
        max_retries: Max retry attempts for API errors (429, 5xx).
        base_delay: Base delay for exponential backoff.
        local_preview: If True, skip API calls and render to local HTML.
        preview_dir: Directory for HTML preview files (only used if local_preview=True).
    """
    self._local_preview = local_preview
    self._preview_dir = Path(preview_dir)
    self._spreadsheet_name = spreadsheet_name
    self._max_retries = max_retries
    self._base_delay = base_delay
    self._gspread_spreadsheet = None
    self._worksheets: dict[str, Worksheet] = {}  # Track all accessed worksheets

    if not local_preview:
        if not credentials:
            raise ValueError('credentials required unless local_preview=True')

        gc = service_account_from_dict(credentials)
        self._gspread_spreadsheet = gc.open(spreadsheet_name)

is_local_preview property

is_local_preview: bool

True if running in local preview mode.

worksheet

worksheet(name: str) -> Worksheet

Get worksheet by name.

Parameters:

Name	Type	Description	Default
name	str	Worksheet title (tab name).	required

Returns:

Type	Description
Worksheet	Worksheet instance for the specified tab.

Raises:

Type	Description
WorksheetNotFound	If worksheet doesn't exist (not in local_preview mode).

Source code in eftoolkit/gsheets/sheet.py

def worksheet(self, name: str) -> Worksheet:
    """Get worksheet by name.

    Args:
        name: Worksheet title (tab name).

    Returns:
        Worksheet instance for the specified tab.

    Raises:
        WorksheetNotFound: If worksheet doesn't exist (not in local_preview mode).
    """
    if name in self._worksheets:
        return self._worksheets[name]

    if self._local_preview:
        ws = Worksheet(
            None,
            self,
            local_preview=True,
            preview_output=self._preview_path_for_worksheet(name),
            worksheet_name=name,
        )
    else:
        gspread_ws = self._gspread_spreadsheet.worksheet(name)
        ws = Worksheet(gspread_ws, self)

    self._worksheets[name] = ws
    return ws

get_worksheet_names

get_worksheet_names() -> list[str]

List all worksheet names.

Returns:

Type	Description
list[str]	List of worksheet titles.

Source code in eftoolkit/gsheets/sheet.py

def get_worksheet_names(self) -> list[str]:
    """List all worksheet names.

    Returns:
        List of worksheet titles.
    """
    if self._local_preview:
        return []

    return [ws.title for ws in self._gspread_spreadsheet.worksheets()]

create_worksheet

create_worksheet(
    name: str,
    rows: int = 1000,
    cols: int = 26,
    *,
    replace: bool = False,
) -> Worksheet

Create a new worksheet.

Parameters:

Name	Type	Description	Default
name	str	Title for the new worksheet.	required
rows	int	Number of rows (default 1000).	1000
cols	int	Number of columns (default 26).	26
replace	bool	If True, delete existing worksheet with same name first.	False

Returns:

Type	Description
Worksheet	Worksheet instance for the new tab.

Source code in eftoolkit/gsheets/sheet.py

def create_worksheet(
    self, name: str, rows: int = 1000, cols: int = 26, *, replace: bool = False
) -> Worksheet:
    """Create a new worksheet.

    Args:
        name: Title for the new worksheet.
        rows: Number of rows (default 1000).
        cols: Number of columns (default 26).
        replace: If True, delete existing worksheet with same name first.

    Returns:
        Worksheet instance for the new tab.
    """
    if self._local_preview:
        if name not in self._worksheets:
            self._worksheets[name] = Worksheet(
                None,
                self,
                local_preview=True,
                preview_output=self._preview_path_for_worksheet(name),
                worksheet_name=name,
            )
        return self._worksheets[name]

    if replace:
        self.delete_worksheet(name, ignore_missing=True)
        # Remove from cache if it existed
        self._worksheets.pop(name, None)

    gspread_ws = self._gspread_spreadsheet.add_worksheet(
        title=name, rows=rows, cols=cols
    )
    ws = Worksheet(gspread_ws, self)
    self._worksheets[name] = ws
    return ws

delete_worksheet

delete_worksheet(
    name: str, *, ignore_missing: bool = True
) -> None

Delete worksheet by name.

Parameters:

Name	Type	Description	Default
name	str	Worksheet title to delete.	required
ignore_missing	bool	If True, don't raise if worksheet doesn't exist.	True

Source code in eftoolkit/gsheets/sheet.py

def delete_worksheet(self, name: str, *, ignore_missing: bool = True) -> None:
    """Delete worksheet by name.

    Args:
        name: Worksheet title to delete.
        ignore_missing: If True, don't raise if worksheet doesn't exist.
    """
    if self._local_preview:
        return

    try:
        ws = self._gspread_spreadsheet.worksheet(name)
        self._gspread_spreadsheet.del_worksheet(ws)
    except WorksheetNotFound:
        if not ignore_missing:
            raise

reorder_worksheets

reorder_worksheets(order: list[str]) -> None

Reorder worksheets (tabs) to the specified order.

Worksheets are reordered to match the given list. Worksheets not in the list are moved to the end in their original relative order. Worksheet names in the list that don't exist in the spreadsheet are skipped.

Parameters:

Name	Type	Description	Default
order	list[str]	List of worksheet titles in the desired order.	required

Example

ss.reorder_worksheets(['Dashboard', 'Draft', 'Manual Adds'])

Dashboard first, then Draft, then Manual Adds, then any other tabs

Source code in eftoolkit/gsheets/sheet.py

def reorder_worksheets(self, order: list[str]) -> None:
    """Reorder worksheets (tabs) to the specified order.

    Worksheets are reordered to match the given list. Worksheets not in the
    list are moved to the end in their original relative order. Worksheet
    names in the list that don't exist in the spreadsheet are skipped.

    Args:
        order: List of worksheet titles in the desired order.

    Example:
        ss.reorder_worksheets(['Dashboard', 'Draft', 'Manual Adds'])
        # Dashboard first, then Draft, then Manual Adds, then any other tabs
    """
    if self._local_preview:
        return

    all_worksheets = self._gspread_spreadsheet.worksheets()
    worksheets_by_title = {ws.title: ws for ws in all_worksheets}

    # Build ordered list: specified worksheets first (if they exist)
    ordered = []
    for title in order:
        if title in worksheets_by_title:
            ordered.append(worksheets_by_title[title])

    # Append remaining worksheets in their original order
    ordered_titles = {ws.title for ws in ordered}
    for ws in all_worksheets:
        if ws.title not in ordered_titles:
            ordered.append(ws)

    self._execute_with_retry(
        lambda: self._gspread_spreadsheet.reorder_worksheets(ordered),
        'reorder_worksheets',
    )

Worksheet

Worksheet

Worksheet(
    gspread_worksheet: Any,
    spreadsheet: Spreadsheet,
    *,
    local_preview: bool = False,
    preview_output: Path | None = None,
    worksheet_name: str | None = None,
)

A single worksheet (tab) within a Google Spreadsheet.

Handles all read/write/format operations for one tab. Operations are queued and flushed via flush() or context manager exit.

Initialize worksheet.

Parameters:

Name	Type	Description	Default
gspread_worksheet	Any	The underlying gspread Worksheet object.	required
spreadsheet	Spreadsheet	Parent Spreadsheet instance.	required
local_preview	bool	If True, skip API calls and render to local HTML.	False
preview_output	Path | None	Path for HTML preview file.	None
worksheet_name	str | None	Worksheet name (used in local_preview mode).	None

Source code in eftoolkit/gsheets/sheet.py

def __init__(
    self,
    gspread_worksheet: Any,
    spreadsheet: 'Spreadsheet',
    *,
    local_preview: bool = False,
    preview_output: Path | None = None,
    worksheet_name: str | None = None,
) -> None:
    """Initialize worksheet.

    Args:
        gspread_worksheet: The underlying gspread Worksheet object.
        spreadsheet: Parent Spreadsheet instance.
        local_preview: If True, skip API calls and render to local HTML.
        preview_output: Path for HTML preview file.
        worksheet_name: Worksheet name (used in local_preview mode).
    """
    self._ws = gspread_worksheet
    self._spreadsheet = spreadsheet
    self._local_preview = local_preview
    self._worksheet_name = worksheet_name
    self._preview_output = preview_output or Path('sheet_preview.html')
    self._value_updates: list[dict] = []
    self._batch_requests: list[dict] = []
    self._preview_history: list[dict] = []  # Accumulates all writes for preview
    self._preview_column_widths: dict[int, int] = {}  # col_index -> width in pixels
    self._preview_notes: dict[tuple[int, int], str] = {}  # (row, col) -> note text

title property

title: str

Worksheet title (tab name).

is_local_preview property

is_local_preview: bool

True if running in local preview mode.

read

read() -> DataFrame

Read worksheet to DataFrame (first row = headers).

Source code in eftoolkit/gsheets/sheet.py

def read(self) -> pd.DataFrame:
    """Read worksheet to DataFrame (first row = headers)."""
    if self._local_preview:
        raise NotImplementedError('read not available in local preview mode')

    all_values = self._ws.get_all_values()
    if not all_values:
        return pd.DataFrame()
    return pd.DataFrame(data=all_values[1:], columns=all_values[0])

write_dataframe

write_dataframe(
    df: DataFrame,
    location: str = 'A1',
    *,
    include_header: bool = True,
    format_dict: dict[str, Any] | None = None,
) -> None

Queue DataFrame write with optional formatting.

Parameters:

Name	Type	Description	Default
df	DataFrame	DataFrame to write.	required
location	str	Cell location to start writing (e.g., 'A1').	'A1'
include_header	bool	If True, include column names as first row.	True
format_dict	dict[str, Any] | None	Optional dict mapping range names to format dicts.	None

Source code in eftoolkit/gsheets/sheet.py

def write_dataframe(
    self,
    df: pd.DataFrame,
    location: str = 'A1',
    *,
    include_header: bool = True,
    format_dict: dict[str, Any] | None = None,
) -> None:
    """Queue DataFrame write with optional formatting.

    Args:
        df: DataFrame to write.
        location: Cell location to start writing (e.g., 'A1').
        include_header: If True, include column names as first row.
        format_dict: Optional dict mapping range names to format dicts.
    """
    values = df.values.tolist()
    if include_header:
        values = [df.columns.tolist()] + values

    self._value_updates.append(
        {
            'range': f'{self.title}!{location}',
            'values': values,
        }
    )

    if format_dict:
        for range_name, fmt in format_dict.items():
            self._batch_requests.append(
                {
                    'type': 'format',
                    'range': range_name,
                    'format': fmt,
                }
            )

write_values

write_values(
    range_name: str, values: list[list[Any]]
) -> None

Queue cell values update.

Parameters:

Name	Type	Description	Default
range_name	str	A1 notation range (e.g., 'A1:B2').	required
values	list[list[Any]]	2D list of values to write.	required

Source code in eftoolkit/gsheets/sheet.py

def write_values(
    self,
    range_name: str,
    values: list[list[Any]],
) -> None:
    """Queue cell values update.

    Args:
        range_name: A1 notation range (e.g., 'A1:B2').
        values: 2D list of values to write.
    """
    # Prepend worksheet name if not already included
    if '!' not in range_name:
        range_name = f'{self.title}!{range_name}'
    self._value_updates.append({'range': range_name, 'values': values})

format_range

format_range(
    range_name: str, format_dict: dict[str, Any]
) -> None

Queue cell formatting.

Parameters:

Name	Type	Description	Default
range_name	str	A1 notation range.	required
format_dict	dict[str, Any]	Format specification dict.	required

Source code in eftoolkit/gsheets/sheet.py

def format_range(
    self,
    range_name: str,
    format_dict: dict[str, Any],
) -> None:
    """Queue cell formatting.

    Args:
        range_name: A1 notation range.
        format_dict: Format specification dict.
    """
    self._batch_requests.append(
        {
            'type': 'format',
            'range': range_name,
            'format': format_dict,
        }
    )

set_borders

set_borders(
    range_name: str, borders: dict[str, Any]
) -> None

Queue border formatting.

Parameters:

Name	Type	Description	Default
range_name	str	A1 notation range.	required
borders	dict[str, Any]	Border specification dict.	required

Source code in eftoolkit/gsheets/sheet.py

def set_borders(
    self,
    range_name: str,
    borders: dict[str, Any],
) -> None:
    """Queue border formatting.

    Args:
        range_name: A1 notation range.
        borders: Border specification dict.
    """
    self._batch_requests.append(
        {
            'type': 'border',
            'range': range_name,
            'borders': borders,
        }
    )

set_column_width

set_column_width(column: str | int, width: int) -> None

Queue column width update.

Parameters:

Name	Type	Description	Default
column	str | int	Column letter or 1-based index.	required
width	int	Width in pixels.	required

Source code in eftoolkit/gsheets/sheet.py

def set_column_width(
    self,
    column: str | int,
    width: int,
) -> None:
    """Queue column width update.

    Args:
        column: Column letter or 1-based index.
        width: Width in pixels.
    """
    self._batch_requests.append(
        {
            'type': 'column_width',
            'column': column,
            'width': width,
        }
    )

auto_resize_columns

auto_resize_columns(start_col: int, end_col: int) -> None

Queue column auto-resize.

Parameters:

Name	Type	Description	Default
start_col	int	1-based start column index.	required
end_col	int	1-based end column index.	required

Source code in eftoolkit/gsheets/sheet.py

def auto_resize_columns(
    self,
    start_col: int,
    end_col: int,
) -> None:
    """Queue column auto-resize.

    Args:
        start_col: 1-based start column index.
        end_col: 1-based end column index.
    """
    self._batch_requests.append(
        {
            'type': 'auto_resize',
            'start_col': start_col,
            'end_col': end_col,
        }
    )

set_notes

set_notes(notes: dict[str, str]) -> None

Queue cell notes.

Parameters:

Name	Type	Description	Default
notes	dict[str, str]	Dict mapping cell references to note text.	required

Source code in eftoolkit/gsheets/sheet.py

def set_notes(
    self,
    notes: dict[str, str],
) -> None:
    """Queue cell notes.

    Args:
        notes: Dict mapping cell references to note text.
    """
    self._batch_requests.append(
        {
            'type': 'notes',
            'notes': notes,
        }
    )

merge_cells

merge_cells(
    range_name: str, merge_type: str = 'MERGE_ALL'
) -> None

Queue cell merge.

Parameters:

Name	Type	Description	Default
range_name	str	A1 notation range to merge (e.g., 'A1:C1').	required
merge_type	str	One of 'MERGE_ALL', 'MERGE_COLUMNS', 'MERGE_ROWS'.	'MERGE_ALL'

Source code in eftoolkit/gsheets/sheet.py

def merge_cells(
    self,
    range_name: str,
    merge_type: str = 'MERGE_ALL',
) -> None:
    """Queue cell merge.

    Args:
        range_name: A1 notation range to merge (e.g., 'A1:C1').
        merge_type: One of 'MERGE_ALL', 'MERGE_COLUMNS', 'MERGE_ROWS'.
    """
    self._batch_requests.append(
        {
            'type': 'merge',
            'range': range_name,
            'merge_type': merge_type,
        }
    )

unmerge_cells

unmerge_cells(range_name: str) -> None

Queue cell unmerge.

Parameters:

Name	Type	Description	Default
range_name	str	A1 notation range to unmerge.	required

Source code in eftoolkit/gsheets/sheet.py

def unmerge_cells(
    self,
    range_name: str,
) -> None:
    """Queue cell unmerge.

    Args:
        range_name: A1 notation range to unmerge.
    """
    self._batch_requests.append(
        {
            'type': 'unmerge',
            'range': range_name,
        }
    )

sort_range

sort_range(
    range_name: str, sort_specs: list[dict[str, Any]]
) -> None

Queue range sort.

Parameters:

Name	Type	Description	Default
range_name	str	A1 notation range to sort.	required
sort_specs	list[dict[str, Any]]	List of sort specifications. Each spec should have: - 'column': 0-based column index within the range - 'ascending': True for ascending, False for descending (default True)	required

Example

ws.sort_range('A1:C10', [{'column': 0, 'ascending': True}])

Source code in eftoolkit/gsheets/sheet.py

def sort_range(
    self,
    range_name: str,
    sort_specs: list[dict[str, Any]],
) -> None:
    """Queue range sort.

    Args:
        range_name: A1 notation range to sort.
        sort_specs: List of sort specifications. Each spec should have:
            - 'column': 0-based column index within the range
            - 'ascending': True for ascending, False for descending (default True)

    Example:
        ws.sort_range('A1:C10', [{'column': 0, 'ascending': True}])
    """
    self._batch_requests.append(
        {
            'type': 'sort',
            'range': range_name,
            'sort_specs': sort_specs,
        }
    )

set_data_validation

set_data_validation(
    range_name: str, rule: dict[str, Any]
) -> None

Queue data validation rule.

Parameters:

Name	Type	Description	Default
range_name	str	A1 notation range for validation.	required
rule	dict[str, Any]	Validation rule dict. Common keys: - 'type': 'ONE_OF_LIST', 'ONE_OF_RANGE', 'NUMBER_BETWEEN', etc. - 'values': List of allowed values (for ONE_OF_LIST) - 'showDropdown': True to show dropdown (default True) - 'strict': True to reject invalid input (default True)	required

Example

ws.set_data_validation('A1:A10', { 'type': 'ONE_OF_LIST', 'values': ['Yes', 'No', 'Maybe'], 'showDropdown': True, })

Source code in eftoolkit/gsheets/sheet.py

def set_data_validation(
    self,
    range_name: str,
    rule: dict[str, Any],
) -> None:
    """Queue data validation rule.

    Args:
        range_name: A1 notation range for validation.
        rule: Validation rule dict. Common keys:
            - 'type': 'ONE_OF_LIST', 'ONE_OF_RANGE', 'NUMBER_BETWEEN', etc.
            - 'values': List of allowed values (for ONE_OF_LIST)
            - 'showDropdown': True to show dropdown (default True)
            - 'strict': True to reject invalid input (default True)

    Example:
        ws.set_data_validation('A1:A10', {
            'type': 'ONE_OF_LIST',
            'values': ['Yes', 'No', 'Maybe'],
            'showDropdown': True,
        })
    """
    self._batch_requests.append(
        {
            'type': 'data_validation',
            'range': range_name,
            'rule': rule,
        }
    )

clear_data_validation

clear_data_validation(range_name: str) -> None

Queue removal of data validation rules.

Parameters:

Name	Type	Description	Default
range_name	str	A1 notation range to clear validation from.	required

Source code in eftoolkit/gsheets/sheet.py

def clear_data_validation(
    self,
    range_name: str,
) -> None:
    """Queue removal of data validation rules.

    Args:
        range_name: A1 notation range to clear validation from.
    """
    self._batch_requests.append(
        {
            'type': 'clear_data_validation',
            'range': range_name,
        }
    )

add_conditional_format

add_conditional_format(
    range_name: str, rule: dict[str, Any]
) -> None

Queue conditional formatting rule.

Parameters:

Name	Type	Description	Default
range_name	str	A1 notation range for conditional format.	required
rule	dict[str, Any]	Conditional format rule dict. Should contain: - 'type': 'CUSTOM_FORMULA', 'NUMBER_GREATER', 'TEXT_CONTAINS', etc. - 'values': Condition values (e.g., formula string) - 'format': Cell format to apply when condition is met	required

Example

ws.add_conditional_format('A1:A10', { 'type': 'CUSTOM_FORMULA', 'values': ['=A1>100'], 'format': {'backgroundColor': {'red': 1, 'green': 0, 'blue': 0}}, })

Source code in eftoolkit/gsheets/sheet.py

def add_conditional_format(
    self,
    range_name: str,
    rule: dict[str, Any],
) -> None:
    """Queue conditional formatting rule.

    Args:
        range_name: A1 notation range for conditional format.
        rule: Conditional format rule dict. Should contain:
            - 'type': 'CUSTOM_FORMULA', 'NUMBER_GREATER', 'TEXT_CONTAINS', etc.
            - 'values': Condition values (e.g., formula string)
            - 'format': Cell format to apply when condition is met

    Example:
        ws.add_conditional_format('A1:A10', {
            'type': 'CUSTOM_FORMULA',
            'values': ['=A1>100'],
            'format': {'backgroundColor': {'red': 1, 'green': 0, 'blue': 0}},
        })
    """
    self._batch_requests.append(
        {
            'type': 'conditional_format',
            'range': range_name,
            'rule': rule,
        }
    )

insert_rows

insert_rows(start_row: int, num_rows: int = 1) -> None

Queue row insertion.

Parameters:

Name	Type	Description	Default
start_row	int	1-based row index where new rows will be inserted.	required
num_rows	int	Number of rows to insert (default 1).	1

Source code in eftoolkit/gsheets/sheet.py

def insert_rows(
    self,
    start_row: int,
    num_rows: int = 1,
) -> None:
    """Queue row insertion.

    Args:
        start_row: 1-based row index where new rows will be inserted.
        num_rows: Number of rows to insert (default 1).
    """
    self._batch_requests.append(
        {
            'type': 'insert_rows',
            'start_row': start_row,
            'num_rows': num_rows,
        }
    )

delete_rows

delete_rows(start_row: int, num_rows: int = 1) -> None

Queue row deletion.

Parameters:

Name	Type	Description	Default
start_row	int	1-based row index of first row to delete.	required
num_rows	int	Number of rows to delete (default 1).	1

Source code in eftoolkit/gsheets/sheet.py

def delete_rows(
    self,
    start_row: int,
    num_rows: int = 1,
) -> None:
    """Queue row deletion.

    Args:
        start_row: 1-based row index of first row to delete.
        num_rows: Number of rows to delete (default 1).
    """
    self._batch_requests.append(
        {
            'type': 'delete_rows',
            'start_row': start_row,
            'num_rows': num_rows,
        }
    )

insert_columns

insert_columns(start_col: int, num_cols: int = 1) -> None

Queue column insertion.

Parameters:

Name	Type	Description	Default
start_col	int	1-based column index where new columns will be inserted.	required
num_cols	int	Number of columns to insert (default 1).	1

Source code in eftoolkit/gsheets/sheet.py

def insert_columns(
    self,
    start_col: int,
    num_cols: int = 1,
) -> None:
    """Queue column insertion.

    Args:
        start_col: 1-based column index where new columns will be inserted.
        num_cols: Number of columns to insert (default 1).
    """
    self._batch_requests.append(
        {
            'type': 'insert_columns',
            'start_col': start_col,
            'num_cols': num_cols,
        }
    )

delete_columns

delete_columns(start_col: int, num_cols: int = 1) -> None

Queue column deletion.

Parameters:

Name	Type	Description	Default
start_col	int	1-based column index of first column to delete.	required
num_cols	int	Number of columns to delete (default 1).	1

Source code in eftoolkit/gsheets/sheet.py

def delete_columns(
    self,
    start_col: int,
    num_cols: int = 1,
) -> None:
    """Queue column deletion.

    Args:
        start_col: 1-based column index of first column to delete.
        num_cols: Number of columns to delete (default 1).
    """
    self._batch_requests.append(
        {
            'type': 'delete_columns',
            'start_col': start_col,
            'num_cols': num_cols,
        }
    )

freeze_rows

freeze_rows(num_rows: int) -> None

Queue freezing rows at the top of the worksheet.

Parameters:

Name	Type	Description	Default
num_rows	int	Number of rows to freeze (0 to unfreeze).	required

Source code in eftoolkit/gsheets/sheet.py

def freeze_rows(
    self,
    num_rows: int,
) -> None:
    """Queue freezing rows at the top of the worksheet.

    Args:
        num_rows: Number of rows to freeze (0 to unfreeze).
    """
    self._batch_requests.append(
        {
            'type': 'freeze_rows',
            'num_rows': num_rows,
        }
    )

freeze_columns

freeze_columns(num_cols: int) -> None

Queue freezing columns at the left of the worksheet.

Parameters:

Name	Type	Description	Default
num_cols	int	Number of columns to freeze (0 to unfreeze).	required

Source code in eftoolkit/gsheets/sheet.py

def freeze_columns(
    self,
    num_cols: int,
) -> None:
    """Queue freezing columns at the left of the worksheet.

    Args:
        num_cols: Number of columns to freeze (0 to unfreeze).
    """
    self._batch_requests.append(
        {
            'type': 'freeze_columns',
            'num_cols': num_cols,
        }
    )

add_raw_request

add_raw_request(request: dict[str, Any]) -> None

Queue a raw batchUpdate request.

Use this for operations not covered by other methods. The request will be passed directly to the Google Sheets batchUpdate API.

Parameters:

Name	Type	Description	Default
request	dict[str, Any]	A single batchUpdate request dict. See Google Sheets API documentation for available request types: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request	required

Example

Add a named range

ws.add_raw_request({ 'addNamedRange': { 'namedRange': { 'name': 'MyRange', 'range': { 'sheetId': 0, 'startRowIndex': 0, 'endRowIndex': 10, 'startColumnIndex': 0, 'endColumnIndex': 5, } } } })

Source code in eftoolkit/gsheets/sheet.py

def add_raw_request(
    self,
    request: dict[str, Any],
) -> None:
    """Queue a raw batchUpdate request.

    Use this for operations not covered by other methods. The request
    will be passed directly to the Google Sheets batchUpdate API.

    Args:
        request: A single batchUpdate request dict. See Google Sheets API
            documentation for available request types:
            https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request

    Example:
        # Add a named range
        ws.add_raw_request({
            'addNamedRange': {
                'namedRange': {
                    'name': 'MyRange',
                    'range': {
                        'sheetId': 0,
                        'startRowIndex': 0,
                        'endRowIndex': 10,
                        'startColumnIndex': 0,
                        'endColumnIndex': 5,
                    }
                }
            }
        })
    """
    self._batch_requests.append(
        {
            'type': 'raw',
            'request': request,
        }
    )

flush

flush() -> None

Execute all queued operations.

In normal mode: sends batched API calls to Google Sheets. In local_preview mode: renders HTML.

Source code in eftoolkit/gsheets/sheet.py

def flush(self) -> None:
    """Execute all queued operations.

    In normal mode: sends batched API calls to Google Sheets.
    In local_preview mode: renders HTML.
    """
    if self._local_preview:
        self._flush_to_preview()
    else:
        self._flush_to_api()

    self._value_updates.clear()
    self._batch_requests.clear()

open_preview

open_preview() -> None

Open the preview HTML in browser (local_preview mode only).

Source code in eftoolkit/gsheets/sheet.py

def open_preview(self) -> None:
    """Open the preview HTML in browser (local_preview mode only)."""
    if not self._local_preview:
        raise RuntimeError('open_preview only available in local_preview mode')

    webbrowser.open(f'file://{self._preview_output.absolute()}')

DashboardRunner

DashboardRunner

DashboardRunner(
    config: dict[str, Any],
    credentials: dict[str, Any],
    worksheets: list[WorksheetDefinition] | None = None,
    *,
    local_preview: bool = False,
)

Orchestrates the 6-phase sheet update workflow.

Executes worksheet generation, writing, and formatting in a structured sequence with logging and error handling.

Attributes:

Name	Type	Description
config		Configuration dictionary passed to worksheet generate() methods.
credentials		Google service account credentials dictionary.
context	dict[str, Any]	Shared state dictionary populated during generation and available to subsequent worksheets.
results	dict[str, list[WorksheetAsset]]	Dictionary mapping worksheet names to their generated assets.

Example

runner = DashboardRunner( ... config={'db': conn, 'sheet_name': 'Q1 Report'}, ... credentials=credentials, ... worksheets=[RevenueWorksheet(), ExpensesWorksheet()], ... ) runner.run()

Initialize DashboardRunner.

Parameters:

Name	Type	Description	Default
config	dict[str, Any]	Configuration dictionary. Must include 'sheet_name' key. Passed to each worksheet's generate() method.	required
credentials	dict[str, Any]	Google service account credentials dictionary.	required
worksheets	list[WorksheetDefinition] | None	List of worksheet definitions to process. If None, uses worksheets from WorksheetRegistry.	None
local_preview	bool	If True, render to local HTML instead of Google Sheets.	False

Raises:

Type	Description
ValueError	If 'sheet_name' not in config.
ValueError	If no worksheets provided and registry is empty.

Source code in eftoolkit/gsheets/runner.py

def __init__(
    self,
    config: dict[str, Any],
    credentials: dict[str, Any],
    worksheets: list[WorksheetDefinition] | None = None,
    *,
    local_preview: bool = False,
) -> None:
    """Initialize DashboardRunner.

    Args:
        config: Configuration dictionary. Must include 'sheet_name' key.
            Passed to each worksheet's generate() method.
        credentials: Google service account credentials dictionary.
        worksheets: List of worksheet definitions to process. If None,
            uses worksheets from WorksheetRegistry.
        local_preview: If True, render to local HTML instead of Google Sheets.

    Raises:
        ValueError: If 'sheet_name' not in config.
        ValueError: If no worksheets provided and registry is empty.
    """
    if 'sheet_name' not in config:
        raise ValueError("config must include 'sheet_name' key")

    self.config = config
    self.credentials = credentials
    self.local_preview = local_preview

    if worksheets is not None:
        self.worksheets = worksheets
    else:
        self.worksheets = WorksheetRegistry.get_ordered_worksheets()

    if not self.worksheets:
        raise ValueError(
            'No worksheets provided. Pass worksheets parameter or register with WorksheetRegistry.'
        )

    self.context: dict[str, Any] = {}
    self.results: dict[str, list[WorksheetAsset]] = {}

run

run() -> None

Execute the full 6-phase workflow.

Phases

1. Validate structure
2. Generate data
3. Write data
4. Apply formatting
5. Run hooks
6. Log summary

Raises:

Type	Description
Exception	Re-raises any exception from individual phases.

Source code in eftoolkit/gsheets/runner.py

def run(self) -> None:
    """Execute the full 6-phase workflow.

    Phases:
        1. Validate structure
        2. Generate data
        3. Write data
        4. Apply formatting
        5. Run hooks
        6. Log summary

    Raises:
        Exception: Re-raises any exception from individual phases.
    """
    logger.info('Starting dashboard run for: %s', self.config['sheet_name'])

    self._phase_1_validate_structure()
    self._phase_2_generate_data()
    self._phase_3_write_data()
    self._phase_4_apply_formatting()
    self._phase_5_run_hooks()
    self._phase_6_log_summary()

    logger.info('Dashboard run complete')

WorksheetRegistry

WorksheetRegistry

Registry of worksheet definitions.

A thread-safe registry that maintains worksheet definitions in registration order. Use reorder() to change the order after registration.

This class uses class-level state, so all operations are performed via classmethods. Use clear() in tests to reset state between test cases.

Example

WorksheetRegistry.register([Summary(), Revenue(), Expenses()]) worksheets = WorksheetRegistry.get_ordered_worksheets() len(worksheets) 3

Or register one at a time:

WorksheetRegistry.register(SummaryWorksheet())

register classmethod

register(
    worksheets: WorksheetDefinition
    | list[WorksheetDefinition],
) -> None

Register one or more worksheet definitions.

Parameters:

Name	Type	Description	Default
worksheets	WorksheetDefinition | list[WorksheetDefinition]	A single worksheet definition or a list of definitions. Must implement the WorksheetDefinition protocol. Worksheets are appended in the order provided.	required

Raises:

Type	Description
ValueError	If a worksheet with the same name is already registered.

Example

WorksheetRegistry.register(SummaryWorksheet()) WorksheetRegistry.register([Revenue(), Expenses()])

Source code in eftoolkit/gsheets/registry.py

@classmethod
def register(
    cls,
    worksheets: WorksheetDefinition | list[WorksheetDefinition],
) -> None:
    """Register one or more worksheet definitions.

    Args:
        worksheets: A single worksheet definition or a list of definitions.
            Must implement the WorksheetDefinition protocol. Worksheets are
            appended in the order provided.

    Raises:
        ValueError: If a worksheet with the same name is already registered.

    Example:
        >>> WorksheetRegistry.register(SummaryWorksheet())
        >>> WorksheetRegistry.register([Revenue(), Expenses()])
    """
    worksheet_list = worksheets if isinstance(worksheets, list) else [worksheets]
    with cls._lock:
        for worksheet in worksheet_list:
            if worksheet.name in cls._worksheets:
                raise ValueError(
                    f"Worksheet '{worksheet.name}' is already registered"
                )

            cls._worksheets[worksheet.name] = worksheet
            cls._order.append(worksheet.name)

get_ordered_worksheets classmethod

get_ordered_worksheets() -> list[WorksheetDefinition]

Return worksheets in registration order.

Returns:

Type	Description
list[WorksheetDefinition]	List of worksheet definitions in the order they were registered,
list[WorksheetDefinition]	respecting any reordering done via reorder().

Example

worksheets = WorksheetRegistry.get_ordered_worksheets() for ws in worksheets: ... print(ws.name)

Source code in eftoolkit/gsheets/registry.py

@classmethod
def get_ordered_worksheets(cls) -> list[WorksheetDefinition]:
    """Return worksheets in registration order.

    Returns:
        List of worksheet definitions in the order they were registered,
        respecting any reordering done via `reorder()`.

    Example:
        >>> worksheets = WorksheetRegistry.get_ordered_worksheets()
        >>> for ws in worksheets:
        ...     print(ws.name)
    """
    with cls._lock:
        return [cls._worksheets[name] for name in cls._order]

get_worksheet classmethod

get_worksheet(name: str) -> WorksheetDefinition | None

Get a worksheet by name.

Parameters:

Name	Type	Description	Default
name	str	The name of the worksheet to retrieve.	required

Returns:

Type	Description
WorksheetDefinition | None	The worksheet definition if found, None otherwise.

Example

revenue = WorksheetRegistry.get_worksheet('Revenue') if revenue: ... assets = revenue.generate(config, context)

Source code in eftoolkit/gsheets/registry.py

@classmethod
def get_worksheet(cls, name: str) -> WorksheetDefinition | None:
    """Get a worksheet by name.

    Args:
        name: The name of the worksheet to retrieve.

    Returns:
        The worksheet definition if found, None otherwise.

    Example:
        >>> revenue = WorksheetRegistry.get_worksheet('Revenue')
        >>> if revenue:
        ...     assets = revenue.generate(config, context)
    """
    with cls._lock:
        return cls._worksheets.get(name)

reorder classmethod

reorder(names: list[str]) -> None

Reorder worksheets to match the specified order.

Sets the worksheet order to match the provided list of names. All registered worksheet names must be included exactly once.

Parameters:

Name	Type	Description	Default
names	list[str]	List of worksheet names in the desired order.	required

Raises:

Type	Description
ValueError	If names don't match registered worksheets exactly (missing names, extra names, or duplicates).

Example

WorksheetRegistry.register([Summary(), Revenue(), Expenses()]) WorksheetRegistry.reorder(["Expenses", "Summary", "Revenue"]) WorksheetRegistry.get_ordered_worksheets()[0].name "Expenses"

Source code in eftoolkit/gsheets/registry.py

@classmethod
def reorder(cls, names: list[str]) -> None:
    """Reorder worksheets to match the specified order.

    Sets the worksheet order to match the provided list of names.
    All registered worksheet names must be included exactly once.

    Args:
        names: List of worksheet names in the desired order.

    Raises:
        ValueError: If names don't match registered worksheets exactly
            (missing names, extra names, or duplicates).

    Example:
        >>> WorksheetRegistry.register([Summary(), Revenue(), Expenses()])
        >>> WorksheetRegistry.reorder(["Expenses", "Summary", "Revenue"])
        >>> WorksheetRegistry.get_ordered_worksheets()[0].name
        "Expenses"
    """
    with cls._lock:
        registered = set(cls._worksheets.keys())
        provided = set(names)

        if len(names) != len(provided):
            raise ValueError('Duplicate names in reorder list')

        missing = registered - provided
        if missing:
            raise ValueError(f'Missing worksheets in reorder: {missing}')

        extra = provided - registered
        if extra:
            raise ValueError(f'Unknown worksheets in reorder: {extra}')

        cls._order = list(names)

clear classmethod

clear() -> None

Clear all registered worksheets.

This method is primarily intended for use in tests to reset the registry state between test cases.

Example

WorksheetRegistry.clear() len(WorksheetRegistry.get_ordered_worksheets()) 0

Source code in eftoolkit/gsheets/registry.py

@classmethod
def clear(cls) -> None:
    """Clear all registered worksheets.

    This method is primarily intended for use in tests to reset
    the registry state between test cases.

    Example:
        >>> WorksheetRegistry.clear()
        >>> len(WorksheetRegistry.get_ordered_worksheets())
        0
    """
    with cls._lock:
        cls._worksheets = {}
        cls._order = []

Types

CellLocation

CellLocation dataclass

CellLocation(cell: str)

Where a DataFrame should be written within a worksheet.

The worksheet name comes from the WorksheetDefinition.name property, so CellLocation only needs the cell address within that worksheet.

Attributes:

Name	Type	Description
cell	str	The cell address where the DataFrame starts (e.g., 'B4', 'A1').

Example

location = CellLocation(cell='B4') location.cell 'B4'

WorksheetAsset

WorksheetAsset dataclass

WorksheetAsset(
    df: DataFrame,
    location: CellLocation,
    format_config_path: Path | None = None,
    format_dict: dict[str, Any] | None = None,
    post_write_hooks: list[Callable] = list(),
)

An asset to be written to a worksheet.

Contains a DataFrame to write, its target location within the worksheet, optional formatting configuration, and post-write hooks.

A WorksheetDefinition.generate() returns a list of WorksheetAssets, allowing multiple DataFrames to be written to different locations on the same worksheet.

Attributes:

Name	Type	Description
df	DataFrame	The DataFrame to write to the sheet.
location	CellLocation	Where to write the DataFrame within the worksheet.
format_config_path	Path | None	Path to a JSON format configuration file.
format_dict	dict[str, Any] | None	Inline format configuration dictionary.
post_write_hooks	list[Callable]	Callables to run after writing (e.g., conditional formatting).

Example

asset = WorksheetAsset( ... df=my_dataframe, ... location=CellLocation(cell='B4'), ... format_config_path=Path('formats/summary.json'), ... )

WorksheetDefinition

WorksheetDefinition

Bases: Protocol

Protocol for defining a worksheet in a spreadsheet.

Each WorksheetDefinition represents one worksheet in the spreadsheet. The generate() method returns a list of WorksheetAssets, allowing multiple DataFrames to be written to different locations on the same worksheet.

A worksheet definition specifies: - Worksheet name (becomes the tab name in the spreadsheet) - Data generation logic (can produce multiple DataFrames) - Format configuration for each DataFrame - Post-write hooks for conditional formatting, notes, merges, etc.

Example implementation

class RevenueWorksheet: @property def name(self) -> str: return 'Revenue' # Worksheet name

def generate(self, config: dict, context: dict) -> list[WorksheetAsset]:
    summary_df = query_summary(config['db'])
    breakdown_df = query_breakdown(config['db'])
    return [
        WorksheetAsset(
            df=summary_df,
            location=CellLocation(cell='B2'),
            format_dict={'header_color': '#4a86e8'},
        ),
        WorksheetAsset(
            df=breakdown_df,
            location=CellLocation(cell='B10'),
            format_config_path=Path('formats/breakdown.json'),
        ),
    ]

def get_format_overrides(self, context: dict) -> dict:
    return {'currency_format': '$#,##0.00'}

name property

name: str

The worksheet name in the spreadsheet.

generate

generate(
    config: dict, context: dict
) -> list[WorksheetAsset]

Generate the worksheet's data and return a list of WorksheetAssets.

Parameters:

Name	Type	Description	Default
config	dict	Configuration dictionary (e.g., database connections, settings).	required
context	dict	Runtime context (e.g., date ranges, user preferences).	required

Returns:

Type	Description
list[WorksheetAsset]	List of WorksheetAssets, each containing a DataFrame and its location
list[WorksheetAsset]	within this worksheet. Multiple assets = multiple DataFrames on one worksheet.

Source code in eftoolkit/gsheets/types.py

def generate(self, config: dict, context: dict) -> list['WorksheetAsset']:
    """Generate the worksheet's data and return a list of WorksheetAssets.

    Args:
        config: Configuration dictionary (e.g., database connections, settings).
        context: Runtime context (e.g., date ranges, user preferences).

    Returns:
        List of WorksheetAssets, each containing a DataFrame and its location
        within this worksheet. Multiple assets = multiple DataFrames on one worksheet.
    """
    ...

get_format_overrides

get_format_overrides(context: dict) -> dict

Return format overrides for this worksheet.

Parameters:

Name	Type	Description	Default
context	dict	Runtime context for dynamic formatting decisions.	required

Returns:

Type	Description
dict	Dictionary of format overrides to apply on top of base config.

Source code in eftoolkit/gsheets/types.py

def get_format_overrides(self, context: dict) -> dict:
    """Return format overrides for this worksheet.

    Args:
        context: Runtime context for dynamic formatting decisions.

    Returns:
        Dictionary of format overrides to apply on top of base config.
    """
    ...