{"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":"python","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,"search_vec":"'1.2.1':32 'add':37 'allow':16 'api':53,58 'bug':41 'cell':20 'client':59 'compat':44 'complet':8 'condit':22 'control':18 'current':29 'data':24 'excel':61 'featur':39 'fix':40 'format':3,6,11,23,60 'googl':9,51,54 'gspread':2,5,14,48,57 'gspread-format':1,4 'librari':49 'maintain':43 'new':38 'occur':34 'period':35 'programmat':17 'provid':7 'releas':33 'sheet':10,52,55 'spreadsheet':56 'style':21 'support':12 'under':47 'valid':25 'version':30 'worksheet':15","created_at":"2026-04-12T08:52:29.539159+00:00","updated_at":"2026-04-16T15:30:22.464617+00:00","problems":[{"fix":"Run `pip install gspread-formatting` in your terminal to install the package.","cause":"The 'gspread-formatting' library is not installed in the Python environment where the script is being executed.","error":"ModuleNotFoundError: No module named 'gspread_formatting'"},{"fix":"Ensure the 'values' parameter for `BooleanCondition` is always a list or tuple, even if it contains only one element. For example, use `values=[100]` instead of `values=100`.","cause":"When defining a `BooleanCondition` for conditional formatting, the 'values' argument was provided as a single scalar value instead of an iterable (list or tuple) containing the value.","error":"ValueError: values parameter must always be list/tuple of values, even for a single element"},{"fix":"Consolidate multiple formatting operations into single API calls using batch functions provided by `gspread-formatting`, such as `format_cell_ranges()`, `set_column_widths()`, `set_row_heights()`, and using `ConditionalFormatRule.save()` after modifying rules.","cause":"The application has exceeded the Google Sheets API's read or write usage limits (e.g., 60 requests per 60 seconds per user or 300 requests per 60 seconds per project) by making too many individual API calls in a short period.","error":"gspread.exceptions.APIError: {'code': 429, 'message': 'Quota exceeded...', 'status': 'RESOURCE_EXHAUSTED'}"},{"fix":"Import the required formatting functions from `gspread_formatting` (e.g., `from gspread_formatting import format_cell_range`) and then call them, passing your `worksheet` object as an argument, like `format_cell_range(worksheet, 'A1:B1', fmt)`.","cause":"A formatting function from `gspread-formatting` was incorrectly attempted to be called as a method directly on a `gspread.Worksheet` object, instead of being imported and called as a standalone function with the worksheet as an argument.","error":"AttributeError: 'Worksheet' object has no attribute 'format_cell_range'"}],"ecosystem":"pypi","meta_description":null,"install_score":null,"quickstart_score":null,"quickstart_tag":null,"pypi_latest":"1.2.1","cli_name":"","cli_version":null,"type":"library","homepage":null,"github":"https://github.com/robin900/gspread-formatting","docs":null,"changelog":null,"pypi":"https://pypi.org/project/gspread-formatting/","npm":null,"openapi_spec":null,"status_page":null,"smithery":null,"categories":["gcp","data"],"base_url":null,"auth_type":null,"provenance":{"verified_status":"passing","verified_at":"2026-06-28","last_verified":"2026-08-29","next_check":"2026-07-28","install_tag":null}}