gspread - Google Spreadsheets Python API

Warnings

• breaking Python 3.7 End-of-Life: gspread v6+ officially drops support for Python 3.7. Your project must use Python 3.8 or newer.
  Fix: Upgrade your Python environment to 3.8 or a later version.
• breaking Worksheet.update() arguments swapped and value format changed. The `values` and `range_name` arguments are swapped. Additionally, `values` must now be a 2D array (list of lists), not a simple list.
  Fix: Either swap the arguments (`worksheet.update(values, range_name)`) or, preferably, use named arguments (`worksheet.update(values=..., range_name=...)`) for clarity and forward compatibility. Ensure `values` is always a list of lists.
• breaking Worksheet.get_records() method removed. This method is no longer available in v6.
  Fix: Use `worksheet.get_all_records()` to retrieve all records or fetch specific ranges with `worksheet.get()` and process them using `gspread.utils.to_records(header, cells)` if partial records are needed.
• breaking Color representation for formatting changed from dictionary to hexadecimal strings.
  Fix: Convert color dictionaries to hexadecimal strings (e.g., `{'red': 1, 'green': 0.5, 'blue': 1}` to `"#FF7FFF"`). A utility function `gspread.utils.convert_colors_to_hex_value()` is available for compatibility.
• gotcha SpreadsheetNotFound error (or similar access issues) when using a Service Account.
  Fix: You *must* share the target Google Sheet with the `client_email` address found in your service account's JSON key file. Treat it like sharing with any other Google user.
• gotcha Google Sheets API rate limits can lead to `gspread.exceptions.APIError: 429 RESOURCE_EXHAUSTED`.
  Fix: The Sheets API has limits (e.g., 100 requests/100 seconds per project, 60 requests/60 seconds per user). Minimize API calls by using batch operations (`update()`, `batch_update()`, `get_all_values()`, `batch_get()`) instead of individual cell/row operations in loops. Consider using `gspread.http_client.BackOffHTTPClient` for automatic retry with exponential back-off.
• gotcha OAuth Client ID authentication sometimes results in `google.auth.exceptions.RefreshError: invalid_grant: Token has been expired or revoked.`
  Fix: This usually means the `authorized_user.json` credentials have expired. Delete the `authorized_user.json` file (located in `~/.config/gspread/` on Linux/macOS or `%APPDATA%\gspread\` on Windows) and re-run your code to initiate a new authentication flow.

Install

• pip install gspread Install latest version

Imports

• gspread

  import gspread

  Main library import for accessing the Google Sheets API.
• gspread.service_account

  import gspread
  gc = gspread.service_account()

  The `service_account()` method is the modern and recommended way for bot/script authentication using a service account JSON key file. `authorize()` is for OAuth2 credentials via `google.auth.credentials.Credentials` objects.
• gspread.oauth

  import gspread
  gc = gspread.oauth()

  Used for end-user authentication, often involving an interactive browser flow.
• gspread.api_key

  import gspread
  gc = gspread.api_key('YOUR_API_KEY')

  Authenticates using an API key, suitable for accessing public spreadsheets only.
• gspread.exceptions.APIError

  from gspread.exceptions import APIError

  Catches API-specific errors (e.g., rate limits, invalid requests) from the Google Sheets API.

Quickstart

This quickstart demonstrates how to authenticate with gspread using a service account, open a spreadsheet, read a cell, and update cells. Ensure you have enabled the Google Drive API and Google Sheets API in your Google Cloud project and shared your spreadsheet with the service account's email. The example uses an environment variable for the keyfile path for better security and flexibility.

import gspread
import os

# Ensure your service account key file path is set as an environment variable
# or replace with the actual path.
# For example: export GSPREAD_SERVICE_ACCOUNT_KEYFILE="./path/to/your/service_account.json"

SERVICE_ACCOUNT_KEYFILE = os.environ.get(
    'GSPREAD_SERVICE_ACCOUNT_KEYFILE',
    './path/to/your/service_account.json' # Placeholder, replace or use env var
)

try:
    # Authenticate using a service account
    # Make sure to share your Google Sheet with the service account email address.
    gc = gspread.service_account(filename=SERVICE_ACCOUNT_KEYFILE)

    # Open a spreadsheet by its title
    spreadsheet_title = "My Test Spreadsheet"
    sh = gc.open(spreadsheet_title)

    # Select the first worksheet
    wks = sh.sheet1

    print(f"Successfully opened spreadsheet: {sh.title}")
    print(f"First worksheet title: {wks.title}")

    # Read a single cell value
    cell_a1 = wks.acell('A1').value
    print(f"Value in A1: {cell_a1}")

    # Update a single cell
    wks.update_acell('B1', 'Hello gspread!')
    print("Updated cell B1.")

    # Update a range of cells (using v6 syntax with 2D array and named args)
    data_to_write = [['Name', 'Age'], ['Alice', 30], ['Bob', 24]]
    wks.update(values=data_to_write, range_name='A3')
    print("Updated range A3:B5.")

    # Get all values from the worksheet as a list of lists
    all_values = wks.get_all_values()
    print("\nAll values in the worksheet:")
    for row in all_values:
        print(row)

except FileNotFoundError:
    print(f"Error: Service account key file not found at {SERVICE_ACCOUNT_KEYFILE}. ")
    print("Please ensure the file exists and the path is correct.")
except gspread.exceptions.SpreadsheetNotFound:
    print(f"Error: Spreadsheet '{spreadsheet_title}' not found or not shared with the service account.")
    print("Ensure the spreadsheet name is correct and shared with the client_email from your service account JSON.")
except gspread.exceptions.APIError as e:
    print(f"Google Sheets API Error: {e}")
    print("This might be a rate limit issue or incorrect API permissions.")
except Exception as e:
    print(f"An unexpected error occurred: {e}")