tessl/pypi-gspread
Google Spreadsheets Python API - Simple interface for working with Google Sheets
data-access.mddocs/
Data Access & Manipulation
The Worksheet class provides comprehensive methods for reading, writing, and manipulating cell data and ranges within individual worksheets.
Capabilities
Reading Data
Access cell values and ranges with various formatting options.
class Worksheet:
def get_all_values(value_render_option: str = "FORMATTED_VALUE",
date_time_render_option: str = "SERIAL_NUMBER",
major_dimension: str = "ROWS") -> List[List]:
"""
Get all values from worksheet as 2D list.
Parameters:
- value_render_option (str): "FORMATTED_VALUE", "UNFORMATTED_VALUE", or "FORMULA". Default: "FORMATTED_VALUE".
- date_time_render_option (str): "SERIAL_NUMBER" or "FORMATTED_STRING". Default: "SERIAL_NUMBER".
- major_dimension (str): "ROWS" or "COLUMNS". Default: "ROWS".
Returns:
List[List]: 2D array of cell values.
"""
def get_values(range_name: str = None, major_dimension: str = "ROWS",
value_render_option: str = "FORMATTED_VALUE",
date_time_render_option: str = "SERIAL_NUMBER",
maintain_size: bool = False) -> List[List]:
"""
Get values from specified range or entire worksheet.
Parameters:
- range_name (str, optional): A1 notation range. If None, gets all values.
- 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".
- maintain_size (bool): Maintain original grid size. Default: False.
Returns:
List[List]: 2D array of cell values.
"""
def get(range_name: str, major_dimension: str = "ROWS",
value_render_option: str = "FORMATTED_VALUE",
date_time_render_option: str = "SERIAL_NUMBER") -> List[List]:
"""
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:
List[List]: 2D array of cell values.
"""
def acell(label: str, value_render_option: str = "FORMATTED_VALUE") -> Cell:
"""
Get cell by A1 notation.
Parameters:
- label (str): A1 notation cell address (e.g., "A1", "B5").
- value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
Returns:
Cell: Cell instance with coordinate and value information.
"""
def cell(row: int, col: int, value_render_option: str = "FORMATTED_VALUE") -> Cell:
"""
Get cell by row and column coordinates (1-indexed).
Parameters:
- row (int): Row number (1-indexed).
- col (int): Column number (1-indexed).
- value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
Returns:
Cell: Cell instance with coordinate and value information.
"""
def row_values(row: int, value_render_option: str = "FORMATTED_VALUE") -> List:
"""
Get all values from specified row.
Parameters:
- row (int): Row number (1-indexed).
- value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
Returns:
List: List of cell values from the row.
"""
def col_values(col: int, value_render_option: str = "FORMATTED_VALUE") -> List:
"""
Get all values from specified column.
Parameters:
- col (int): Column number (1-indexed).
- value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
Returns:
List: List of cell values from the column.
"""Finding and Searching
Locate cells by value or pattern matching.
class Worksheet:
def find(query: str, in_row: int = None, in_column: int = None, case_sensitive: bool = True) -> Cell:
"""
Find first cell matching query.
Parameters:
- query (str): Text or regular expression to search for.
- in_row (int, optional): Limit search to specific row.
- in_column (int, optional): Limit search to specific column.
- case_sensitive (bool): Case-sensitive search. Default: True.
Returns:
Cell: First matching cell.
Raises:
CellNotFound: If no matching cell is found.
"""
def find_all(query: str, in_row: int = None, in_column: int = None, case_sensitive: bool = True) -> List[Cell]:
"""
Find all cells matching query.
Parameters:
- query (str): Text or regular expression to search for.
- in_row (int, optional): Limit search to specific row.
- in_column (int, optional): Limit search to specific column.
- case_sensitive (bool): Case-sensitive search. Default: True.
Returns:
List[Cell]: List of matching cells.
"""
def findall(query: str, in_row: int = None, in_column: int = None) -> List[Cell]:
"""
Find all cells matching query (deprecated, use find_all).
Parameters:
- query (str): Text or regular expression to search for.
- in_row (int, optional): Limit search to specific row.
- in_column (int, optional): Limit search to specific column.
Returns:
List[Cell]: List of matching cells.
"""Writing and Updating Data
Update cell values and ranges with various input options.
class Worksheet:
def update(range_name: str = None, values: List[List] = None, value_input_option: str = "RAW",
major_dimension: str = None, include_values_in_response: bool = None,
response_value_render_option: str = None, response_date_time_render_option: str = None) -> Dict:
"""
Update values in specified range or entire worksheet.
Parameters:
- range_name (str, optional): A1 notation range. If None, starts from A1.
- values (List[List], optional): 2D array of values to update.
- value_input_option (str): "RAW" or "USER_ENTERED". Default: "RAW".
- major_dimension (str, optional): "ROWS" or "COLUMNS".
- include_values_in_response (bool, optional): Include updated values in response.
- response_value_render_option (str, optional): Value rendering for response.
- response_date_time_render_option (str, optional): Date/time rendering for response.
Returns:
Dict: Response from update operation.
"""
def update_acell(label: str, value: Any) -> Cell:
"""
Update single cell by A1 notation.
Parameters:
- label (str): A1 notation cell address (e.g., "A1", "B5").
- value (Any): Value to set in the cell.
Returns:
Cell: Updated cell instance.
"""
def update_cell(row: int, col: int, value: Any) -> Cell:
"""
Update single cell by row and column coordinates.
Parameters:
- row (int): Row number (1-indexed).
- col (int): Column number (1-indexed).
- value (Any): Value to set in the cell.
Returns:
Cell: Updated cell instance.
"""
def update_cells(range_name: str, values: List[List], value_input_option: str = "RAW") -> Dict:
"""
Update multiple cells in specified range.
Parameters:
- range_name (str): A1 notation range.
- values (List[List]): 2D array of values.
- value_input_option (str): "RAW" or "USER_ENTERED". Default: "RAW".
Returns:
Dict: Response from update operation.
"""Appending Data
Add new data to existing ranges.
class Worksheet:
def append_row(values: List, value_input_option: str = "RAW", insert_data_option: str = "INSERT_ROWS",
table_range: str = None, include_values_in_response: bool = False) -> Dict:
"""
Append single row of data.
Parameters:
- values (List): List of values for the new row.
- value_input_option (str): "RAW" or "USER_ENTERED". Default: "RAW".
- insert_data_option (str): "OVERWRITE" or "INSERT_ROWS". Default: "INSERT_ROWS".
- table_range (str, optional): A1 notation range to append to.
- include_values_in_response (bool): Include updated values in response. Default: False.
Returns:
Dict: Response from append operation.
"""
def append_rows(values: List[List], value_input_option: str = "RAW", insert_data_option: str = "INSERT_ROWS",
table_range: str = None, include_values_in_response: bool = False) -> Dict:
"""
Append multiple rows of data.
Parameters:
- values (List[List]): 2D array of values for new rows.
- value_input_option (str): "RAW" or "USER_ENTERED". Default: "RAW".
- insert_data_option (str): "OVERWRITE" or "INSERT_ROWS". Default: "INSERT_ROWS".
- table_range (str, optional): A1 notation range to append to.
- include_values_in_response (bool): Include updated values in response. Default: False.
Returns:
Dict: Response from append operation.
"""Batch Operations
Perform multiple operations efficiently.
class Worksheet:
def batch_get(ranges: List[str], value_render_option: str = "FORMATTED_VALUE",
date_time_render_option: str = "SERIAL_NUMBER", major_dimension: str = "ROWS") -> List[List]:
"""
Get multiple ranges in single request.
Parameters:
- ranges (List[str]): List of A1 notation ranges.
- value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
- date_time_render_option (str): Date/time rendering option. Default: "SERIAL_NUMBER".
- major_dimension (str): "ROWS" or "COLUMNS". Default: "ROWS".
Returns:
List[List]: List of value arrays for each range.
"""
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.
"""
def batch_update(body: Dict) -> Dict:
"""
Execute batch update request.
Parameters:
- body (Dict): Batch update request body.
Returns:
Dict: Response from batch update operation.
"""Data Processing
Work with structured data and records.
class Worksheet:
def get_all_records(empty_value: str = "", head: int = 1, expected_headers: List[str] = None,
default_blank: str = "", allow_underscores_in_numeric_literals: bool = False,
numericise_ignore: List[str] = None, value_render_option: str = "FORMATTED_VALUE") -> List[Dict]:
"""
Get all records as list of dictionaries using first row as headers.
Parameters:
- empty_value (str): Value to use for empty cells. Default: "".
- head (int): Row number containing headers (1-indexed). Default: 1.
- expected_headers (List[str], optional): List of expected header names.
- default_blank (str): Default value for blank cells. Default: "".
- allow_underscores_in_numeric_literals (bool): Allow underscores in numbers. Default: False.
- numericise_ignore (List[str], optional): Headers to not convert to numbers.
- value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
Returns:
List[Dict]: List of dictionaries with headers as keys.
"""Clearing Data
Remove values from cells and ranges.
class Worksheet:
def clear() -> Dict:
"""
Clear all values from worksheet.
Returns:
Dict: Response from clear operation.
"""Usage examples:
# Get all values
all_data = worksheet.get_all_values()
# Get specific range
range_data = worksheet.get('A1:C10')
# Get single cell
cell = worksheet.acell('B5')
print(f"Value: {cell.value}, Row: {cell.row}, Col: {cell.col}")
# Find cells
found_cell = worksheet.find('Alice')
all_matches = worksheet.find_all('.*@gmail\.com', case_sensitive=False)
# Update single cell
worksheet.update_acell('A1', 'New Value')
# Update range
worksheet.update('A1:C2', [
['Name', 'Age', 'City'],
['Alice', 25, 'NYC']
])
# Append data
worksheet.append_row(['Bob', 30, 'SF'])
# Get records as dictionaries
records = worksheet.get_all_records()
for record in records:
print(f"Name: {record['Name']}, Age: {record['Age']}")Install with Tessl CLI
npx tessl i tessl/pypi-gspread