1. Spreadsheet docs
  2. Developers

Working with data

Read and write cells, values, formulas, styles and number formats; manage sheets, rows, names and tables; sort, filter, validate and calculate from code.

The workbook API reads and writes the same model the user edits: every change from code shows on screen at once, can be undone, and fires the same events as a change made by hand. The API is the same in a mounted workbook and in a headless one in Node.

The object model has four levels; the API reference lists every member.

  • Workbook (wb): sheets, names, theme, history, calculation, files.
  • Sheet (wb.activeSheet, wb.sheet('Data')): ranges, rows and columns, tables, charts and shapes, PivotTables, notes and comments, validation, protection, printing.
  • Range (sheet.range('A1:D9')): values, formulas, styles, cell types, sorting, filtering, merging.
  • Table (sheet.tables, sheet.addTable(...)): a named Excel table with headers, styles and a totals row.

A complete example

This sample builds an order list from your data: a header row, values, a formula column, number formats, a table with a totals row, a defined name and a dropdown list.

JavaScript

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

const orders = [
  { region: 'North', product: 'Notebook', units: 12, price: 4.5 },
  { region: 'South', product: 'Backpack', units: 3, price: 39 },
  { region: 'East', product: 'Desk lamp', units: 7, price: 24 },
];

const wb = await mountWorkbook(document.querySelector('#sheet'), { chrome: 'light' });
fill(wb, orders);

function fill(wb, rows) {
  wb.history.transaction('Load orders', () => {
    const sheet = wb.activeSheet;
    sheet.name = 'Orders';
    const end = rows.length + 1;
    sheet.range('A1').values = [
      ['Region', 'Product', 'Units', 'Price', 'Revenue'],
      ...rows.map((r) => [r.region, r.product, r.units, r.price, null]),
    ];
    const table = sheet.addTable(`A1:E${end}`, { name: 'Sales', style: 'TableStyleMedium2' });
    sheet.range(`E2:E${end}`).formulas = '=[@Units]*[@Price]';
    sheet.range(`D2:E${end}`).style = { numFmt: '#,##0.00' };
    table.setOption('totalsRow', true);   // sums the last column
    sheet.setValidation(`A2:A${end}`, { type: 'list', formula1: '"North,South,East,West"' });
    sheet.setColWidth('A', 'B', 110);
    sheet.freeze(1);
    wb.names.set('TotalRevenue', `Orders!$E$${end + 1}`);
  });
}

React

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

export function Orders({ orders }) {
  function fill(wb) {
    wb.history.transaction('Load orders', () => {
      const sheet = wb.activeSheet;
      sheet.name = 'Orders';
      const end = orders.length + 1;
      sheet.range('A1').values = [
        ['Region', 'Product', 'Units', 'Price', 'Revenue'],
        ...orders.map((r) => [r.region, r.product, r.units, r.price, null]),
      ];
      const table = sheet.addTable(`A1:E${end}`, { name: 'Sales', style: 'TableStyleMedium2' });
      sheet.range(`E2:E${end}`).formulas = '=[@Units]*[@Price]';
      sheet.range(`D2:E${end}`).style = { numFmt: '#,##0.00' };
      table.setOption('totalsRow', true);
      sheet.setValidation(`A2:A${end}`, { type: 'list', formula1: '"North,South,East,West"' });
      sheet.freeze(1);
    });
    wb.history.clear();   // the loaded data isn't something the user should undo
  }

  return (
    <div style={{ height: 480 }}>
      <Sheet options={{ chrome: 'light' }} onReady={fill} />
    </div>
  );
}

Vue

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

const props = defineProps({ orders: { type: Array, required: true } });

