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<void>;

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

clear(): Promise<void>;

Remove all pivot tables from this worksheet.

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

Rename a pivot table by name. @param name - Pivot table name @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

getInfo(name: string): Promise<PivotTableInfo | null>;

Get plain data information about a pivot table by name. Unlike `get()` which returns a handle with bound methods, this returns a plain data object suitable for serialization and inspection. @param name - Pivot table name @returns Pivot table info, or null if not found

has(name: string): Promise<boolean>;

Check if a pivot table exists by name. @param name - Pivot table name @returns True if the pivot table exists

getCount(): Promise<number>;

Get the total number of pivot tables on this worksheet. @returns The count of pivot tables

addPlacement(pivotId: string, spec: PivotPlacementSpec): Promise<PivotPlacementMutationReceipt>;

updatePlacement(
    pivotId: string,
    placementId: PlacementId,
    patch: PivotPlacementPatch,
  ): Promise<PivotKernelMutationReceipt>;

removePlacement(pivotId: string, placementId: PlacementId): Promise<PivotKernelMutationReceipt>;

movePlacement(
    pivotId: string,
    placementId: PlacementId,
    toArea: PivotFieldArea,
    toPosition: number,
  ): Promise<PivotKernelMutationReceipt>;

renameValuePlacement(
    pivotId: string,
    placementId: PlacementId,
    displayName: string | null,
  ): Promise<PivotKernelMutationReceipt>;

setSortByValue(
    pivotId: string,
    axisPlacementId: PlacementId,
    valuePlacementId: PlacementId,
    config: { order: SortOrder; columnKey?: string } | null,
  ): Promise<PivotKernelMutationReceipt>;

