API and events

TREB spreadsheets are fully scriptable with an API, and you can subscribe to events to react to spreadsheet changes. On this page we’ll walk through the basics. Full API docs are available here.

Creating a spreadsheet

When you create a spreadsheet with the API, you’ll get back a reference to the spreadsheet.

import { TREB } from '@trebco/treb';
const sheet = TREB.CreateSpreadsheet({ container });

import { TREB } from '@trebco/treb';
const sheet = TREB.CreateSpreadsheet({ container });

This is an instance of EmbeddedSpreadsheet. The npm library includes typings so your editor should be able to offer tooltips and autocomplete.

Cell values

When working with the API, cell values are usually intrinsic types (number, string, boolean, undefined) but there are some special types like Complex which represents a complex number. The cell value type is defined here.

GetRange/SetRange

The most commonly used API functions are GetRange and SetRange. These are used to get or set values in the spreadshet.

Typically you would use a string to identify an address ("A1") or range ("C3:D8"). There’s an object type you can use for programmatic access.

// get value. we will need to check the type of the value, 
// because it could be any type (including undefined).
const value = sheet.GetRange('A1');

// get a range. the result will be a 2d array of cell values.
const values = sheet.GetRange('A1:C7');

// set a value
sheet.SetRange('B2', 14); 

// set a formula* (see note below)
sheet.SetRange('B3', '=A1 * B2');

// set a range, using a 2d array
sheet.SetRange('D4:E5', [[ 'text', false ], [ 3, 4 ]]);

// get value. we will need to check the type of the value, 
// because it could be any type (including undefined).
const value = sheet.GetRange('A1');

// get a range. the result will be a 2d array of cell values.
const values = sheet.GetRange('A1:C7');

// set a value
sheet.SetRange('B2', 14); 

// set a formula* (see note below)
sheet.SetRange('B3', '=A1 * B2');

// set a range, using a 2d array
sheet.SetRange('D4:E5', [[ 'text', false ], [ 3, 4 ]]);

When working with ranges, values are get or set as 2-dimensional arrays. TREB never returns (or accepts) a one-dimensional array.

Setting a cell formula

When you call SetRange to set a cell formula, you should indicate which argument separator you are using. This is an option you can pass in to the function with the SetRangeOptions object.

Depending on the browser locale, the default argument separator might be a comma or a semicolon. If you explicitly set an argument separator when calling SetRange, it will work in every locale without needing to translate.

// this will break if the locale uses a different argument separator
sheet.SetRange('A1', '=Sum(1, 2, 3)');

// these will work in any locale
sheet.SetRange('B2', '=Sum(1, 2, 3)', { argument_separator: ','});
sheet.SetRange('B2', '=Sum(1; 2; 3)', { argument_separator: ';'});

// this will break if the locale uses a different argument separator
sheet.SetRange('A1', '=Sum(1, 2, 3)');

// these will work in any locale
sheet.SetRange('B2', '=Sum(1, 2, 3)', { argument_separator: ','});
sheet.SetRange('B2', '=Sum(1; 2; 3)', { argument_separator: ';'});

Subscribing to events

With a reference to the spreadsheet, you can subscribe to events by calling Subscribe. The argument is a callback function that will get called with events. Events are dispatched asynchronously and may be batched.

sheet.Subscribe((event) => {
  // ...handle the event...
});

sheet.Subscribe((event) => {
  // ...handle the event...
});

Event is a union with a type field you can switch on to determine the type of event.