function fill(wb) {
  wb.history.transaction('Load orders', () => {
    const sheet = wb.activeSheet;
    sheet.name = 'Orders';
    const end = props.orders.length + 1;
    sheet.range('A1').values = [
      ['Region', 'Product', 'Units', 'Price', 'Revenue'],
      ...props.orders.map((r) => [r.region, r.product, r.units, r.price, null]),
    ];
    const table = sheet.addTable(`A1:E${end}`, { name: 'Sales', style: 'TableStyleMedium2' });
    sheet.range(`E2:E${end}`).formulas = '=[@Units]*[@Price]';
    sheet.range(`D2:E${end}`).style = { numFmt: '#,##0.00' };
    table.setOption('totalsRow', true);
    sheet.setValidation(`A2:A${end}`, { type: 'list', formula1: '"North,South,East,West"' });
    sheet.freeze(1);
  });
  wb.history.clear();
}
</script>

<template>
  <Sheet :options="{ chrome: 'light' }" style="height: 480px" @ready="fill" />
</template>

Angular

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

interface Order { region: string; product: string; units: number; price: number }

@Component({
  selector: 'app-orders',
  imports: [SheetComponent],
  template: `<eight-sheet class="grid" [options]="{ chrome: 'light' }" (ready)="fill($event)" />`,
  styles: `.grid { height: 480px; }`,
})
export class Orders {
  @Input() orders: Order[] = [];

  fill(wb: SheetsWorkbook) {
    wb.history.transaction('Load orders', () => {
      const sheet = wb.activeSheet;
      sheet.name = 'Orders';
      const end = this.orders.length + 1;
      sheet.range('A1').values = [
        ['Region', 'Product', 'Units', 'Price', 'Revenue'],
        ...this.orders.map((r) => [r.region, r.product, r.units, r.price, null]),
      ];
      const table = sheet.addTable(`A1:E${end}`, { name: 'Sales', style: 'TableStyleMedium2' });
      sheet.range(`E2:E${end}`).formulas = '=[@Units]*[@Price]';
      sheet.range(`D2:E${end}`).style = { numFmt: '#,##0.00' };
      table.setOption('totalsRow', true);
      sheet.setValidation(`A2:A${end}`, { type: 'list', formula1: '"North,South,East,West"' });
      sheet.freeze(1);
    });
    wb.history.clear();
  }
}

Sheets

TaskCode
The sheet on screenwb.activeSheet; set it to show another: wb.activeSheet = 'Data'
All sheets, in orderwb.sheets
A sheet by name or positionwb.sheet('Data'), wb.sheet(0)
Add a sheet at the end and show itwb.addSheet('Data')
Rename, rewriting formulas that refer to itsheet.name = 'Sales' or wb.renameSheet('Data', 'Sales')
Move, copy, deletewb.moveSheet('Data', 0), wb.duplicateSheet('Data'), wb.removeSheet('Data')
Hide, colour the tab, hide gridlinessheet.hidden = true, sheet.tabColor = '#2563eb', sheet.gridlines = false
Freeze the top rows and left columnssheet.freeze(1, 2)
Zoomsheet.zoom = 1.25 (0.1 to 4)

The last visible sheet can't be removed.

Ranges

A range is a rectangle of cells. Get one from A1 text, from zero-based row and column numbers, or from another range:

const sheet = wb.activeSheet;
sheet.range('B2:D9');            // A1 text, also 'A:A' or '3:3'; for another sheet use wb.sheet('Data').range(...)
sheet.range(1, 1, 8, 3);         // rows 1–8, columns 1–3, zero-based: B2:D9
sheet.cell(1, 1);                // one cell: B2
sheet.usedRange;                 // the cells that hold anything, or null on an empty sheet
sheet.range('B2').currentRegion();   // the block of data around B2, as Ctrl+A finds it
sheet.range('A1:C1').offset(1, 0);   // A2:C2
sheet.range('B2:D9').address;        // 'B2:D9'

Whole rows and columns, such as A:A, read only as far as the used range, so reading range('A:A').values costs what the data costs, not a million rows.

Values

range.values reads and writes plain values, row by row: numbers, strings, booleans, and null for empty cells. Errors read as their text, such as #N/A.

