1. Spreadsheet docs
  2. Developers

PivotTables and slicers from code

Build PivotTables from a range or a table, change their fields and layout, filter them, add slicers and refresh them from code.

A PivotTable summarises a list of records without formulas: sales by region and year, counts by status, averages by team. From code you create one with sheet.addPivotTable, then change its fields, layout and filters through the SheetPivot it returns, and add slicers that filter it with buttons. Each change rewrites the table's cells and is one undo step.

Build a report

JavaScript

import { mountWorkbook } from '@8sheets/core';

const wb = await mountWorkbook(document.querySelector('#sheet'));
const data = wb.activeSheet;
data.name = 'Data';
data.range('A1').values = [
  ['Region', 'Product', 'Year', 'Sales'],
  ['East', 'Pen', 2026, 120], ['West', 'Pen', 2026, 80], ['East', 'Ink', 2027, 150], ['West', 'Ink', 2027, 95],
];

const report = wb.addSheet('Report');
const pivot = report.addPivotTable('Data!A1:D5', 'A3', {
  rows: ['Region'],
  columns: ['Year'],
  values: [{ field: 'Sales', summarize: 'sum', name: 'Total sales' }],
  layout: 'tabular',
  style: 'PivotStyleMedium9',
});

const slicer = pivot.addSlicer('Product', { x: 360, y: 20, width: 160, height: 180 });
slicer.selected = ['Pen'];                 // filters the PivotTable, as clicking the buttons does
console.log(pivot.range.values);

React

import { Sheet } from '@8sheets/react';

function buildReport(wb) {
  const report = wb.addSheet('Report');
  const pivot = report.addPivotTable('Sales', 'A3', {   // a table's name works as the source
    rows: ['Region'], columns: ['Year'], values: [{ field: 'Sales', summarize: 'sum' }],
  });
  pivot.addSlicer('Product');
}

export function SalesReport({ onOpen }) {
  return (
    <div style={{ height: 600 }}>
      <Sheet onReady={async (wb) => { await onOpen(wb); buildReport(wb); }} />
    </div>
  );
}

Vue

<script setup>
import { Sheet } from '@8sheets/vue';

function buildReport(wb) {
  const report = wb.addSheet('Report');
  const pivot = report.addPivotTable('Sales', 'A3', {
    rows: ['Region'], columns: ['Year'], values: [{ field: 'Sales', summarize: 'sum' }],
  });
  pivot.addSlicer('Product');
}
</script>

<template>
  <Sheet style="height: 600px" @ready="buildReport" />
</template>

Angular

import { Component } from '@angular/core';
import { SheetComponent, SheetsWorkbook } from '@8sheets/angular';

@Component({
  selector: 'app-sales-report',
  imports: [SheetComponent],
  template: `<eight-sheet class="grid" (ready)="buildReport($event)" />`,
  styles: `.grid { height: 600px; }`,
})
export class SalesReport {
  buildReport(wb: SheetsWorkbook) {
    const report = wb.addSheet('Report');
    const pivot = report.addPivotTable('Sales', 'A3', {
      rows: ['Region'], columns: ['Year'], values: [{ field: 'Sales', summarize: 'sum' }],
    });
    pivot.addSlicer('Product');
  }
}

The React, Vue and Angular samples assume a table named Sales with Region, Product, Year and Sales columns, such as one opened from a file.

Create

sheet.addPivotTable(source, at, options) adds a PivotTable on sheet, with its top-left cell at at.

  • source: a range with a header row ('Data!A1:D99' for another sheet), a table, or a table's name.
  • options: the layout below, plus name and force. Adding throws when the table would cover cells that hold data; force: true writes over them.
Layout fieldWhat it does
rows, columnsSource headers, outermost first
filtersReport filters, shown above the table
valuesHeaders, or { field, summarize, name }. summarize is sum, count, average, max, min, product, countNums, stdDev, stdDevp, var or varp; left out, sum for numbers and count otherwise. name is the caption, Sum of Sales by default
layoutcompact, outline or tabular, as Report Layout offers
grandTotalRows, grandTotalColumnsThe totals row at the bottom and the totals column at the right
styleA PivotTable style such as PivotStyleMedium9

Change

pivot.setLayout({ rows: ['Region', 'Product'], filters: ['Year'] });   // areas left out keep their fields
pivot.filter('Region', ['East', 'West']);    // show only these items; null shows every item again
pivot.items('Region');                       // [{ value, label, visible }]
pivot.layout;                                // the layout now, in the form setLayout takes
pivot.fields;                                // the source's headers
pivot.range.address;                         // the cells it fills now, report filters included
await pivot.refresh();                       // read the source again, after its data changed
pivot.remove();                              // delete it and clear its cells

report.pivotTables lists the PivotTables on a sheet, and report.pivotTable(name) finds one by name. refresh() also rewrites every PivotTable that shares the source, and calculates the source's formulas first.

Slicers

pivot.addSlicer(field, options) adds a slicer for a source field; options takes caption, columns (of buttons) and a position and size in points: x, y, width, height. It filters this PivotTable and every one that shares its source; a mounted workbook shows it over the grid.

MemberWhat it does
selectedThe values of the selected items. Set an array to select exactly those, null or [] to clear
itemsEvery item with whether it is selected and has data
caption, fieldThe caption over the buttons, and the field it filters
clear()Shows every item again
remove()Takes the slicer away; the filter it set stays, as in Excel

pivot.slicers finds the slicers of a PivotTable again.

In the app

Users build PivotTables with Insert > PivotTable and change them in the field list; see Data tools. From your own button, wb.commands.execute('ui.insertPivotTable') opens the same dialog for the selection, and ui.insertSlicer and ui.insertTimeline add slicers and timelines.

Limits

  • Headless workbooks create, change and refresh PivotTables, and their cells are saved. The definition itself lives only while the workbook does: a headless workbook doesn't write PivotTable definitions to .xlsx or JSON. A mounted workbook saves them in .xlsx and .8sh, as the app does.
  • Slicers aren't written to .xlsx yet; the filter they set is. Slicers of tables and timelines aren't in the API yet.
  • PivotCharts, calculated fields, grouping of dates and numbers, and "show values as" are available in the app, not yet in the API.