tessl/pypi-gspread
Google Spreadsheets Python API - Simple interface for working with Google Sheets
spreadsheet-management.mddocs/
Spreadsheet Management
The Spreadsheet class provides methods for managing worksheets, metadata, batch operations, and spreadsheet-level sharing controls.
Capabilities
Worksheet Management
Create, access, and manage worksheets within a spreadsheet.
class Spreadsheet:
def add_worksheet(title: str, rows: int = 100, cols: int = 26, index: int = None) -> Worksheet:
"""
Add new worksheet to spreadsheet.
Parameters:
- title (str): Title for the new worksheet.
- rows (int): Initial number of rows. Default: 100.
- cols (int): Initial number of columns. Default: 26.
- index (int, optional): Position to insert worksheet. If None, adds at end.
Returns:
Worksheet: Newly created worksheet instance.
"""
def del_worksheet(worksheet: Worksheet) -> None:
"""
Delete worksheet from spreadsheet.
Parameters:
- worksheet (Worksheet): Worksheet instance to delete.
Returns:
None
"""
def del_worksheet_by_id(worksheet_id: Union[str, int]) -> Any:
"""
Delete worksheet by ID.
Parameters:
- worksheet_id (Union[str, int]): The ID of the worksheet to delete.
Returns:
Any: The deletion response.
"""
def worksheet(title: str) -> Worksheet:
"""
Get worksheet by title.
Parameters:
- title (str): Exact worksheet title.
Returns:
Worksheet: Worksheet instance.
Raises:
WorksheetNotFound: If no worksheet with the given title exists.
"""
def get_worksheet(index: int) -> Worksheet:
"""
Get worksheet by index (0-based).
Parameters:
- index (int): Worksheet index.
Returns:
Worksheet: Worksheet instance.
Raises:
WorksheetNotFound: If index is out of range.
"""
def get_worksheet_by_id(id: int) -> Worksheet:
"""
Get worksheet by ID.
Parameters:
- id (int): Worksheet ID.
Returns:
Worksheet: Worksheet instance.
Raises:
WorksheetNotFound: If no worksheet with the given ID exists.
"""
def duplicate_sheet(source_sheet_id: int, insert_sheet_index: int = None,
new_sheet_id: int = None, new_sheet_name: str = None) -> Worksheet:
"""
Duplicate existing worksheet.
Parameters:
- source_sheet_id (int): ID of worksheet to duplicate.
- insert_sheet_index (int, optional): Position to insert duplicated sheet.
- new_sheet_id (int, optional): ID for new sheet.
- new_sheet_name (str, optional): Name for new sheet. If None, uses "Copy of [original name]".
Returns:
Worksheet: Duplicated worksheet instance.
"""
def reorder_worksheets(worksheets_in_desired_order: List[Worksheet]) -> None:
"""
Reorder worksheets.
Parameters:
- worksheets_in_desired_order (List[Worksheet]): List of worksheets in desired order.
Returns:
None
"""Batch Operations
Perform bulk operations for improved performance.
class Spreadsheet:
def batch_update(body: Dict) -> Dict:
"""
Execute batch update request.
Parameters:
- body (Dict): Batch update request body following Google Sheets API format.
Returns:
Dict: Response from batch update operation.
"""
def values_batch_get(ranges: List[str], major_dimension: str = "ROWS",
value_render_option: str = "FORMATTED_VALUE",
date_time_render_option: str = "SERIAL_NUMBER") -> Dict:
"""
Get multiple ranges in single request.
Parameters:
- ranges (List[str]): List of A1 notation ranges.
- major_dimension (str): "ROWS" or "COLUMNS". Default: "ROWS".
- value_render_option (str): How values should be represented. Default: "FORMATTED_VALUE".
- date_time_render_option (str): How dates/times should be represented. Default: "SERIAL_NUMBER".
Returns:
Dict: Response containing values for all requested ranges.
"""
def values_batch_update(body: Dict) -> Dict:
"""
Update multiple ranges in single request.
Parameters:
- body (Dict): Batch update values request body.
Returns:
Dict: Response from batch update operation.
"""
def batch_clear(ranges: List[str]) -> Dict:
"""
Clear multiple ranges in single request.
Parameters:
- ranges (List[str]): List of A1 notation ranges to clear.
Returns:
Dict: Response from clear operation.
"""Sharing and Permissions
Manage spreadsheet sharing and access permissions.
class Spreadsheet:
def share(value: str, perm_type: str = "user", role: str = "reader",
notify: bool = True, email_message: str = None, with_link: bool = False) -> Dict:
"""
Share spreadsheet with user, group, or make public.
Parameters:
- value (str): Email address, domain, or "anyone".
- perm_type (str): "user", "group", "domain", or "anyone". Default: "user".
- role (str): "owner", "writer", or "reader". Default: "reader".
- notify (bool): Send notification email. Default: True.
- email_message (str, optional): Custom notification message.
- with_link (bool): Grant access via link sharing. Default: False.
Returns:
Dict: Permission details.
"""
def list_permissions() -> List[Dict]:
"""
List all permissions for this spreadsheet.
Returns:
List[Dict]: List of permission details.
"""
def remove_permissions(permission_id: str) -> None:
"""
Remove permission by ID.
Parameters:
- permission_id (str): Permission ID to remove.
Returns:
None
"""
def transfer_ownership(permission_id: str) -> Dict:
"""
Transfer ownership to another user.
Parameters:
- permission_id (str): Permission ID of the new owner.
Returns:
Dict: Updated permission details.
"""Values Operations
Direct spreadsheet-level value operations that work across ranges.
class Spreadsheet:
def values_get(range_name: str, major_dimension: str = "ROWS",
value_render_option: str = "FORMATTED_VALUE",
date_time_render_option: str = "SERIAL_NUMBER") -> Dict:
"""
Get values from specified range.
Parameters:
- range_name (str): A1 notation range.
- major_dimension (str): "ROWS" or "COLUMNS". Default: "ROWS".
- value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
- date_time_render_option (str): Date/time rendering option. Default: "SERIAL_NUMBER".
Returns:
Dict: Response containing range values.
"""
def values_update(range_name: str, values: List[List], value_input_option: str = "RAW") -> Dict:
"""
Update values in specified range.
Parameters:
- range_name (str): A1 notation range.
- values (List[List]): 2D array of values to update.
- value_input_option (str): "RAW" or "USER_ENTERED". Default: "RAW".
Returns:
Dict: Response from update operation.
"""
def values_append(range_name: str, values: List[List], value_input_option: str = "RAW",
insert_data_option: str = "OVERWRITE", include_values_in_response: bool = False) -> Dict:
"""
Append values to range.
Parameters:
- range_name (str): A1 notation range.
- values (List[List]): 2D array of values to append.
- value_input_option (str): "RAW" or "USER_ENTERED". Default: "RAW".
- insert_data_option (str): "OVERWRITE" or "INSERT_ROWS". Default: "OVERWRITE".
- include_values_in_response (bool): Include updated values in response. Default: False.
Returns:
Dict: Response from append operation.
"""
def values_clear(range_name: str) -> Dict:
"""
Clear values in specified range.
Parameters:
- range_name (str): A1 notation range to clear.
Returns:
Dict: Response from clear operation.
"""Export Operations
Export spreadsheet content in various formats.
class Spreadsheet:
def export(format: str = "pdf") -> bytes:
"""
Export entire spreadsheet.
Parameters:
- format (str): Export format ("pdf", "xlsx", "ods", "csv", "tsv", "zip"). Default: "pdf".
Returns:
bytes: Exported file content.
"""Spreadsheet Properties
class Spreadsheet:
@property
def id -> str:
"""Spreadsheet ID."""
@property
def title -> str:
"""Spreadsheet title."""
@property
def url -> str:
"""Spreadsheet URL."""
@property
def creationTime -> str:
"""Creation timestamp."""
@property
def lastUpdateTime -> str:
"""Last update timestamp."""
@property
def locale -> str:
"""Spreadsheet locale setting."""
@property
def timezone -> str:
"""Spreadsheet timezone setting."""
@property
def sheet1 -> Worksheet:
"""First worksheet (convenience property)."""
@property
def worksheets_property -> List[Dict]:
"""List of worksheet metadata dictionaries."""Named Range Management
Create and manage named ranges within the spreadsheet.
class Spreadsheet:
def define_named_range(name: str, range_name: str) -> Dict:
"""
Define a named range in the spreadsheet.
Parameters:
- name (str): A string with range value in A1 notation, e.g. 'A1:A5'.
- range_name (str): The name to assign to the range of cells.
Returns:
Dict: The response body from the request.
"""
def delete_named_range(named_range_id: str) -> Dict:
"""
Delete a named range by its ID.
Parameters:
- named_range_id (str): The ID of the named range to delete. Can be obtained with list_named_ranges().
Returns:
Dict: The response body from the request.
"""Usage examples:
# Access spreadsheet
sheet = gc.open("My Spreadsheet")
# Create new worksheet
worksheet = sheet.add_worksheet("New Sheet", rows=50, cols=10)
# Get worksheet by name
ws = sheet.worksheet("Sheet1")
# Batch operations
batch_result = sheet.values_batch_get(['A1:B10', 'D1:F5'])
# Share spreadsheet
sheet.share('user@example.com', role='writer')
# Export as Excel
xlsx_data = sheet.export('xlsx')Install with Tessl CLI
npx tessl i tessl/pypi-gspread