ws.pivots

add(config: PivotCreateConfig): Promise<PivotTableConfig>;

Create a new pivot table on this worksheet. Accepts either: - **Simple config**: `{ name, dataSource: "Sheet1!A1:D100", rowFields: ["Region"], ... }` Fields are auto-detected from source headers, placements are generated from field arrays. - **Full config**: `{ name, sourceSheetName, sourceRange, fields, placements, filters, ... }` Direct wire format — no conversion needed. @param config - Pivot table configuration @returns The created pivot table configuration (with generated id, timestamps)

addWithSheet(
    sheetName: string,
    config: PivotCreateConfig,
  ): Promise<{ sheetId: string; config: PivotTableConfig }>;

Atomically create a new sheet AND a pivot table on it. Both operations happen in a single transaction for undo atomicity. Accepts the same simple or full config formats as `add()`. @param sheetName - Name for the new sheet @param config - Pivot table configuration @returns The new sheet ID and the created pivot config

remove(name: string): Promise<PivotRemoveReceipt>;

Remove a pivot table by name. @param name - Pivot table name

rename(pivotId: string, newName: string): Promise<void>;

Rename a pivot table. @param pivotId - Pivot table ID @param newName - New name for the pivot table

list(): Promise<PivotTableInfo[]>;

List all pivot tables on this worksheet. @returns Array of pivot table summary information

get(name: string): Promise<PivotTableHandle | null>;

Get a pivot table handle by name. @param name - Pivot table name @returns A handle for the pivot table, or null if not found

addField(
    pivotId: string,
    fieldId: string,
    area: PivotFieldArea,
    options?: {
      position?: number;
      aggregateFunction?: AggregateFunction;
      sortOrder?: SortOrder;
      displayName?: string;
      showValuesAs?: ShowValuesAsConfig;
    },
  ): Promise<void>;

Add a field to a pivot table area. @param pivotId - Pivot table ID @param fieldId - Field ID to add @param area - Target area (row, column, value, or filter) @param options - Optional configuration (position, aggregateFunction, sortOrder, displayName)

removeField(pivotId: string, fieldId: string, area: PivotFieldArea): Promise<void>;

Remove a field from a pivot table area. @param pivotId - Pivot table ID @param fieldId - Field ID to remove @param area - Area to remove the field from

moveField(
    pivotId: string,
    fieldId: string,
    fromArea: PivotFieldArea,
    toArea: PivotFieldArea,
    toPosition: number,
  ): Promise<void>;

Move a field to a different area or position. @param pivotId - Pivot table ID @param fieldId - Field ID to move @param fromArea - Source area @param toArea - Target area @param toPosition - Target position within the area

setAggregateFunction(
    pivotId: string,
    fieldId: string,
    aggregateFunction: AggregateFunction,
  ): Promise<void>;

Set the aggregate function for a value field. @param pivotId - Pivot table ID @param fieldId - Field ID (must be in the 'value' area) @param aggregateFunction - New aggregation function

setShowValuesAs(
    pivotId: string,
    fieldId: string,
    showValuesAs: ShowValuesAsConfig | null,
  ): Promise<void>;

Set the "Show Values As" calculation for a value field. Only applies to fields in the 'value' area. Pass null to clear. @param pivotId - Pivot table ID @param fieldId - Field ID (must be in the 'value' area) @param showValuesAs - ShowValuesAs configuration, or null to clear

setSortOrder(pivotId: string, fieldId: string, sortOrder: SortOrder): Promise<void>;

Set the sort order for a row or column field. @param pivotId - Pivot table ID @param fieldId - Field ID (must be in 'row' or 'column' area) @param sortOrder - Sort order ('asc', 'desc', or 'none')

setFilter(pivotId: string, fieldId: string, filter: Omit<PivotFilter, 'fieldId'>): Promise<void>;

Set (add or update) a filter on a field. @param pivotId - Pivot table ID @param fieldId - Field ID to filter @param filter - Filter configuration (without fieldId)

removeFilter(pivotId: string, fieldId: string): Promise<void>;

Remove a filter from a field. @param pivotId - Pivot table ID @param fieldId - Field ID whose filter should be removed

resetField(pivotId: string, fieldId: string): Promise<void>;

Reset a field placement to defaults (clear aggregateFunction, sortOrder, displayName, showValuesAs, etc.) and remove any associated filter. @param pivotId - Pivot table ID @param fieldId - Field ID to reset

setLayout(pivotId: string, layout: Partial<PivotTableLayout>): Promise<void>;

Set layout options for a pivot table. @param pivotId - Pivot table ID @param layout - Partial layout configuration to merge with existing

setStyle(pivotId: string, style: Partial<PivotTableStyle>): Promise<void>;

