iOfficeAI/OfficeCLI · error · ArgumentException

'calculatedFields' must be a JSON array

Error message

'calculatedFields' must be a JSON array

What it means

The 'calculatedFields' property is parsed as JSON and the parser requires its root element to be a JSON array. Passing any other top-level JSON shape (object, string, number) is rejected up front rather than silently coerced, because the helper expects a list of {name,formula} objects. This keeps malformed input from producing a partial or wrong set of calc fields.

Source

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

    ///   calculatedField=Name:=Formula
    ///   calculatedField=Name:Formula     (leading '=' optional)
    ///   calculatedField1=..., calculatedField2=...
    ///   calculatedFields=[{"name":"X","formula":"..."}, ...]  (JSON)
    /// </summary>
    private static List<(string name, string formula)> ParseCalculatedFieldSpecs(
        Dictionary<string, string> properties)
    {
        var result = new List<(string, string)>();

        // JSON form first — higher fidelity when user wants multiple specs.
        if (properties.TryGetValue("calculatedFields", out var jsonRaw)
            && !string.IsNullOrWhiteSpace(jsonRaw))
        {
            try
            {
                using var doc = System.Text.Json.JsonDocument.Parse(jsonRaw);
                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}");
            }

View on GitHub (pinned to 1ced45e900)

Solutions

1. Wrap the object(s) in an array: calculatedFields=[{"name":"X","formula":"=A1"}]
2. For a single field, prefer the singular colon form: calculatedField=X:=A1
3. Validate the JSON shape with a quick parse before submitting if you generate it programmatically

Example fix

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

using var doc = System.Text.Json.JsonDocument.Parse(jsonRaw);
if (doc.RootElement.ValueKind != JsonValueKind.Array)
    throw new InvalidOperationException("calculatedFields must be a JSON array");

static bool IsJsonArray(string json)
{
    try { return JsonDocument.Parse(json).RootElement.ValueKind == JsonValueKind.Array; }
    catch { return false; }
}

try { AddCalculatedFields(props); }
catch (ArgumentException ex) when (ex.Message.Contains("must be a JSON array"))
{ /* rewrite the value as an array or switch to the singular prop */ }

When it happens

Trigger: Passing a single JSON object instead of an array: calculatedFields={"name":"X","formula":"=A1"}; passing a JSON string or number like calculatedFields="\"X\"" or calculatedFields=42; passing a nested array shape the parser does not expect.

Common situations: User copy-pastes one object from documentation that shows the array element rather than the whole array; tooling that emits a single object when there is only one field; misunderstanding that 'calculatedFields' (plural, JSON) differs from 'calculatedField' (singular, colon form).

Related errors

• each calculatedFields entry must be a JSON object
• invalid JSON for calculatedFields: {ex.Message}
• calculatedField requires a non-empty name
• calculatedField '{name}' requires a non-empty formula
• calculatedField '{name}' collides with an existing field nam

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