addField(
    name: 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, resolved by pivot name. @deprecated Legacy facade. Prefer `addPlacement(pivotId, spec)`. @param name - Pivot table name @param fieldId - Field ID to add @param area - Target area (row, column, value, or filter) @param options - Optional configuration (position, aggregateFunction, sortOrder, displayName)

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

Remove a field from a pivot table area, resolved by pivot name. @deprecated Legacy facade. Prefer `removePlacement(pivotId, placementId)`. @param name - Pivot table name @param fieldId - Field ID to remove @param area - Area to remove the field from

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

Move a field to a different area or position, resolved by pivot name. @deprecated Legacy facade. Prefer `movePlacement(pivotId, placementId, toArea, toPosition)`. @param name - Pivot table name @param fieldId - Field ID to move @param fromArea - Source area @param toArea - Target area @param toPosition - Target position within the area

setAggregateFunction(
    pivotId: string,
    placementId: PlacementId,
    aggregateFunction: AggregateFunction,
  ): Promise<PivotKernelMutationReceipt>;

setShowValuesAs(
    pivotId: string,
    placementId: PlacementId,
    showValuesAs: ShowValuesAsConfig | null,
  ): Promise<PivotKernelMutationReceipt>;

setSortOrder(
    pivotId: string,
    placementId: PlacementId,
    sortOrder: SortOrder | null,
  ): Promise<PivotKernelMutationReceipt>;

resetPlacement(pivotId: string, placementId: PlacementId): Promise<PivotKernelMutationReceipt>;

setAggregateFunctionLegacy(
    name: string,
    fieldId: string,
    aggregateFunction: AggregateFunction,
  ): Promise<void>;

Set the aggregate function for a value field, resolved by pivot name. @deprecated Legacy facade. Prefer `setAggregateFunction(pivotId, placementId, aggregateFunction)`. @param name - Pivot table name @param fieldId - Field ID (must be in the 'value' area) @param aggregateFunction - New aggregation function

setShowValuesAsLegacy(
    name: 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. @deprecated Legacy facade. Prefer `setShowValuesAs(pivotId, placementId, showValuesAs)`. @param name - Pivot table name @param fieldId - Field ID (must be in the 'value' area) @param showValuesAs - ShowValuesAs configuration, or null to clear

setSortOrderLegacy(name: string, fieldId: string, sortOrder: SortOrder): Promise<void>;

Set the sort order for a row or column field, resolved by pivot name. @deprecated Legacy facade. Prefer `setSortOrder(pivotId, placementId, sortOrder)`. @param name - Pivot table name @param fieldId - Field ID (must be in 'row' or 'column' area) @param sortOrder - Sort order ('asc', 'desc', or 'none')

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

Set (add or update) a filter on a field, resolved by pivot name. @param name - Pivot table name @param fieldId - Field ID to filter @param filter - Filter configuration (without fieldId)

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

Remove a filter from a field, resolved by pivot name. @param name - Pivot table name @param fieldId - Field ID whose filter should be removed

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

Reset a field placement to defaults, resolved by pivot name. @deprecated Legacy facade. Prefer `resetPlacement(pivotId, placementId)`. @param name - Pivot table name @param fieldId - Field ID to reset

setLayout(name: string, layout: Partial<PivotTableLayout>): Promise<PivotKernelMutationReceipt>;

Set layout options for a pivot table, resolved by pivot name. @param name - Pivot table name @param layout - Partial layout configuration to merge with existing

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

Set style options for a pivot table, resolved by pivot name. @param name - Pivot table name @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(name: string, forceRefresh?: boolean): Promise<PivotTableResult | null>;

Compute a pivot table result by name (uses cache if available). @param name - Pivot table name @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(name: string): Promise<PivotRefreshReceipt | PivotCommandReceipt>;

Refresh a pivot table by name (recompute without cache). @param name - Pivot table name

refreshAll(): Promise<void>;

Refresh all pivot tables on this worksheet.

getDrillDownData(name: string, rowKey: string, columnKey: string): Promise<CellValue[][]>;

Get drill-down data for a pivot table cell, resolved by pivot name. @param name - Pivot table name @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: PivotCalculatedFieldSpec): Promise<PivotKernelMutationReceipt & { calculatedFieldId: CalculatedFieldId }>;

addCalculatedFieldAndPlace(
    pivotId: string,
    field: PivotCalculatedFieldSpec,
    placement: Omit<PivotPlacementSpec, 'source' | 'fieldId'>,
  ): Promise<PivotKernelMutationReceipt & { calculatedFieldId: CalculatedFieldId; placementId: PlacementId }>;

updateCalculatedField(
    pivotId: string,
    calculatedFieldId: CalculatedFieldId,
    updates: Partial<Pick<PivotCalculatedFieldSpec, 'name' | 'formula'>>,
  ): Promise<PivotKernelMutationReceipt>;

removeCalculatedField(pivotId: string, calculatedFieldId: CalculatedFieldId): Promise<PivotKernelMutationReceipt>;

addCalculatedFieldLegacy(name: string, field: CalculatedField): Promise<void>;

Add a calculated field to a pivot table, resolved by pivot name. @deprecated Legacy facade. Prefer define-only `addCalculatedField(pivotId, spec)`. @param name - Pivot table name @param field - Calculated field definition (fieldId, name, formula)

removeCalculatedFieldLegacy(name: string, fieldId: string): Promise<void>;

Remove a calculated field from a pivot table, resolved by pivot name. @deprecated Legacy facade. Prefer `removeCalculatedField(pivotId, calculatedFieldId)`. @param name - Pivot table name @param fieldId - Calculated field ID to remove

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

Update a calculated field on a pivot table, resolved by pivot name. @deprecated Legacy facade. Prefer `updateCalculatedField(pivotId, calculatedFieldId, patch)`. @param name - Pivot table name @param fieldId - Calculated field ID to update @param updates - Partial updates (name and/or formula)

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

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

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

Get the range of the data body, resolved by pivot name. @param name - Pivot table name @returns CellRange covering the data body, or null if not computed

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

Get the range of column label headers, resolved by pivot name. @param name - Pivot table name @returns CellRange covering the column headers, or null if not computed

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

Get the range of row label headers, resolved by pivot name. @param name - Pivot table name @returns CellRange covering the row headers, or null if not computed

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

Get the range of the filter area, resolved by pivot name. @param name - Pivot table name @returns CellRange covering filter dropdowns, or null if no filter fields

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

Get pivot items for all placed fields, resolved by pivot name. @param name - Pivot table name @returns Items grouped by field

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

Set visibility of specific items in a field, resolved by pivot name. @param name - Pivot table name @param fieldId - Field ID @param visibleItems - Map of item value (as string) to visibility boolean

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

Toggle expansion state for a header, resolved by pivot name. @param name - Pivot table name @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(name: string, expanded: boolean): Promise<void>;

Set expansion state for all headers, resolved by pivot name. @param name - Pivot table name @param expanded - Whether all headers should be expanded (true) or collapsed (false)

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

Get the current expansion state, resolved by pivot name. @param name - Pivot table name @returns Expansion state with expandedRows and expandedColumns maps

getDataSourceType(name: string): Promise<DataSourceType>;

Get the data source type of a pivot table (range, table, or external). @param name - Pivot table name @returns The data source type

setDataSource(name: string, dataSource: string): Promise<void>;

Change the source data range for a pivot table without refreshing it. `dataSource` must be a qualified A1 range such as `Sheet1!A1:D100` or `'Bob''s Data'!A1:D100`.

getAllowMultipleFiltersPerField(name: string): Promise<boolean>;

Get whether multiple filters per field are allowed on a pivot table. @param name - Pivot table name @returns True if multiple filters per field are allowed

setAllowMultipleFiltersPerField(name: string, allow: boolean): Promise<void>;

Set whether multiple filters per field are allowed on a pivot table. @param name - Pivot table name @param allow - True to allow multiple filters per field

getAutoFormat(name: string): Promise<boolean>;

Get whether the pivot table auto-formats when refreshed. @param name - Pivot table name @returns True if auto-formatting is enabled

setAutoFormat(name: string, autoFormat: boolean): Promise<void>;

Set whether the pivot table auto-formats when refreshed. @param name - Pivot table name @param autoFormat - True to enable auto-formatting

getPreserveFormatting(name: string): Promise<boolean>;

Get whether custom formatting is preserved on refresh. @param name - Pivot table name @returns True if custom formatting is preserved

setPreserveFormatting(name: string, preserve: boolean): Promise<void>;

Set whether custom formatting is preserved on refresh. @param name - Pivot table name @param preserve - True to preserve custom formatting

getDataHierarchy(name: string, row: number, col: number): Promise<PivotDataHierarchyInfo | null>;

Identify which data hierarchy (value field) a pivot cell belongs to. Given an output cell position (row, col) in the rendered pivot table, returns information about the value field (aggregate) that produced the cell. @param name - Pivot table name @param row - 0-based row index in the rendered pivot table @param col - 0-based column index in the rendered pivot table @returns Data hierarchy info, or null if the cell is not a data cell

getPivotItems(name: string, axis: 'row' | 'column', row: number, col: number): Promise<PivotItemLocation[] | null>;

Identify which row/column items intersect at a given pivot output cell. Returns the pivot items (group values) from the specified axis that define the cell's position in the pivot table. @param name - Pivot table name @param axis - 'row' or 'column' @param row - 0-based row index in the rendered pivot table @param col - 0-based column index in the rendered pivot table @returns Array of pivot item locations, or null if the cell is outside the data area

getEnableMultipleFilterItems(name: string, fieldId: string): Promise<boolean>;

Get whether multiple filter items per field are enabled for a specific field. When enabled, multiple `PivotFilter` entries can exist for the same field, combined with AND logic during evaluation. @param name - Pivot table name @param fieldId - Field ID to check @returns True if multiple filter items are enabled for this field

setEnableMultipleFilterItems(name: string, fieldId: string, enabled: boolean): Promise<void>;

Set whether multiple filter items per field are enabled for a specific field. When enabled, multiple `PivotFilter` entries can exist for the same field, combined with AND logic during evaluation. @param name - Pivot table name @param fieldId - Field ID to configure @param enabled - True to enable multiple filter items