PebbleScript JSON Notation

PebbleScript JSON Notation is a structured form of PebbleScript designed for AI agents and tools. Every directive in JSON Notation corresponds to exactly one directive in textual PebbleScript, and each can be converted to the other mechanically.

The two forms have different jobs:

JSON Notation is never written to a PSOSF archive or an Excel workbook. A tool that receives JSON Notation converts it to textual PebbleScript before writing.

Why a second form? Textual PebbleScript passes arguments by position. In pebblestream:join("Rates", "id", "rate-id", "date", "rate-date"), nothing but position says which values pair up. JSON Notation names every argument and groups repeated ones, so the same directive can be validated exactly with JSON Schema and is harder for an agent to get wrong.

PebbleScript is declarative

PebbleScript describes what a worksheet is, not a sequence of steps to perform. Where a directive is written, and in what order, has no effect on when it runs: the same directives produce the same result whether they appear in one annotation or spread across several columns, and in any order.

Transformative directives on a worksheet always run in this fixed order:

1. from
2. cartesian-product
3. join
4. outer-join
5. filter
6. sort
7. group, together with the directives that run within it:
   sum,
   ratio,
   mult,
   equal,
   yield,
   npv,
   pivot and
   regex. The order among these does not matter. They may read the same column, but no two may write to the same column. pivot cannot be combined with any of the others.
8. group-unique (deprecated)
9. group-sum (deprecated)
10. group-ratio (deprecated)
11. header-map
12. split (deprecated)
13. prune, which always runs last

All transformative directives run before the worksheet's formulas. A worksheet is computed in three stages:

1. The transformative directives run, in the order above.
2. Formula columns are filled down to the transformed rows and calculated.
3. Annotative directives that check cell values run on the completed worksheet.

For example, on a worksheet with join, filter and group, rows are joined first, then filtered, then grouped, however the three are written. So a filter can test a column brought in by the join, but it always sees rows before they are grouped and cannot test a group's total. To remove groups by their total, use prune, which runs after grouping: pebblestream:prune("total", ">=0") keeps only groups with a non-negative total. Neither filter nor prune can test a formula column, since both run before formulas are calculated; to remove rows by a formula's result, compute it on one worksheet and filter on a second worksheet that reads from it.

After the transformative directives run, every formula column is filled down to the transformed rows automatically. This is implied by the design; don't write match-rows to get it. An explicit match-rows cannot be combined with a transformative directive on the same worksheet.

Annotative directives that check cell values, such as fail, warn, not-blank, unique and allowed-values, run on the completed worksheet, after its transformative directives.

fail and warn are evaluated on formula cells. They annotate a formula column and test that column's formulas after the formulas have been filled down to the transformed rows. They never check a cell produced directly by a transformative directive. To check a value a transformative directive produces, such as a group's total, add a formula column that tests it and put fail or warn on that column:

type and total are produced by the group step; total is negative is a formula, filled down to one row per group, and fail stops the run for any group whose total is negative.

Annotative directives that do not check cell values, such as hint, out and the template directives, have no order. A worksheet may have fail and warn on as many columns as it needs.

When writing JSON Notation, order directives in the sequence above for readability. The order of items in an annotation array preserves where comments sit; it never changes what the worksheet computes.

The position of the remaining transformative directives in this order will be documented as it is confirmed.

Which directives may repeat

These directives may appear only once on a worksheet: from, cartesian-product, join, outer-join, group, sort, header-map, paths, apportion, relieve, expand, generate-rows, match-rows, template-name and workflow-filter. To join a second worksheet, for example, join on an intermediate worksheet first.

workflow-all, workflow-each, workflow-group and workflow-iterate may appear on only one worksheet in a spreadsheet, and only once, except that workflow-group annotates every column of the group key on that worksheet.

Directives that write a column may repeat, as long as each writes a different column: the directives within group (such as sum, yield, npv and pivot) and paths-operation. filter and prune may also repeat; separate filter directives are combined with OR.

Declared columns determine the result