sheet.range('A1').values = [['Item', 'Qty'], ['Pens', 4]];   // an array of rows starts at the top-left cell
sheet.range('C2:C9').values = 0;                              // one value fills the range
const rows = sheet.range('A1:B2').values;                     // [['Item', 'Qty'], ['Pens', 4]]
sheet.range('C2:C9').clear();                                 // contents; clear('all') removes formats too

Text written through values is stored as text, never parsed: '=1+1' stays a string and '12%' stays text. To enter text the way a user types it, use input, which parses numbers, dates, percentages, booleans and formulas under Excel's entry rules and picks a matching number format:

sheet.range('A1').input([['12%', '2027-03-01', '=SUM(B2:B9)', 'TRUE']]);

Dates. A spreadsheet stores a date as a serial number of days. Writing a JavaScript Date stores its serial, counted in UTC, so create dates with Date.UTC (new Date(Date.UTC(2027, 0, 5))) to get whole days. Reading gives the number back. Give the cells a date format so they show as dates:

sheet.range('A2').values = [[new Date(Date.UTC(2027, 0, 5))]];
sheet.range('A2:A99').style = { numFmt: 'yyyy-mm-dd' };

Formulas

range.formulas reads formulas as the user sees them, with a leading =; cells without a formula read as null. Writing one string to a range fills it the way a fill handle does, adjusting relative references:

sheet.range('D2:D100').formulas = '=B2*C2';        // D3 gets =B3*C3, and so on
sheet.range('E2').formulas = [['=SUM(D2:D100)']];  // or an array of rows
sheet.range('F2').formulas = '=XLOOKUP(A2, Prices[Item], Prices[Price])';
await wb.whenCalculated();                          // resolves when every formula is current
console.log(sheet.range('E2').values[0][0]);

Formulas use English function names and commas, as stored in the file, whatever the user's formula language. All 502 supported functions, dynamic arrays, structured references, names and LAMBDA work from code as they do when typed.

Calculation. The workbook recalculates in short time slices, so the page stays responsive. await wb.whenCalculated() waits until values are current; reading a value through the API also calculates that cell first if it is waiting. For manual calculation, set wb.calculationMode = 'manual' (or the calculation: 'manual' option) and call wb.calculate() when you want new results. The calc-end event fires when calculation finishes.

Styles and number formats

range.style applies a partial style to every cell of the range, and reads the effective style of the top-left cell as a plain object:

sheet.range('A1:E1').style = {
  bold: true,
  color: '#FFFFFF',
  fill: { theme: 'accent1' },          // follows the workbook theme
  hAlign: 'center',
  borders: { b: { style: 'medium', color: '#1F3864' } },
};
sheet.range('D:D').style = { numFmt: '#,##0.00 "USD"' };   // a whole column costs one record
sheet.range('F2:F99').style = { numFmt: '0.0%', fill: { theme: 'accent6', tint: 0.8 } };
console.log(sheet.range('A1').style.bold);   // true
FieldValues
fontName, fontSizeA font name; a size in points
bold, italic, striketrue or false
underline0 none, 1 single, 2 double
color, fill'#RRGGBB', a theme colour { theme: 'accent1', tint: 0.4 } (tint from -1 to 1), or null
borders{ l, r, t, b }, each { style, color } or null; styles as in the file format, such as thin, medium, thick, dashed, dotted, double
numFmtA number format code, such as '#,##0.00', '0%', 'yyyy-mm-dd', '[Red]-#,##0'
hAlignleft, center, right, fill, justify, centerContinuous, distributed
vAligntop, center, bottom, justify, distributed
wrap, shrinkWrap text; shrink to fit
indent, rotationAn indent level; a text angle in degrees
unlockedtrue leaves the cell editable on a protected sheet

Theme colour slots are dk1, lt1, dk2, lt2, accent1 to accent6, hlink and folHlink. A colour given by slot follows the theme here and in Excel.

