Functions

Each function maps to a formula under the CONNXL namespace, addressed by its module and name:

=CONNXL.SALES.TOP_CUSTOMERS(10)

Output shapes

A function's output shape decides what the result looks like once it lands in the grid — you pick it in the builder. (Separately, a function's kind — the sort of data source it runs against — is set when you choose a connection; the Functions reference lists all of them.)

• Single value — one value into the calling cell: a number, a string, a boolean.
• Row — a single record laid out across a row of cells.
• Table — rows × columns that spill into a range, with per-column headers and number formats mapped from the response.
• JSON — the raw JSON response as text, for when you want to parse it yourself.
• Entity — an Excel linked data type: a chip in the cell whose properties become field accessors like =A2.Price. Async-only.

Execution models

• Sync — a local Excel operation, run in the add-in with no call to the agent. Best for lightweight transforms.
• Async — the default. Excel shows #GETTING_DATA while the agent fetches; the add-in batches many cell calls into one request transparently.
• Streaming — the cell updates live as new values arrive. The agent streams to the add-in over Server-Sent Events on desktop, and falls back to polling on Excel for the web.

Build it in the dashboard

The function builder walks you through choosing a connection, declaring parameters, writing the query or request, and mapping the response to your chosen output kind. There is no JSON file to author by hand — the dashboard owns the metadata, and the agent generates everything Excel needs from your configuration.

A worked example

Say you have a REST connection named Catalog API and want your top products as a spilled table:

1. What it does — Fetch data. Basic info — name TOP_PRODUCTS, module CATALOG.
2. Connection — Catalog API. Request — GET /products, query parameter limit = 10.
3. Output — Table / Matrix. Rows path $.data[*]; columns Name → $.name, SKU → $.sku, Price → $.price.
4. Save, then publish the environment. In Excel: =CATALOG.TOP_PRODUCTS() spills the table.

A single value works the same way with the JSON path output — e.g. GET /products/{'{{'}id{'}}'} with a numeric id parameter and path data.name returns one product's name. A database function is identical except the request step is a SQL statement (SELECT name, total FROM orders ORDER BY total DESC) against a Postgres/MySQL/SQL Server connection.

A sync formula example

For Local calculation (sync) functions, the "parameters" step becomes a formula editor instead: no connection, no request, nothing for the agent to call out to. Write it in familiar Excel syntax, and the dashboard compiles it into the exact expression tree the agent's sandboxed evaluator runs in-cell, without ever touching a data source:

=CONCAT(UPPER([name]), " — ", [id])

Every [bracketed] reference becomes a function parameter, in the order it first appears — nothing to declare separately. The supported function set is closed and deliberately small, so it can be sandboxed safely: text (CONCAT/CONCATENATE, UPPER, LOWER, TRIM, LEN, LEFT, RIGHT, MID, SUBSTITUTE), math (MOD, ROUND, ABS, MIN, MAX, plus the ordinary + - * / operators), comparisons (= < > <= >= <>) and logic (IF, AND, OR, NOT, COALESCE), and date parts (YEAR, MONTH, DAY). & concatenates, same as Excel.

Mapping a table or entity response

When the output is Table or Entity, the Output step includes a sampler so you don't have to guess JSONPath by hand:

1. Fetch a sample response — runs your request once, live, against the real connection, and shows you the actual JSON that comes back.
2. Click the fields you want — the sample is parsed into a flat list of candidate fields; clicking one adds it as a column (or entity property) and fills in its path for you.
3. If the response contains more than one plausible list of rows — a nested array, a wrapped envelope — a row-candidate picker lets you pick which one is "the table"; the field list updates for whichever candidate you choose.
4. A live preview renders the first rows as an actual table, so you can check the mapping before you save.

{
"data": [
  { "name": "Widget", "sku": "W-100", "price": 19.99 },
  { "name": "Gadget", "sku": "G-200", "price": 34.5 }
]
}

Fetching a sample like this one detects $.data[*] as the row path and offers name, sku, and price as one-click columns.

Writing data back

Most functions read. Some connections can also write, and the builder's first step offers the write operation next to Fetch data when the connection supports one — turning the workbook into an upload client instead of a report.

• SQL databases — Insert rows. You pick the target table and the parameter that carries the rows, and the agent assembles a parameterized multi-row INSERT for you. Works on every pooled engine, Supabase included. There is no free-text SQL here: table and column names must be plain identifiers, and the values travel as bound parameters, so a workbook header can never turn into a statement.
• MongoDB — insertOne, insertMany, updateMany, and deleteMany on a collection.
• DynamoDB — put_item for a single object, put_items for a whole array.
• Cosmos DB — upsert writes the rows into a container.

Uploads are safe to retry. Each click mints one upload identity that every chunk carries, and the connectors use it to replace rather than append: the SQL insert stamps the rows and swaps the chunk in a single transaction, Cosmos derives its document ids from it, DynamoDB overwrites by table key. So re-clicking after a partial failure fixes the upload instead of doubling it. When you upload to your own API instead, the ${upload.*} template variables hand you the same identity to deduplicate on — see the Functions reference.

Testing a function

Versioning and Promote

Functions are edited only in the source environment (Development). When you're ready to move a change forward, Promote it to a downstream environment — QA, staging, production. Promote copies the structure while preserving the target environment's own connection values, and brand-new resources arrive with blank credentials for you to fill. You can promote from a sibling environment's live config or from a saved named version, so releases are repeatable.