steinnes

import test from "./tapiary.ts";
import FormulaError from "./excel/FormulaError.ts";
import type { JSF } from "./jsf.ts";
import Model from "./Model.ts";

// Google Sheets' XLSX exporter writes t="str" on every formula cell with a non-numeric result,
// which conflates blank with "", and an error with text naming it. The tests below pass the
// recalc flags as false so that only the saved value can queue a cell for recalculation.

test('Model.addWorkbook: GS-flagged JSF recomputes formula cell with v:"" on load', t => {

steinnes

diff --git a/.changeset/google-sheets-ambiguous-saved-values.md b/.changeset/google-sheets-ambiguous-saved-values.md
new file mode 100644
index 0000000000..9d2f46a50a
--- /dev/null
+++ b/.changeset/google-sheets-ambiguous-saved-values.md
@@ -0,0 +1,7 @@
+---
+'@grid-is/apiary': minor
+---
+

steinnes

# Ambiguous formula results in a Google Sheets export

How Excel resolves formula results that a foreign producer saved ambiguously, using Google
Sheets as the producer.

Google Sheets' XLSX exporter writes `t="str"` on every formula cell whose result is text, an
error, or blank. (Boolean results still get `t="b"` and numbers the default numeric type, so
the conflation is not simply "everything non-numeric".) That collapses distinctions Google
Sheets itself makes, so both members of each of these pairs come out byte-identical:

steinnes

> @grid-is/apiary@18.3.2 ts
> TS=true ./test.sh gs-untrusted-saved-values-test

Running test in lib/gs-untrusted-saved-values-test.ts

(node:25111) ExperimentalWarning: Importing WebAssembly module instances is an experimental feature and might change at any time
(Use `node --trace-warnings ...` to show where the warning was created)
(node:25111) Warning: `--localstorage-file` was provided without a valid path

steinnes

{"id":5807795515,"node_id":"PRR_kwDODMCBMc8AAAABWivtOw","user":{"login":"steinnes","id":1097582,"node_id":"MDQ6VXNlcjEwOTc1ODI=","avatar_url":"https://avatars.githubusercontent.com/u/1097582?u=81ed2199c0bf799dc1e4c7195b96d7b375605194&v=4","gravatar_id":"","url":"https://api.github.com/users/steinnes","html_url":"https://github.com/steinnes","followers_url":"https://api.github.com/users/steinnes/followers","following_url":"https://api.github.com/users/steinnes/following{/other_user}","gists_url":"https://api.github.com/users/steinnes/gists{/gist_id}","starred_url":"https://api.github.com/users/steinnes/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/steinnes/subscriptions","organizations_url":"https://api.github.com/users/steinnes/orgs","repos_url":"https://api.github.com/users/steinnes/repos","events_url":"https://api.github.com/users/steinnes/events{/privacy}","received_events_url":"https://api.github.com/users/steinnes/received_events","type":"User","user_view_type":"public","site_

steinnes

{"id":5807795515,"node_id":"PRR_kwDODMCBMc8AAAABWivtOw","user":{"login":"steinnes","id":1097582,"node_id":"MDQ6VXNlcjEwOTc1ODI=","avatar_url":"https://avatars.githubusercontent.com/u/1097582?u=81ed2199c0bf799dc1e4c7195b96d7b375605194&v=4","gravatar_id":"","url":"https://api.github.com/users/steinnes","html_url":"https://github.com/steinnes","followers_url":"https://api.github.com/users/steinnes/followers","following_url":"https://api.github.com/users/steinnes/following{/other_user}","gists_url":"https://api.github.com/users/steinnes/gists{/gist_id}","starred_url":"https://api.github.com/users/steinnes/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/steinnes/subscriptions","organizations_url":"https://api.github.com/users/steinnes/orgs","repos_url":"https://api.github.com/users/steinnes/repos","events_url":"https://api.github.com/users/steinnes/events{/privacy}","received_events_url":"https://api.github.com/users/steinnes/received_events","type":"User","user_view_type":"public","site_

steinnes

{
  "commit_id": "853937ed56c99efd7c7624e10d48743066587bbd",
  "event": "APPROVE",
  "body": "\u2705 Approved at `853937e` vs `main` `f2a5889` (0 blocking, 0 non-blocking)\n\n## What was checked\n- Changed-contract reproducers: the 12 new `Model.addWorkbook` tests covering the Google Sheets saved-value ambiguity. Executed at the candidate head: `lib/Model-addWorkbook-test.ts` runs 185 tests with 0 failures. Negative control: the same 12 tests, ported onto base `f2a5889`, fail on 5 test cases (7 failing assertions of 23), so the tests pin the behavior the PR implements; the non-GS controls and the identification/opt-out cases pass at base as expected.\n- Preservation: outside Google Sheets, `t=\"str\"` stays authoritative \u2014 the non-GS and sub-confidence-floor / external-reference cases keep their saved values at base and head.\n- Oracle: the pinned GRID-is/excel-test findings (commit `ff1479af`, Excel for Mac 16.112, 2026-07-15) confirm Excel recalculates such files on load (no `calcId`) and resolves each

steinnes

  Sub DumpTints()
      Dim names As Variant, i As Long, c As Long, nm As String
      names = Array("20% - Accent1", "40% - Accent1", "60% - Accent1", _
                    "20% - Accent2", "40% - Accent2", "60% - Accent2", _
                    "20% - Accent3", "40% - Accent3", "60% - Accent3", _
                    "20% - Accent4", "40% - Accent4", "60% - Accent4", _
                    "20% - Accent5", "40% - Accent5", "60% - Accent5", _
                    "20% - Accent6", "40% - Accent6", "60% - Accent6")
      Cells(1, 1) = "Style": Cells(1, 2) = "Hex (RRGGBB)"
      For i = 0 To UBound(names)

steinnes

A simple way to test this is to run wait_for_port.sh against localhost on a random unused port:

$ .ci/wait_for_port.sh localhost 12345
.....

Then in another shell, listen on that port:

$ nc -l 12345

steinnes

import sys

UTF8Writer = codecs.getwriter('utf8')

class SafeStdoutWriter(UTF8Writer):
    """ A safe stdout writer class which expects to output only unicode string objects
    but if it encounters a decoding error (because the given string is not unicode but
    a normal 'str') it will attempt to convert it to unicode and try again.  If that
    conversion fails we fall back to decoding iso-8859-1 which is the default codec
    the CSVOutput writer uses.