A worksheet's headers determine which columns appear after its transformation. A column from the source worksheet appears in the result only if the worksheet declares it as a header; any other source column is left out. The one requirement is that every column a transformative directive writes to must be declared.

In the fail example above, total must be declared because sum writes to it. type, the grouping column, is not a target, so it may be left out; include it when consumers of the output need to know which group each row belongs to. The same holds for header-map: each new header it writes must be declared, while source columns it does not touch are optional.

For example, take two worksheets with two columns each:

A worksheet that takes their cartesian product can declare just one of the four columns:

Its result is the single fund column. The rows are still the full product, one per fund and period, so Fund A and Fund B each appear twice. Leaving columns out changes which columns appear, not how many rows there are.

Directives

A directive is a JSON object. The directive key names it, without the pebblestream: prefix. Every other key is an argument.

pebblestream:join("Rates", "id", "rate-id", "date", "rate-date")

{
  "directive": "join",
  "sheet": "Rates",
  "on": [
    {"column": "id", "source-column": "rate-id"},
    {"column": "date", "source-column": "rate-date"}
  ]
}

Rules:

• Keys are lowercase and hyphenated.
• Each directive accepts only the keys listed for it in the catalog. Unknown keys are an error.
• An argument repeated in text (such as the sheets in from) is a JSON array. A repeated group of arguments (such as the column pairs in join) is an array of objects.
• An optional argument is omitted when absent. It is never null.
• Header names, sheet names and other text are JSON strings. Numbers are JSON numbers.
• Any argument may instead be a cell reference.

Cell references

Any argument can be a cell reference instead of a literal value. In JSON Notation a reference is an object with a single ref key holding the reference in Excel's A1 notation:

pebblestream:filter("amount", ">0", "fund", Inputs!B2)
pebblestream:generate-rows('Run Settings'!$B$1)

{"directive": "filter",
 "conditions": [{"column": "amount", "predicate": ">0"},
                {"column": "fund", "predicate": {"ref": "Inputs!B2"}}]}

{"directive": "generate-rows", "count": {"ref": "'Run Settings'!$B$1"}}

The object form keeps a reference distinct from a string that happens to look like one: {"ref": "Inputs!B2"} is a reference, while "Inputs!B2" is the literal text Inputs!B2.

Rules:

• ref holds one cell, optionally qualified by a sheet name and optionally absolute (B2, $B$2, Inputs!B2). A sheet name containing spaces or punctuation is enclosed in single quotes, with any single quote inside it doubled, as in Excel.
• A reference can stand anywhere a literal can, including inside arrays ({"directive": "from", "sheets": [{"ref": "Setup!A2"}, "Accounts"]}) and inside repeated groups.
• In the catalog's key tables, each key's type describes the literal it accepts. Every key also accepts a reference.

Annotations

An annotation is the full content of one column's header note: directives and free-form comments, in order. In JSON Notation it is an array whose items are either a directive object or a comment object.

Totals of all ledger balances, by account type.
pebblestream:from("Accounts")
pebblestream:group("type")
pebblestream:sum("amount")
Rebuilt nightly from the general ledger extract.

[
  {"comment": "Totals of all ledger balances, by account type."},
  {"directive": "from", "sheets": ["Accounts"]},
  {"directive": "group", "columns": ["type"]},
  {"directive": "sum", "column": "amount"},
  {"comment": "Rebuilt nightly from the general ledger extract."}
]

A comment object holds exactly one line of text. Its text must not contain a line break, and must not begin (after leading whitespace) with pebblestream:, since that line would be read back as a directive.

An empty annotation is an empty array.

Converting between JSON and text

JSON to text

Each item becomes one line, in order. A comment item becomes its text. A directive item becomes:

1. pebblestream: followed by the directive name.
2. If the directive has no arguments present, nothing more. This is the bare form, e.g. pebblestream:unique.
3. Otherwise, the arguments in the directive's positional order, separated by , and enclosed in parentheses. Repeated groups are flattened in array order, and an absent optional argument is left out.
4. Strings are enclosed in double quotes; numbers are written without quotes; cell references are written without quotes, exactly as held in ref. A column predicate is written as its quoted operator, &, and the quoted header ("<="&"Balance"). A sort order on a sort column is written as its :asc or :desc suffix.

