Skip to main content

​
Overview

The spreadsheet automation tools provide comprehensive functionality for working with UniverJS-based spreadsheets (Univer Sheets). These tools enable cell manipulation, formula creation, data insertion, visualization with charts, and analysis with pivot tables. All tools execute client-side through the univerAgentBridge, ensuring responsive spreadsheet interactions.

​
Key Characteristics

  • Client-Side Execution: Tools return UI actions that execute in the browser
  • Excel-Compatible: Supports standard Excel formulas and operations
  • Data Integration: Direct database query integration with one-step data insertion
  • Regulatory Compliance: ASIC RG94-compliant analysis and validation
  • Comprehensive Visualization: Multiple chart types and pivot table support

​
P0 Critical Tools

These four tools are used in 50-80% of spreadsheet workflows and should be prioritized:

​
1. prepare_spreadsheet_workspace

Type: async (purpose: str, suggestedWorkbookName?: str, autoInitialize?: bool) -> dict Purpose: Initialize a spreadsheet workspace for data operations. Description: Prepares an Excel-like spreadsheet workspace by creating a new workbook, navigating to it, and selecting cell A1. Use this as the first step before any spreadsheet operations. Automatically handles workbook creation if none is loaded. Parameters:
  • purpose (string, required): Why this workspace is being created (e.g., “add portfolio data”, “create NAV reconciliation”)
  • suggestedWorkbookName (string, optional): Suggested name for the workbook. Defaults to ” Workbook”
  • autoInitialize (boolean, optional): Automatically create and navigate to workbook. Default: true
Returns: 
{
  "success": true,
  "message": "Setting up 'Portfolio Data Workbook' for you...",
  "purpose": "add portfolio data",
  "workbookName": "Portfolio Data Workbook",
  "uiAction": {
    "action": "ui:prepare_spreadsheet_workspace",
    "params": { /* workspace initialization params */ }
  }
}
Examples: 
# Prepare workspace for adding data
await prepare_spreadsheet_workspace(
    purpose="add portfolio data",
    suggestedWorkbookName="Q4 2025 Portfolios"
)

# Prepare workspace with auto-initialization disabled
await prepare_spreadsheet_workspace(
    purpose="create NAV reconciliation",
    autoInitialize=False
)
Use Cases:
  • Starting any spreadsheet workflow
  • Before calling insert_data_to_spreadsheet
  • When user asks to “add data to a spreadsheet”

​
2. fetch_and_insert_to_spreadsheet

Type: async (query: str, explanation: str, includeHeaders?: bool, startRow?: int, startCol?: int) -> dict Purpose: Atomic operation combining database fetch and spreadsheet insertion. Description: Executes a SQL query and inserts the results directly into the active spreadsheet in a single operation. This is the preferred approach for adding database data to spreadsheets as it’s more reliable than separate fetch/insert operations. Automatically formats headers when includeHeaders=true. Parameters:
  • query (string, required): SQL query using schema.table format (e.g., SELECT * FROM investment.portfolios)
  • explanation (string, required): Description of what the query does and why
  • includeHeaders (boolean, optional): Add column headers in first row. Default: true
  • startRow (integer, optional): Starting row index (0-based). Default: 0
  • startCol (integer, optional): Starting column index (0-based). Default: 0
Returns: 
{
  "success": true,
  "rowCount": 23,
  "columnsCount": 7,
  "message": "Fetched 23 rows and will insert them into the spreadsheet",
  "uiAction": {
    "action": "ui:insert_data_to_spreadsheet",
    "params": {
      "data": [ /* array of row objects */ ],
      "startRow": 0,
      "startCol": 0,
      "includeHeaders": true
    }
  }
}
Examples: 
# Fetch and insert active portfolios
await fetch_and_insert_to_spreadsheet(
    query="SELECT * FROM investment.portfolios WHERE portfolio_status = 'Active'",
    explanation="Fetching active portfolios to display in spreadsheet",
    includeHeaders=True,
    startRow=0,
    startCol=0
)

# Fetch holdings data and insert starting at row 5
await fetch_and_insert_to_spreadsheet(
    query="SELECT security_id, holding_units, market_value FROM investment.holdings WHERE portfolio_id = 'PORT001'",
    explanation="Fetching holdings for portfolio PORT001",
    startRow=5,
    startCol=0
)
Requirements:
  • Database query must use schema.table format
  • Query must be valid SQL
  • User must have read permissions on the queried table
