Sort & Filter a JavaScript Spreadsheet — Excel-Style AutoFilter, by Click and in Code

Data validation locked down what goes into the grid. Now the data’s in — a few hundred or a few thousand rows — and someone needs to find their way around it. Which orders are still Open? Who’s in the Osaka office? Sort by revenue, descending. This is the everyday spreadsheet move: click the header dropdown, tick the values you want, sort a column. In ReoGrid Web it’s one call to switch on, and a small API to drive from code.

Sort & Filter is a Pro feature (pricing) — createAutoFilter, sortColumn, and the frozen-header helper all live in the Pro edition.

Sorting a column

The simplest interaction is a plain sort. Give sortColumn a column index and a direction:

const ws = grid.worksheet;

ws.sortColumn(4, 'desc'); // sort by column E (Revenue), highest first
ws.sortColumn(1, 'asc');  // then by column B (Name), A→Z

It reorders the data rows in place. If an auto-filter is active, the sort stays inside the filter’s range and updates the dropdown’s sort indicator; if not, the whole sheet sorts. The call returns a SortResult whose originalOrder snapshots where each row sat before the sort — exactly what you need to build your own undo:

const { originalOrder } = ws.sortColumn(4, 'desc');
// originalOrder[i] = the row that used to be at data position i

To read the current state back, ws.getSortState() returns { column, order } — or null when nothing is sorted (technically: when there’s no active auto-filter recording it).

Auto-filter — the header dropdowns

A sort reorders rows; a filter hides the ones you don’t care about. createAutoFilter turns a header row into Excel’s familiar dropdown arrows — click one and you get a checklist of that column’s values plus ascending/descending sort.

const filter = ws.createAutoFilter({
  headerRow: 0,   // the row with column labels
  startColumn: 0, // first column in the filter range
  endColumn: 4,   // last column (inclusive)
});

That’s the whole setup. Data rows are everything below headerRow; the dropdowns appear on the header cells from startColumn to endColumn. From here the user drives it by mouse — but every one of those dropdown actions has a code equivalent, which is what makes the feature scriptable.

Worked example: a filterable data table

Let’s build the thing people actually ship — a table with a styled header, zebra striping, a frozen header row, filter dropdowns on every column, and a live “N of M rows visible” readout. This is the complete build:

import { createReogrid } from '@reogrid/pro';

const { worksheet: ws } = createReogrid('#grid');

ws.suspendRender(); // batch the initial build

// ── Columns ──
const HEADERS = ['ID', 'Name', 'Department', 'Region', 'Revenue'];
[60, 150, 130, 110, 110].forEach((w, c) => { ws.column(c).width = w; });

HEADERS.forEach((h, c) => {
  ws.cell(0, c).setValue(h).setStyle({
    bold: true, backgroundColor: '#1e3a5f', color: '#e2e8f0', textAlign: 'center',
  });
});
ws.row(0).height = 30;

// ── Data (set with setCellInput so the filter can read each cell's text) ──
const ROWS = [
  [1, 'Alice Johnson', 'Sales',       'Tokyo',   124500],
  [2, 'Bob Smith',     'Engineering', 'Osaka',    98200],
  [3, 'Carol White',   'Marketing',   'Tokyo',    87600],
  [4, 'David Lee',     'Sales',       'Nagoya',  142300],
  [5, 'Eva Chen',      'Engineering', 'Tokyo',   105800],
  [6, 'Frank Kim',     'Marketing',   'Osaka',    76400],
  [7, 'Grace Liu',     'Sales',       'Osaka',   131900],
  [8, 'Henry Park',    'Engineering', 'Nagoya',   92100],
];
ROWS.forEach((row, i) => {
  row.forEach((val, c) => { ws.setCellInput(i + 1, c, String(val)); });
});

// ── Zebra striping applied by row position, so it survives a sort ──
ws.setAlternateRowColors({ evenColor: '#ffffff', oddColor: '#f8fafc' });

ws.resumeRender();

// ── Freeze the header, then switch on the dropdowns ──
ws.setFrozenRows(1);
const filter = ws.createAutoFilter({ headerRow: 0, startColumn: 0, endColumn: 4 });