The colon form (pebblestream:name:a,b) is deprecated and is never produced.

Text to JSON

A converter accepts every form textual PebbleScript allows: bare, parentheses, and the deprecated colon form. Which form was used is not recorded in JSON Notation.

A quoted argument becomes a string. A worksheet or column name that contains no space may also be written without quotes (pebblestream:from(Accounts)), and becomes the same string. An unquoted number becomes a number, an unquoted cell reference becomes a {"ref": ...} object, and an operator joined to a header with & becomes a column predicate object.

Quotes and backslashes in text

Inside a quoted string argument:

• A quote is written as two quotes, as in an Excel formula: "12"" pipe" is 12" pipe. A backslash before the quote also works: "12\" pipe".
• A backslash escapes the character after it, so a literal backslash is written as two: "C:\\data" is C:\data.
• A quote that is neither doubled nor escaped makes the directive invalid.

A backslash before any other character is removed, so "C:\data" reads as C:data. Because that is rarely what was meant, the compiler warns whenever it happens.

When converting JSON to text, a string is written with its quotes doubled and its backslashes doubled, so it always reads back exactly and never draws the warning. A name written without quotes must contain no space; quote any name that contains punctuation.

Round trips

• JSON → text → JSON returns the same JSON, apart from key order.
• Text → JSON → text returns the same directives, in canonical form: a directive written with the colon form comes back with parentheses, and pebblestream:unique() comes back as pebblestream:unique.

Because the second round trip normalizes the text, converters that move annotations between PSOSF and Excel copy the text verbatim and never pass it through JSON Notation. Normalization happens only when an agent's JSON is written as new text.

Directive catalog

Each entry shows the textual form and its JSON Notation, followed by the directive's keys in positional order.

Argument names marked provisional are placeholders. The directive's arguments are positional in text, and their meanings have not yet been named; the names will change before this notation is final.

Sources

from

pebblestream:from("Accounts", "Adjustments")

{"directive": "from", "sheets": ["Accounts", "Adjustments"]}

cartesian-product

pebblestream:cartesian-product("Funds", "Periods")

{"directive": "cartesian-product", "sheets": ["Funds", "Periods"]}

join and outer-join

join combines each row of the from worksheet with the matching rows of another worksheet (an inner join); outer-join also keeps rows that have no match. Rows match on the column pairs given in on. When on is omitted, they match on every header the two worksheets have in common. Each directive can be used once per worksheet.

pebblestream:join("Orders")
pebblestream:join("Taxable", "From", "Account From", "To", "Account To")

{"directive": "join", "sheet": "Orders"}

{"directive": "join", "sheet": "Taxable",
 "on": [{"column": "From", "source-column": "Account From"},
        {"column": "To", "source-column": "Account To"}]}

outer-join has the same keys.

Selecting, ordering and renaming rows

filter and prune

filter keeps only the rows that meet every one of its conditions. To keep rows that meet any of several sets of conditions, write one filter per set: separate filter directives on a worksheet are combined with OR.

A condition tests one column against a predicate:

• A literal predicate compares the column with a value: ">0", "<60", or "blue" for equality.
• A column predicate compares the column with another column in the same row. In text it joins an operator to a quoted header with &, as in "<="&"Balance"; in JSON it is an object with operator and column.
• A condition with no predicate keeps rows where the column's value is TRUE. It must be the directive's only condition.

pebblestream:filter("Active")
pebblestream:filter("Amount", ">0", "Color", "blue", "Amount", "<="&"Balance")

{"directive": "filter", "conditions": [{"column": "Active"}]}

{"directive": "filter",
 "conditions": [{"column": "Amount", "predicate": ">0"},
                {"column": "Color", "predicate": "blue"},
                {"column": "Amount", "predicate": {"operator": "<=", "column": "Balance"}}]}

