Google Sheets Sdk

Google Spreadsheet SDK module for synchronous operations.

This module provides utilities for interacting with Google Sheets for experiment tracking functionality.

append_rows(worksheet, values, value_input_option=ValueInputOption.raw, **kwargs)

Append rows to a worksheet.

Parameters:

Name	Type	Description	Default
worksheet	Worksheet	The worksheet to append rows to.	required
values	list[list[Any]]	The values to append.	required
value_input_option	ValueInputOption	How the input data should be interpreted. - ValueInputOption.raw: Values are stored as-is - ValueInputOption.user_entered: Values are parsed as if typed by user (formulas work)	raw
**kwargs	Any	Additional keyword arguments to pass to the append_rows method.	{}

Returns:

Type	Description
dict[str, Any]	dict[str, Any]: The response from the append_rows method.

Raises:

Type	Description
Exception	If there's an error appending rows.

apply_cell_formatting(worksheet, cell_range, format_dict)

Apply visual formatting to cells in a worksheet.

This function applies visual styling such as bold text, colors, borders, etc. to a range of cells using Google Sheets API formatting.

Parameters:

Name	Type	Description	Default
worksheet	Worksheet	The worksheet to format.	required
cell_range	str	The range of cells to format (e.g., "A1:B2" or "1:1" for entire row).	required
format_dict	dict[str, Any]	The format to apply (e.g., {"textFormat": {"bold": True}}).	required

Returns:

Type	Description
dict[str, Any]	dict[str, Any]: The response from the format method.

Raises:

Type	Description
Exception	If there's an error formatting cells.

apply_formula(cell, row_number, data)

Apply formula to a cell value.

This method supports placeholder replacement in formulas: - {row_number}: Current row number - {column_[name]}: Column letter for the specified column name

Parameters:

Name	Type	Description	Default
cell	str	The cell value (potentially containing formulas).	required
row_number	int	The current row number.	required
data	dict[str, Any]	The data dictionary with all columns.	required

Returns:

Name	Type	Description
str	str	The cell value with placeholders replaced.

Examples:

cell = "=SUM(A{row_number}:B{row_number})" row_number = 5

Returns: "=SUM(A5:B5)"

cell = "=AVERAGE({column_score}:{column_score})" data has column "score" at index 2 (column C)

Returns: "=AVERAGE(C:C)"

build_sheets_cell_value(value, row_number, data, max_cell_length=50000)

Prepare and convert a value for insertion into a Google Sheets cell.

This function transforms Python values into appropriate formats for Google Sheets, handling type conversion, formula processing, and content truncation.

Handles: - Formulas (strings starting with '=') - Large strings (truncated to max_cell_length) - Numeric values (converted to float/int) - Regular strings

Parameters:

Name	Type	Description	Default
value	Any	The value to prepare.	required
row_number	int	The current row number (for formula placeholders).	required
data	dict[str, Any]	The full data dictionary (for formula placeholders).	required
max_cell_length	int	Maximum length for cell values. Defaults to 50000.	50000

Returns:

Type	Description
str | float | int	str | float | int: The prepared cell value ready for insertion.

column_to_letter(columns=None, column_name=None, index=None)

Convert a column name or index to a spreadsheet column letter (A, B, ..., Z, AA, AB, ...).

Either provide (columns + column_name) OR just index.

Parameters:

Name	Type	Description	Default
columns	list[str] | None	List of column names to search in.	None
column_name	str | None	The name of the column to convert.	None
index	int | None	The 0-based index of the column.	None

Returns:

Name	Type	Description
str	str	The letter representation of the column (e.g., 'A', 'Z', 'AA').

Raises:

Type	Description
ValueError	If columns is None when index is not provided, or if column_name not found.

get_header_columns(worksheet)

Get the header columns from the worksheet.

Parameters:

Name	Type	Description	Default
worksheet	Worksheet	The worksheet to get headers from.	required

Returns:

Type	Description
list[str]	list[str]: List of header column names.

get_next_row_number(worksheet)

Get the next available row number.

Parameters:

Name	Type	Description	Default
worksheet	Worksheet	The worksheet to check.	required

Returns:

Name	Type	Description
int	int	The next row number.

get_or_create_worksheet(spreadsheet, worksheet_name)

Create or get a worksheet.

Parameters:

Name	Type	Description	Default
spreadsheet	Spreadsheet	The spreadsheet instance.	required
worksheet_name	str	The name of the worksheet.	required

Returns:

Name	Type	Description
Worksheet	Worksheet	The worksheet instance.

get_spreadsheet_instance(spreadsheet_id, client_email=None, private_key=None)

Get the spreadsheet instance.

Parameters:

Name	Type	Description	Default
spreadsheet_id	str	The ID of the Google Spreadsheet.	required
client_email	str | None	The client email for Google Sheets API. If None, reads from environment variables.	None
private_key	str | None	Base64-encoded private key for Google Sheets API. If None, reads from environment variables.	None

Returns:

Name	Type	Description
Spreadsheet	Spreadsheet	The spreadsheet instance.

Raises:

Type	Description
ValueError	If credentials are invalid.
Exception	If there's an error accessing the spreadsheet.

google_sheets_retry(max_retries=5, initial_backoff=1.0, max_backoff=32.0, retry_on_status_codes=(429, 403, 500, 503))

Decorator for Google Sheets operations with exponential backoff retry logic.

This decorator provides a unified retry mechanism for all Google Sheets operations, handling rate limits and transient errors with exponential backoff.

Parameters:

Name	Type	Description	Default
max_retries	int	Maximum number of retry attempts.	5
initial_backoff	float	Initial backoff delay in seconds.	1.0
max_backoff	float	Maximum backoff delay in seconds.	32.0
retry_on_status_codes	tuple[int, ...]	HTTP status codes that trigger retries.	(429, 403, 500, 503)

Returns:

Type	Description
Callable	Decorated function with retry logic.

Example

@google_sheets_retry(max_retries=3) def my_sheets_operation(worksheet): return worksheet.get_all_values()

is_sheet_empty(worksheet)

Check if the sheet is empty (no header row exists).

Parameters:

Name	Type	Description	Default
worksheet	Worksheet	The worksheet to check.	required

Returns:

Name	Type	Description
bool	bool	True if the sheet is empty (no data in first row).