What causes "invalid JSON for calculatedFields: {ex.Message}" in iOfficeAI/OfficeCLI?

Run the JSON through a linter/validator before passing it; the inner exception pinpoints the position

iOfficeAI/OfficeCLI · error · ArgumentException

invalid JSON for calculatedFields: {ex.Message}

Error message

invalid JSON for calculatedFields: {ex.Message}

What it means

The 'calculatedFields' string could not be parsed as JSON at all — System.Text.Json threw a JsonException, which the helper wraps in an ArgumentException with the parser's own message appended. This is a hard parse failure (trailing comma, unquoted key, single quotes, unterminated string), not a shape problem. The inner exception text identifies the byte position of the syntax error.

Source

Thrown at src/officecli/Core/PivotTableHelper.Definition.cs:1732

                if (doc.RootElement.ValueKind != System.Text.Json.JsonValueKind.Array)
                    throw new ArgumentException("'calculatedFields' must be a JSON array");
                foreach (var el in doc.RootElement.EnumerateArray())
                {
                    if (el.ValueKind != System.Text.Json.JsonValueKind.Object)
                        throw new ArgumentException("each calculatedFields entry must be a JSON object");
                    string? name = null, formula = null;
                    foreach (var p in el.EnumerateObject())
                    {
                        if (p.NameEquals("name")) name = p.Value.GetString();
                        else if (p.NameEquals("formula")) formula = p.Value.GetString();
                    }
                    if (name != null && formula != null)
                        result.Add((name, formula));
                }
            }
            catch (System.Text.Json.JsonException ex)
            {
                throw new ArgumentException($"invalid JSON for calculatedFields: {ex.Message}");
            }
        }

        // Numbered + bare calculatedField props (ordinal sort so calculatedField1
        // appears before calculatedField2 regardless of insertion order).
        var cfKeys = properties.Keys
            .Where(k => System.Text.RegularExpressions.Regex.IsMatch(
                k, @"^calculatedField\d*$", System.Text.RegularExpressions.RegexOptions.IgnoreCase))
            .OrderBy(k => k, StringComparer.OrdinalIgnoreCase)
            .ToList();
        foreach (var key in cfKeys)
        {
            var raw = properties[key];
            if (string.IsNullOrWhiteSpace(raw)) continue;
            var colonIdx = raw.IndexOf(':');
            if (colonIdx < 0)
                throw new ArgumentException(
                    $"calculatedField '{raw}' must be 'Name:=Formula' (colon-separated)");

View on GitHub (pinned to 1ced45e900)

Solutions

Run the JSON through a linter/validator before passing it; the inner exception pinpoints the position
For anything beyond one field, prefer the singular calculatedField/ calculatedField1 / calculatedField2 props to avoid shell-JSON friction
Ensure double quotes around keys and string values, no trailing commas, and proper escaping of inner quotes

Example fix

// before
calculatedFields="[{name:'X',formula:'=A1'}]"
// after
calculatedFields="[{\"name\":\"X\",\"formula\":\"=A1\"}]"

Defensive patterns

Strategy: try-catch

Validate before calling

try { using var _ = System.Text.Json.JsonDocument.Parse(jsonRaw); }
catch (System.Text.Json.JsonException ex)
{ throw new InvalidOperationException($"calculatedFields JSON is invalid: {ex.Message}"); }

Type guard

static bool IsValidJson(string json)
{
    try { using var _ = JsonDocument.Parse(json); return true; }
    catch { return false; }
}

Try / catch

try { AddCalculatedFields(props); }
catch (ArgumentException ex) when (ex.Message.Contains("invalid JSON for calculatedFields"))
{ /* show inner parser message and re-prompt */ }

Prevention

Run JSON through a linter before submitting
Generate JSON with a serializer rather than string concatenation
Watch for trailing commas, single quotes, and unescaped inner quotes under shell quoting

When it happens

Trigger: Trailing comma: calculatedFields=[{"name":"X","formula":"=A1"},]; single-quoted keys (JSON requires double quotes); unescaped quotes inside the value; unterminated array or object; stray characters around the JSON.

Common situations: Hand-written JSON in a CLI argument that breaks under shell quoting; values containing quotes that weren't escaped; copy-paste from a pretty-printed source that picked up a comment or trailing punctuation; CRLF/encoding artefacts.

Understand the failure class

Parsing and encoding errors: unexpected token, malformed input — why parsers reject input and how to find the real culprit.

Related errors

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/32721b50412c0cb5. Report an issue: GitHub.