Related Tools:
  • prepare_spreadsheet_workspace - Initialize workspace first
  • insert_data_to_spreadsheet - Manual data insertion
  • fetch_and_display - Universal fetch for multiple UI targets

​
3. insert_data_to_spreadsheet

Type: async (data: List[Dict], startRow?: int, startCol?: int, includeHeaders?: bool) -> dict Purpose: Insert tabular data directly into spreadsheet cells. Description: Inserts an array of row objects into the spreadsheet at the specified position. Each object’s keys become column headers (if includeHeaders=true), and values populate the corresponding cells. Commonly used after fetching data or preparing data arrays. Parameters:
  • data (array, required): Array of row objects. Example: [{"id": 1, "name": "John"}, {"id": 2, "name": "Jane"}]
  • startRow (integer, optional): Starting row index (0-based). Default: 0
  • startCol (integer, optional): Starting column index (0-based). Default: 0
  • includeHeaders (boolean, optional): Add column headers in first row. Default: true
Returns: 
{
  "success": true,
  "rowsToInsert": 13,
  "columnsToInsert": 5,
  "message": "Will insert 13 rows into the spreadsheet",
  "uiAction": {
    "action": "ui:insert_data_to_spreadsheet",
    "params": {
      "data": [ /* array of rows */ ],
      "startRow": 0,
      "startCol": 0,
      "includeHeaders": true
    }
  }
}
Examples: 
# Insert transaction data starting at A1 with headers
await insert_data_to_spreadsheet(
    data=[
        {"transaction_id": "TXN001", "date": "2025-01-15", "amount": 5000.00, "status": "Complete"},
        {"transaction_id": "TXN002", "date": "2025-01-16", "amount": 3500.00, "status": "Complete"},
        {"transaction_id": "TXN003", "date": "2025-01-17", "amount": 2200.00, "status": "Pending"}
    ],
    startRow=0,
    startCol=0,
    includeHeaders=True
)

# Insert data without headers starting at B2
await insert_data_to_spreadsheet(
    data=[
        {"value": 100, "percentage": 0.25},
        {"value": 200, "percentage": 0.50}
    ],
    startRow=1,
    startCol=1,
    includeHeaders=False
)
Validation:
  • data array cannot be empty
  • All objects should have consistent keys
  • Non-empty data is required

​
4. fetch_and_display

Type: async (query: str, explanation: str, uiTarget?: str, formatting?: dict) -> dict Purpose: Universal fetch operation routing data to multiple UI component types. Description: A versatile tool that executes a SQL query and routes results to the appropriate UI component based on the target. Supports spreadsheets, documents, slides, widgets, dashboards, and formula builders. The uiTarget parameter determines where data is displayed. Parameters:
  • query (string, required): SQL query using schema.table format
  • explanation (string, required): What this query does and why
  • uiTarget (string, optional): Target UI component: "auto", "spreadsheet", "document", "slide", "widget", "dashboard", "formula". Default: "auto" (routes to spreadsheet)
  • formatting (object, optional): UI-specific formatting options
Returns: 
{
  "success": true,
  "rowCount": 15,
  "columnsCount": 6,
  "targetUI": "spreadsheet",
  "message": "Fetched 15 rows and will insert them into the spreadsheet",
  "uiAction": {
    "action": "ui:insert_data_to_spreadsheet",
    "params": { /* routing-specific params */ }
  }
}
Examples: 
# Fetch and display in active spreadsheet (default)
await fetch_and_display(
    query="SELECT * FROM investment.portfolios WHERE active = true",
    explanation="Fetching active portfolios",
    uiTarget="spreadsheet"
)

# Fetch and display in document as table
await fetch_and_display(
    query="SELECT * FROM investment.transactions",
    explanation="Fetching transaction history",
    uiTarget="document",
    formatting={"styleAsTable": True}
)

# Fetch and create widget with chart
await fetch_and_display(
    query="SELECT asset_class, COUNT(*) as count FROM investment.holdings GROUP BY asset_class",
    explanation="Holdings by asset class distribution",
    uiTarget="widget",
    formatting={"chartType": "pie"}
)
UI Targets:
  • spreadsheet - Univer Sheets (Excel-like cells/formulas)
  • document - Univer Docs (rich text with tables)
  • slide - Univer Slides (presentation format)
  • widget - Dashboard widget (chart/table)
  • dashboard - Full dashboard layout
  • formula - Formula builder context
  • auto - Automatically detect active UI context

