DOCSEARCHbyAlgolia
Submit Docs

gspread-pandas

repository·master·Indexed 19 days ago

https://github.com/aiguofer/gspread-pandas

A library that bridges Google Sheets and Pandas DataFrames, enabling users to read from and write to spreadsheets while handling complex features such as MultiIndex headers, merged cells, and frozen rows. It provides a Client object for authentication and Google Drive file/folder management, and a Spread object for worksheet manipulation and Pandas integration via methods like sheet_to_df and df_to_sheet.

Tokens
4.2K
Snippets
8
Records
18
Agent score
65%

What's inside gspread-pandas

  1. Understand the core objects: Client and Spread

    master

    gspread-pandas revolves around two primary abstractions designed to bridge Google Sheets and Pandas DataFrames:

    1. Client: An extension of the gspread.Client that handles authentication, directory management in Google Drive, and advanced file querying.
    2. Spread: Represents an open Google Spreadsheet. It manages worksheets and provides high-level methods for common spreadsheet tasks (like merging cells or handling multi-level headers) and Pandas integration.

    While a Spread object manages a specific spreadsheet, it internally holds a Client instance to perform operations. You can share a single Client instance across multiple Spread objects to maintain consistent authentication and directory state.

  2. Choose between OAuth client and Service Account credentials

    master

    There are two main types of app credentials you can use:

    1. OAuth client: Allows the library to act as a specific Google user. Each user must grant permissions to your app. This is generally preferred for interactive work. Credentials for each user are stored in the creds subdirectory.
    2. Service Account: The library acts as the service account itself, using the service account's email and Google Drive. It can only access Spreadsheets that have been explicitly shared with the service account's email. This is useful for batch processes.

  4. Use the Spread object to manipulate Google Sheets and Pandas DataFrames

    master

    The Spread object represents an open Google Spreadsheet and acts as the primary interface for data manipulation.

    Worksheet Management

    A Spread object contains multiple Worksheets, but only one is 'active' at a time. Most functions act on the currently open worksheet. To act on a different worksheet, pass the sheet parameter (accepting a worksheet name or index) to the function call.

    Key Properties

    • spread: The underlying gspread Spreadsheet object.
    • sheet: The currently active gspread Worksheet object.
    • client: The Client instance used by this Spread.
    • sheets: A list of all available Worksheets in the spreadsheet.
    • _sheet_metadata: Private property containing metadata (merged cells, frozen rows/cols). Use refresh_spread_metadata() to update this.

    Common Tasks

    • Data Integration:
      • sheet_to_df: Convert a Worksheet into a Pandas DataFrame.
      • df_to_sheet: Create a Worksheet from a Pandas DataFrame.
    • Formatting & Structure:
      • freeze: Freeze rows or columns.
      • merge_cells / unmerge_cells: Manage cell merging.
      • add_filter: Apply a data filter to a range.
      • clear_sheet: Clear values and resize the worksheet.
      • delete_sheet: Remove a worksheet.
    • Permissions & Organization:
      • add_permission / add_permissions: Manage spreadsheet access.
      • list_permissions: View current permissions.
      • move: Relocate the spreadsheet in Google Drive.

    Note: Functions supporting cell selection accept A1 notation or tuples (e.g., (1, 1)).

  5. Use the Client object for authentication and file management

    master

    The Client object extends gspread.Client to reduce boilerplate and add Google Drive integration.

    Key Capabilities

    • Authentication: Simplifies credential handling.
    • Directory Management: Allows storing and working with file paths within Google Drive. To enable this, pass load_dirs=True during instantiation or call Client.refresh_directories() if the client is already active.
    • File Querying: Provides methods to navigate your Drive, including:
      • list_spreadsheet_files
      • list_spreadsheet_files_in_folder
      • find_folders
      • find_spreadssheet_files_in_folders
      • create_folder
      • move_file
    • Resilience: Automatically retries requests when encountering a 100-second quota exhaustion error.

    For detailed API documentation, refer to the gspread_pandas.client.Client documentation.

  6. Use Spread and Client to interact with Google Sheets

    master

    The core workflow involves using the Spread class to interact with specific spreadsheets and the Client class to manage files and folders.

    • Spread(title_or_id): Opens a spreadsheet by its name or ID. It handles authentication automatically if credentials are not yet present.
    • Spread.sheets: Returns a list of available worksheets in the spreadsheet.
    • Spread.df_to_sheet(df, ...): Pushes a Pandas DataFrame to a worksheet. Use replace=True to overwrite existing data and resize the sheet.
    • Client(): A separate instance used to query folders and find spreadsheet files.
    • Client.find_spreadsheet_files_in_folders(folder_name): Returns a dictionary of spreadsheet files found within specific folders.
    import pandas as pd
