{"record":{"id":"e16f34924e433ff4","repo":"iOfficeAI/OfficeCLI","slug":"arrayformula-true-requires-a-formula-pass-the-tex","errorCode":null,"errorMessage":"arrayformula=true requires a formula: pass the text directly (arrayformula=\"B1:B3*C1:C3\") or combine with formula=.","messagePattern":"arrayformula=true requires a formula: pass the text directly \\(arrayformula=\"B1:B3\\*C1:C3\"\\) or combine with formula=\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Add.Cells.cs","lineNumber":803,"sourceCode":"        {\n            ApplyPhoneticToCell(cell, cellWorksheet, phoneticText, properties);\n        }\n\n        // Array formula support during Add\n        if (properties.TryGetValue(\"arrayformula\", out var arrFormula))\n        {\n            // arrayformula=true|1|yes is flag intent (\"make my formula= an\n            // array formula\"), not formula text. Writing it verbatim replaced\n            // the real formula with the literal string \"true\" — silent\n            // corruption. Substitute the companion formula= text; without one\n            // there is nothing to convert, so reject clearly.\n            if (arrFormula.Equals(\"true\", StringComparison.OrdinalIgnoreCase)\n                || arrFormula == \"1\"\n                || arrFormula.Equals(\"yes\", StringComparison.OrdinalIgnoreCase))\n            {\n                arrFormula = properties.GetValueOrDefault(\"formula\")\n                    ?? cell.CellFormula?.Text\n                    ?? throw new ArgumentException(\n                        \"arrayformula=true requires a formula: pass the text directly (arrayformula=\\\"B1:B3*C1:C3\\\") or combine with formula=.\");\n            }\n            RejectCrossWorkbookFormula(arrFormula);\n            ValidateFormulaCellRefs(arrFormula);\n            // BUG-R36-B1: if ref was a range (A1:C3), use the full range as\n            // arrRef so the array formula spills correctly; otherwise default\n            // to the single cellRef.\n            var arrRef = arrayFormulaRefRange ?? properties.GetValueOrDefault(\"ref\", cellRef);\n            // CONSISTENCY(value-child-uniqueness): drop any stale <is> placeholder\n            // so the cell holds a single value child (invalid otherwise).\n            cell.RemoveAllChildren<InlineString>();\n            cell.CellFormula = new CellFormula(Core.PivotTableHelper.SanitizeXmlText(Core.ModernFunctionQualifier.Qualify(Core.ModernFunctionQualifier.AutoQuoteSheetRefs(arrFormula.TrimStart('=')))))\n            {\n                FormulaType = CellFormulaValues.Array,\n                Reference = arrRef\n            };\n            EnsureFullCalcOnLoad(); // CONSISTENCY(cell-formula-calc)\n            cell.CellValue = null;","sourceCodeStart":785,"sourceCodeEnd":821,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Add.Cells.cs#L785-L821","documentation":"The arrayformula=true/1/yes tokens are flag intent ('make my formula= an array formula'), not formula text. Writing them verbatim replaced the real formula with the literal string 'true' — silent corruption. AddCell substitutes the companion formula= text (or the cell's existing CellFormula text); if neither exists there is nothing to convert, so it rejects clearly.","triggerScenarios":"Add(\"/Sheet1/A1\",\"cell\",pos,{[\"arrayformula\"]=\"true\"}) with no formula= and no existing formula on the cell; arrayformula=1 or yes under the same conditions.","commonSituations":"Using arrayformula=true as if it were the formula text; forgetting to also pass formula=; expecting the flag to convert a formula that does not yet exist on the cell.","solutions":["Pass the actual formula text in arrayformula= directly: arrayformula=\"B1:B3*C1:C3\".","Or combine the flag with formula=: { arrayformula=\"true\", formula=\"B1:B3*C1:C3\" }.","Ensure the target cell already holds a formula if you intend the flag to convert an existing one."],"exampleFix":"// before\nhandler.Add(\"/Sheet1/A1\", \"cell\", null, new() { [\"arrayformula\"] = \"true\" });\n// after\nhandler.Add(\"/Sheet1/A1\", \"cell\", null, new() { [\"arrayformula\"] = \"B1:B3*C1:C3\" });","handlingStrategy":"validation","validationCode":"if (props.GetValueOrDefault(\"arrayformula\")?.ToLowerInvariant() is \"true\" or \"1\" or \"yes\")\n{\n    if (!props.ContainsKey(\"formula\"))\n        throw new ArgumentException(\"arrayformula=true requires a companion formula=\");\n}","typeGuard":null,"tryCatchPattern":"try { h.Add(parentPath, \"cell\", pos, props); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"arrayformula=true requires a formula\"))\n{ /* supply formula text in arrayformula= directly and retry */ }","preventionTips":["Put the formula text directly in arrayformula= instead of using the flag form.","If using arrayformula=true, always also pass formula=.","Remember the flag is intent only, not formula text."],"tags":["excel","xlsx","cell","arrayformula","validation"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}