iOfficeAI/OfficeCLI · error · ArgumentException

pivottable requires 'source' property (e.g. source=Sheet1!A1

Error message

pivottable requires 'source' property (e.g. source=Sheet1!A1:D100)

What it means

Thrown by AddPivotTable when neither a 'source' nor a 'src' property is present in the properties dictionary. The source range (e.g. Sheet1!A1:D100) is mandatory because it defines the pivot cache's source data; without it the pivot cannot be built. The null-coalescing expression throws when both GetValueOrDefault calls return null.

Source

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

        tableParts.Count = (uint)tableParts.Elements<TablePart>().Count();
        SaveWorksheet(tblWorksheet);

        var tblIdx = PathIndex.FromArrayIndex(tblWorksheet.TableDefinitionParts.ToList().IndexOf(tableDefPart));
        return $"/{tblSheetName}/table[{tblIdx}]";
    }

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

        // Source: "Sheet1!A1:D100" or "A1:D100" (same sheet)
        var sourceSpec = properties.GetValueOrDefault("source", "")
            ?? properties.GetValueOrDefault("src", "")
            ?? throw new ArgumentException("pivottable requires 'source' property (e.g. source=Sheet1!A1:D100)");
        if (string.IsNullOrEmpty(sourceSpec))
            throw new ArgumentException("pivottable requires 'source' property (e.g. source=Sheet1!A1:D100)");

        // R8-7: incidental whitespace around the source spec or its
        // components (" Sheet1 ! A1:D10 ") is a common paste-from-docs
        // artefact. Trim the whole string and both sides of the '!'
        // split so the downstream sheet/range lookup sees clean values.
        sourceSpec = sourceSpec.Trim();

        // R8-3: external workbook refs such as [other.xlsx]Sheet1!A1:D10
        // used to fall through to FindWorksheet and surface as the
        // misleading "Source sheet not found: [other.xlsx]Sheet1".
        // Detect the '[' prefix up front and throw a clear error so
        // users know the feature is not supported rather than blaming
        // a missing sheet.
        if (sourceSpec.StartsWith("["))
            throw new ArgumentException(
                "External workbook references are not supported in pivot source. "

View on GitHub (pinned to 1ced45e900)

Solutions

1. Add a 'source' property with an explicit range, e.g. source=Data!A1:D100.
2. For same-sheet sources, a bare range like source=A1:D100 is accepted.
3. Use the 'src' alias if preferred; both keys are checked.

Example fix

// before
add /Report/pivottable
// after
add /Report/pivottable --prop source=Data!A1:D100

// Ensure a pivot source is present before Add.
if (!props.ContainsKey("source") && !props.ContainsKey("src"))
    throw new InvalidOperationException("pivottable requires a 'source' property.");

try { handler.Add(parentPath, "pivottable", null, props); }
catch (ArgumentException ex) when (ex.Message.Contains("requires 'source' property"))
{ /* prompt for the source range and retry */ }

When it happens

Trigger: Calling Add('/Report/pivottable', ...) with no source/src key at all, or with the keys misspelled (e.g. 'data', 'from').

Common situations: Forgetting the source argument, using an unsupported alias, or assuming the handler infers the source from the host sheet's used range.

Related errors

• Pivot name '{explicitName}' already exists in workbook
• Property 'sqref' (or 'range'/'ref') is required for validati
• Property 'ref' or 'range' is required for table
• Sheet not found: {ptSheetName}
• External workbook references are not supported in pivot sour

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