Pygsheets: Google Spreadsheets Python API v4

2.0.6 · active · verified Fri Apr 10

Pygsheets is a simple, intuitive Python library that provides an interface to the Google Sheets API v4. It enables programmatic interaction with Google Spreadsheets, allowing users to create, open, edit, and share spreadsheets, as well as manage worksheets, cells, and ranges. The library supports advanced features like conditional formatting, data validation, and batch operations. The current version is 2.0.6, and it maintains an active development cycle with regular updates and bug fixes.

Warnings

breaking Version 2.0.0 introduced significant breaking changes, including function renames and `authorize()` parameter changes.
Fix: Old functions like `update_cell()`, `update_cells()`, and `update_cells_prop()` were renamed to `update_value()`, `update_values()`, and `update_cells()` respectively. Parameters for `authorize()` like `outh_file` changed to `client_secret` and `service_file` to `service_account_file`. Review the 2.0.0 release notes and documentation for a full migration guide.
breaking In version 2.0.3, the internal handling of cell addressing was fundamentally changed, introducing `Address` and `GridRange` objects.
Fix: While many high-level functions might still accept string notations (e.g., 'A1'), direct manipulation or advanced range operations might require adapting to the new `Address` and `GridRange` objects. Consult the documentation for the correct usage of addressing.
breaking The default behavior of `get_as_df` changed in version 2.0.5.
Fix: If you relied on the previous default behavior of `get_as_df`, you might need to adjust parameters like `include_tailing_empty` or `include_tailing_empty_rows` to match the desired output.
gotcha Authorization requires careful setup of a Google Cloud Project.
Fix: You must enable the Google Sheets API and Google Drive API, create a service account, and download its JSON key file. Share your spreadsheet with the service account's email address (found in the JSON key file) to grant it access.
gotcha When working with `Cell` objects, properties other than `value` are not fetched by default.
Fix: If you need to access or modify cell properties (like format, color, notes), you must explicitly call `cell.fetch()` on the `Cell` object to retrieve its full properties from the spreadsheet, or ensure the cell was obtained with a fetch operation that included properties.
deprecated The `link()` and `unlink()` methods on `Worksheet` objects are deprecated.
Fix: Use the `batch_mode` context manager on the `Client` object for efficient batching of updates instead of manually linking and unlinking worksheets.

Install

pip install pygsheets Install stable version from PyPI

Imports

pygsheets
```
import pygsheets
```

Quickstart

This quickstart demonstrates how to authorize `pygsheets` using a service account, open or create a Google Sheet, and perform basic operations like updating and retrieving cell values. Remember to set up a Google Cloud Project, enable the Google Sheets API and Google Drive API, create a service account, and download its JSON key file. The path to this file should be provided via the `PYGSHEETS_SERVICE_ACCOUNT_FILE` environment variable for secure credential handling.

import pygsheets
import os

# Ensure your service account JSON key file is present and its path is in an environment variable
# Instructions to get one: https://pygsheets.readthedocs.io/en/latest/authorization.html#service-account
service_file_path = os.environ.get('PYGSHEETS_SERVICE_ACCOUNT_FILE', 'path/to/your/service_account.json')

if not os.path.exists(service_file_path) or service_file_path == 'path/to/your/service_account.json':
    print(f"Warning: Service account file not found at '{service_file_path}'. Please set PYGSHEETS_SERVICE_ACCOUNT_FILE environment variable or update the path.")
    print("Cannot run quickstart without valid service account credentials.")
else:
    try:
        # Authorize with service account credentials
        gc = pygsheets.authorize(service_account_file=service_file_path)

        # Open a spreadsheet by name or create it if it doesn't exist
        spreadsheet_name = "My Test Spreadsheet"
        try:
            sh = gc.open(spreadsheet_name)
            print(f"Opened existing spreadsheet: {spreadsheet_name}")
        except pygsheets.exceptions.SpreadsheetNotFound:
            sh = gc.create(spreadsheet_name)
            # Share with your Google account if you want to see it in your Drive
            # sh.share('your-email@example.com', role='writer', type='user')
            print(f"Created new spreadsheet: {spreadsheet_name}")

        # Select the first worksheet
        wks = sh[0]

        # Update a cell
        wks.update_value('A1', 'Hello from pygsheets!')
        print(f"Updated cell A1 in '{spreadsheet_name}' with 'Hello from pygsheets!'")

        # Get a value
        cell_value = wks.get_value('A1')
        print(f"Value in A1: {cell_value}")

        # Update a range of values
        data = [[1, 2, 3], [4, 5, 6]]
        wks.update_values('B2', data)
        print(f"Updated range B2:D3 with: {data}")

    except Exception as e:
        print(f"An error occurred: {e}")

view raw JSON →