{"id":4361,"library":"gspread-formatting","title":"gspread-formatting","description":"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.","status":"active","version":"1.2.1","language":"en","source_language":"en","source_url":"https://github.com/robin900/gspread-formatting","tags":["google sheets","spreadsheet","gspread","api client","formatting","excel"],"install":[{"cmd":"pip install gspread-formatting","lang":"bash","label":"Install stable version"}],"dependencies":[{"reason":"This library extends gspread functionality; gspread is required for all core operations like authentication and sheet access.","package":"gspread","optional":false}],"imports":[{"symbol":"format_cell_range","correct":"from gspread_formatting import format_cell_range"},{"symbol":"CellFormat","correct":"from gspread_formatting import CellFormat"},{"symbol":"Color","correct":"from gspread_formatting import Color"},{"symbol":"DataValidationRule","correct":"from gspread_formatting import DataValidationRule"},{"symbol":"BooleanCondition","correct":"from gspread_formatting import BooleanCondition"}],"quickstart":{"code":"import gspread\nfrom gspread_formatting import format_cell_range, CellFormat, Color\nimport os\n\n# --- gspread client setup (outside gspread-formatting scope) ---\n# Replace with your actual service account file path or other authentication method\nSERVICE_ACCOUNT_FILE = os.environ.get('GSPREAD_SERVICE_ACCOUNT_FILE', 'path/to/your/service_account.json')\n\ntry:\n    # Initialize gspread client (service account recommended for automation)\n    gc = gspread.service_account(filename=SERVICE_ACCOUNT_FILE)\nexcept Exception as e:\n    print(f\"Error initializing gspread client: {e}\")\n    print(\"Please ensure GSPREAD_SERVICE_ACCOUNT_FILE environment variable is set or path is correct.\")\n    exit(1)\n\n# Open a sheet\ntry:\n    spreadsheet = gc.open('My Formatted Sheet')\nexcept gspread.exceptions.SpreadsheetNotFound:\n    spreadsheet = gc.create('My Formatted Sheet')\n    print(f\"Created new spreadsheet: {spreadsheet.url}\")\n\nworksheet = spreadsheet.sheet1\n\n# --- gspread-formatting usage ---\n\n# Define a cell format\ncell_format = CellFormat(\n    backgroundColor=Color(1, 0.9, 0.9), # Light red background\n    textFormat=CellFormat.textFormat(\n        bold=True,\n        foregroundColor=Color(1, 0, 0)\n    )\n)\n\n# Apply the format to a range of cells (e.g., A1:C5)\nformat_cell_range(worksheet, 'A1:C5', cell_format)\n\nprint(\"Formatted cells A1:C5 with a light red background and bold red text.\")\nprint(\"Check your spreadsheet to see the changes.\")","lang":"python","description":"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`."},"warnings":[{"fix":"Upgrade gspread-formatting to version 1.0.0 or higher: `pip install --upgrade gspread-formatting`.","message":"gspread-formatting versions prior to 1.0.0 are not compatible with gspread 5.0.0 and newer. The underlying gspread API changed significantly.","severity":"breaking","affected_versions":"<1.0.0"},{"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')`.","message":"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.","severity":"gotcha","affected_versions":"All"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All"},{"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.","message":"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`.","severity":"gotcha","affected_versions":"All"}],"env_vars":null,"last_verified":"2026-04-12T00:00:00.000Z","next_check":"2026-07-11T00:00:00.000Z"}