Functions reference

Function kinds

The kind is inferred from the connection you build on. Each maps to one family of data source; the builder shows the fields that kind needs (a SQL query, an HTTP request, a collection filter, and so on).

• http — a REST/HTTP request: method, URL template, query, headers, and body, with parameters bound from the formula arguments.
• graphql — a GraphQL query with variables against a GraphQL endpoint.
• database — a parameterized SQL query. The dialect follows the connection: Postgres, MySQL, SQL Server, Oracle, Azure Synapse, or AWS Athena.
• mongo — a MongoDB find, aggregate, or count on a collection (filter, pipeline, sort, limit).
• cosmos — an Azure Cosmos DB SQL query against a container.
• dynamodb — an AWS DynamoDB get-item or query by partition/sort key.
• lambda — an AWS Lambda invocation with a JSON payload, optionally reading a field out of the response.
• azure_function — an HTTP-triggered Azure Function.
• gcp_function — an HTTP-triggered Google Cloud Function.
• file — reads a remote file (over HTTP(S), S3, SFTP, FTP, Azure Blob, or Google Cloud Storage) parsed as CSV, JSON, or JSONL.
• kv — a read from a key-value cache (get / mget / scan) against a Valkey- or Redis-compatible store.

Four of these — database, mongo, dynamodb and cosmos — can also write; see Write operations below.

Output shapes

The shape decides how the result lands in the grid.

• single — one value into the calling cell (number, string, boolean).
• row — one record across a row of cells.
• table — rows × columns that spill into a range, with headers and number formats mapped from the response.
• json — the raw JSON response as text.
• entity — an Excel linked data type: a cell chip whose properties become field accessors like =A2.Price. Async-only; these appear under the taskpane's Data types tab.

Execution models

• sync — computed locally in the add-in, with no call to the agent. Best for lightweight transforms.
• async — the default. The agent fetches while Excel shows #GETTING_DATA; the add-in batches many cell calls into one request.
• streaming — the cell updates live as new values arrive. Server-Sent Events on desktop, with a polling fallback on Excel for the web.

Streaming details

A streaming function's stream block controls exactly how updates reach the cell:

• Transport — sse (Server-Sent Events) or polling. SSE is only available when the function's connection is http — every other connection kind streams by polling, and Excel for the web always falls back to polling, since long-lived SSE connections aren't reliable across every browser sandbox. Polling carries its own interval, in seconds; SSE has none — the agent forwards events as they arrive.
• emitOnChangeOnly — when set, the agent only pushes a new value into the cell when it actually changed, instead of re-emitting the same value on every tick.
• cancelUpstream — on by default. When the cell is deleted or the user navigates away, the agent tears down its upstream subscription instead of continuing to poll or hold a connection open for nobody.

Write operations

Four kinds send data the other way. The operation is picked in the builder, and a function has exactly one.

The SQL path never assembles a statement from free text: the table name and every column name must be a plain identifier — a letter or _ followed by letters, digits, and _ — and the values ride as bound parameters. A workbook header can't smuggle SQL into a statement.

Uploads are safe to retry by construction. Every chunk of one upload carries the same identity (the ${upload.*} variables below): the SQL insert stamps _upload_id and _chunk onto each row and replaces that chunk inside a single transaction, Cosmos derives its document ids from the upload identity, and DynamoDB overwrites by table key. Re-clicking after a partial failure therefore replaces what was already written instead of duplicating it.

Function flags

Three independent switches, available regardless of kind:

Template variables

Anywhere a function definition takes free text that is sent to the data source — an HTTP endpoint path, header or query values, the body template, a SQL statement, or a parameter's default — you can reference predefined variables with ${...}. The agent resolves them fresh on every call, server-side; the workbook never sees or supplies the values, so they can't be spoofed from a cell. An unknown ${...} reference is left untouched rather than blanked.

Built-ins — always available

The signed-in user — ${auth.*}

Resolved from the end user's validated sign-in token. Each is an empty string when no user is signed in — combine with a user_token connection or access control when a value must be present.

Upload identity — ${upload.*}

Set only when the call comes from a taskpane Upload range block; each is an empty string otherwise. Large uploads are sent in chunks, and a retried click re-sends the same identity — so your API can deduplicate by (id, chunk) instead of storing duplicates.

Values are escaped for the context they land in (a SQL statement escapes differently from a JSON body), so a name containing a quote can't break the request.