Sheets Service

The Sheets service allows you to create, read, and manipulate Google Sheets programmatically.

Getting Started

python
from gspace import GSpace

gspace = GSpace.from_oauth(
    credentials_file="credentials.json",
    scopes=["sheets"]
)

sheets = gspace.sheets()

Creating Spreadsheets

python
# Create a new spreadsheet
spreadsheet = sheets.create_spreadsheet(
    title="Monthly Report",
    sheets=[{
        'properties': {
            'title': 'Data',
            'gridProperties': {
                'rowCount': 1000,
                'columnCount': 26
            }
        }
    }]
)

print(f"Spreadsheet ID: {spreadsheet.get('spreadsheetId')}")

Reading Data

python
# Read values from a range
values = sheets.get_values(
    spreadsheet_id="spreadsheet_id_here",
    range_name="Sheet1!A1:D5"
)

for row in values:
    print(row)

Writing Data

python
# Write values to cells
sheets.update_values(
    spreadsheet_id="spreadsheet_id_here",
    range_name="Sheet1!A1:D5",
    values=[
        ["Name", "Department", "Salary", "Start Date"],
        ["John Doe", "Engineering", 75000, "2023-01-15"],
        ["Jane Smith", "Marketing", 65000, "2023-03-20"],
        ["Bob Johnson", "Sales", 70000, "2023-02-10"]
    ]
)

Formatting Cells

python
# Format cells
sheets.format_cells(
    spreadsheet_id="spreadsheet_id_here",
    range_name="Sheet1!A1:D1",
    format_properties={
        'backgroundColor': {'red': 0.8, 'green': 0.8, 'blue': 0.8},
        'textFormat': {'bold': True},
        'horizontalAlignment': 'CENTER'
    }
)

Managing Sheets

python
# Get spreadsheet metadata
spreadsheet_info = sheets.get_spreadsheet(
    spreadsheet_id="spreadsheet_id_here"
)

# Add a new sheet
sheets.add_sheet(
    spreadsheet_id="spreadsheet_id_here",
    title="New Sheet"
)

# Delete a sheet
sheets.delete_sheet(
    spreadsheet_id="spreadsheet_id_here",
    sheet_id=sheet_id
)

Batch Operations

python
# Batch update values
sheets.batch_update_values(
    spreadsheet_id="spreadsheet_id_here",
    data=[
        {
            'range': 'Sheet1!A1:B2',
            'values': [['Name', 'Age'], ['John', 30]]
        },
        {
            'range': 'Sheet1!C1:D2',
            'values': [['City', 'Country'], ['NYC', 'USA']]
        }
    ]
)

Working with Formulas

python
# Add formulas
sheets.update_values(
    spreadsheet_id="spreadsheet_id_here",
    range_name="Sheet1!E2",
    values=[["=SUM(B2:D2)"]]
)

Conditional Formatting

python
# Apply conditional formatting
sheets.apply_conditional_formatting(
    spreadsheet_id="spreadsheet_id_here",
    range_name="Sheet1!B2:B10",
    rule={
        'condition': {
            'type': 'GREATER_THAN',
            'values': [{'userEnteredValue': '50000'}]
        },
        'format': {
            'backgroundColor': {'red': 1.0, 'green': 0.8, 'blue': 0.8}
        }
    }
)

Data Validation

python
# Add data validation
sheets.add_data_validation(
    spreadsheet_id="spreadsheet_id_here",
    range_name="Sheet1!C2:C10",
    rule={
        'condition': {
            'type': 'ONE_OF_LIST',
            'values': [
                {'userEnteredValue': 'Engineering'},
                {'userEnteredValue': 'Marketing'},
                {'userEnteredValue': 'Sales'}
            ]
        },
        'showCustomUi': True,
        'strict': True
    }
)

Charts and Graphs

python
# Create a chart
sheets.create_chart(
    spreadsheet_id="spreadsheet_id_here",
    sheet_id=sheet_id,
    chart={
        'chartType': 'COLUMN',
        'sourceRange': {
            'sources': [{
                'sheetId': sheet_id,
                'startRowIndex': 0,
                'endRowIndex': 10,
                'startColumnIndex': 0,
                'endColumnIndex': 3
            }]
        }
    }
)

Best Practices

  1. Use batch operations - Group multiple updates into batch requests
  2. Specify ranges precisely - Always use A1 notation (e.g., "Sheet1!A1:B10")
  3. Handle large datasets - Use pagination for reading large ranges
  4. Cache spreadsheet IDs - Store IDs to avoid repeated lookups