Docs / Reading a workbook

Reading a workbook.

Four read-only tools, each answering a different question. Used in order, they let an agent orient on a 50,000-formula model in three calls without reading it cell by cell.

1. describe_workbook: what is in the file

A structural map without cell data. For each sheet: the used range, the detected header row with its headers mapped to column letters, and a table of contents of sections, the contiguous row bands with their titles. Also lists defined names. Served from an in-memory index, so it is near-instant.

{ "path": "~/models/forecast.xlsx" }

{
  "sheets": [
    {
      "name": "Model",
      "used_range": "A1:BX412",
      "header_row": 4,
      "headers": { "B": "FY2023A", "C": "FY2024A", "D": "FY2025E", "E": "FY2026E" },
      "sections": [
        { "rows": "5-42", "title": "Revenue build" },
        { "rows": "44-97", "title": "Operating expenses" }
      ]
    }
  ],
  "defined_names": [ { "name": "TaxRate", "ref": "Assumptions!B7" } ]
}

Pass sheet to limit the map to one sheet. Call it first on any file.

2. find_rows: where is the row I need

Searches row labels and column headers by name and returns exact positions. Matching is case-insensitive substring plus word prefix, so "tot rev" matches "Total revenue". Each match carries its sheet, 1-indexed row, containing section, and one evaluated sample cell read live from the grid.

{ "path": "~/models/forecast.xlsx", "query": "total revenue" }

{
  "matches": [
    { "sheet": "Model", "row": 42, "label": "Total revenue", "section": "Revenue build",
      "sample": "E42 = =SUM(E5:E41) → 39001.1" }
  ],
  "total": 1,
  "truncated": false
}

max_results defaults to 20. Use this instead of scanning with read_range: the row numbers in the reply are exact and go straight into formulas.

3. query_rows: which rows match

Filter, project and aggregate the rows of one sheet by column values, served by the calculation engine on any sheet size. Columns are letters or header texts, matched case-insensitively. Predicates in where are ANDed.

{
  "path": "~/data/sites.xlsx",
  "sheet": "Sites",
  "where": [
    { "column": "Owner", "op": "contains", "value": "AWS" },
    { "column": "Capacity (MW)", "op": "gte", "value": 100 }
  ],
  "columns": ["Site", "Market", "Capacity (MW)", "Online"],
  "order_by": { "column": "Capacity (MW)", "desc": true },
  "limit": 20
}

Operators: eq, ne, contains, not_contains, starts_with, ends_with, gt, gte, lt, lte, between (value is [low, high]), in and not_in (value is a list), empty, not_empty. Text compares trimmed and case-insensitively, numbers numerically, dates as ISO strings against date-formatted cells.

The reply holds matching rows with their 1-indexed row and the requested columns keyed by letter, plus matched (the total count) and truncated. limit defaults to 50, maximum 500.

With group_by and aggregate you get one entry per distinct value instead of rows:

{
  "path": "~/data/sites.xlsx",
  "sheet": "Sites",
  "group_by": "Market",
  "aggregate": [ { "column": "Capacity (MW)", "fn": "sum" } ]
}

Aggregates are sum, count, min, max, avg. The header row is auto-detected; pass header_row when it picks the wrong one, and start_row or end_row to bound the scan.

4. read_range: exact cells

Evaluated values and formulas for a specific A1 range, up to 500 cells. Use it to verify a write landed where expected, or to pull the handful of values a formula needs to reference.

{ "path": "~/models/forecast.xlsx", "sheet": "Model", "range": "B42:E42" }

{
  "sheet": "Model",
  "range": "B42:E42",
  "cells": [
    { "cell": "B42", "value": 31250.4, "formula": "=SUM(B5:B41)", "text": "31,250" },
    { "cell": "C42", "value": 34800.0, "formula": "=SUM(C5:C41)", "text": "34,800" }
  ],
  "truncated": false
}

For anything larger than 500 cells, use query_rows, or read inside a run_script, where reads are unbudgeted.

What the engine evaluates

Dynamic arrays and spills (FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, VSTACK, HSTACK, TAKE, DROP, CHOOSECOLS, CHOOSEROWS, TOCOL, WRAPROWS), LET and LAMBDA with MAP, BYROW and SCAN, XLOOKUP, the IFS family, SUMPRODUCT, TEXTJOIN over arrays, OFFSET and INDIRECT, date and finance functions, RANK, PERCENTILE, QUARTILE, MEDIAN and STDEV. Not implemented and returning #NAME?: AGGREGATE, GETPIVOTDATA, HYPERLINK, GROUPBY, PIVOTBY. Formulas are stored as written, so Excel recalculates them on open regardless.

Found a mistake? Open an issue or email support@gridpath.dev.