gspread-pandas

Warnings

• breaking Version 3.0.0 removed support for Python 2.7. Users must migrate to Python 3.x (minimum Python 3.5, though newer versions are recommended).
  Fix: Upgrade your Python environment to Python 3.x.
• breaking Version 3.0.0 upgraded the internal `gspread` dependency to version `5>=`. This might introduce breaking changes if your project relied on specific `gspread` APIs that changed between its major versions.
  Fix: Review the `gspread` changelog for versions 5.0.0 and above. Specifically, `gspread` v6 swapped arguments for `Worksheet.update` and requires `values` to be a 2D array.
• deprecated The `raw_column_names` parameter was removed in version 3.0.2. Code using this parameter will break.
  Fix: Remove the `raw_column_names` parameter from your function calls. Adjust your code to handle column names as per current documentation.
• gotcha When uploading large DataFrames, you might encounter Google Sheets API limits (e.g., max number of cells in a worksheet). If `df_to_sheet` adds rows/columns by default, it might exceed limits.
  Fix: Use `replace=True` in `df_to_sheet` to first resize and clear the worksheet, or manually resize the sheet to `1x1` using `spread.sheet.resize(1, 1)` before calling `df_to_sheet`.
• gotcha Authentication requires setting up Google Cloud Project credentials (Service Account or OAuth Client ID) and placing the `google_secret.json` file in `~/.config/gspread_pandas/` (or `%APPDATA%\gspread_pandas` on Windows) by default. Without this, operations will fail.
  Fix: Follow the detailed authentication steps in the `gspread-pandas` or `gspread` documentation to create and configure your Google Cloud project and credentials. Ensure the service account has editor access to your spreadsheets.
• gotcha Internal `pandas` operations no longer use `inplace=True` as of version 3.2.1 to avoid `SettingWithCopyWarning`. If your code implicitly relied on previous in-place modifications (which were often ambiguous) or suppressed these warnings, behavior might change.
  Fix: Review DataFrame assignments and transformations. Prefer explicit copying (`.copy()`) or chained operations that return new DataFrames over methods that modify in-place if you require specific behavior. Pandas 3.0 introduced Copy-on-Write semantics, further emphasizing this.

Install

• pip install gspread-pandas Install with pip

Imports

• Spread

  from gspread_pandas import Spread

  The primary class for interacting with a Google Spreadsheet via a Pandas DataFrame.
• Client

  from gspread_pandas import Client

  Used for managing Google API client and authentication. Often implicitly handled by `Spread`.

Quickstart

This quickstart demonstrates how to authenticate, open/create a Google Spreadsheet, write a Pandas DataFrame to a specified worksheet, and then read it back. Proper authentication (e.g., setting up a service account JSON file in `~/.config/gspread_pandas/google_secret.json` and sharing your spreadsheet with the service account email) is a prerequisite for running this code.

import pandas as pd
import os
from gspread_pandas import Spread

# IMPORTANT: Ensure your Google client credentials (e.g., service account JSON)
# are set up at ~/.config/gspread_pandas/google_secret.json or via environment variables.
# See documentation for detailed authentication setup.

# Replace with your actual spreadsheet name, or fetch from environment
spreadsheet_name = os.environ.get('GSPREAD_PANDAS_SHEET_NAME', 'My Example Spreadsheet')

# Create a dummy DataFrame to write to the spreadsheet
data = {'col1': [1, 2, 3], 'col2': ['A', 'B', 'C']}
df_to_write = pd.DataFrame(data)

try:
    # Open the spreadsheet (or create if it doesn't exist and create_spread=True)
    # By default, it looks for ~/.config/gspread_pandas/google_secret.json
    spread = Spread(spreadsheet_name, create_spread=True)
    
    # Get a list of existing worksheets
    print(f"Worksheets in '{spreadsheet_name}': {spread.sheets}")

    # Write DataFrame to a new worksheet or an existing one
    # Using 'overwrite=True' to clear existing data, 'sheet=' to specify sheet name
    sheet_name = 'DataFrame Data'
    print(f"Writing DataFrame to sheet: '{sheet_name}'")
    spread.df_to_sheet(df_to_write, sheet=sheet_name, index=False, overwrite=True)
    
    # Read data back into a DataFrame
    df_from_sheet = spread.sheet_to_df(sheet=sheet_name)
    print(f"\nData read from '{sheet_name}':")
    print(df_from_sheet.head())

except Exception as e:
    print(f"An error occurred: {e}")
    print("Please ensure your Google API credentials are correctly configured and the spreadsheet exists/is accessible.")