tessl/pypi-gspread
Google Spreadsheets Python API - Simple interface for working with Google Sheets
formatting.mddocs/
Formatting & Display
Methods for controlling visual appearance including cell formatting, merging, colors, notes, and display options.
Capabilities
Cell and Range Formatting
Apply formatting to cells and ranges.
class Worksheet:
def format(ranges: str, format: Dict) -> Dict:
"""
Apply formatting to specified range.
Parameters:
- ranges (str): A1 notation range to format.
- format (Dict): Formatting specification following Google Sheets API format.
Returns:
Dict: Response from format operation.
"""
def batch_format(formats: List[Dict]) -> Dict:
"""
Apply multiple formats in single request.
Parameters:
- formats (List[Dict]): List of format requests.
Returns:
Dict: Response from batch format operation.
"""Cell Merging
Merge and unmerge cells.
class Worksheet:
def merge_cells(name: str, merge_type: str = "MERGE_ALL") -> Dict:
"""
Merge cells in specified range.
Parameters:
- name (str): A1 notation range to merge.
- merge_type (str): Type of merge ("MERGE_ALL", "MERGE_COLUMNS", "MERGE_ROWS"). Default: "MERGE_ALL".
Returns:
Dict: Response from merge operation.
"""
def unmerge_cells(name: str) -> Dict:
"""
Unmerge cells in specified range.
Parameters:
- name (str): A1 notation range to unmerge.
Returns:
Dict: Response from unmerge operation.
"""Tab Appearance
Control worksheet tab appearance.
class Worksheet:
def update_tab_color(color: Dict) -> Dict:
"""
Update worksheet tab color.
Parameters:
- color (Dict): Color specification with 'red', 'green', 'blue' keys (0-1 values).
Returns:
Dict: Response from color update.
"""Notes and Comments
Add and manage cell notes.
class Worksheet:
def get_notes(default_empty_value: str = "", grid_range: str = None) -> List[List[str]]:
"""
Returns a list of lists containing all notes in the sheet or range.
Parameters:
- default_empty_value (str): Determines which value to use for cells without notes. Default: "".
- grid_range (str, optional): Range name in A1 notation, e.g. 'A1:A5'. If None, gets all notes.
Returns:
List[List[str]]: List of lists containing all notes in the sheet or range.
"""
def update_notes(notes: Dict[str, str]) -> None:
"""
Update multiple notes. The notes are attached to a certain cell.
Parameters:
- notes (Dict[str, str]): A dict of notes with their cells coordinates (A1 format) and respective content.
Returns:
None
"""
def update_note(cell: str, content: str) -> None:
"""
Update the content of the note located at cell.
Parameters:
- cell (str): A string with cell coordinates in A1 notation, e.g. 'D7'.
- content (str): The text note to insert.
Returns:
None
"""
def insert_notes(notes: Dict[str, str]) -> None:
"""
Insert multiple notes. The notes are attached to a certain cell.
Parameters:
- notes (Dict[str, str]): A dict of notes with their cells coordinates (A1 format) and respective content.
Returns:
None
"""
def clear_notes(ranges: List[str]) -> None:
"""
Clear all notes located at the coordinates pointed to by ranges.
Parameters:
- ranges (List[str]): List of A1 coordinates where to clear the notes, e.g. ["A1", "GH42", "D7"].
Returns:
None
"""
def clear_note(cell: str) -> None:
"""
Clear a note. The note is attached to a certain cell.
Parameters:
- cell (str): A string with cell coordinates in A1 notation, e.g. 'D7'.
Returns:
None
"""
def batch_merge(merges: List[Dict], merge_type: str = "MERGE_ALL") -> Any:
"""
Merge multiple ranges at the same time.
Parameters:
- merges (List[Dict]): List of dictionaries with the ranges (A1-notation) and optional 'mergeType' field.
- merge_type (str): Default merge type for all merges missing the mergeType. Default: "MERGE_ALL".
Returns:
Any: The body of the request response.
"""Data Organization
Sort and filter data.
class Worksheet:
def sort(specs: List[Dict], range: str = None) -> Dict:
"""
Sort data by specified criteria.
Parameters:
- specs (List[Dict]): List of sort specifications with 'dimension', 'sortOrder' keys.
- range (str, optional): A1 notation range to sort. If None, sorts entire sheet.
Returns:
Dict: Response from sort operation.
"""
def set_basic_filter(name: str = None) -> Dict:
"""
Set basic filter on data range.
Parameters:
- name (str, optional): A1 notation range for filter. If None, applies to entire sheet.
Returns:
Dict: Response from filter setup.
"""Protected Ranges
Create and manage protected ranges for access control.
class Worksheet:
def add_protected_range(name: str, editor_users_emails: List[str] = None,
editor_groups_emails: List[str] = None, description: str = None,
warning_only: bool = False, requesting_user_can_edit: bool = False) -> Dict:
"""
Add protected range to prevent unauthorized edits.
Parameters:
- name (str): A1 notation range to protect.
- editor_users_emails (List[str], optional): List of user emails who can edit.
- editor_groups_emails (List[str], optional): List of group emails who can edit.
- description (str, optional): Description of the protection.
- warning_only (bool): Show warning instead of preventing edits. Default: False.
- requesting_user_can_edit (bool): Allow requesting user to edit. Default: False.
Returns:
Dict: Response containing protection details.
"""
def delete_protected_range(protected_range_id: str) -> Dict:
"""
Remove protected range.
Parameters:
- protected_range_id (str): ID of protected range to delete.
Returns:
Dict: Response from deletion.
"""Export Options
Export worksheet with specific formatting.
class Worksheet:
def export(format: str, gid: int = None) -> str:
"""
Export worksheet in specified format.
Parameters:
- format (str): Export format ("csv", "tsv", "pdf", "zip").
- gid (int, optional): Grid ID. If None, uses worksheet's grid ID.
Returns:
str: Export URL or content.
"""Range Operations
Work with named ranges and value ranges.
class Worksheet:
def range(name: str) -> List[Cell]:
"""
Get range as list of Cell objects.
Parameters:
- name (str): A1 notation range.
Returns:
List[Cell]: List of Cell instances in the range.
"""Display Properties
class Worksheet:
@property
def hidden -> bool:
"""Whether worksheet is hidden."""
@property
def tab_color -> Dict:
"""Worksheet tab color specification."""Formatting Examples
Basic Cell Formatting
# Format text style
text_format = {
"textFormat": {
"bold": True,
"italic": False,
"fontSize": 12,
"fontFamily": "Arial"
}
}
worksheet.format('A1:C1', text_format)
# Format background color
bg_format = {
"backgroundColor": {
"red": 0.8,
"green": 0.9,
"blue": 1.0
}
}
worksheet.format('A1:A10', bg_format)
# Format numbers
number_format = {
"numberFormat": {
"type": "CURRENCY",
"pattern": "$#,##0.00"
}
}
worksheet.format('B1:B10', number_format)Advanced Formatting
# Comprehensive cell formatting
full_format = {
"backgroundColor": {"red": 0.9, "green": 0.9, "blue": 0.9},
"textFormat": {
"foregroundColor": {"red": 0, "green": 0, "blue": 0},
"fontSize": 11,
"bold": True
},
"borders": {
"top": {"style": "SOLID", "width": 1},
"bottom": {"style": "SOLID", "width": 1},
"left": {"style": "SOLID", "width": 1},
"right": {"style": "SOLID", "width": 1}
},
"horizontalAlignment": "CENTER",
"verticalAlignment": "MIDDLE"
}
worksheet.format('A1:Z1', full_format)
# Conditional formatting (via batch_format)
conditional_format = {
"requests": [{
"addConditionalFormatRule": {
"rule": {
"ranges": [{"sheetId": worksheet.id, "startRowIndex": 1, "endRowIndex": 100, "startColumnIndex": 0, "endColumnIndex": 5}],
"booleanRule": {
"condition": {
"type": "NUMBER_GREATER",
"values": [{"userEnteredValue": "100"}]
},
"format": {
"backgroundColor": {"red": 0.8, "green": 1, "blue": 0.8}
}
}
},
"index": 0
}
}]
}
worksheet.batch_format([conditional_format])Merging and Organization
# Merge header cells
worksheet.merge_cells('A1:E1', merge_type='MERGE_ALL')
# Set tab color
worksheet.update_tab_color({
'red': 0.2,
'green': 0.6,
'blue': 0.9
})
# Sort data by column A (ascending), then column B (descending)
sort_specs = [
{'dimension': 0, 'sortOrder': 'ASCENDING'}, # Column A
{'dimension': 1, 'sortOrder': 'DESCENDING'} # Column B
]
worksheet.sort(sort_specs, 'A2:E100')
# Add notes
worksheet.insert_note('A1', 'This is the header row')
# Protect important data
worksheet.add_protected_range(
'A1:E1',
editor_users_emails=['admin@company.com'],
description='Header row - protected from edits'
)Install with Tessl CLI
npx tessl i tessl/pypi-gspread