google-sheets-helper 1.1.1

Helper module to parse data from GSheets into database-optimized DataFrames

Google Sheets Helper

A Python ETL driver for reading and transforming Google Sheets and Excel data from Google Drive. Simplifies the process of extracting spreadsheet data and converting it to database-ready pandas DataFrames with comprehensive optimization features.

Features

• Google Sheets & Excel Support: Read Google Sheets and Excel files directly from Google Drive
• Database-Ready DataFrames: Optimized data types and encoding for seamless database storage
• Flexible Column Naming: Choose between snake_case or camelCase column conventions, with robust ASCII normalization and automatic removal of unnamed columns (e.g., from Excel/CSV exports)
• Smart Type Detection: Dynamic conversion of metrics to appropriate int64/float64 types
• Configurable Missing Values: Granular control over NaN/NaT handling by column type
• Character Encoding Cleanup: Automatic text sanitization for database compatibility
• Robust Error Handling: Comprehensive error handling with specific exceptions
• Progress Bar for Large Downloads: Uses tqdm to show download progress for large Excel files
• Type Hints: Full type hint support for better IDE experience

Installation

pip install google-sheets-helper

Quick Start

1. Set up credentials

Place your Google service account credentials in secrets/client_secret.json.

2. Basic usage

from google_sheets_helper import GoogleSheetsHelper, load_client_secret, setup_logging

setup_logging()
client_secret = load_client_secret()
gs_helper = GoogleSheetsHelper(client_secret)

spreadsheet_id = "your_spreadsheet_id"
worksheet_name = "your_worksheet_name"

df = gs_helper.load_sheet_as_dataframe(spreadsheet_id, worksheet_name)
utils = DataframeUtils()

df = utils.fix_data_types(df, skip_columns=None)
df = utils.handle_missing_values(df)
df = utils.clean_text_encoding(df)
df = utils.transform_column_names(df, naming_convention="snake_case")

print(df.head(), df.dtypes)

os.makedirs("data", exist_ok=True)
filename = os.path.join("data", f"{spreadsheet_id}_{worksheet_name}.csv")

df.to_csv(filename, index=False)

Data Cleaning Pipeline

You can use the built-in DataFrame utilities for further cleaning, including robust column name normalization and unnamed column removal:

from google_sheets_helper import DataframeUtils

utils = DataframeUtils()
df = utils.fix_data_types(df)
df = utils.handle_missing_values(df)
df = utils.clean_text_encoding(df)
df = utils.transform_column_names(df, naming_convention="snake_case")
df = utils.remove_unnamed_columns(df)

API Reference

• GoogleSheetsHelper: Main class for reading and transforming Google Sheets/Excel data
• get_drive_file_metadata: Retrieve file name and MIME type from Google Drive
• list_files_in_folder: List files in a Google Drive folder
• load_client_secret: Loads credentials from a JSON file
• setup_logging: Configures logging for the package
• DataframeUtils: Utility class for DataFrame cleaning and optimization
• remove_unnamed_columns: Remove columns named 'Unnamed: ...' (common in Excel/CSV)
• Exception classes: AuthenticationError, APIError, ConfigurationError, DataProcessingError, ValidationError

Error Handling

from google_sheets_helper import (
    GoogleSheetsHelper,
    AuthenticationError,
    ValidationError,
    APIError,
    DataProcessingError,
    ConfigurationError
)

try:
    df = gs_helper.load_sheet_as_dataframe(spreadsheet_id, worksheet_name)
except AuthenticationError:
    # Handle credential issues
    pass
except ValidationError:
    # Handle input validation errors
    pass
except APIError:
    # Handle API errors
    pass
except DataProcessingError:
    # Handle data processing errors
    pass

Examples

Check the examples/ directory for comprehensive usage examples:

• basic_usage.py - Simple sheet extraction and cleaning

Requirements

• Python 3.9-3.12
• pandas >= 2.0.0
• gspread >= 5.10.0
• google-api-python-client >= 2.0.0
• tqdm >= 4.65.0

Development

For development installation:

git clone https://github.com/machado000/google-sheets-helper
cd google-sheets-helper
pip install -e ".[dev]"

License

MIT License. See LICENSE file for details.

Support

• Documentation
• Issues
• Examples

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.