range.altText sets what a screen reader says for a cell; see Accessibility for developers.

Rows and columns

sheet.insertRows(5, 3);            // three rows above row index 5; references in every sheet adjust
sheet.deleteRows(10);
sheet.insertCols(2);               // before column C
sheet.setRowHeight(0, 0, 28);      // first row, last row, height in points
sheet.setColWidth('A', 'C', 120);  // letters or zero-based numbers, width in points
sheet.hideRows(20, 29);            // hideRows(from, to, false) shows them again
sheet.hideCols(4, 4);
sheet.range('A1:E1').merge();      // Excel keeps only the first value; unmerge() undoes it

At 100% zoom one point is one CSS pixel. The default row is 16 points high and the default column 65 points wide.

Names

Defined names work in formulas, the name box and validation lists:

wb.names.set('TaxRate', 'Settings!$B$1');                     // no leading =
wb.names.set('Regions', 'Lists!$A$2:$A$5');
wb.names.set('LocalTotal', 'Q1!$F$20', { sheet: 'Q1' });      // a name local to one sheet
wb.names.list();          // [{ name, refersTo, sheet }]
wb.names.get('TaxRate');  // 'Settings!$B$1'
wb.names.remove('Regions');

A name can also hold a formula or a LAMBDA, as in the Name Manager.

Tables

sheet.addTable(range, options) turns a range whose first row holds headers into an Excel table. Formulas can then use structured references such as Sales[Revenue] and [@Units].

const table = sheet.addTable('A1:E120', { name: 'Sales', style: 'TableStyleMedium2' });
table.setOption('totalsRow', true);          // also headerRow, filterButton, showRowStripes, showColumnStripes,
table.setOption('showRowStripes', false);    // showFirstColumn, showLastColumn
table.name = 'Orders';                       // rewrites structured references
table.resize('A1:E200');
table.columns;                               // the columns, with their names
table.range.address;                         // 'A1:E201' with the totals row
table.remove();                              // back to ordinary cells
sheet.tables;                                // the tables on the sheet

setOption throws when a change can't be made, such as a totals row over data.

Sort and filter

// Sort rows in place by Units, largest first, then by Region; the first row stays as headers.
sheet.range('A1:E120').sort([{ col: 'C', desc: true }, { col: 'A' }], { headers: true });

// AutoFilter: the first row holds the headers. Keep the rows whose Region shows North or East.
sheet.range('A1:E120').filter({ A: ['North', 'East'] });
sheet.clearFilter();   // removes the AutoFilter and shows hidden rows

A sort key's col is a column letter or a zero-based column number of the sheet (so 2 is column C, wherever the range starts). For your own order, register a comparer with registerSortCompare(name, fn) and name it in the key's compare, or set info.compareFunction in a range-sorting listener.

Data validation

sheet.setValidation(range, rule) puts a rule on a range, as Data > Data Validation does: those cells leave any other rule, and fields you leave out take the dialog's defaults. Each call is one undo step.

sheet.setValidation('D2:D120', {
  type: 'whole', operator: 'between', formula1: '1', formula2: '1000',
  errorTitle: 'Units', error: 'Units are a whole number from 1 to 1000.',
});
sheet.setValidation('A2:A120', { type: 'list', formula1: '"North,South,East,West"' });   // a dropdown list
sheet.setValidation('B2:B120', { type: 'list', formula1: 'Regions' });                  // a list from a name
sheet.setValidation('E2:E120', { type: 'date', operator: 'greaterThan', formula1: 'TODAY()',
  showPrompt: true, promptTitle: 'Due date', prompt: 'A date after today.' });