Set style options for a pivot table. @param pivotId - Pivot table ID @param style - Partial style configuration to merge with existing

detectFields(
    sourceSheetId: string,
    range: { startRow: number; startCol: number; endRow: number; endCol: number },
  ): Promise<any[]>;

Detect fields from source data for pivot table creation. @param sourceSheetId - Sheet ID containing the source data @param range - Source data range @returns Array of detected pivot fields

compute(pivotId: string, forceRefresh?: boolean): Promise<any>;

Compute a pivot table result (uses cache if available). @param pivotId - Pivot table ID @param forceRefresh - Force recomputation ignoring cache @returns Computed pivot table result

queryPivot(
    pivotName: string,
    filters?: Record<string, CellValue | CellValue[]>,
  ): Promise<PivotQueryResult | null>;

Query a pivot table by name, returning flat records optionally filtered by dimension values. Eliminates the need to manually traverse hierarchical PivotTableResult trees. @param pivotName - Pivot table name @param filters - Optional dimension filters: field name → value or array of values to include @returns Flat query result, or null if pivot not found or not computable

refresh(pivotId: string): Promise<PivotRefreshReceipt>;

Refresh a pivot table (recompute without cache). @param pivotId - Pivot table ID

refreshAll(): Promise<void>;

Refresh all pivot tables on this worksheet.

getDrillDownData(pivotId: string, rowKey: string, columnKey: string): Promise<any[][]>;

Get drill-down data for a pivot table cell. @param pivotId - Pivot table ID @param rowKey - Row key from the pivot result @param columnKey - Column key from the pivot result @returns Source data rows that contribute to this cell

addCalculatedField(pivotId: string, field: CalculatedField): Promise<void>;

Add a calculated field to a pivot table. @param pivotId - Pivot table ID @param field - Calculated field definition (fieldId, name, formula)

removeCalculatedField(pivotId: string, fieldId: string): Promise<void>;

Remove a calculated field from a pivot table. @param pivotId - Pivot table ID @param fieldId - Calculated field ID to remove

updateCalculatedField(
    pivotId: string,
    fieldId: string,
    updates: Partial<Pick<CalculatedField, 'name' | 'formula'>>,
  ): Promise<void>;

Update a calculated field on a pivot table. @param pivotId - Pivot table ID @param fieldId - Calculated field ID to update @param updates - Partial updates (name and/or formula)

getRange(pivotId: string): Promise<CellRange | null>;

Get the full range occupied by the rendered pivot table. @param pivotId - Pivot table ID @returns CellRange covering the entire pivot table, or null if not computed

getDataBodyRange(pivotId: string): Promise<CellRange | null>;

Get the range of the data body (values only, excluding headers and totals row labels). @param pivotId - Pivot table ID @returns CellRange covering the data body, or null if not computed

getColumnLabelRange(pivotId: string): Promise<CellRange | null>;

Get the range of column label headers. @param pivotId - Pivot table ID @returns CellRange covering the column headers, or null if not computed

getRowLabelRange(pivotId: string): Promise<CellRange | null>;

Get the range of row label headers. @param pivotId - Pivot table ID @returns CellRange covering the row headers, or null if not computed

getFilterAxisRange(pivotId: string): Promise<CellRange | null>;

Get the range of the filter area (page fields above the pivot table). @param pivotId - Pivot table ID @returns CellRange covering filter dropdowns, or null if no filter fields

getAllPivotItems(pivotId: string): Promise<PivotFieldItems[]>;

Get pivot items for all placed fields (excluding value fields). Each item includes its display value, expansion state, and visibility. @param pivotId - Pivot table ID @returns Items grouped by field

setPivotItemVisibility(
    pivotId: string,
    fieldId: string,
    visibleItems: Record<string, boolean>,
  ): Promise<void>;

Set visibility of specific items in a field. Updates the field's filter include/exclude list. @param pivotId - Pivot table ID @param fieldId - Field ID @param visibleItems - Map of item value (as string) to visibility boolean

toggleExpanded(pivotId: string, headerKey: string, isRow: boolean): Promise<boolean>;

Toggle expansion state for a header. @param pivotId - Pivot table ID @param headerKey - Header key to toggle @param isRow - Whether this is a row header (true) or column header (false) @returns The new expansion state (true = expanded, false = collapsed)

setAllExpanded(pivotId: string, expanded: boolean): Promise<void>;

Set expansion state for all headers. @param pivotId - Pivot table ID @param expanded - Whether all headers should be expanded (true) or collapsed (false)

getExpansionState(pivotId: string): Promise<PivotExpansionState>;

Get the current expansion state for a pivot table. @param pivotId - Pivot table ID @returns Expansion state with expandedRows and expandedColumns maps