python-google-sheets 1.0.0

A lightweight and efficient Python wrapper for the Google Sheets API v4

python-google-sheets

A lightweight and efficient Python wrapper for the Google Sheets API v4, leveraging service account credentials for seamless authentication.

Features

• Batch Operations: Read multiple ranges or write values and formatting in a single batchUpdate request to optimize API quotas.
• Conditional Formatting: Full support for boolean rules, gradient rules, presets, updating and deleting rules.
• Rich Styling: Built-in ColorStyle objects for all standard Google Sheets web interface colors.
• Developer Friendly: Designed to simplify complex API interactions into intuitive Pythonic calls.

Table of Contents

• Features
• Installation
• Quick Start
• GoogleSheets methods
• ApiRequest methods for batchUpdate
  • Cell updates
  • Conditional formatting
  • Sheet-level operations
  • Merge and freeze
  • Rows and columns
• Utility helpers
• Full example: send multiple requests at once
• Support & Feedback

Installation

pip install python-google-sheets

Quickstart

from google_sheets import GoogleSheets

service = GoogleSheets.build_service('service_account.json')
spreadsheet_id = '1abc...'
sheet_id = 0

---

GoogleSheets methods

build_service(path_to_creds: str = 'service_account.json') -> 'Resource'

Create an authorized Google Sheets service client.

service = GoogleSheets.build_service('service_account.json')

create_spreadsheet(title: str, folder_id: str = None, editing_permissions_for_everyone: bool = False, emails: list[str] = None, email: str = None, path_to_creds: str = 'service_account.json') -> tuple[str, str]

Create a new spreadsheet and optionally share it. editing_permissions_for_everyone=True will allow anyone with the link to edit, while emails and email parameters can be used to share with specific users or groups. You can also specify a folder_id to create the spreadsheet in a specific Google Drive folder.

Important: this method may not work with service accounts created after May 2024 due to Google's updated security policies.

spreadsheet_id, url = GoogleSheets.create_spreadsheet(
    title='Monthly Sales Report',
    folder_id='1a2b3c4d5e6f7g8h9i0j',
    emails=['[e-mail]', '[e-mail]'],
    path_to_creds='service_account.json',
)

update_spreadsheet(spreadsheet_id: str, api_requests: list[dict], service: 'Resource') -> None

Execute a batchUpdate with one or more request objects.

from google_sheets import ApiRequest

requests = [
    ApiRequest.update_cells(sheet_id=0, range_='A1:C1', values=['Product', 'Qty', 'Revenue']),
]
GoogleSheets.update_spreadsheet(spreadsheet_id, requests, service)

copy_sheet(source_spreadsheet_id: str, source_sheet_id: str, destination_spreadsheet_id: str, service: 'Resource') -> SheetProperties

Copy a sheet from one spreadsheet into another.

GoogleSheets.copy_sheet(
    source_spreadsheet_id='1source...',
    source_sheet_id=0,
    destination_spreadsheet_id='1destination...',
    service=service,
)

get_spreadsheet(spreadsheet_id: str, service: 'Resource') -> Spreadsheet

Fetch spreadsheet metadata (sheets, properties, etc.).

spreadsheet = GoogleSheets.get_spreadsheet(spreadsheet_id, service)
print([sheet.properties.title for sheet in spreadsheet.sheets])

get_spreadsheet_range_values(spreadsheet_id: str, sheets: list[str | int], ranges: list[list[str]], service: 'Resource') -> list[list[SimpleType] | list[list[SimpleType]]]

Read values from multiple ranges across one or more sheets.

values = GoogleSheets.get_spreadsheet_range_values(
    spreadsheet_id=spreadsheet_id,
    sheets=[0, 'Summary'],
    ranges=[['A2:C8'], ['B2:B12']],
    service=service,
)
print(values)

---

ApiRequest methods for batchUpdate

Each method below returns a request dict that you can pass to GoogleSheets.update_spreadsheet(...).

Cell updates

update_cells(sheet_id: int, range_: str, values: list[list[int | float | bool | str]] | list[int | float | bool | str] = None, cell_formats: list[list[CellFormat]] | list[CellFormat] = None) -> dict

Write values, formats, or both.

from google_sheets import ApiRequest, CellFormat, TextFormat

req = ApiRequest.update_cells(
    sheet_id=0,
    range_='A1:B2',
    values=[['Plan', 120], ['Actual', 135]],
    cell_formats=[
        [CellFormat(text_format=TextFormat(bold=True)), None],
        [CellFormat(text_format=TextFormat(italic=True)), None],
    ],
)

Conditional formatting

add_boolean_format_rule(*, sheet_id: int, ranges: list[str], condition_type: ConditionType, condition_values: list[ConditionValue] = None, cell_format: CellFormat) -> dict

Add a rule (for example, highlight values greater than 100).

from google_sheets import ConditionType, ConditionValue, CellFormat, Color_, TextFormat