prune has the same keys and works the same way as filter, but always runs last, after every other transformative directive and before formulas are calculated. Use filter to remove rows before they are joined or grouped, and prune to remove rows from the transformed result, for example groups whose total fails a test.

sort

Columns are sorted in the order given: by the first column, then by the second within it, and so on. Each column sorts ascending unless it carries the :desc suffix; :asc may be written explicitly.

pebblestream:sort("Last", "First", "Date":desc)

{"directive": "sort",
 "columns": [{"column": "Last"},
             {"column": "First"},
             {"column": "Date", "order": "desc"}]}

An order key is written as a suffix only when present, so {"column": "Date", "order": "asc"} becomes "Date":asc and {"column": "Date"} becomes "Date". Both sort ascending, and each round-trips to itself.

header-map

pebblestream:header-map("Acct No", "account", "Bal", "amount")

{"directive": "header-map",
 "mappings": [{"column": "Acct No", "new-column": "account"},
              {"column": "Bal", "new-column": "amount"}]}

Grouping

group

group collapses the worksheet's rows into one row per group: one row for each distinct combination of values in the grouping columns. Groups appear in the order their first row appears, the same way every time. The directives that run within group, such as sum, compute a value for each group.

Two directives keep the group's rows instead of collapsing them: ratio, the one sub-directive that does so, and expand, which returns every row of each group alongside its computed values.

pebblestream:group("fund", "type")

{"directive": "group", "columns": ["fund", "type"]}

group-unique, group-sum and group-ratio (deprecated)

These directives are deprecated. Don't use them in new worksheets. Existing worksheets that contain them still convert, with the same keys as group:

pebblestream:group-sum("fund")

{"directive": "group-sum", "columns": ["fund"]}

sum, min, max, average, mult, equal and ratio

pebblestream:sum("amount")
pebblestream:sum("amount", "total")

{"directive": "sum", "column": "amount"}
{"directive": "sum", "column": "amount", "new-column": "total"}

min, max, average, mult, equal and ratio have the same keys.

Within the group step, several directives may read the same column, but no two may write to the same column. A directive writes to new-column when it is given, and otherwise to column. So sum and ratio can both read amount if they write to different new columns:

[
  {"directive": "sum", "column": "amount", "new-column": "total"},
  {"directive": "ratio", "column": "amount", "new-column": "share"}
]

But two directives writing to total, or two directives on amount that both omit new-column, are an error.

balance

pebblestream:balance("amount", "debit", "credit")

{"directive": "balance", "column": "amount", "new-column-1": "debit", "new-column-2": "credit"}

expand

expand makes group return every row of each group, with the group's computed values repeated on each row, instead of one row per group. Each row then carries its group's totals directly, so later formulas on the worksheet need no lookup to find them.

For example, an Order worksheet has three rows for order o1, with amounts 1, 2 and 3. A worksheet with these directives:

pebblestream:from("Order")
pebblestream:group("order id")
pebblestream:sum("amount", "total")

returns one row: o1 with a total of 6. Adding pebblestream:expand returns all three rows, each with the group's total:

A formula column such as share (=B2/C2) can then compute each row's share of its order directly.

pebblestream:expand

{"directive": "expand"}

No keys.

yield and npv

pebblestream:yield("cash-flow", "date", "price", "settlement", "yield")

{"directive": "yield",
 "column-1": "cash-flow", "column-2": "date",
 "column-3": "price", "column-4": "settlement",
 "new-column": "yield"}

npv has the same keys.

yield and npv run within group, and each may be used more than once on a worksheet, as long as each writes a different column.

pivot

pivot runs within group, and cannot be combined with any other directive that runs within group. It may be used more than once on a worksheet. Pivots that share a category column must also share its value column: one category column cannot pull from different value columns.

pebblestream:pivot("period", "Q1", "amount", "number")
pebblestream:pivot("period", "Q2", "amount", "number")

{"directive": "pivot",
 "category-column": "period", "value": "Q1", "value-column": "amount",
 "filter-type": "number"}

Allocation and paths

apportion and relieve

