Skip to main content

Value Serialization

This document describes how ICellRange reads and writes values at the human/agent boundary.

API Overview​

MethodDirectionBehavior
getValue()readNative JS type — number, boolean, string, error, or null
getValues()read2D array of native JS types
getText()readDisplay string with string-prefix type annotation
setValue(native)writeStores value directly, no parsing
setValue(string)writeParses using Excel conventions (same as typing into a cell)
setValue(string, { textParser: false })writeStores string literally, no Excel parsing

getValue() returns raw internal values — useful for programmatic computation in UDFs and data pipelines, but requires format knowledge to interpret numbers, dates, and percentages correctly. For example, a date returns 34495 and a percentage returns 0.04 — the display meaning is lost without the format context.

getText() is the recommended format for agent I/O because it returns values exactly as the user sees them — 10/23/1995 and 4% — with type annotation via the ' prefix for strings. No format knowledge required, and output round-trips naturally through setValue().

Agents should not do arithmetic on cell values directly. The calculation engine handles this correctly at scale. If computation is needed, write a formula — the engine computes, the agent reads the result via getText().

Reading Values​

getValue() — Native Type​

Returns the raw stored value as a native JS type:

cell.getValue()  // → 1234                    (number)
cell.getValue()  // → true                    (boolean)
cell.getValue()  // → "hello"                 (string)
cell.getValue()  // → FormulaError.Known.NA   (error)
cell.getValue()  // → null                    (blank cell)

getText() — Display String​

Returns values exactly as seen on screen. Strings are prefixed with a single quote (') to distinguish them from numbers, booleans, and errors — matching Excel's own text-prefix convention. All other types are undecorated.

cell.getText()  // → 4%          (number 0.04, percent format — no prefix)
cell.getText()  // → 2024-01-01  (date serial, date format — no prefix)
cell.getText()  // → 'hello      (string — single quote prefix)
cell.getText()  // → #N/A        (error — no prefix)
cell.getText()  // → TRUE        (boolean — no prefix)
cell.getText()  // → '           (empty string — bare single quote)
// blank cell → omitted from range output entirely

Type reference​

Cell containsgetText()getText({ hideStringPrefix: true })
Number 123412341234
Number 0.04, percent format4%4%
Date serial, date format2024-01-012024-01-01
BooleanTRUE or FALSETRUE or FALSE
Error#N/A, #VALUE!, etc.#N/A, #VALUE!, etc.
String hello'hellohello
String #N/A'#N/A#N/A
String TRUE'TRUETRUE
Explicit empty string'(empty)
Blank cell(omitted)(omitted)

Blank cells are omitted from range output entirely. A bare ' means the cell explicitly contains an empty string. Both display identically to the user but are semantically distinct — COUNTA(A1) returns 0 for a blank cell and 1 for "".

hideStringPrefix​

Suppresses the single quote prefix, returning bare display values. Use for UI rendering and clipboard operations where type annotation is not needed:

cell.getText({ hideStringPrefix: true })  // → hello    (string — no prefix)
cell.getText({ hideStringPrefix: true })  // → #N/A     (error or string — now ambiguous)

Note that suppressing the prefix makes strings, errors, and booleans visually indistinguishable when their display values collide (e.g. a string "TRUE" vs boolean TRUE). Prefer the default for any context where type matters.

format — Multi-cell output​

Controls how values are joined when getText() is called on a multi-cell range. The GetTextFormat options are shown below.

range.getText()                              // → markdown table (see below, default)
range.getText({ format: 'tsv' })             // → 'Apples\t0.69\t40
range.getText({ format: { delimiter: ';' }}) // → 'Apples;0.69;40
range.getText({ format: 'markdown' })        // → markdown table (see below)

hideStringPrefix and format are orthogonal. format is a layout choice; hideStringPrefix is a semantic choice (lossless vs. human-readable). They combine freely:

ContexthideStringPrefixLossless?
Agent I/O, unit test fixturesfalse (default)✓
Human rendering, UI clipboardtrue✗
Markdown for agentsfalse (default)✓
Markdown for humanstrue✗
range.getText({ format: 'markdown' })                          // lossless — agents
range.getText({ format: 'markdown', hideStringPrefix: true })  // readable — humans

Markdown format​

format: 'markdown' produces a GitHub-flavored markdown table. Column headers are sourced from the enclosing table definition when available, otherwise column letters (A, B, C...) are used. Row numbers appear in the first column for positional reference.

ABC
2'Apples0.6940
3'Bananas0.3438

When the selected range is part of a defined table, column headers are used instead of column letters.

FruitPriceCount
2'Apples0.6940
3'Bananas0.3438

Pipe characters: | inside cell values is escaped as \|. The column count is defined by the header row, so a parser can always distinguish escaped content from column separators. Markdown round-trips correctly for strings containing |.

Writing Values​

setValue(native) — Native Type​

Non-string values are stored directly with no parsing:

cell.setValue(1234)                    // number
cell.setValue(true)                    // boolean
cell.setValue(FormulaError.Known.NA)   // error
cell.setValue(null)                    // blank cell

setValue(string) — Excel-Convention Parsing​

String inputs are parsed using Excel conventions — the same rules as typing into a cell:

Input stringStored asDisplays as
'1234'number 12341234
'4%'number 0.04, percent format4%
'$1,234'number 1234, currency format$1,234
'2024-01-01'date serial, date format2024-01-01
'=SUM(A1:A10)'formulacomputed result
"'hello"string hello, quotePrefix formathello
"'123"string 123, quotePrefix format123

Forcing text with a leading apostrophe: prefix the value with ' — matching Excel's own text-prefix convention. setValue("'123") stores the string "123", not the number 123.

setValue(string, { textParser: false }) — Literal String​

Skips all parsing. The string is stored exactly as provided:

cell.setValue("'123")                         // stores string "123"  (apostrophe stripped, quotePrefix set)
cell.setValue("'123", { textParser: false })  // stores string "'123" (apostrophe kept, no parsing)
cell.setValue("4%",   { textParser: false })  // stores string "4%"   (not parsed as 0.04)

Round-Trip: getText <-> setValue​

getText() output round-trips naturally through setValue() — an agent reading output and writing input uses the same surface representation throughout:

getText()setValue(...)Stores
4%'4%'0.04, percent format
2024-01-01'2024-01-01'date serial, date format
1234'1234'number 1234
'hello"'hello"string hello, quotePrefix format
'#N/A"'#N/A"string #N/A, quotePrefix format
#N/A'#N/A'#N/A as FormulaError

The format anchor is always what the user sees. Neither an agent nor a fixture author needs to think about internal storage values.

Round-trip limitations by format:

FormatStrings with ,Strings with |Strings with \n
csvunescaped — not safesafesafe
tsvsafesafesafe
markdown (default)safesafe — escaped as |safe — escaped as \n
native getValues()safesafesafe

For guaranteed round-tripping of all values: use getValues() / setValues() with native JS types, or use getText({ format: 'tsv' }) if a text format is required.

Notes for Test Fixture Authors​

  • Inputs — use setValue string syntax (what the user would type), or native JS types for programmatic precision. true (boolean) and 'TRUE' (string that parses to boolean) are not the same.
  • Expected values — use getText() default format: strings prefixed with ', everything else undecorated.
  • Blank vs empty string — blank cells are omitted entirely; ' (bare single quote) means the cell explicitly contains an empty string.
  • textParser: false — only use when testing that a literal string survives storage without Excel parsing.