req = ApiRequest.add_boolean_format_rule(
    sheet_id=0,
    ranges=['C2:C100'],
    condition_type=ConditionType.NUMBER_GREATER,
    condition_values=[ConditionValue(user_entered_value=100)],
    cell_format=CellFormat(
        background_color_style=Color_.ConditionalFormatting.GREEN,
        text_format=TextFormat(bold=True),
    ),
)

GradientRule.add(*, sheet_id: int, ranges: list[str], interpolation_points: tuple[IPTypeAndValue, IPTypeAndValue] | tuple[IPTypeAndValue, IPTypeAndValue, IPTypeAndValue], interpolation_point_colors: tuple[ColorStyle, ColorStyle] | tuple[ColorStyle, ColorStyle, ColorStyle]) -> dict

Add a custom color scale with 2 or 3 interpolation points.

from google_sheets import InterpolationPointType, Color_

req = ApiRequest.GradientRule.add(
    sheet_id=0,
    ranges=['D2:D100'],
    interpolation_points=(
        (InterpolationPointType.MIN, None),
        (InterpolationPointType.NUMBER, 150),
        (InterpolationPointType.MAX, None),
    ),
    interpolation_point_colors=(
        Color_('#2ca958'),
        Color_.Basic.LIGHT_YELLOW_3,
        Color_.ConditionalFormatting.RED,
    )
)

GradientRule.add_preset(*, sheet_id: int, ranges: list[str], preset: Preset) -> dict

Add a gradient rule from a built-in preset.

req = ApiRequest.GradientRule.add_preset(
    sheet_id=0,
    ranges=['E2:E100'],
    preset=ApiRequest.GradientRule.Preset.RED_YELLOW_GREEN_PERCENTILE,
)

delete_conditional_format_rule(*, sheet_id: int, index: int) -> dict

Delete a conditional format rule by index.

req = ApiRequest.delete_conditional_format_rule(sheet_id=0, index=0)

update_conditional_format_rule(*, sheet_id: int, index: int, rule: ConditionalFormatRule) -> dict

Replace an existing conditional format rule by index.

from google_sheets import GoogleSheets, ApiRequest
from google_sheets import (
    ConditionalFormatRule,
    GradientRule,
    InterpolationPoint,
    InterpolationPointType,
    BooleanRule,
    BooleanCondition,
    ConditionType,
    ConditionValue,
    GridRange,
    CellFormat,
    TextFormat,
    Color_,
)

rule1 = ConditionalFormatRule(
    ranges=[GridRange(sheet_id=0, **ApiRequest._split_excel_range('A1:A1000',return_as_dict=True))],
    boolean_rule=BooleanRule(
        condition=BooleanCondition(
            type=ConditionType.NUMBER_NOT_BETWEEN,
            values=[ConditionValue(user_entered_value=10), ConditionValue(user_entered_value=30)]
        ),
        format=CellFormat(
            background_color_style=Color_.ConditionalFormatting.GREEN,
            text_format=TextFormat(bold=True)
        ),
    )
)
rule2 = ConditionalFormatRule(
    ranges=[GridRange(sheet_id=0, **ApiRequest._split_excel_range('A1:A1000',return_as_dict=True))],
    gradient_rule=GradientRule(
        minpoint=InterpolationPoint(
            color_style=Color_.ConditionalFormatting.GREEN,
            type=InterpolationPointType.MIN,
        ),
        midpoint=InterpolationPoint(
            color_style=Color_.ConditionalFormatting.YELLOW,
            type=InterpolationPointType.PERCENTILE,
            value=50,
        ),
        maxpoint=InterpolationPoint(
            color_style=Color_.ConditionalFormatting.RED,
            type=InterpolationPointType.MAX,
        ),
    )
)

req1 = ApiRequest.update_conditional_format_rule(sheet_id=0, index=0, rule=rule1)
req2 = ApiRequest.update_conditional_format_rule(sheet_id=0, index=1, rule=rule2)

GoogleSheets.update_spreadsheet(SPREADSHEET_ID, api_requests=[req1, req2], service=service)

Sheet-level operations

update_sheet_title(sheet_id: int, title: str) -> dict

Rename a sheet.

req = ApiRequest.update_sheet_title(sheet_id=0, title='June 2026')

remove_grid(sheet_id: int) -> dict

Hide grid lines on a sheet.

req = ApiRequest.remove_grid(sheet_id=0)

set_sheet_size(sheet_id: int, rows: int = None, columns: int = None) -> dict

Resize a sheet.

req = ApiRequest.set_sheet_size(sheet_id=0, rows=200, columns=12)

delete_sheet(sheet_id: int) -> dict

Delete a sheet.

req = ApiRequest.delete_sheet(sheet_id=3)

add_sheet(*, sheet_id: int = None, title: str = None, index: int = None, hidden: bool = None, row_count: int = None, column_count: int = None, frozen_row_count: int = None, frozen_column_count: int = None, hide_grid_lines: bool = None) -> dict

Create a new sheet with optional properties.

