or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

authentication.mdcell-range-operations.mdcharts.mddata-validation-formatting.mdindex.mdspreadsheet-management.mdworksheet-operations.md
tile.json

tessl/pypi-pygsheets

Google Spreadsheets Python API v4

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
pypipkg:pypi/pygsheets@2.0.x

To install, run

npx @tessl/cli install tessl/pypi-pygsheets@2.0.0

index.mddocs/

pygsheets

A comprehensive Python library for interacting with Google Spreadsheets using the Google Sheets API v4. It provides an object-oriented interface for creating, reading, updating, and deleting spreadsheet data with advanced features like formatting, charts, data validation, and pandas integration.

Package Information

  • Package Name: pygsheets
  • Language: Python
  • Installation: pip install pygsheets

Core Imports

import pygsheets

Common imports for working with specific components:

from pygsheets import authorize, Spreadsheet, Worksheet, Cell, DataRange, Chart, Address, GridRange
from pygsheets import FormatType, WorkSheetProperty, ChartType, ValueRenderOption, DateTimeRenderOption

Basic Usage

import pygsheets
import numpy as np

# Authenticate with Google Sheets API
gc = pygsheets.authorize()

# Open spreadsheet and get first worksheet
sh = gc.open('my new sheet')
wks = sh.sheet1

# Update a single cell
wks.update_value('A1', "Hello World")

# Update a range with data
my_array = np.random.randint(10, size=(3, 4))
wks.update_values('A2', my_array.tolist())

# Work with individual cells
cell = wks.cell('B1')
cell.value = 'Heights'
cell.text_format['bold'] = True
cell.update()

# Work with named ranges
heights_range = wks.range('B2:B5', returnas='range')
heights_range.name = "heights"
heights_range.update_values([[50], [60], [67], [66]])

# Use formulas with named ranges
wks.update_value('B6', '=average(heights)')

# Share the spreadsheet
sh.share("friend@gmail.com")

Architecture

The pygsheets library follows a hierarchical object model:

  • Client: Main entry point for authentication and spreadsheet access
  • Spreadsheet: Represents a Google Spreadsheet document with multiple worksheets
  • Worksheet: Individual sheets within a spreadsheet containing cells and data
  • Cell: Individual cells with values, formatting, and formulas
  • DataRange: Named or protected ranges of cells for bulk operations
  • Address/GridRange: Flexible address representation for cell and range references

Capabilities

Authentication and Client Management

Authenticate with Google APIs and manage spreadsheet access using OAuth2 or service account credentials.

def authorize(client_secret='client_secret.json',
              service_account_file=None,
              service_account_env_var=None,
              service_account_json=None,
              credentials_directory='',
              scopes=_SCOPES,
              custom_credentials=None,
              local=False,
              **kwargs) -> Client

Authentication

Spreadsheet Operations

Create, open, share, and manage Google Spreadsheets with full CRUD operations and permission control.

class Client:
    def create(self, title, template=None, folder=None, folder_name=None, **kwargs) -> Spreadsheet
    def open(self, title) -> Spreadsheet
    def open_by_key(self, key) -> Spreadsheet
    def open_by_url(self, url) -> Spreadsheet

Spreadsheet Management

Worksheet Operations

Manage individual worksheets with comprehensive data manipulation, formatting, and structural operations.

class Worksheet:
    def get_values(self, start=None, end=None, returnas='matrix', **kwargs)
    def update_values(self, crange=None, values=None, **kwargs)
    def get_all_records(self, **kwargs) -> list
    def set_dataframe(self, df, start='A1', **kwargs)
    def get_as_df(self, **kwargs)

Worksheet Operations

Cell and Range Manipulation

Work with individual cells and ranges including formatting, formulas, and bulk operations.

class Cell:
    def set_text_format(self, attribute, value)
    def set_number_format(self, format_type, pattern=None)
    def update(self, force=False)

class DataRange:
    def update_values(self, values, **kwargs)
    def apply_format(self, cell_list, fields="userEnteredFormat")

Cell and Range Operations

Charts and Visualizations

Create and manage charts within worksheets with various chart types and customization options.

class Chart:
    def update_chart(self, chart_type=None, domain=None, ranges=None, **kwargs)
    def delete()

class Worksheet:
    def add_chart(self, data, start=None, end=None, chart_type=None, **kwargs) -> Chart

Charts

Data Validation and Formatting

Set up data validation rules, conditional formatting, and advanced cell formatting options.

class Worksheet:
    def set_data_validation(self, crange, condition_type, condition_values, **kwargs)
    def add_conditional_formatting(self, crange, condition_type, condition_values, **kwargs)
    def apply_format(self, ranges, cell_format, fields="userEnteredFormat")

Data Validation and Formatting

Types

Core Classes

class Client:
    def __init__(self, credentials, retries=3, seconds_per_quota=100)

class Spreadsheet:
    def __init__(self, client, properties=None, jsonsheet=None)
    
class Worksheet:
    def __init__(self, spreadsheet, properties=None, jsonsheet=None)
    
class Cell:
    def __init__(self, pos, val='', worksheet=None, cell_data=None)
    
class DataRange:
    def __init__(self, namedjson=None, name_id='', worksheet=None, protectedjson=None, protect_id='')

Address Types

class Address:
    def __init__(self, label=None, row=None, col=None, index=1)
    
class GridRange:
    def __init__(self, label=None, start=None, end=None, worksheet=None)
    @staticmethod
    def create(start, end=None, worksheet=None) -> GridRange

Enumerations

class FormatType:
    CUSTOM = None
    TEXT = 'TEXT'
    NUMBER = 'NUMBER'
    PERCENT = 'PERCENT'
    CURRENCY = 'CURRENCY'
    DATE = 'DATE'
    TIME = 'TIME'
    DATE_TIME = 'DATE_TIME'
    SCIENTIFIC = 'SCIENTIFIC'

class ChartType:
    BAR = 'BAR'
    LINE = 'LINE'
    AREA = 'AREA'
    COLUMN = 'COLUMN'
    SCATTER = 'SCATTER'
    COMBO = 'COMBO'
    STEPPED_AREA = 'STEPPED_AREA'

class ValueRenderOption:
    FORMATTED_VALUE = 'FORMATTED_VALUE'
    UNFORMATTED_VALUE = 'UNFORMATTED_VALUE'
    FORMULA = 'FORMULA'

class DateTimeRenderOption:
    SERIAL_NUMBER = 'SERIAL_NUMBER'
    FORMATTED_STRING = 'FORMATTED_STRING'

class WorkSheetProperty:
    TITLE = 'title'
    ID = 'id'
    INDEX = 'index'

class ExportType:
    XLS = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet:.xls"
    ODT = "application/x-vnd.oasis.opendocument.spreadsheet:.odt"
    PDF = "application/pdf:.pdf"
    CSV = "text/csv:.csv"
    TSV = 'text/tab-separated-values:.tsv'
    HTML = 'application/zip:.zip'

Exception Types

class PyGsheetsException(Exception): pass
class AuthenticationError(PyGsheetsException): pass
class SpreadsheetNotFound(PyGsheetsException): pass
class NoValidUrlKeyFound(PyGsheetsException): pass
class IncorrectCellLabel(PyGsheetsException): pass
class WorksheetNotFound(PyGsheetsException): pass
class RequestError(PyGsheetsException): pass
class CellNotFound(PyGsheetsException): pass
class InvalidUser(PyGsheetsException): pass
class InvalidArgumentValue(PyGsheetsException): pass