​
Standard Spreadsheet Tools

​
spreadsheet_set_cell

Type: async (row: int, col: int, value: any, sheetId?: str) -> dict Purpose: Update a single cell value. Parameters:
  • row (integer, required): Row index (0-based)
  • col (integer, required): Column index (0-based)
  • value (any, required): Cell value (string, number, boolean, or null)
  • sheetId (string, optional): Target sheet ID
Examples: 
# Set header in cell A1
await spreadsheet_set_cell(row=0, col=0, value="Product Name")

# Set numeric value in cell C2
await spreadsheet_set_cell(row=1, col=2, value=1500.50)

# Set status in cell B6
await spreadsheet_set_cell(row=5, col=1, value="Completed")

​
spreadsheet_get_cell

Type: async (row: int, col: int, sheetId?: str) -> dict Purpose: Read a cell value from the spreadsheet. Parameters:
  • row (integer, required): Row index (0-based)
  • col (integer, required): Column index (0-based)
  • sheetId (string, optional): Target sheet ID
Returns: 
{
  "success": true,
  "message": "Retrieved cell (0, 0)",
  "data": {
    "row": 0,
    "col": 0,
    "value": "Product Name"
  }
}

​
spreadsheet_set_formula

Type: async (row: int, col: int, formula: str, sheetId?: str) -> dict Purpose: Insert an Excel-style formula into a cell. Supported Functions:
  • Math: SUM(), AVERAGE(), MIN(), MAX(), ROUND()
  • Logic: IF(), AND(), OR(), NOT()
  • Text: CONCATENATE(), LEFT(), RIGHT(), MID()
  • References: Cell references (A1, B2), Ranges (A1:A10)
Examples: 
# Sum range A1:A10
await spreadsheet_set_formula(row=10, col=0, formula="=SUM(A1:A10)")

# Calculate percentage
await spreadsheet_set_formula(row=5, col=3, formula="=A1/B1*100")

# Conditional logic
await spreadsheet_set_formula(row=2, col=4, formula="=IF(A2>100, 'High', 'Low')")

# Average calculation
await spreadsheet_set_formula(row=15, col=1, formula="=AVERAGE(B2:B14)")

​
spreadsheet_select_range

Type: async (startRow: int, startCol: int, endRow: int, endCol: int, sheetId?: str) -> dict Purpose: Highlight a range of cells to draw user attention. Parameters:
  • startRow (integer, required): Start row (0-based, inclusive)
  • startCol (integer, required): Start column (0-based, inclusive)
  • endRow (integer, required): End row (0-based, inclusive)
  • endCol (integer, required): End column (0-based, inclusive)
  • sheetId (string, optional): Target sheet ID
Examples: 
# Select header row (A1:K1)
await spreadsheet_select_range(startRow=0, startCol=0, endRow=0, endCol=10)

# Select data block (A2:F100)
await spreadsheet_select_range(startRow=1, startCol=0, endRow=99, endCol=5)

# Select single column C (C1:C1000)
await spreadsheet_select_range(startRow=0, startCol=2, endRow=999, endCol=2)

​
select_spreadsheet_cell

Type: async (row: int, col: int, sheetId?: str, reason?: str) -> dict Purpose: Select a single cell with optional guidance message. Parameters:
  • row (integer, required): Row index (0-based)
  • col (integer, required): Column index (0-based)
  • sheetId (string, optional): Target sheet ID
  • reason (string, optional): Guidance message for the user
Examples: 
# Select portfolio name cell
await select_spreadsheet_cell(row=0, col=0, reason="Enter portfolio name here")

# Navigate to NAV calculation
await select_spreadsheet_cell(row=10, col=5, reason="Check NAV calculation")

# Highlight total cell
await select_spreadsheet_cell(row=20, col=3, reason="This is where the total should go")

​
spreadsheet_insert_row

Type: async (rowIndex: int, count?: int, sheetId?: str) -> dict Purpose: Insert one or more blank rows at a specific position. Parameters:
  • rowIndex (integer, required): Row position to insert at (0-based)
  • count (integer, optional): Number of rows to insert (1-100). Default: 1
  • sheetId (string, optional): Target sheet ID
Examples: 
# Insert single row at position 5
await spreadsheet_insert_row(rowIndex=5, count=1)