req = ApiRequest.add_sheet(
    title='Archive',
    row_count=100,
    column_count=8,
    hide_grid_lines=True,
)

Merge and freeze

merge_cells(sheet_id: int, range_: str, merge_type: MergeType = MergeType.MERGE_ALL) -> dict

Merge cells in a range.

req = ApiRequest.merge_cells(sheet_id=0, range_='A1:C1')

unmerge_cells(sheet_id: int, range_: str = None, start_row: int = None, end_row: int = None, start_column: int | str = None, end_column: int | str = None) -> dict

Unmerge cells in a range (or by explicit indexes).

req = ApiRequest.unmerge_cells(sheet_id=0, range_='A1:C1')

freeze(sheet_id: int, rows: int = 0, columns: int = 0) -> dict

Freeze top rows and/or left columns.

req = ApiRequest.freeze(sheet_id=0, rows=1, columns=1)

Rows and columns

insert_rows(sheet_id: int, start_index: int, end_index: int = None, inherit_from_before: bool = True) -> dict

Insert one or more rows (zero-based indexes).

req = ApiRequest.insert_rows(sheet_id=0, start_index=5, end_index=9)

insert_columns(sheet_id: int, start_index: int, end_index: int = None, inherit_from_before: bool = True) -> dict

Insert columns (zero-based indexes).

req = ApiRequest.insert_columns(sheet_id=0, start_index=2, end_index=3)

clear_columns(sheet_id: int, rows_count: int, start_index: int, end_index: int = None) -> dict

Clear values and formatting in one or more columns.

req = ApiRequest.clear_columns(sheet_id=0, rows_count=500, start_index=4, end_index=5)

set_column_width(sheet_id: int, col_no_or_letter: int | str, width: int) -> dict

Set a specific column width in pixels.

req = ApiRequest.set_column_width(sheet_id=0, col_no_or_letter='B', width=220)

set_row_height(sheet_id: int, row_no: int, height: int) -> dict

Set a specific row height in pixels.

req = ApiRequest.set_row_height(sheet_id=0, row_no=1, height=36)

set_standard_cell_dimensions(sheet_id: int, rows: int, columns: int) -> tuple[dict, dict]

Apply default row/column dimensions to a full area.

row_req, col_req = ApiRequest.set_standard_cell_dimensions(
    sheet_id=0,
    rows=1000,
    columns=26,
)

add_dimension_group(sheet_id: int, dimension: Dimension, start_index: int, end_index: int) -> dict

Create an outline group for rows or columns.

from google_sheets import Dimension

req = ApiRequest.add_dimension_group(
    sheet_id=0,
    dimension=Dimension.ROWS,
    start_index=10,
    end_index=30,
)

delete_dimension_group(sheet_id: int, dimension: Dimension, start_index: int, end_index: int) -> dict

Remove an existing outline group.

req = ApiRequest.delete_dimension_group(
    sheet_id=0,
    dimension=Dimension.ROWS,
    start_index=10,
    end_index=30,
)

---

Utility helpers

get_spreadsheet_id_from_url(url: str) -> str

Extract a spreadsheet ID from a full Google Sheets URL.

from google_sheets import get_spreadsheet_id_from_url

spreadsheet_id = get_spreadsheet_id_from_url(
    "https://docs.google.com/spreadsheets/d/1abcDEFghiJKLmnoPQRstuVWXyz/edit?gid=0#gid=0"
)

col_num_to_letter(col_num: int) -> str

Convert a 1-based column number to letters.

from google_sheets import col_num_to_letter

print(col_num_to_letter(28))  # AB

col_letter_to_num(col_letter: str) -> int

Convert column letters to a 1-based number.

from google_sheets import col_letter_to_num

print(col_letter_to_num('AB'))  # 28

rowcol_to_a1(row: int, col: int) -> str

Convert (row, col) into A1 notation.

from google_sheets import rowcol_to_a1

print(rowcol_to_a1(7, 3))  # C7

a1_to_rowcol(a1: str) -> tuple[int, int]

Convert A1 notation into (row, col).

from google_sheets import a1_to_rowcol

print(a1_to_rowcol('C7'))  # (7, 3)

float_sum(*floats: float) -> float

Sum floating-point numbers with high precision using the Decimal class.

from google_sheets import float_sum

print(float_sum(0.1, 0.2, 0.3))  # 0.6

---

Full example: send multiple requests at once

requests = [
    ApiRequest.update_cells(sheet_id=0, range_='A1', values=['KPI']),
    ApiRequest.freeze(sheet_id=0, rows=1),
    ApiRequest.set_column_width(sheet_id=0, col_no_or_letter=1, width=240),
]
GoogleSheets.update_spreadsheet(spreadsheet_id, requests, service)

---

Support & Feedback

If you have any questions, encounter issues, or want to suggest improvements:

• Bug Reports & Feature Requests: Please use GitHub Issues to report bugs or submit ideas.
• Questions: For general inquiries or help with integration, feel free to reach out via email at [e-mail].