pebblestream:apportion("partner", "amount", "ratio", "period")

{"directive": "apportion",
 "column-1": "partner", "column-2": "amount", "column-3": "ratio", "column-4": "period"}

relieve has the same keys.

paths

pebblestream:paths("parent", "child", "weight")

{"directive": "paths", "column-1": "parent", "column-2": "child", "column-3": "weight"}

paths-operation

pebblestream:paths-operation("product", "effective-ownership")

{"directive": "paths-operation", "operation": "product", "new-column": "effective-ownership"}

paths-operation may be used more than once on a worksheet, as long as each writes a different column.

Generating rows

match-rows

pebblestream:match-rows("Accounts")

{"directive": "match-rows", "sheet": "Accounts"}

generate-rows

pebblestream:generate-rows(12)

{"directive": "generate-rows", "count": 12}

stop

stop annotates a column, and may appear on more than one column of a worksheet. The first column to evaluate to true, in execution order, stops the worksheet's execution. Execution order follows the dependencies between formulas, not the columns' positions, so a stop column to the left of another is not necessarily evaluated first.

The optional number is the most rows the worksheet will generate. When it is omitted, the limit configured for the runtime applies. Always set it, so the worksheet's behavior doesn't depend on how the runtime is configured.

pebblestream:stop(1000)
pebblestream:stop

{"directive": "stop", "max-rows": 1000}
{"directive": "stop"}

Annotating columns and worksheets

Directives without arguments

These directives take no arguments. In JSON Notation they have only the directive key, and in text they are written bare.

pebblestream:not-blank

{"directive": "not-blank"}

unique, grouped, warn, fail, not-blank, ignore, do-not-compile, out

fail and warn may each appear on more than one column of a worksheet.

sorted

sorted stops execution if the column it annotates is not in order. It checks ascending order unless order is desc. When several columns on a worksheet carry sorted, they form one combined sort key, and priority gives each column's place in it for breaking ties (lower first).

pebblestream:sorted
pebblestream:sorted("desc", 2)

{"directive": "sorted"}
{"directive": "sorted", "order": "desc", "priority": 2}

allowed-values

pebblestream:allowed-values("individual", "corporate")

{"directive": "allowed-values", "values": ["individual", "corporate"]}

hint

hint attaches a named annotation to a worksheet for agents and other tools to read. Each hint has a key and an optional value; hints with the same key on one worksheet are collected into a list of values for that key.

pebblestream:hint("balance", "Use the closing balance, not the average balance.")
pebblestream:hint("reviewed")

{"directive": "hint", "key": "balance", "value": "Use the closing balance, not the average balance."}
{"directive": "hint", "key": "reviewed"}

identical-columns

pebblestream:identical-columns("Budget", "Actuals")

{"directive": "identical-columns", "sheets": ["Budget", "Actuals"]}

id

id marks a column as part of a worksheet's primary key. Every column marked with the same id forms one key, and the combination of their values must be unique across the worksheet. This is a stronger check than unique, which checks a single column on its own. A worksheet can have more than one key by giving each a group name.

pebblestream:id
pebblestream:id("account key")

{"directive": "id"}
{"directive": "id", "group": "account key"}

Column types: text, number, boolean and date

These directives set the type of the values in the column they annotate, overriding the type Excel would otherwise give them. Where possible, rely on the column's declared type instead.

pebblestream:date

{"directive": "date"}

No keys. text, number and boolean are the same. A column annotated with date accepts text dates in the forms yyyy-MM-dd, yyyy/MM/dd, MM/dd/yyyy and MM-dd-yyyy, with or without leading zeros on the month and day, and converts them to Excel dates.

Workflows

Workflow directives tell Pebble Stream Flow how to execute a spreadsheet within a workflow. They take effect when the spreadsheet runs in Flow and do not change what a single execution computes.

workflow-each, workflow-group and workflow-all

These directives annotate the driving input worksheet, and decide how its rows are divided among executions.

Only one of the three can be used in a spreadsheet, on one worksheet: the driving input worksheet.

