Getting started with Apiary

In this guide we’ll load an Excel file into GRID’s spreadsheet engine, run a formula on the data it contains, and output the answer. This guide assumes you’re using Node.js and npm on a Unix-like system. We used Node.js v22.11.0 and npm v10.9.0 when writing this tutorial.

1. Let’s start by creating a new directory and using it for a new npm-based project:

   mkdir apiary-quickstart
   cd apiary-quickstart
   npm init -y

2. Next, we’ll configure npm to download packages within the @grid-is scope from npmjs.com. This will give you access to GRID’s private spreadsheet engine package, Apiary. Create a .npmrc file:

   cat << 'EOF' > .npmrc
   @grid-is:registry=https://registry.npmjs.org
   //registry.npmjs.org:_authToken=${NPM_TOKEN}
   EOF

   Note

   This assumes you already have an environment variable, NPM_TOKEN, that gives you access to our packages on npmjs.com. If you don’t have such a token, please ask us for access.

3. Now we can add the project’s dependencies. We’ll need two direct dependencies:

   • The spreadsheet engine itself, known as Apiary
   • The open-source xlsx-convert package to convert Excel spreadsheets into the JSON format used by Apiary

   npm add @grid-is/apiary @borgar/xlsx-convert

4. Grab a copy of our example spreadsheet. For this demo, we’ll use a simple spreadsheet that tracks monthly household expenses. It categorises our spending, provides a budget for each category, and tracks actual spending. Save a local copy of budget.xlsx so we can use it in our project.

5. Now to the meat of it. Let’s write some code that can open a spreadsheet and interact with it. Create an index.js file and copy this code in:

   import xlsxConvert from "@borgar/xlsx-convert";
   import { Model } from '@grid-is/apiary';
   
   await Model.preconditions;
   
   const wb = await xlsxConvert("budget.xlsx");
   const model = Model.fromJSF(wb);
   const totalSpent = model.runFormula("=SUM(D:D)");
   console.info({ totalSpent });

   What does the code do?

   We can go through the code line-by-line to understand what’s happening:

   import xlsxConvert from "@borgar/xlsx-convert";
   import { Model } from '@grid-is/apiary';

   The xlsxConvert function converts a .xlsx file into JSF (JSON Spreadsheet Format), which is Apiary’s preferred format for loading workbook data. JSF is an open format maintained by the JSFKit project.

   The Model class is Apiary’s interface to a collection of one or more workbooks. Your interactions with your spreadsheets will happen through an instance of Model.

   await Model.preconditions;

   Model.preconditions is a promise that must be awaited before loading a spreadsheet into a model. This ensures that the WebAssembly-based formula parser is ready to use.

   const wb = await xlsxConvert("budget.xlsx");
   const model = Model.fromJSF(wb);

   Next we open a local file, budget.xlsx, and convert it from Excel’s XML-based format into JSF, and use that to create an Apiary model instance. The Model.fromJSF() method accepts JSF data and creates a model containing that workbook. Once this is done, we have an in-memory representation of the workbook that we can interact with.

   const totalSpent = model.runFormula("=SUM(D:D)");
   console.info({ totalSpent });

   The final step is to calculate the answer to an Excel formula. Here, SUM(D:D) simply sums up all numbers in column D. Looking at our spreadsheet, column D contains our actual monthly expenditure broken down by category. By summing it up we can calculate how much we spent last month, then log it to the console.

6. Once you have the budget.xlsx file and the code in index.js, we can run it using Node. Most of Apiary is written in TypeScript, but its formula parser is written in Rust and loaded as a WebAssembly module. Because of that, we need to run Node with the --experimental-wasm-modules parameter:

   node --experimental-wasm-modules index.js

   After running that, you’ll see our monthly expenditure printed out in your terminal:

   { totalSpent: 6945 }