gspread-dataframe

Warnings

• breaking In version 4.0.0, the `get_as_dataframe` function's `drop_empty_rows` and `drop_empty_columns` parameters changed their default value to `True`. This means empty rows and columns at the end of a sheet will now be automatically discarded when reading, which might alter the DataFrame structure for existing code expecting these rows/columns.
  Fix: If you need to retain all rows and columns, including trailing empty ones, explicitly set `drop_empty_rows=False` and `drop_empty_columns=False` when calling `get_as_dataframe`.
• gotcha Version 4.0.0 and later of `gspread-dataframe` officially support Python 3 only. If you are using Python 2.7, you must use `gspread-dataframe` releases prior to 4.0.0 (e.g., 2.1.1 or earlier).
  Fix: Upgrade to Python 3 or pin your `gspread-dataframe` dependency to a version compatible with Python 2.7 (e.g., `gspread-dataframe<4.0.0`).
• gotcha `gspread-dataframe` requires specific versions of its core dependencies. For `gspread-dataframe` versions 4.0.0+, you need `gspread>=3.0.0` and `pandas>=0.24.0`. Using older `gspread-dataframe` versions (2.1.1 or earlier) is necessary if you are tied to older `gspread` versions.
  Fix: Ensure your `gspread` and `pandas` installations meet the minimum requirements for your `gspread-dataframe` version to avoid compatibility issues.
• gotcha The `get_as_dataframe` function uses the 'python' engine for pandas' text parsing, which means only options supported by this engine can be passed via the `**options` argument (e.g., `parse_dates`, `skiprows`, `header`). Some advanced `pandas.read_csv` options might not work.
  Fix: Consult the pandas documentation for `pandas.read_csv` and ensure any passed options are compatible with the 'python' parsing engine. Process the DataFrame further with pandas after initial loading if more complex parsing is needed.
• gotcha The underlying `gspread` library, and by extension `gspread-dataframe`, requires careful handling of Google Sheets API authentication (e.g., Service Account or OAuth Client ID) and explicit sharing of the target spreadsheet with the authenticated client email. Misconfiguration is a common source of `gspread.exceptions.SpreadsheetNotFound` or permission errors.
  Fix: Follow the `gspread` authentication guide to set up credentials correctly. Crucially, share your Google Sheet with the client email address specified in your service account JSON file or OAuth client ID.
• gotcha When reading data from Google Sheets, `get_as_dataframe` may initially return all columns with a pandas 'object' dtype. This requires manual type conversion (e.g., to `int`, `float`, `datetime`) if numeric or date-time operations are intended.
  Fix: After loading the DataFrame, use `df['column'].astype(type)` or `pd.to_numeric(df['column'])`, `pd.to_datetime(df['column'])` to cast columns to their appropriate data types.

Install

• pip install gspread-dataframe Install with pip

Imports

• get_as_dataframe

  from gspread_dataframe import get_as_dataframe

• set_with_dataframe

  from gspread_dataframe import set_with_dataframe

Quickstart

This quickstart demonstrates how to read data from a Google Sheet into a pandas DataFrame using `get_as_dataframe` and write a DataFrame back to a sheet using `set_with_dataframe`. It includes a mock gspread worksheet for immediate runnability. In a real scenario, you'd authenticate with `gspread` (e.g., via a service account JSON file) and obtain a `worksheet` object from your Google Spreadsheet.

import gspread
import pandas as pd
from gspread_dataframe import get_as_dataframe, set_with_dataframe
import os

# --- Gspread Authentication (replace with your actual setup) ---
# For service account authentication, ensure 'service_account.json' is in your path
# and shared with the client_email in that file.
# For a runnable example, we'll mock gspread client/worksheet objects.
# In a real application, you would use:
# gc = gspread.service_account(filename=os.environ.get('GOOGLE_APPLICATION_CREDENTIALS'))
# spreadsheet = gc.open('Your Spreadsheet Name')
# worksheet = spreadsheet.worksheet('Sheet1')

class MockWorksheet:
    def __init__(self, data=None):
        self._values = [list(row) for row in data] if data else []

    def get_all_values(self):
        return self._values

    def update(self, range_name, values):
        # Simple mock update for demonstration
        if range_name == 'A1': # Assume A1 starts the update
            for r_idx, row in enumerate(values):
                if r_idx < len(self._values):
                    for c_idx, val in enumerate(row):
                        if c_idx < len(self._values[r_idx]):
                            self._values[r_idx][c_idx] = val
                        else:
                            self._values[r_idx].append(val)
                else:
                    self._values.append(list(row))

# Create a mock worksheet with some initial data
mock_data = [
    ['Name', 'Age', 'City'],
    ['Alice', '30', 'New York'],
    ['Bob', '24', 'London'],
    ['Charlie', '35', 'Paris']
]
worksheet = MockWorksheet(mock_data)

# Read worksheet into a DataFrame
df = get_as_dataframe(worksheet)
print("DataFrame from Worksheet:")
print(df)

# Modify the DataFrame
df['Age'] = df['Age'].astype(int) + 1
df['Country'] = ['USA', 'UK', 'France']

# Write DataFrame back to worksheet
# Note: resize=True will clear existing data and resize the sheet.
# include_index=False by default.
set_with_dataframe(worksheet, df, resize=True, include_column_header=True)

print("\nUpdated Worksheet (mocked values):")
print(worksheet.get_all_values())

# Example of reading with pandas options
# df_parsed = get_as_dataframe(worksheet, parse_dates=['birth_date'], skiprows=1, header=None)
# print(df_parsed)