// ── Live count of what's visible ──
const info = document.getElementById('info')!;
filter.onFilterChange((e) => {
  info.textContent = `${ROWS.length - e.hiddenRowCount} of ${ROWS.length} rows visible`;
});

Click the Department dropdown and pick Sales; the table collapses to three rows and the readout follows. Click Revenue and sort descending. Everything below the frozen header reflows, the striping stays put. Try the live version in the data filter & sort demo, which runs the same setup against 300 to 10,000 rows.

Two details worth pulling apart: how you filter from code, and why the striping didn’t scramble.

Filtering from code

A filter is just a set of shown values per column. setColumnFilter takes the column and a Set<string> of the values to keep visible; apply() commits it. Pass null to clear one column back to “show all”:

// Show only Sales and Marketing in the Department column (index 2)
filter.setColumnFilter(2, new Set(['Sales', 'Marketing']));
filter.apply();

// Later: clear just that column
filter.setColumnFilter(2, null);
filter.apply();

You rarely hard-code the values, though — you ask the column what it holds. getColumnValues returns the unique cell texts below the header, sorted alphabetically, which is exactly what the dropdown checklist shows:

filter.getColumnValues(2);       // ['Engineering', 'Marketing', 'Sales']
filter.getColumnFilter(2);       // { column: 2, selectedValues: Set(...) } or null
filter.hasActiveFilters();       // true if any column is filtering
filter.clearAll();               // drop every filter, unhide every row

Because the criteria are plain sets of strings, the filter matches on each cell’s text — the same text getColumnValues hands you. That’s why the example stores data with setCellInput: it’s the input text, not a typed number, that the filter compares.

Sort within the filter, or the whole sheet

There are two sortColumn entry points and the difference is scope:

ws.sortColumn(4, 'desc');     // worksheet: sorts within the active filter's range,
                              // or the whole sheet if there's no filter
filter.sortColumn(4, 'desc'); // the filter: always sorts its own data range

With an auto-filter in place they do the same thing — sort the filter’s data rows and record the state, so filter.getSortState() (and ws.getSortState()) report { column: 4, order: 'desc' }. The distinction only matters when you sort without a filter: ws.sortColumn still works and reorders the entire sheet, but there’s no filter to remember it, so getSortState() stays null. If you want the indicator without moving data — say you sorted through your own code path — filter.setSortState(col, order) sets it, and filter.clearSortState() clears it.

Reacting to changes

Filtering works by hiding rows, never deleting them — the data in a hidden row is untouched and comes back the moment the filter clears. Two hooks let you respond:

// Fires after any filter change; event carries the running hidden-row count
const off = filter.onFilterChange((e) => {
  console.log(e.column, e.selectedValues, e.hiddenRowCount);
});

// Fires when a user clicks a header dropdown arrow — build your own filter UI here
ws.onFilterDropdownClick((column) => {
  openCustomFilterPanel(column);
});

onFilterChange is what drove the visible-row readout in the example. onFilterDropdownClick is the escape hatch: intercept the click and render your own panel — a date-range picker, a search box, a multi-select with counts — instead of the built-in checklist. Both return an unsubscribe function; call it (like off()) when you tear the grid down.

To manage the filter itself: ws.getAutoFilter() hands back the current AutoColumnFilter (or null), and ws.removeAutoFilter() takes the dropdowns off and unhides everything.

Where Lite ends and Pro begins

Unlike some features that split enforcement from authoring, Sort & Filter is Pro end to end:

The data and its styling are free; the interactive sort/filter engine — and the frozen panes that keep the header visible while you scroll a filtered list — are where Pro begins.

Wrapping up

Sort & Filter turns a static table into something users can actually navigate: sortColumn for ascending/descending order, createAutoFilter for Excel-style header dropdowns, and a compact code API — getColumnValues, setColumnFilter, apply, clearAll, onFilterChange — that mirrors every click. Rows are hidden, never lost, so a filtered view is always one clearAll() away from whole again.

Try it in the data filter & sort demo — filter and sort up to 10,000 rows — or read the full API in the sort & filter docs. To stop bad data before it ever reaches a filter, see the data validation article; to style rows by their values, the conditional formatting article.