iOfficeAI/OfficeCLI · error · ArgumentException

Property 'sqref' (or 'range'/'ref') is required for validati

Error message

Property 'sqref' (or 'range'/'ref') is required for validation

What it means

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.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs:514

            vmlDoc.Save(wstream);
        }
        catch { }
    }

    private string AddValidation(string parentPath, string type, InsertPosition? position, Dictionary<string, string> properties)
    {
        var index = position?.Index;
        var dvSegments = parentPath.TrimStart('/').Split('/', 2);
        var dvSheetName = dvSegments[0];
        var dvWorksheet = FindWorksheet(dvSheetName)
            ?? throw new ArgumentException($"Sheet not found: {dvSheetName}");

        // CONSISTENCY(range-alias): cf/colorscale/iconset/pivottable all take
        // sqref/range/ref interchangeably; validation lacked `range` only.
        var dvSqref = properties.GetValueOrDefault("sqref")
            ?? properties.GetValueOrDefault("range")
            ?? properties.GetValueOrDefault("ref")
            ?? throw new ArgumentException("Property 'sqref' (or 'range'/'ref') is required for validation");

        // NOTE: multi-region sqref ("A1:A5 C1:C5") is legal and opens fine in
        // real Excel — a fuzz report claiming otherwise was a render-service
        // cache false positive (fresh-content retest and an openpyxl gold
        // sample both open cleanly). Do not add a guard for multi-region —
        // but DO validate each token's A1 shape: an arbitrary string landed
        // verbatim in sqref= and real Excel refused the file (0x800A03EC)
        // while schema validation stayed green.
        dvSqref = ValidateSqref(dvSqref, "validation ref");

        var dv = new DataValidation
        {
            SequenceOfReferences = new ListValue<StringValue>(
                dvSqref.Split(' ').Select(s => new StringValue(s)))
        };

        if (properties.TryGetValue("type", out var dvType))
        {

View on GitHub (pinned to 1ced45e900)

Solutions

1. Add properties["sqref"] = "A1:A5".
2. Alternatively use the aliases properties["range"] or properties["ref"].
3. Pre-validate the property set before calling so the missing key is surfaced with your own message.

Example fix

// before
handler.Add("/Sheet1", "validation", null, new() { ["type"] = "list", ["formula1"] = "1,2,3" });
// after
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "B1:B10", ["type"] = "list", ["formula1"] = "1,2,3" });

if (!properties.ContainsKey("sqref") && !properties.ContainsKey("range") && !properties.ContainsKey("ref"))
    throw new InvalidOperationException("validation requires sqref, range, or ref");

static bool HasValidationRange(Dictionary<string,string> p) =>
    p.ContainsKey("sqref") || p.ContainsKey("range") || p.ContainsKey("ref");

When it happens

Trigger: Call Add type "validation" with a properties dictionary that contains none of sqref, range, or ref.

Common situations: Forgetting the target range; using a different key name the API does not accept; constructing properties dynamically and dropping the range entry.

Related errors

• Chart requires a 'data' property. Use: data="Series1:1,2,3;S
• 'src' property is required for picture type
• Property 'ref' is required for comment
• Property 'formula1' is empty for validation type=list; suppl
• Property 'formula1' is required for validation type=list; su

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