SPARKLINE: implement the Google Sheets in-cell chart function

[hhimanshu]

What

Implement SPARKLINE, a Google Sheets function TrueCalc does not currently have. Confirmed absent: no match for sparkline anywhere under crates/.

Why

SPARKLINE is a real conformance gap. It is one of the few Sheets functions whose result is a miniature chart rather than a scalar, and it is widely used for trend columns inside tables. Any consumer rendering a Sheets-compatible grid needs the engine to accept and validate it, even if that consumer draws the pixels itself.

Signature

SPARKLINE(data, [options])

• data — a range or array. For most chart types a single row or column; for bar mode, two values.
• options — an optional 2-column range or inline array of key/value pairs, e.g. {"charttype","column"; "color","red"}.

Google Sheets' documented option keys include charttype (line | bar | column | winloss), color, lowcolor, highcolor, firstcolor, lastcolor, negcolor, axis, axiscolor, ymin, ymax, linewidth, max, min, empty, nan, rtl. Do not take that list as ground truth — verify against the live oracle before implementing (see below).

The design question this raises

SPARKLINE returns something that is not a number, string, bool, date or error — it is a render spec. That does not fit the existing Value shape, so this needs a deliberate decision before code:

1. A new Value variant carrying the parsed spec, which every consumer must then handle; or
2. A distinct typed value that renders as a spec and coerces predictably elsewhere (what does =SPARKLINE(A1:A5)+1 do? what does LEN() of it do? what does it export as?)

Whatever is chosen, the cross-surface determinism guarantee still applies: the same workbook must produce the same result everywhere. Decide and record this before implementing — it is the load-bearing part, not the option parsing.

Ground truth first — do not self-confirm

Per the repo's rules, expected values come from the Google Sheets conformance fixtures, never from reasoning about what Sheets probably does. Before implementing:

• Confirm the accepted option keys, their casing/synonyms, and their defaults.
• Confirm the error behaviour: what happens with a non-range data, an odd-shaped options, an unknown option key, a bar sparkline given three values, empty/blank cells inside the range, text inside the range.
• Confirm what charttype defaults to when omitted.

Fixtures are immutable ground truth; add rows through the fixtures pipeline rather than hand-writing expected values.

Acceptance criteria

• A recorded decision on how a non-scalar render spec fits the value model, including coercion and export behaviour
• SPARKLINE parses data + options and validates them, with Sheets-matching errors for malformed input
• Conformance fixture rows covering: each charttype, omitted charttype, colour options, ymin/ymax, blanks inside the range, text inside the range, wrong-arity bar, unknown option key, malformed options shape
• Tests live in separate test files (not inline #[cfg(test)] blocks), per the repo convention
• The function is listed by the function-registry surfaces and documented
• CI green

Notes

Renderers already exist on the consumer side, so the engine work here is parsing, validation and the value-model decision — not drawing. Scope this issue to the engine.

[hhimanshu]

Value-model decision — blocked on live-oracle verification, need owner input

I looked into this before writing any code, per the issue's design-question section. Summary: I mapped the architecture blast radius of adding a new Value variant, and I attempted to verify the empirical Sheets behaviors the issue calls out (+1 coercion, LEN(), export shape, error text for malformed input) — but I don't have a way to drive an authenticated Google Sheets session from this environment, so none of that verification happened. Recording what I found and what's still open, rather than guessing.

Architecture facts (from this repo, not Sheets)

A value coming out of the evaluator doesn't have one representation — it has four, each hand-rolled and each would need a new arm for any new variant:

1. crates/core/src/types/value.rs — the internal Value enum (Number, Text, Bool, Error, ErrorMsg, Empty, Array, Date, Zoned). No serde derive; coercion (to_number/to_string_val/to_bool in crates/core/src/eval/coercion/mod.rs) and comparison (crates/core/src/eval/mod.rs) are exhaustive matches with no wildcard — every variant gets an explicit, deliberate semantics decision, by design.
2. crates/workbook/src/value.rs — the wire/JSON Value (tagged {"type": "...", "value": ...}), with hand-written Serialize/Deserialize that rejects unknown type tags on decode.
3. crates/wasm/src/lib.rs — EvalResult, a third enum (Tsify-derived, generates the TS types npm consumers see).
4. crates/mcp/src/main.rs — two more ad hoc serde_json::Value builders (value_to_json for core Value, wb_value_to_json for workbook Value), each with its own tag shape.

There's zero precedent in the codebase for a non-scalar/rich result — no IMAGE, no chart type, no "declined to fully implement, returns X" convention beyond ordinary error codes. The closest existing vocabulary is ErrorKind::Unsupported (#UNSUPPORTED!), used elsewhere for "not implemented for this engine," and Value::ErrorMsg(ErrorKind::Value, "<diagnostic>") for malformed-input errors (the pattern QUERY's options parser uses).