from gspread_pandas import Spread, Client

# Load data
file_name = "http://stats.idre.ucla.edu/stat/data/binary.csv"
df = pd.read_csv(file_name)

# Interact with a specific spreadsheet
spread = Spread('Example Spreadsheet')

# List worksheets
print(spread.sheets)

# Push DataFrame to a sheet (creates sheet if it doesn't exist)
spread.df_to_sheet(df, index=False, sheet='New Test Sheet', start='A2', replace=True)

# Update specific cells
spread.update_cells('A1', 'B1', ['Created by:', spread.email])

# Use Client to find files in folders
client = Client()
available_sheets = client.find_spreadsheet_files_in_folders('example dir')
for sheet_id in available_sheets.get('example dir', []):
    new_spread = Spread(sheet_id['id'], client=client)

  7. Use alternate configuration workflows

    master

    If you need to deviate from the default file-based configuration, you can use the following methods:

    • Custom Config Files: Load different client credentials by passing conf_dir and/or file_name to gspread_pandas.conf.get_config.
    • Manual Credential Injection: Once you have retrieved a configuration, you can pass it directly to a Client or Spread instance. Alternatively, you can pass credentials to gspread_pandas.conf.get_creds.
    • Direct Credential Generation: You can call get_creds directly to run the OAuth2 flow. If you want to avoid saving credentials to disk, or want to change the storage location, you can override the creds_dir when calling this function.

    Note on Service Accounts: When using a Service Account, the user parameter is ignored in Client, Spread, and get_creds.

  8. Manage multiple user credentials with OAuth

    master

    When using OAuth client credentials, you can store multiple user credentials on the same machine (e.g., on a shared Jupyter notebook server).

    To manage different users, use the user parameter in the Spread class. This parameter acts as a key to identify a user's credentials. By default, the key is default.

    When you call get_creds for a specific key for the first time, you will be prompted to authenticate via a text-based OAuth prompt. This supports headless environments like SSH or Jupyter notebooks. Once authenticated, tokens are stored in the creds subdirectory and are automatically refreshed during use.

  10. Configure Google Client Credentials

    master

    To use the Google Drive and Sheets APIs, you must generate credentials via the Google Cloud Console.

    1. Create a Project: Name your project in the Google Cloud Console.
    2. Enable APIs: Enable both the Drive API and the Sheets API in the API Manager.
    3. Configure OAuth Consent Screen: Select your email and provide a product name.
    4. Create Credentials:
      • OAuth client ID (for personal accounts): Select Application type: Desktop app. Download the JSON file.
      • Service account key (for automated service accounts): Create a new service account, download the JSON key, and note the Service account ID to grant permissions to your spreadsheets.
    5. Store Credentials: Move the downloaded JSON file to ~/.config/gspread_pandas/google_secret.json.

    You can also configure a custom directory and filename by calling gspread_pandas.conf.get_config.

  11. Configure the default configuration directory

    master

    By default, gspread-pandas stores configuration in:

    • Nix systems: $HOME/.config/gspread_pandas
    • Windows: %APPDATA%\gspread_pandas

    You can override this location by setting the GSPREAD_PANDAS_CONFIG_DIR environment variable to your desired directory. If you use this environment variable, the client credentials must still be named google_secret.json and user credentials will be stored in a creds subdirectory within that path.

  12. Handle 'Exceeding 10,000,000 cells' error when uploading DataFrames

    master

    When uploading large DataFrames, you may hit the Google Sheets cell limit because df_to_sheet adds rows/columns to accommodate the data, potentially exceeding the total cell count of the workbook.

    To resolve this, use one of these two methods:

    1. Use replace=True: Pass replace=True to Spread.df_to_sheet. This resizes the worksheet and clears values before uploading.
    2. Manual Resize: Manually resize the sheet to a 1x1 grid before uploading using spread.sheet.resize(1, 1).