tessl/pypi-pygsheets
Google Spreadsheets Python API v4
76
1.22x
worksheet-operations.mddocs/
Worksheet Operations
Comprehensive worksheet management including data manipulation, structural operations, and formatting capabilities for individual sheets within a spreadsheet.
Capabilities
Reading Data
Read data from worksheets in various formats and ranges.
class Worksheet:
def get_value(self, addr, value_render_option=ValueRenderOption.FORMATTED_VALUE):
"""
Get value from a single cell.
Parameters:
- addr (str): Cell address (e.g., 'A1', 'B2')
- value_render_option (ValueRenderOption): How values should be represented
Returns:
Value from the cell (str, int, float, or None)
"""
def get_values(self, start, end, returnas='matrix', majdim='ROWS', include_tailing_empty=True,
include_tailing_empty_rows=False, value_render=ValueRenderOption.FORMATTED_VALUE,
date_time_render_option=DateTimeRenderOption.SERIAL_NUMBER, grange=None, **kwargs):
"""
Get values from a range of cells.
Parameters:
- start (str): Start cell address
- end (str): End cell address
- returnas (str): Return format ('matrix', 'cell', 'range')
- majdim (str): Major dimension ('ROWS' or 'COLUMNS')
- include_tailing_empty (bool): Include trailing empty cells
- include_tailing_empty_rows (bool): Include trailing empty rows
- value_render (ValueRenderOption): How values should be represented
- date_time_render_option (DateTimeRenderOption): How dates should be represented
- grange (GridRange): GridRange object instead of start/end
- **kwargs: Additional options
Returns:
Requested data in specified format
"""
def get_all_values(self, returnas='matrix', **kwargs):
"""
Get all values from the worksheet.
Parameters:
- returnas (str): Return format ('matrix', 'cell')
- **kwargs: Additional options
Returns:
All worksheet data in specified format
"""
def get_all_records(self, **kwargs) -> list:
"""
Get all data as list of dictionaries using first row as headers.
Parameters:
- **kwargs: Additional options (empty_value, head, etc.)
Returns:
list: List of dictionaries with column headers as keys
"""
def get_row(self, row, returnas='matrix', **kwargs):
"""
Get all values from a specific row.
Parameters:
- row (int): Row number (1-indexed)
- returnas (str): Return format
- **kwargs: Additional options
Returns:
Row data in specified format
"""
def get_col(self, col, returnas='matrix', **kwargs):
"""
Get all values from a specific column.
Parameters:
- col (int): Column number (1-indexed)
- returnas (str): Return format
- **kwargs: Additional options
Returns:
Column data in specified format
"""Writing Data
Update worksheet data with various methods for different data types and ranges.
class Worksheet:
def update_value(self, addr, val, **kwargs):
"""
Update value in a single cell.
Parameters:
- addr (str): Cell address (e.g., 'A1')
- val: Value to set
- **kwargs: Additional options
"""
def update_values(self, crange=None, values=None, **kwargs):
"""
Update values in a range of cells.
Parameters:
- crange (str): Range to update (e.g., 'A1:C3')
- values: Data to update (list of lists)
- **kwargs: Additional options (majordimension, value_input_option, etc.)
"""
def update_row(self, index, values, **kwargs):
"""
Update entire row with values.
Parameters:
- index (int): Row number (1-indexed)
- values (list): Values to set in row
- **kwargs: Additional options
"""
def update_col(self, index, values, **kwargs):
"""
Update entire column with values.
Parameters:
- index (int): Column number (1-indexed)
- values (list): Values to set in column
- **kwargs: Additional options
"""
def append_table(self, values, start='A1', **kwargs):
"""
Append data to worksheet as a table.
Parameters:
- values: Data to append (list of lists)
- start (str): Starting cell address
- **kwargs: Additional options (dimension, value_input_option, etc.)
"""Worksheet Structure
Manage worksheet structure including size, rows, columns, and layout.
class Worksheet:
def resize(self, rows=None, cols=None):
"""
Resize worksheet dimensions.
Parameters:
- rows (int): New number of rows
- cols (int): New number of columns
"""
def add_rows(self, rows):
"""
Add rows to worksheet.
Parameters:
- rows (int): Number of rows to add
"""
def add_cols(self, cols):
"""
Add columns to worksheet.
Parameters:
- cols (int): Number of columns to add
"""
def delete_rows(self, index, number=1):
"""
Delete rows from worksheet.
Parameters:
- index (int): Starting row index (1-indexed)
- number (int): Number of rows to delete
"""
def delete_cols(self, index, number=1):
"""
Delete columns from worksheet.
Parameters:
- index (int): Starting column index (1-indexed)
- number (int): Number of columns to delete
"""
def insert_rows(self, row, number=1, values=None, **kwargs):
"""
Insert rows into worksheet.
Parameters:
- row (int): Row index where to insert (1-indexed)
- number (int): Number of rows to insert
- values: Optional values for new rows
- **kwargs: Additional options
"""
def insert_cols(self, col, number=1, values=None, **kwargs):
"""
Insert columns into worksheet.
Parameters:
- col (int): Column index where to insert (1-indexed)
- number (int): Number of columns to insert
- values: Optional values for new columns
- **kwargs: Additional options
"""Worksheet Properties
Access and modify worksheet properties and metadata.
class Worksheet:
@property
def id(self) -> int:
"""Worksheet ID."""
@property
def index(self) -> int:
"""Worksheet index/position."""
@property
def title(self) -> str:
"""Worksheet title/name."""
@property
def url(self) -> str:
"""Worksheet URL."""
@property
def rows(self) -> int:
"""Number of rows in worksheet."""
@property
def cols(self) -> int:
"""Number of columns in worksheet."""
@property
def frozen_rows(self) -> int:
"""Number of frozen rows."""
@property
def frozen_cols(self) -> int:
"""Number of frozen columns."""
@property
def hidden(self) -> bool:
"""Whether worksheet is hidden."""
def adjust_column_width(self, start, end=None, pixel_size=100):
"""
Adjust column width.
Parameters:
- start (int): Starting column index
- end (int): Ending column index (None for single column)
- pixel_size (int): Width in pixels
"""
def adjust_row_height(self, start, end=None, pixel_size=100):
"""
Adjust row height.
Parameters:
- start (int): Starting row index
- end (int): Ending row index (None for single row)
- pixel_size (int): Height in pixels
"""Search and Replace
Find and replace data within worksheets.
class Worksheet:
def find(self, query, **kwargs):
"""
Find cells matching query.
Parameters:
- query (str): Search query
- **kwargs: Additional search options
Returns:
list: List of matching Cell objects
"""
def replace(self, find, replace, **kwargs):
"""
Replace all occurrences of text.
Parameters:
- find (str): Text to find
- replace (str): Replacement text
- **kwargs: Additional replace options
Returns:
int: Number of replacements made
"""DataFrame Integration
Seamless integration with pandas DataFrames for data analysis workflows.
class Worksheet:
def set_dataframe(self, df, start='A1', **kwargs):
"""
Set worksheet content from pandas DataFrame.
Parameters:
- df (pandas.DataFrame): DataFrame to write
- start (str): Starting cell address
- **kwargs: Additional options (copy_index, copy_head, etc.)
"""
def get_as_df(self, **kwargs):
"""
Get worksheet content as pandas DataFrame.
Parameters:
- **kwargs: Additional options (has_header, index_col, etc.)
Returns:
pandas.DataFrame: Worksheet data as DataFrame
"""Usage Examples
Basic Data Operations
import pygsheets
import pandas as pd
# Get worksheet
gc = pygsheets.authorize()
sh = gc.open('My Spreadsheet')
wks = sh.sheet1
# Read single value
value = wks.get_value('A1')
# Read range of values
values = wks.get_values('A1:C3')
# Read all values
all_data = wks.get_all_values()
# Read as records (first row as headers)
records = wks.get_all_records()
# Update single cell
wks.update_value('A1', 'Hello World')
# Update range
data = [['Name', 'Age'], ['Alice', 25], ['Bob', 30]]
wks.update_values('A1:B3', data)
# Append data
new_data = [['Charlie', 35], ['Diana', 28]]
wks.append_table(new_data, start='A4')DataFrame Integration
# Create DataFrame
df = pd.DataFrame({
'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 35],
'City': ['NYC', 'LA', 'Chicago']
})
# Write DataFrame to worksheet
wks.set_dataframe(df, start='A1', copy_index=False)
# Read worksheet as DataFrame
df_from_sheet = wks.get_as_df(has_header=True)Worksheet Structure Management
# Resize worksheet
wks.resize(rows=100, cols=20)
# Add rows and columns
wks.add_rows(10)
wks.add_cols(5)
# Insert rows with data
wks.insert_rows(5, number=2, values=[['New', 'Data'], ['More', 'Info']])
# Delete rows and columns
wks.delete_rows(10, number=3)
wks.delete_cols(15, number=2)
# Adjust dimensions
wks.adjust_column_width(1, 3, pixel_size=150)
wks.adjust_row_height(1, pixel_size=30)Types
Value Render Options
class ValueRenderOption:
FORMATTED_VALUE = 'FORMATTED_VALUE'
UNFORMATTED_VALUE = 'UNFORMATTED_VALUE'
FORMULA = 'FORMULA'
class DateTimeRenderOption:
SERIAL_NUMBER = 'SERIAL_NUMBER'
FORMATTED_STRING = 'FORMATTED_STRING'Exceptions
class WorksheetNotFound(PyGsheetsException):
"""Raised when worksheet cannot be found."""
pass
class CellNotFound(PyGsheetsException):
"""Raised when cell cannot be found."""
pass
class RangeNotFound(PyGsheetsException):
"""Raised when range cannot be found."""
passInstall 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