gspread-formatting

1.2.1 · active · verified Sun Apr 12

gspread-formatting provides complete Google Sheets formatting support for gspread worksheets, allowing programmatic control over cell styles, conditional formatting, data validation, and more. The current version is 1.2.1. Releases occur periodically to add new features, fix bugs, and maintain compatibility with the underlying gspread library and Google Sheets API.

Warnings

breaking gspread-formatting versions prior to 1.0.0 are not compatible with gspread 5.0.0 and newer. The underlying gspread API changed significantly.
Fix: Upgrade gspread-formatting to version 1.0.0 or higher: `pip install --upgrade gspread-formatting`.
gotcha Applying new formats to a cell range will overwrite any existing formatting for those cells. To preserve specific aspects of existing formatting, you must explicitly include them in the new `CellFormat` object or retrieve the existing format first.
Fix: Be mindful that formatting operations are generally 'set' operations. If you need to incrementally update formats, retrieve the current format first, modify it, then reapply. E.g., `get_user_entered_format(worksheet, 'A1')`.
gotcha Complex formatting objects (like `CellFormat` or `DataValidationRule`) can be intricate to construct. Missing fields or incorrect values may lead to API errors or unexpected behavior.
Fix: Refer to the official Google Sheets API documentation for `CellFormat`, `TextFormat`, `Color`, etc., to understand all available properties and their valid values. Experiment with small examples to verify complex formats before applying them broadly.
gotcha This library relies on `gspread` for authentication and interaction with Google Sheets. Any issues with `gspread` client initialization, permissions, or API quotas will manifest when using `gspread-formatting`.
Fix: Ensure your `gspread` client is correctly authenticated and has the necessary permissions for the target spreadsheet. Handle `gspread.exceptions` appropriately for network or permission-related errors. Be aware of Google Sheets API rate limits for extensive formatting operations.

Install

pip install gspread-formatting Install stable version

Imports

format_cell_range

from gspread_formatting import format_cell_range

CellFormat

from gspread_formatting import CellFormat

Color
```
from gspread_formatting import Color
```

DataValidationRule

from gspread_formatting import DataValidationRule

BooleanCondition

from gspread_formatting import BooleanCondition

Quickstart

This quickstart demonstrates how to initialize a gspread client (required for gspread-formatting), create or open a spreadsheet, define a custom `CellFormat` object, and apply it to a specific cell range using `format_cell_range`.

import gspread
from gspread_formatting import format_cell_range, CellFormat, Color
import os

# --- gspread client setup (outside gspread-formatting scope) ---
# Replace with your actual service account file path or other authentication method
SERVICE_ACCOUNT_FILE = os.environ.get('GSPREAD_SERVICE_ACCOUNT_FILE', 'path/to/your/service_account.json')

try:
    # Initialize gspread client (service account recommended for automation)
    gc = gspread.service_account(filename=SERVICE_ACCOUNT_FILE)
except Exception as e:
    print(f"Error initializing gspread client: {e}")
    print("Please ensure GSPREAD_SERVICE_ACCOUNT_FILE environment variable is set or path is correct.")
    exit(1)

# Open a sheet
try:
    spreadsheet = gc.open('My Formatted Sheet')
except gspread.exceptions.SpreadsheetNotFound:
    spreadsheet = gc.create('My Formatted Sheet')
    print(f"Created new spreadsheet: {spreadsheet.url}")

worksheet = spreadsheet.sheet1

# --- gspread-formatting usage ---

# Define a cell format
cell_format = CellFormat(
    backgroundColor=Color(1, 0.9, 0.9), # Light red background
    textFormat=CellFormat.textFormat(
        bold=True,
        foregroundColor=Color(1, 0, 0)
    )
)

# Apply the format to a range of cells (e.g., A1:C5)
format_cell_range(worksheet, 'A1:C5', cell_format)

print("Formatted cells A1:C5 with a light red background and bold red text.")
print("Check your spreadsheet to see the changes.")

view raw JSON →