Worked examples · Document fill

Filling a workbook from its sources.

Each empty cell of a declared column is filled from a declared source, by exact match on the row's key. A cell with no match, with more than one, or that already holds a value is refused, and left as it was.

The problem

In the most widely used spreadsheet program, the standard lookup function matches approximately unless it is told otherwise, and on an unsorted column it can return a value from another row without saying so.

Research on spreadsheets has found errors to be frequent and consequential. A filled cell that cannot say where its value came from cannot be checked.

Recorded outputOne fill call: a small constructed workbook, two small sources made for the purpose and one public data set, under a declaration of three columns. No AI engine was configured.

The declaration

The declaration names the sheet, the header row, the key column, and for each column to fill: the source, the field, and how the value is written.

countries: CSV, 146 bytes, 6 facts read
gdp: CSV, 562,767 bytes, 27,958 facts read
names: HTML, 278 bytes, 6 facts read

Recorded outputThe declaration, as sent.

{
  "sheet": "Lookup",
  "headerRow": 1,
  "keyColumn": "A",
  "fill": [
    {
      "column": "B",
      "source": "countries",
      "field": "latitude",
      "as": "number"
    },
    {
      "column": "C",
      "source": "names",
      "field": "name"
    },
    {
      "column": "D",
      "source": "gdp",
      "field": "Value"
    }
  ],
  … 1 more field: the schema identifier, printed in the API reference
}

Every cell, filled or refused

POST /api/tenants/{tenant}/fill · Content-Type: application/json

filled5refusedno_match 8refusedtarget_not_empty 1refusedambiguous 1

Cell	Key	Result	Value	Source, or why not
B2	AD	filled	42.546245	countries · latitude
C2	AD	filled	Andorra	names · name
D2	AD	refusedno_match		no fact in this source has the subject "AD" (case and whitespace aside) under the field "Value", so the cell is left blank rather than filled from a near match. This source is a GRID (csv or xlsx), and the shipped grid reader emits numeric cells only, so a text-valued column is not retrievable from it at all - a named coverage bound of the reader, not a statement that the source lacks the value.
B3	AE	filled	23.424076	countries · latitude
C3	AE	filled	United Arab Emirates	names · name
D3	AE	refusedno_match		no fact in this source has the subject "AE" (case and whitespace aside) under the field "Value", so the cell is left blank rather than filled from a near match. This source is a GRID (csv or xlsx), and the shipped grid reader emits numeric cells only, so a text-valued column is not retrievable from it at all - a named coverage bound of the reader, not a statement that the source lacks the value.
B4	ZZ	refusedno_match		no fact in this source has the subject "ZZ" (case and whitespace aside) under the field "latitude", so the cell is left blank rather than filled from a near match. This source is a GRID (csv or xlsx), and the shipped grid reader emits numeric cells only, so a text-valued column is not retrievable from it at all - a named coverage bound of the reader, not a statement that the source lacks the value.
C4	ZZ	refusedno_match		no fact in this source has the subject "ZZ" (case and whitespace aside) under the field "name", so the cell is left blank rather than filled from a near match.
D4	ZZ	refusedno_match		no fact in this source has the subject "ZZ" (case and whitespace aside) under the field "Value", so the cell is left blank rather than filled from a near match. This source is a GRID (csv or xlsx), and the shipped grid reader emits numeric cells only, so a text-valued column is not retrievable from it at all - a named coverage bound of the reader, not a statement that the source lacks the value.
B5	AF	filled	33.93911	countries · latitude
C5	AF	refusedtarget_not_empty		cell C5 already holds a value or a formula; a fill writes into blanks and never overwrites what the workbook states.
D5	AF	refusedno_match		no fact in this source has the subject "AF" (case and whitespace aside) under the field "Value", so the cell is left blank rather than filled from a near match. This source is a GRID (csv or xlsx), and the shipped grid reader emits numeric cells only, so a text-valued column is not retrievable from it at all - a named coverage bound of the reader, not a statement that the source lacks the value.
B7	Afghanistan	refusedno_match		no fact in this source has the subject "Afghanistan" (case and whitespace aside) under the field "latitude", so the cell is left blank rather than filled from a near match. This source is a GRID (csv or xlsx), and the shipped grid reader emits numeric cells only, so a text-valued column is not retrievable from it at all - a named coverage bound of the reader, not a statement that the source lacks the value.
C7	Afghanistan	refusedno_match		no fact in this source has the subject "Afghanistan" (case and whitespace aside) under the field "name", so the cell is left blank rather than filled from a near match.
D7	Afghanistan	refusedambiguous		23 facts in this source have the subject "Afghanistan" under the field "Value" and they state 23 different values ("3521418059.923445", "2813571753.8725324", "3825701438.9996333", "4520946818.545814", "5224896718.67782", "6203256538.709669", …); no rule picks one, so the cell is left blank.

The third source is keyed by country name, not by code, so no row's code matched it, and every cell in that column was refused rather than guessed. Where a row's key was a name, that source held 23 values for it, one per year, and the cell was refused as ambiguous.

A cell that already held a value was left alone. A fill writes into blanks and never overwrites what the workbook states.

Output: lookup.filled.xlsx, 2,110 bytes
Output sha256: fa0edb9ff84e0845a796dc3c4bb6acd48c8e45093505d1903f7904d80a210ed5
Rows with no key: 6

Boundary

CERTIFIED: every filled cell holds, verbatim, the one value its declared source states for that row's key under the declared field, located by an exact key match (case and whitespace aside); every cell not filled names why; and the output workbook is exactly the file whose sha256 is stated here.

NOT ATTESTED This certificate does not attest that any source value is true. It does not attest that the declared field is the right field for the column. It does not attest that a key with no match has no value anywhere. It does not attest anything about cells the declaration did not name.

Check it without Factum

The certificate is published as it was returned. Download it with the key and the Factum Verifier, and run the command. It needs Node 18 or later and nothing else.

Verifiedexit 0

The files: certificate.json, public-key.txt, the workbook, the filled workbook, SHA256SUMS.

The certificate signs the filled workbook's sha256. Hash the downloaded workbook and compare.

Recorded outputThe verifier's output on this certificate (exit code 0).

node factum-verify.mjs certificate.json public-key.txt

VERIFIED
  keyId            vd-2b223ab58835e187
  canonical bytes  8745
  barcode          … (the signed barcode, 634 characters, printed in full when you run it)
  surface          FULL - the body carries no surface block, so every claim the run captured is on it

  CERTIFIED BY THIS CHECK: this document was signed by the holder of the private half of the
  key you supplied, over the canonical form named in its signature block, and has not been
  altered by one byte since.
  NOT CERTIFIED BY THIS CHECK: that the key you supplied is the operator's. That comes from
  WHERE YOU OBTAINED THE KEY and from nothing in this file - a valid signature under the wrong
  key is exactly what a forgery looks like.