pebblestream:workflow-each
pebblestream:workflow-group
pebblestream:workflow-all

{"directive": "workflow-each"}
{"directive": "workflow-group"}
{"directive": "workflow-all"}

No keys.

Iteration: workflow-iterate, workflow-terminate and workflow-filter

An iterative workflow runs repeatedly until it is told to stop. A spreadsheet containing workflow-iterate or workflow-terminate is the workflow's iteration spreadsheet. A workflow has at most one, and Flow runs it before every execution of the workflow, including the first. Iteration spreadsheets are meant to be small and quick; they decide whether and how the workflow runs again, not the workflow's main processing.

workflow-iterate and workflow-terminate cannot be used in the same spreadsheet.

workflow-iterate

Runs the workflow a fixed number of times. It may appear only once in a spreadsheet.

pebblestream:workflow-iterate(10)

{"directive": "workflow-iterate", "iterations": 10}

workflow-terminate

Annotates a column of the iteration spreadsheet. The workflow stops when any cell in that column evaluates to TRUE, so when to stop is computed by the column's formulas. The number is a safety limit: the workflow never runs more than this many times, even if no cell becomes TRUE.

pebblestream:workflow-terminate(10)

{"directive": "workflow-terminate", "max-iterations": 10}

workflow-filter

Annotates a worksheet of the iteration spreadsheet. Its headers name key columns, and its rows give the key values to process in the next iteration. For every input worksheet in the workflow that has all of those columns, only rows matching one of the filter rows are sent to the workflow; several filter rows are combined with OR. Without workflow-filter, each iteration receives the whole input again.

pebblestream:workflow-filter

{"directive": "workflow-filter"}

No keys. workflow-filter can be used with either workflow-iterate or workflow-terminate.

Templates

template-name

pebblestream:template-name("Capital Account Statement")

{"directive": "template-name", "name": "Capital Account Statement"}

template-out-style

pebblestream:template-out-style("number.percentage", "number.currency")

{"directive": "template-out-style", "styles": ["number.percentage", "number.currency"]}

Template directives without arguments

block-id, template-row, template-column, leftmost-column, rightmost-column, top-row, bottom-row, block-row-count, row-oriented-headers, column-oriented-headers, template-out-header, template-out-cell, template-out-down, template-out-right, template-out-group, template-out-value-type, template-out-generate-clean

pebblestream:template-out-down

{"directive": "template-out-down"}

Validation

Because every argument is named and every repeated group is an array of objects, each directive's JSON Notation can be described exactly in JSON Schema. For join:

{
  "type": "object",
  "properties": {
    "directive": {"const": "join"},
    "sheet": {"anyOf": [{"$ref": "#/$defs/sheet"}, {"$ref": "#/$defs/cell-ref"}]},
    "on": {
      "type": "array",
      "minItems": 1,
      "items": {
        "type": "object",
        "properties": {
          "column": {"anyOf": [{"$ref": "#/$defs/header"}, {"$ref": "#/$defs/cell-ref"}]},
          "source-column": {"anyOf": [{"$ref": "#/$defs/header"}, {"$ref": "#/$defs/cell-ref"}]}
        },
        "required": ["column", "source-column"],
        "additionalProperties": false
      }
    }
  },
  "required": ["directive", "sheet"],
  "additionalProperties": false
}

Here #/$defs/cell-ref is an object with one required key, ref, whose value matches the A1 notation described under Cell references, and no other keys.

JSON Schema checks the shape of a directive. It cannot check rules that depend on the spreadsheet, such as whether source-column exists on the sheet named in sheet, or whether a directive's requires and excludes are satisfied by the other directives on the worksheet. The compiler checks those; submit the worksheet to the compiler service's validate endpoint before deploying.

Directives not yet in this notation

The Board directives and the connector directives will be added once their signatures are confirmed. div/0, header-count and split are deprecated and have no JSON Notation. coalesce is deprecated too: group now collapses each group into one row by default, so coalesce is redundant alongside it. Worksheets that already use it continue to work; leave it out of new worksheets. regex is disabled.