# Insert 3 rows at top
await spreadsheet_insert_row(rowIndex=0, count=3)

# Insert rows before last row
await spreadsheet_insert_row(rowIndex=99, count=2)

​
Visualization Tools

​
create_spreadsheet_chart

Type: async (input: CreateSpreadsheetChartInput) -> dict Purpose: Create visual charts from spreadsheet data. Description: Generates charts from data already inserted into the spreadsheet. Charts provide visual representation of trends, comparisons, and distributions. Must be used after data is loaded into cells. Chart Types:
  • bar - Horizontal bars, best for category comparisons
  • column - Vertical bars, best for time series
  • line - Line graphs, best for trends over time
  • pie - Pie charts, best for proportions/percentages
  • area - Area charts, best for cumulative totals
  • scatter - Scatter plots, best for correlations
Parameters:
  • title (string, required): Chart title
  • chartType (string, required): One of the chart types above
  • dataRange (object, required): Data range specification
    • startRow, startCol, endRow, endCol (all integers)
  • categoryColumn (integer, required): Column index for category labels
  • seriesColumns (array, required): Column indices for data series
  • position (object, optional): Chart placement
    • startRow, startCol - Position in spreadsheet
  • options (object, optional):
    • showLegend (boolean) - Show legend. Default: true
    • showDataLabels (boolean) - Show value labels. Default: false
    • colors (array) - Custom color array
Examples: 
# Create bar chart from portfolio data
await create_spreadsheet_chart(
    title="Portfolio Holdings",
    chartType="bar",
    dataRange={
        "startRow": 0,
        "startCol": 0,
        "endRow": 10,
        "endCol": 3
    },
    categoryColumn=0,
    seriesColumns=[1, 2],
    options={"showLegend": True, "showDataLabels": False}
)

# Create line chart for performance trends
await create_spreadsheet_chart(
    title="NAV Trend",
    chartType="line",
    dataRange={
        "startRow": 0,
        "startCol": 0,
        "endRow": 20,
        "endCol": 2
    },
    categoryColumn=0,
    seriesColumns=[1],
    position={"startRow": 0, "startCol": 5}
)

​
Analysis Tools

​
create_pivot_table

Type: async (input: CreatePivotTableInput) -> dict Purpose: Summarize and aggregate spreadsheet data. Description: Creates a pivot table for analyzing data by grouping and calculating aggregations. Essential for cross-tab analysis, totals, and summaries. Aggregation Functions:
  • sum - Add values
  • count - Count items
  • average - Calculate mean
  • max - Find maximum
  • min - Find minimum
  • median - Find middle value
Parameters:
  • dataRange (object, required): Source data range
  • rowFields (array, required): Field names for row grouping
  • columnFields (array, optional): Field names for column grouping
  • valueFields (array, required): Aggregation specifications
    • field (string) - Column to aggregate
    • aggregation (string) - Function (sum, count, average, etc.)
    • label (string, optional) - Display label
  • showSubtotals (boolean, optional): Show subtotal rows. Default: true
  • showGrandTotal (boolean, optional): Show grand total. Default: true
  • position (object, optional): Output location
Examples: 
# Total NAV by portfolio
await create_pivot_table(
    dataRange={"startRow": 0, "startCol": 0, "endRow": 50, "endCol": 5},
    rowFields=["portfolio_name"],
    valueFields=[
        {
            "field": "nav",
            "aggregation": "sum",
            "label": "Total NAV"
        }
    ]
)

# Holdings analysis by asset class with multiple aggregations
await create_pivot_table(
    dataRange={"startRow": 0, "startCol": 0, "endRow": 100, "endCol": 8},
    rowFields=["asset_class"],
    columnFields=["strategy"],
    valueFields=[
        {"field": "market_value", "aggregation": "sum"},
        {"field": "units", "aggregation": "sum"},
        {"field": "price", "aggregation": "average"}
    ],
    showSubtotals=True,
    showGrandTotal=True
)

# Fee analysis by month
await create_pivot_table(
    dataRange={"startRow": 0, "startCol": 0, "endRow": 30, "endCol": 4},
    rowFields=["month"],
    valueFields=[
        {"field": "management_fee", "aggregation": "sum"},
        {"field": "performance_fee", "aggregation": "sum"}
    ]
)

​
spreadsheet_analyze

