iOfficeAI/OfficeCLI · error · ArgumentException

each calculatedFields entry must be a JSON object

Error message

each calculatedFields entry must be a JSON object

What it means

After confirming the JSON root is an array, the parser iterates each element and requires it to be a JSON object (so it can extract 'name' and 'formula' properties). Any non-object element — string, number, array, null, boolean — is rejected immediately. This mirrors the strict-enum policy used elsewhere: surface malformed input at Add/Set time instead of producing a malformed pivot.

Source

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

    /// </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}");
            }
        }

        // Numbered + bare calculatedField props (ordinal sort so calculatedField1
        // appears before calculatedField2 regardless of insertion order).

View on GitHub (pinned to 1ced45e900)

Solutions

1. Make every array element an object: calculatedFields=[{"name":"X","formula":"=A1"}]
2. If you prefer the colon-string form, use the singular prop: calculatedField=X:=A1 (or calculatedField1=..., calculatedField2=...)
3. Inspect each element of your generated array before submission

Example fix

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

foreach (var el in doc.RootElement.EnumerateArray())
    if (el.ValueKind != JsonValueKind.Object)
        throw new InvalidOperationException("Each calculatedFields entry must be a JSON object");

static bool AllEntriesAreObjects(string json)
{
    try
    {
        var root = JsonDocument.Parse(json).RootElement;
        return root.ValueKind == JsonValueKind.Array &&
               root.EnumerateArray().All(e => e.ValueKind == JsonValueKind.Object);
    }
    catch { return false; }
}

try { AddCalculatedFields(props); }
catch (ArgumentException ex) when (ex.Message.Contains("must be a JSON object"))
{ /* convert string entries to {name,formula} objects */ }

When it happens

Trigger: calculatedFields=["X:=A1"] (array of strings rather than objects); calculatedFields=[42]; calculatedFields=[null]; mixed arrays like [{...},"Y:=B1"].

Common situations: User assumes the array accepts the same colon-string syntax as the singular prop; migration from a different tool that used string specs; auto-generated JSON that mixed element shapes.

Related errors

• 'calculatedFields' must be a JSON array
• 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/5f05a49a818fd2f5. Report an issue: GitHub.