Net: a real new Value variant is not a small change — it's ~15 files inside crates/core (everywhere Value::Zoned currently appears is a reasonable proxy, since that was the last variant added) plus the 4 parallel representations above, ~22 files total, each requiring a deliberate semantics call, not a mechanical stub.

What I could not verify

The issue is explicit that +1 coercion, LEN()/text coercion, export shape, and error text all have to come from the live Google Sheets oracle, never guessed. I tried two paths to reach it and both are closed off in this session:

• The deployed evaluator endpoint requires an authenticated Google session (an unauthenticated request to it returns Google's generic "Sorry, unable to open the file at present" — Drive-level auth gate, not a formula-level response).
• Driving a real browser against that authenticated session (the normal way to reach it) needs a locally reachable browser debug port, and this environment's outbound access to localhost/loopback is sandboxed off — so even with a running, logged-in Chrome, nothing here can attach to it.

So no fixture rows for this issue got created, and the value-model decision itself is still open. I don't think it's right for me to invent an answer here — the issue text is explicit that reasoning about what Sheets "probably" does is not an acceptable substitute for the oracle, and I'd be doing exactly that if I picked one of the below unverified.

What's actually worth checking, concretely

Since SPARKLINE's in-cell result is a rendered chart, not text, the pipeline's normal "read the cell's displayed value" approach only tells you something directly for the error paths (a malformed call almost certainly still shows a plain-text #...! error in the cell). For the value-model question, the useful probes are formulas that wrap a SPARKLINE() call and read text back out of the wrapper cell:

• =SPARKLINE({1,2,3})+1 — does it error, and with what code, or does it coerce to something numeric?
• =LEN(SPARKLINE({1,2,3}))
• =TYPE(SPARKLINE({1,2,3})) — which of Sheets' TYPE() buckets it falls into
• =ISERROR(SPARKLINE({1,2,3}))
• ="x"&SPARKLINE({1,2,3}) — concatenation/text coercion
• What a spreadsheets.values.get read (or copy → paste-special "values only") returns for a cell holding SPARKLINE(...) — this is the closest proxy for "what it exports as"
• Error text/code for: unknown option key, malformed options shape, bar given the wrong number of values, non-range data, blank/text inside the range — these are likely plain-text errors and verifiable through the pipeline's normal path once someone with oracle access runs them

My (unverified) leaning, offered as input not a decision

Given the blast radius above, if I had to bet: representing the happy-path result as Value::ErrorMsg(ErrorKind::Unsupported, "...") (i.e. the engine parses and validates SPARKLINE()'s arguments fully — real Sheets-matching errors for every malformed case — but the valid case surfaces as a value the engine is explicit about not rendering, rather than inventing a fifth parallel non-scalar type across four representations) is the cheaper and more honest option unless the oracle shows Sheets treats a valid SPARKLINE() result as something arithmetic/text code actively depends on (e.g. if +1 or LEN() on it behaves like a normal value rather than erroring, that would argue for a real payload-carrying variant instead). But this is a guess about engineering cost, not a claim about Sheets — I'd want the oracle answers above before either of us commits to it.

Where this leaves the issue

Scoping to what's verifiable without the oracle: none of the acceptance criteria can be safely closed out yet — even the "validate with Sheets-matching errors" criterion needs the oracle to know the actual error codes/text. I'm stopping here rather than shipping a guess. Happy to pick this back up and do the full implementation (value-model change + parsing/validation + fixture rows) once the formulas above have real oracle answers, however they come in.

[hhimanshu]

Unblocked — the value-model question is answered from the oracle

Ground truth now exists: 26 probe rows evaluated against real Google Sheets through the conformance pipeline, rather than reasoned about.

The answer

=TYPE(SPARKLINE({1,2,3}))  →  128

TYPE() documents 1=number, 2=text, 4=boolean, 16=error, 64=array. 128 is undocumented. So Sheets models a sparkline as its own value kind — not as any existing scalar, and not as an error (ISERROR is FALSE).

That settles the design question this issue was stuck on: there is no way to represent this faithfully by folding it into an existing variant. Sheets itself gave it a distinct type tag.

Coercion is inconsistent, and not guessable

Expression	Result
SPARKLINE(…) + 1	#VALUE!
"x" & SPARKLINE(…)	#VALUE!
LEN(SPARKLINE(…))	0
N(SPARKLINE(…))	0
TO_TEXT(SPARKLINE(…))	""
SPARKLINE(…) = SPARKLINE(…)	TRUE
ISERROR(SPARKLINE(…))	FALSE

Arithmetic and concatenation reject it; LEN/N/TO_TEXT quietly treat it as empty/zero. Equality works. Any implementation has to reproduce that asymmetry deliberately — it is not derivable from a principle.

Error behaviour

Case	Result
unknown charttype value	#VALUE!
unknown option key	no error — silently ignored
odd-shaped options	#REF!
scalar instead of a range	#N/A
empty array	#REF!
single value	#N/A
no arguments	#N/A
text inside the data	renders, no error
bar with three values	renders, no error

Note the asymmetry: an unknown option key is ignored, while an unknown charttype value errors. And bar tolerates a third value despite being documented as two-valued.

One row not to trust

=SPARKLINE({1,,3}) → #ERROR! is most likely an array-literal parse error, not SPARKLINE semantics. Re-probe with a real range containing a blank cell before encoding anything from it.

What this means for the work here

The acceptance criterion "a recorded decision on how a non-scalar render spec fits the value model" now has evidence behind it rather than a judgement call. The remaining decision is narrower: whether to mirror Sheets' distinct type tag with a new value variant — which ripples through the four value representations noted earlier — or to approximate it and accept documented divergence on the coercion rows above.

That is still a real decision, but it is now an informed one, and the option parsing / validation / fixtures work behind it is mechanical.

Fixture rows are in the private fixtures repo pending review; they export here once merged.

[hhimanshu]

Ground truth is now recorded — 28 rows, observed live

The conformance fixtures for SPARKLINE have been captured through the Google Sheets pipeline, so the "do not self-confirm" blocker on this issue is cleared. Summary of what Sheets actually does, so the value-model decision can be made against evidence rather than guesses.

The result is a distinct value kind, not text and not an error

Probe	Result
=TYPE(SPARKLINE({1,2,3}))	128
=ISERROR(SPARKLINE({1,2,3}))	FALSE
=ISNA(SPARKLINE({1,2,3}))	FALSE
=SPARKLINE({1,2,3}) displayed	(empty string)

128 is not in the documented TYPE return set (1 number / 2 text / 4 boolean / 16 error / 64 array). Sheets is reporting an undocumented sixth kind. That is the single most useful fact here: it settles option 1 vs option 2 in the issue body — Sheets itself models this as a distinct value kind, not as a specially-formatted string.

Coercion is asymmetric, and the asymmetry is the interesting part

Probe	Result
=SPARKLINE({1,2,3})+1	#VALUE!
="x"&SPARKLINE({1,2,3})	#VALUE!
=LEN(SPARKLINE({1,2,3}))	0
=N(SPARKLINE({1,2,3}))	0
=TO_TEXT(SPARKLINE({1,2,3}))	""
=SPARKLINE({1,2,3})=SPARKLINE({1,2,3})	TRUE

So it is not simply "empty text": & on empty text would concatenate, but here it errors. Arithmetic and concatenation reject it outright, while LEN/N/TO_TEXT treat it as empty. Any implementation that models this as Value::Text("") will pass LEN/N/TO_TEXT and then silently diverge on & — worth a targeted test.

Equality against an identically-constructed sparkline is TRUE, which means value identity is over the parsed spec, not over object identity.

Errors and validation

Probe	Result
=SPARKLINE(5) (scalar, not a range)	#N/A
=SPARKLINE({5}) (single value)	#N/A
=SPARKLINE() (no arguments)	#N/A
=SPARKLINE({}) (empty array)	#REF!
=SPARKLINE({1,2,3},{"charttype"}) (odd-shaped options)	#REF!
=SPARKLINE({1,2,3},{"charttype","bogus"}) (unknown charttype value)	#VALUE!
=SPARKLINE({1,2,3},{"bogus","x"}) (unknown option key)	ignored — renders fine

Three distinct error codes for three distinct failure classes, and they do not follow the intuitive grouping:

• Arity/shape of data → #N/A (including no-args, which is unusual — most functions give #N/A only on lookup misses)
• Structural malformation (empty array, non-pair options) → #REF!
• Bad option value → #VALUE!
• Bad option key → silently ignored, not an error

That last one matters for forward compatibility: an unrecognised key is not a failure, so a workbook using a newer option key still evaluates.

Cases that render fine (no error)

• Each charttype: line, column, bar, winloss
• bar given three values, despite bar being documented as two-valued
• Colour options ({"charttype","line";"color","red"})
• ymin/ymax
• Text inside the data ({1,"a",3})
• All-negative data ({-1,-2,-3})
• A genuine blank cell inside the source range

One correction to an earlier reading

The probe =SPARKLINE({1,,3}) returns #ERROR!, which looks like "blanks in the data are an error." It is not. {1,,3} is invalid array-literal syntax, so Sheets fails at parse time and SPARKLINE never runs.

Re-probed against a real range with a deliberately-empty cell: =SPARKLINE(H1:H3) with H2 blank returns the same empty-string sparkline as the all-present control =SPARKLINE(I1:I3). A genuine blank inside the range is fine. Implementing an error path for blanks off the original row would have baked a real divergence into the conformance suite.

Suggested reading of the value-model question

There is already precedent in crates/workbook/src/value.rs: the ErrorMsg variant was added for Google Sheets parity, carrying additive diagnostic metadata that is excluded from value identity (hand-written PartialEq/Hash) and dropped by canonical serialization. A sparkline variant can follow the same shape — payload is the parsed, validated spec, participating in equality (the = probe requires it), with the serialization question decided separately.

The remaining open question this ground truth does not answer is the export/serialization shape, since every text projection of the value is "". That needs its own decision rather than an inferred one.

[hhimanshu]

Decision: serialization carries the parsed spec

The one question the fixtures could not answer — what a sparkline value serializes as — is settled: serialize the parsed spec.

Reasoning, from the recorded ground truth rather than preference:

=SPARKLINE({1,2,3})=SPARKLINE({1,2,3}) returns TRUE. Value identity is therefore over the parsed spec, not over object identity and not over the (always empty) text projection. Any lossier serialization — empty string, or dropping the value and recomputing from the formula — collapses two genuinely different sparklines into one canonical form, which breaks equality and anything built on top of canonical hashing.

This is the point where the ErrorMsg precedent stops applying. ErrorMsg's extra payload is excluded from value identity by its hand-written PartialEq/Hash, which is exactly why dropping it from canonical serialization is safe. A sparkline's spec is the opposite: it is the identity. Follow ErrorMsg's shape (a variant carrying a validated payload), not its identity/serialization treatment.

Practical consequence for the implementation: PartialEq/Hash for the sparkline variant should be derived over the parsed spec, not hand-written to ignore it.

[hhimanshu]

Correction: the identity claim in my earlier comment was wrong

My comment above ("Decision: serialization carries the parsed spec") justified the design with this:

=SPARKLINE({1,2,3})=SPARKLINE({1,2,3}) returns TRUE. Value identity is therefore over the parsed spec.

That inference does not hold. The row was missing its control. A fresh oracle probe supplies it:

Probe	Result
=SPARKLINE({1,2,3})=SPARKLINE({1,2,3})	TRUE
=SPARKLINE({1,2,3})=SPARKLINE({9,9,9})	TRUE
=SPARKLINE({1,2,3},{"charttype","column"})=SPARKLINE({1,2,3},{"charttype","line"})	TRUE
=SPARKLINE({1,2,3},{"bogus","x"})=SPARKLINE({1,2,3})	TRUE

Every sparkline compares equal to every other sparkline under =, whatever its data, charttype or options. The original row was equally consistent with "identity is the spec" and with "all sparklines are equal"; only the control separates them, and it lands on the second.

The conclusion survives, for a different reason

Do still carry the parsed spec through serialization — but the justification is COUNTUNIQUE, not =:

Probe	Result
=COUNTUNIQUE(SPARKLINE({1,2,3}),SPARKLINE({9,9,9}))	2
=COUNTUNIQUE(SPARKLINE({1,2,3}),SPARKLINE({1,2,3}))	1

The spec is retained and distinguishable — Sheets simply uses a deeper key there than = does. A lossy serialization would still be wrong; it would just break COUNTUNIQUE instead of =.

Concrete consequence for the implementation: PartialEq must NOT be derived over the spec, because the = operator has to report TRUE for any two sparklines. The uniqueness key is a separate, deeper comparison. That is close to the inverse of what my earlier comment asked for, and it is the part to get right.

Everything else the probe settled

Ordering is real, not an error. A sparkline ranks above every scalar; two sparklines are mutually equal:

>1 TRUE · >"zzzz" TRUE · >TRUE TRUE · > another sparkline FALSE · < another FALSE · >= itself TRUE

Text and boolean contexts are permissive. The +1 and & errors made an "everything errors" reading tempting; it is wrong:

LEFT → "" · TEXT → "" · TEXTJOIN → "" · CONCATENATE(sparkline,"x") → "x" · EXACT(sparkline,"") → TRUE · IF(sparkline,1,2) → 2 (falsy) · COUNTA → 1 · ISBLANK → FALSE

Note & errors while CONCATENATE concatenates — a genuine Sheets asymmetry. And =SPARKLINE(...)="" is FALSE even though EXACT(...,"") is TRUE.

Aggregates skip rather than error: SUM(sparkline, 1) → 1, MAX(sparkline, 1) → 1.

The alias functions agree with the operators: EQ → TRUE, NE → FALSE, <> → FALSE. Any divergence between = and EQ is an implementation defect, not Sheets behaviour.

Method note

Nine of the probe formulas were initially written without a leading =, so Sheets stored them as literal text and returned plausible-looking garbage. The run reported success. It was caught by reading the output values rather than trusting the exit status — and one of the nine was the control row above, the one that changes the conclusion. Worth stating plainly, because the failure mode is silent and the first pass would have confirmed the wrong answer.