tessl/pypi-pygsheets
Google Spreadsheets Python API v4
76
1.22x
cell-range-operations.mddocs/
Cell and Range Operations
Individual cell manipulation and range-based operations including formatting, formulas, and bulk data handling.
Capabilities
Cell Access and Manipulation
Work with individual cells including values, formulas, and formatting.
class Worksheet:
def cell(self, addr) -> Cell:
"""
Get cell object by address.
Parameters:
- addr (str): Cell address (e.g., 'A1', 'B2')
Returns:
Cell: Cell object for manipulation
"""
def range(self, name, returnas='cells') -> DataRange:
"""
Get cells in a given range.
Parameters:
- name (str): Range name or address (e.g., 'A1:C3', 'myRange')
- returnas (str): Return format ('cells', 'matrix', 'range')
Returns:
DataRange or other format based on returnas parameter
"""Cell Properties and Values
Access and modify cell properties, values, and metadata.
class Cell:
def __init__(self, pos, val='', worksheet=None, cell_data=None):
"""
Initialize cell object.
Parameters:
- pos (str or tuple): Cell position
- val: Initial value
- worksheet (Worksheet): Parent worksheet
- cell_data (dict): Cell data from API
"""
@property
def row(self) -> int:
"""Row number (1-indexed)."""
@property
def col(self) -> int:
"""Column number (1-indexed)."""
@property
def label(self) -> str:
"""Cell label (e.g., 'A1')."""
@property
def address(self) -> str:
"""Cell address."""
@property
def value(self):
"""Cell value (formatted)."""
@value.setter
def value(self, val):
"""Set cell value."""
@property
def value_unformatted(self):
"""Cell value (unformatted)."""
@property
def formula(self) -> str:
"""Cell formula."""
@formula.setter
def formula(self, formula):
"""Set cell formula."""
@property
def note(self) -> str:
"""Cell note/comment."""
@note.setter
def note(self, note):
"""Set cell note/comment."""Cell Formatting
Apply various formatting options to cells including text, number, and visual formatting.
class Cell:
def set_text_format(self, attribute, value):
"""
Set text formatting attribute.
Parameters:
- attribute (str): Format attribute ('bold', 'italic', 'underline', etc.)
- value: Attribute value
Returns:
Cell: Self for method chaining
"""
def set_number_format(self, format_type, pattern=None):
"""
Set number formatting.
Parameters:
- format_type (FormatType): Number format type
- pattern (str): Custom format pattern
Returns:
Cell: Self for method chaining
"""
def set_text_rotation(self, angle):
"""
Set text rotation angle.
Parameters:
- angle (int): Rotation angle in degrees
Returns:
Cell: Self for method chaining
"""
def set_horizontal_alignment(self, alignment):
"""
Set horizontal text alignment.
Parameters:
- alignment (HorizontalAlignment): Alignment option
Returns:
Cell: Self for method chaining
"""
def set_vertical_alignment(self, alignment):
"""
Set vertical text alignment.
Parameters:
- alignment (VerticalAlignment): Alignment option
Returns:
Cell: Self for method chaining
"""
@property
def color(self) -> tuple:
"""Cell background color as RGB tuple."""
@color.setter
def color(self, color_value):
"""Set cell background color."""Cell Operations
Perform operations on cells including updates, linking, and navigation.
class Cell:
def update(self, force=False):
"""
Apply pending changes to cell.
Parameters:
- force (bool): Force update even if no changes detected
"""
def refresh(self):
"""Refresh cell data from API."""
def fetch(self):
"""Fetch latest cell data from API."""
def neighbour(self, direction) -> Cell:
"""
Get neighboring cell.
Parameters:
- direction (str): Direction ('right', 'left', 'up', 'down')
Returns:
Cell: Neighboring cell object
"""
def link(self, worksheet=None, update=True):
"""
Link cell to worksheet for automatic updates.
Parameters:
- worksheet (Worksheet): Worksheet to link to
- update (bool): Whether to update immediately
"""
def unlink(self):
"""Unlink cell from worksheet."""Data Range Operations
Work with ranges of cells for bulk operations and advanced data manipulation.
class DataRange:
def __init__(self, start=None, end=None, worksheet=None, name='', data=None, name_id=None, namedjson=None, protectedjson=None, grange=None):
"""
Initialize data range object.
Parameters:
- namedjson (dict): Named range JSON data
- name_id (str): Named range ID
- worksheet (Worksheet): Parent worksheet
- protectedjson (dict): Protected range JSON data
- protect_id (str): Protected range ID
"""
@property
def name(self) -> str:
"""Range name."""
@name.setter
def name(self, name):
"""Set range name."""
@property
def protected(self) -> bool:
"""Whether range is protected."""
@property
def start_addr(self) -> Address:
"""Start address of range."""
@property
def end_addr(self) -> Address:
"""End address of range."""
@property
def range(self) -> str:
"""Range address string."""
@property
def cells(self) -> list:
"""List of Cell objects in range."""
def update_values(self, values, **kwargs):
"""
Update values in the range.
Parameters:
- values: Data to update (list of lists)
- **kwargs: Additional options
"""
def apply_format(self, cell_list, fields="userEnteredFormat"):
"""
Apply formatting to range.
Parameters:
- cell_list: List of Cell objects with formatting
- fields (str): Fields to update
"""
def sort(self, basecolumnindex=0, sortorder="ASCENDING"):
"""
Sort range by column.
Parameters:
- basecolumnindex (int): Column index to sort by
- sortorder (str): Sort order ('ASCENDING' or 'DESCENDING')
"""
def clear(self, fields="userEnteredValue"):
"""
Clear range contents.
Parameters:
- fields (str): Fields to clear
"""Address Utilities
Flexible address representation and manipulation for cells and ranges.
class Address:
def __init__(self, label=None, row=None, col=None, index=1):
"""
Initialize address object.
Parameters:
- label (str): Cell label (e.g., 'A1')
- row (int): Row number
- col (int): Column number
- index (int): Base index (0 or 1)
"""
@property
def label(self) -> str:
"""Address label (e.g., 'A1')."""
@property
def row(self) -> int:
"""Row number."""
@property
def col(self) -> int:
"""Column number."""
@property
def index(self) -> tuple:
"""Address as (row, col) tuple."""
class GridRange:
def __init__(self, label=None, start=None, end=None, worksheet=None):
"""
Initialize grid range object.
Parameters:
- label (str): Range label (e.g., 'A1:C3')
- start (Address): Start address
- end (Address): End address
- worksheet (Worksheet): Parent worksheet
"""
@staticmethod
def create(start, end=None, worksheet=None) -> GridRange:
"""
Create GridRange from addresses.
Parameters:
- start (str or Address): Start address
- end (str or Address): End address
- worksheet (Worksheet): Parent worksheet
Returns:
GridRange: New grid range object
"""
@property
def start(self) -> Address:
"""Start address."""
@property
def end(self) -> Address:
"""End address."""
@property
def label(self) -> str:
"""Range label."""
@property
def height(self) -> int:
"""Range height in rows."""
@property
def width(self) -> int:
"""Range width in columns."""Usage Examples
Basic Cell Operations
import pygsheets
# Get worksheet
gc = pygsheets.authorize()
sh = gc.open('My Spreadsheet')
wks = sh.sheet1
# Get cell and set value
cell = wks.cell('A1')
cell.value = 'Hello World'
cell.update()
# Method chaining for formatting
wks.cell('B1').set_text_format('bold', True).value = 'Bold Text'
# Set formula
wks.cell('C1').formula = '=SUM(A1:A10)'
# Add note/comment
wks.cell('D1').note = 'This is a comment'
# Format cell
cell = wks.cell('E1')
cell.value = 1234.56
cell.set_number_format(pygsheets.FormatType.CURRENCY)
cell.color = (1.0, 0.8, 0.8) # Light red background
cell.update()Range Operations
# Get range of cells
cells_range = wks.range('A1:C3', returnas='range')
# Set range name
cells_range.name = 'my_data'
# Update range values
data = [['Name', 'Age', 'City'],
['Alice', 25, 'NYC'],
['Bob', 30, 'LA']]
cells_range.update_values(data)
# Apply formatting to range
header_cells = wks.range('A1:C1', returnas='cells')
for cell in header_cells:
cell.set_text_format('bold', True)
cell.color = (0.8, 0.8, 1.0) # Light blue
cells_range.apply_format(header_cells)
# Sort range
cells_range.sort(basecolumnindex=1, sortorder="DESCENDING") # Sort by age column
# Clear range
cells_range.clear()Address Manipulation
# Create addresses
addr1 = pygsheets.Address('A1')
addr2 = pygsheets.Address(row=5, col=3) # C5
# Create grid range
grid_range = pygsheets.GridRange.create('A1', 'C5')
print(f"Range: {grid_range.label}") # A1:C5
print(f"Size: {grid_range.width}x{grid_range.height}") # 3x5
# Use with worksheet
cells = wks.range(grid_range.label, returnas='cells')Types
Alignment Types
class HorizontalAlignment:
LEFT = 'LEFT'
RIGHT = 'RIGHT'
CENTER = 'CENTER'
NONE = 'NONE'
class VerticalAlignment:
TOP = 'TOP'
MIDDLE = 'MIDDLE'
BOTTOM = 'BOTTOM'
NONE = 'NONE'Format Types
class FormatType:
CUSTOM = 'CUSTOM'
TEXT = 'TEXT'
NUMBER = 'NUMBER'
PERCENT = 'PERCENT'
CURRENCY = 'CURRENCY'
DATE = 'DATE'
TIME = 'TIME'
DATE_TIME = 'DATE_TIME'
SCIENTIFIC = 'SCIENTIFIC'Install with Tessl CLI
npx tessl i tessl/pypi-pygsheetsdocs
evals
scenario-1
scenario-2
scenario-3
scenario-4
scenario-5
scenario-6
scenario-7
scenario-8
scenario-9
scenario-10