{"record":{"id":"f58f43b475856167","repo":"iOfficeAI/OfficeCLI","slug":"property-sqref-or-range-ref-is-required-fo","errorCode":null,"errorMessage":"Property 'sqref' (or 'range'/'ref') is required for validation","messagePattern":"Property 'sqref' \\(or 'range'/'ref'\\) is required for validation","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs","lineNumber":514,"sourceCode":"            vmlDoc.Save(wstream);\n        }\n        catch { }\n    }\n\n    private string AddValidation(string parentPath, string type, InsertPosition? position, Dictionary<string, string> properties)\n    {\n        var index = position?.Index;\n        var dvSegments = parentPath.TrimStart('/').Split('/', 2);\n        var dvSheetName = dvSegments[0];\n        var dvWorksheet = FindWorksheet(dvSheetName)\n            ?? throw new ArgumentException($\"Sheet not found: {dvSheetName}\");\n\n        // CONSISTENCY(range-alias): cf/colorscale/iconset/pivottable all take\n        // sqref/range/ref interchangeably; validation lacked `range` only.\n        var dvSqref = properties.GetValueOrDefault(\"sqref\")\n            ?? properties.GetValueOrDefault(\"range\")\n            ?? properties.GetValueOrDefault(\"ref\")\n            ?? throw new ArgumentException(\"Property 'sqref' (or 'range'/'ref') is required for validation\");\n\n        // NOTE: multi-region sqref (\"A1:A5 C1:C5\") is legal and opens fine in\n        // real Excel — a fuzz report claiming otherwise was a render-service\n        // cache false positive (fresh-content retest and an openpyxl gold\n        // sample both open cleanly). Do not add a guard for multi-region —\n        // but DO validate each token's A1 shape: an arbitrary string landed\n        // verbatim in sqref= and real Excel refused the file (0x800A03EC)\n        // while schema validation stayed green.\n        dvSqref = ValidateSqref(dvSqref, \"validation ref\");\n\n        var dv = new DataValidation\n        {\n            SequenceOfReferences = new ListValue<StringValue>(\n                dvSqref.Split(' ').Select(s => new StringValue(s)))\n        };\n\n        if (properties.TryGetValue(\"type\", out var dvType))\n        {","sourceCodeStart":496,"sourceCodeEnd":532,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs#L496-L532","documentation":"Thrown by AddValidation when none of the range keys is present. dvSqref is resolved from properties \"sqref\", then \"range\", then \"ref\" (a CONSISTENCY alias so validation matches cf/colorscale/iconset/pivottable). If all three are absent the coalesce falls through to the throw.","triggerScenarios":"Call Add type \"validation\" with a properties dictionary that contains none of sqref, range, or ref.","commonSituations":"Forgetting the target range; using a different key name the API does not accept; constructing properties dynamically and dropping the range entry.","solutions":["Add properties[\"sqref\"] = \"A1:A5\".","Alternatively use the aliases properties[\"range\"] or properties[\"ref\"].","Pre-validate the property set before calling so the missing key is surfaced with your own message."],"exampleFix":"// before\nhandler.Add(\"/Sheet1\", \"validation\", null, new() { [\"type\"] = \"list\", [\"formula1\"] = \"1,2,3\" });\n// after\nhandler.Add(\"/Sheet1\", \"validation\", null,\n    new() { [\"sqref\"] = \"B1:B10\", [\"type\"] = \"list\", [\"formula1\"] = \"1,2,3\" });","handlingStrategy":"validation","validationCode":"if (!properties.ContainsKey(\"sqref\") && !properties.ContainsKey(\"range\") && !properties.ContainsKey(\"ref\"))\n    throw new InvalidOperationException(\"validation requires sqref, range, or ref\");","typeGuard":"static bool HasValidationRange(Dictionary<string,string> p) =>\n    p.ContainsKey(\"sqref\") || p.ContainsKey(\"range\") || p.ContainsKey(\"ref\");","tryCatchPattern":null,"preventionTips":["Standardize on one key (sqref) across your codebase for validation ranges.","Assert the range key is present in your builder before calling Add.","Remember range/ref are accepted aliases, but at least one must be set."],"tags":["excel","validation","required-property","validation"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}