Automation with scripts
8sheets automates workbooks with JavaScript, not VBA. Open the Automate tab and choose Scripts (Opt+F8), or record your actions and edit the script that comes out.
The script API
A script is the body of an async function that receives one object, workbook. It can use await.
| Member | What it does |
|---|---|
workbook.activeSheet | The sheet on screen |
workbook.getSheet(name) | A sheet by name, ignoring case |
workbook.addSheet(name) | Adds a sheet and returns it |
workbook.sheetNames | The names of all sheets |
Each sheet has:
| Member | What it does |
|---|---|
name | The sheet's name |
usedRange | { rows, columns } of the used area |
getValue('B2') | One value: a number, text, true/false, an error such as '#N/A', or null |
getValues('A1:C10') | Values as an array of rows |
setValue('B2', value) | Types a value or a formula into a cell |
setValues('A1', rows) | Types an array of rows, starting at a cell |
setFormat('A1:C1', style) | Applies a style, for example { bold: true, fill: '#FFF2CC', numFmt: '0.0%' } |
Example
// Add a total under the numbers in column B and make it bold.
const sheet = workbook.activeSheet;
const last = sheet.usedRange.rows;
sheet.setValue(`A${last + 1}`, 'Total');
sheet.setValue(`B${last + 1}`, `=SUM(B2:B${last})`);
sheet.setFormat(`A${last + 1}:B${last + 1}`, { bold: true });
Each setValue, setValues and setFormat call can be undone with Cmd+Z.
Safety
Scripts are stored in your browser and only code you typed or recorded yourself ever runs. Nothing runs from an opened file: VBA macros in .xlsm files are kept in the file but never executed.
For building 8sheets into your own product, see the full JavaScript API.