Type: async (input: SpreadsheetAnalyzeInput) -> dict Purpose: Generate ASIC RG94-compliant validation rules with AI-powered analysis. Description: Uses AI to analyze spreadsheet data and generate regulatory validation rules. Supports NAV reconciliation, cash validation, holdings analysis, fee calculations, and general reconciliation with compliance guidance. Validation Types:
  • NAV - Net Asset Value validation (3bp default tolerance)
  • cash - Cash balance reconciliation (zero tolerance)
  • holdings - Position reconciliation (1bp default tolerance)
  • fees - Fee calculation validation (5bp default tolerance)
  • reconciliation - Total assets reconciliation (3bp default tolerance)
Parameters:
  • range (string, required): Cell range (e.g., “A1:D100”)
  • validationType (string, required): Type of validation
  • context (object, optional):
    • fundType (string) - Fund type (e.g., “mutual”, “hedge”, “private equity”)
    • assetClass (string) - Asset class (e.g., “equities”, “fixed income”, “alternatives”)
    • toleranceBps (number) - Custom tolerance in basis points
Returns: 
{
  "success": true,
  "analysis": {
    "summary": "Generated 2 NAV validation rule(s) with 3bp tolerance",
    "aiInsights": "Detailed analysis and insights...",
    "validationType": "NAV",
    "rulesGenerated": 2
  },
  "suggestedRules": [
    {
      "ruleCode": "NAV-001",
      "ruleName": "NAV Variance Check",
      "formula": "=NAV_COMPARE(Admin_NAV, Custodian_NAV, 0.0003)",
      "tolerance": 3,
      "severity": "error",
      "description": "Compares administrator NAV against custodian NAV..."
    }
  ],
  "references": ["https://asic.gov.au/..."]
}
Examples: 
# Analyze NAV reconciliation data
await spreadsheet_analyze(
    range="A1:D100",
    validationType="NAV",
    context={
        "fundType": "mutual",
        "assetClass": "equities",
        "toleranceBps": 3
    }
)

# Analyze cash reconciliation with custom tolerance
await spreadsheet_analyze(
    range="B2:E50",
    validationType="cash",
    context={"toleranceBps": 0}
)

# Analyze holdings with context
await spreadsheet_analyze(
    range="A1:Z200",
    validationType="holdings",
    context={
        "fundType": "hedge",
        "assetClass": "alternatives"
    }
)
Returns:
  • analysis - AI-generated insights and summary
  • suggestedRules - Array of ASIC RG94-compliant rules
  • references - Links to regulatory guidance

​
Use Cases and Workflows

​
Workflow 1: Import and Visualize Portfolio Data

1. prepare_spreadsheet_workspace(purpose="portfolio analysis")
2. fetch_and_insert_to_spreadsheet(
     query="SELECT * FROM investment.portfolios",
     explanation="Loading all portfolios"
   )
3. create_spreadsheet_chart(
     title="Portfolio Distribution",
     chartType="pie",
     dataRange={...},
     categoryColumn=0,
     seriesColumns=[2]
   )

​
Workflow 2: NAV Reconciliation

1. prepare_spreadsheet_workspace(purpose="NAV reconciliation")
2. fetch_and_insert_to_spreadsheet(
     query="SELECT admin_nav, custodian_nav FROM investment.nav_data",
     explanation="Loading NAV comparison"
   )
3. spreadsheet_analyze(
     range="A1:C100",
     validationType="NAV"
   )

​
Workflow 3: Holdings Analysis with Pivot Table

1. prepare_spreadsheet_workspace(purpose="holdings analysis")
2. fetch_and_insert_to_spreadsheet(
     query="SELECT * FROM investment.holdings",
     explanation="Loading holdings"
   )
3. create_pivot_table(
     dataRange={...},
     rowFields=["asset_class"],
     valueFields=[{"field": "market_value", "aggregation": "sum"}]
   )

​
Best Practices

  1. Always Prepare Workspace First: Call prepare_spreadsheet_workspace before other operations
  2. Use Fetch-and-Insert: Prefer fetch_and_insert_to_spreadsheet over separate fetch/insert calls
  3. Data Before Visualization: Insert data before creating charts or pivot tables
  4. A1 Notation: Tools use 0-based indexing internally but display A1 notation to users
  5. Include Headers: Set includeHeaders=true for proper column identification
  6. Validate Ranges: Ensure chart/pivot table ranges match actual data
  7. Compliance-First: Use spreadsheet_analyze for regulatory validation scenarios