sheet.validationAt('A5');      // the rule covering a cell, or null
sheet.validations;             // every rule on the sheet, with its sqref
sheet.clearValidation('E2:E120');
Rule fieldValues
typelist, whole, decimal, date, time, textLength, custom, none
operatorbetween, notBetween, equal, notEqual, greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual
formula1, formula2A list in quotes ('"a,b,c"'), a range such as $H$2:$H$9, a name, a number, or a formula without =
allowBlank, showDropDownAllow empty cells; show the in-cell list of a list rule
showError, errorStyle, errorTitle, errorThe alert for invalid input: stop, warning or information
showPrompt, promptTitle, promptThe input message shown when the cell is selected

The flagInvalidData: true option circles cells that break their rule, and the validation-failed event fires when a user enters text that does. sheet.addValidation(rule) remains for adding a rule in the file format's own shape, with its sqref.

Conditional formatting

sheet.addConditionalFormatRule(range, rule, format) adds a rule on top of the others, as the New Formatting Rule dialog does. Rules follow the file format (cfRule in ECMA-376); formulas are relative to the top-left cell of the range and have no leading =. Rules that colour matching cells take a format; colour scales, data bars and icon sets carry their own look.

// Highlight amounts over 1,000 in red.
sheet.addConditionalFormatRule('E2:E120', { type: 'cellIs', operator: 'greaterThan', formulas: ['1000'] },
  { fill: '#FFC7CE', color: '#9C0006' });

// Bold the whole row when its status is Overdue.
sheet.addConditionalFormatRule('A2:F120', { type: 'expression', formulas: ['$F2="Overdue"'] }, { bold: true });

// Top three, duplicates, and a two-colour scale.
sheet.addConditionalFormatRule('D2:D120', { type: 'top10', rank: 3 }, { fill: '#C6EFCE', color: '#006100' });
sheet.addConditionalFormatRule('A2:A120', { type: 'duplicateValues' }, { color: '#9C5700', fill: '#FFEB9C' });
sheet.addConditionalFormatRule('C2:C120', { type: 'colorScale', colorScale: {
  cfvo: [{ type: 'min' }, { type: 'max' }], colors: ['#F8696B', '#63BE7B'],
} });

sheet.conditionalFormats;              // every format on the sheet, { sqref, rules }, in priority order
sheet.clearConditionalFormats('C2:C120');   // or the whole sheet with no range

format takes fill, color, bold, italic, underline, strike, borders and numFmt, with colours as #RRGGBB. A rule that reads another sheet is stored in Excel's extension form, as Excel stores it. sheet.addConditionalFormat(block) remains for adding a block in the file format's own shape.

sheet.setNote('B4', 'Checked against the invoice', { author: 'Finance' });
sheet.setNote('B5', 'Always shown', { visible: true });
sheet.note('B4');        // { address, row, col, author, text, visible }, or null
sheet.notes;             // every note on the sheet
sheet.removeNote('B5');

sheet.setHyperlink('A2', { url: 'https://example.com/orders/1042', text: 'Order 1042', tooltip: 'Open in the shop' });
sheet.setHyperlink('A3', { location: 'Summary!A1', text: 'Go to the summary' });
sheet.hyperlink('A2');   // { address, url, location, tooltip }, or null
sheet.setHyperlink('A3', null);   // removes the link and its look

Threaded comments aren't notes, and a cell holds one or the other: sheet.addComment(cell, text) starts a thread. See Users, comments and collaboration.

Sparklines

// One line sparkline per row of B2:M5, in N2:N5.
sheet.addSparklines('line', 'B2:M5', 'N2:N5');
sheet.addSparklines('column', 'Data!B2:M2', 'N7');   // one cell charts the whole data range
sheet.addSparklines('stacked', 'B9:M9', 'N9');       // stacked is win/loss
sheet.sparklines;                                    // groups with their type, colours and items
sheet.removeSparklines('N7');

Outline groups

sheet.groupRows(1, 12);                   // rows 2–13 one level deeper, as Data > Group does
sheet.groupCols(1, 3);
sheet.showOutlineLevel({ rows: 1 });      // fold to the top level, as the 1 button does
sheet.rowOutlineLevel(4);                 // 1
sheet.ungroupRows(1, 12);

Find and replace

const hits = await sheet.findAll('overdue', { lookIn: 'values', matchCase: false });   // [{ row, col, address }]
const count = await sheet.replaceAll('2026', '2027', { wholeCell: false });           // one undo step

findAll waits for calculation, takes * and ? as wildcards (~* finds a star), and searches values (what cells show, the default), formulas or notes, optionally only in a range and up to a limit. replaceAll works on cell text and formulas, and leaves locked cells of a protected sheet alone.

Protect a sheet

sheet.range('C2:C20').style = { unlocked: true };   // the cells users may still edit
await sheet.protectWithPassword('s3cret');          // or sheet.protect() with no password
sheet.protectionAllows('sort');                     // whether users may sort now
await wb.protectStructure();                        // no adding, moving or deleting sheets

Protection limits the user, as in Excel, and is saved in the file; it doesn't stop writes from your code. Protection covers sheets, the workbook structure, passwords and the actions you can allow.

Cell types

Cells can show controls: checkboxes, buttons, dropdown lists, option buttons, progress bars, links and more. The value underneath stays an ordinary value.

sheet.range('B2:B20').cellType = { type: 'checkbox', text: 'Done' };
sheet.range('C2:C20').cellType = { type: 'combobox', items: ['High', 'Medium', 'Low'] };

See Cell types.

Return values of the sheet features

setValidation, clearValidation, addConditionalFormatRule, clearConditionalFormats, setNote, removeNote, setHyperlink, addSparklines, removeSparklines, the outline calls and showOutlineLevel each make one undo step and return true, or false when nothing changed or the change was refused. The API reference has the details.

Charts, shapes and PivotTables

const chart = sheet.chart(sheet.addChart('column', 'A1:C13', { x: 380, y: 20, w: 480, h: 280 }));
chart.title = 'Revenue by month';
chart.altText = 'Revenue by month, January to December';

const pivot = wb.addSheet('Report').addPivotTable('Data!A1:D500', 'A3', {
  rows: ['Region'], columns: ['Year'], values: [{ field: 'Sales', summarize: 'sum' }],
});

Charts and shapes have handles for their name, position, title, text, fill and drawing order; see Charts and shapes from code. PivotTables can change fields, layout and filters, refresh, and take slicers; see PivotTables and slicers from code. Threaded comments are on Users, comments and collaboration.

Undo

Every change from code is an undo step, labelled for the undo list. Group several into one:

wb.history.transaction('Import prices', () => {
  sheet.range('A1').values = rows;
  sheet.range('A1:F1').style = { bold: true };
});   // one Undo reverts both; if the function throws, its edits are undone

// Around asynchronous work, open and close the step yourself.
wb.history.begin('Fetch and fill');
try {
  sheet.range('A1').values = await loadRows();
  wb.history.commit();
} catch (err) {
  wb.history.rollback();
  throw err;
}

wb.history.clear();   // after loading data the user shouldn't undo

wb.history.undo(), redo(), canUndo, canRedo, undoEntries and jumpTo(depth) let you build your own undo menu. The undo: false option keeps no history at all.

Large data

range.values is the convenient path. For hundreds of thousands of cells, the bulk paths write typed arrays straight into cell storage, one array per column:

const units = new Float64Array(500_000);    // NaN writes an empty cell
const names = new Array(500_000).fill('Notebook');
sheet.range('A2').setNumbers([units], { undoable: false });   // skip the undo copy for fresh data
sheet.range('B2').setStrings([names], { undoable: false });
const column = sheet.range('A:A').numbers();                  // one column back as a Float64Array

See Large files and performance.

Your own data

wb.customData is an object for your own plain data, such as a record id or the source of an import. It is saved in JSON and in .8sh documents, not in .xlsx.

wb.customData.reportId = 'Q3-2027';