Skip to main content

Airtable

Airtable is a collaborative dataset hosting service.

SheetJS is a JavaScript library for reading and writing data from spreadsheets.

This demo uses SheetJS to properly exchange data with spreadsheet files. We'll explore how to use the airtable NodeJS library and SheetJS in two data flows:

  • "Exporting data": Data in Airtable will be pulled into an array of objects and exported to XLSB spreadsheets using SheetJS libraries.

  • "Importing data": Data in an XLSX spreadsheet will be parsed using SheetJS libraries and appended to a dataset in Airtable

NodeJS Integration​

Installation​

The SheetJS NodeJS module can be required in NodeJS scripts that interact with Airtable.

The Airtable connector module is airtable and can be installed with npm:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.2/xlsx-0.20.2.tgz airtable

Authentication​

Airtable recommends Personal Access Tokens ("PAT") for interacting with the API.

The "Personal Access Token" section walks through the process of creating a PAT.

The connector constructor accepts an options argument. The PAT should be passed using the apiKey property:

const Airtable = require("airtable");
const apiKey = "PAT..."; // personal access token
const conn = new Airtable({apiKey, /* see docs for other options ... */});

The base method opens a specified workspace. The internal workspace name is the first fragment in the Airtable URL, typically starts with "app":

const base = conn.base("app...");

The table method of the workspace object opens a specified table:

const table = base.table("tablename...");

Exporting Data​

When querying data, a result set will be a simple array of Record objects. The fields property of each record object is a simple JS object. Mapping over the result set and picking the fields field yields a standard array of objects:

/** Create array of objects from Airtable table */
async function airtable_to_aoo(table) {
  /* get all rows */
  const result = await table.select().all();

  /* pull raw objects from the result */
  const aoo = result.map(r => r.fields);
  return aoo;
}

The SheetJS json_to_sheet utility function1 can generate a worksheet object from the array of objects:

const XLSX = require("xlsx");

/** Create SheetJS worksheet from Airtable table */
async function airtable_to_worksheet(table) {
  /* get all rows */
  const result = await table.select().all();

  /* pull raw objects from the result */
  const aoo = result.map(r => r.fields);

  /* create a worksheet */
  const worksheet = XLSX.utils.json_to_sheet(aoo);
  return worksheet;
}

The results are not guaranteed to be sorted. The official API includes options for sorting by fields.

The worksheet object must be added to a new workbook object using the book_new and book_append_sheet helper functions2:

/** Create SheetJS workbook from Airtable table */
async function airtable_to_workbook(table) {
  /* generate worksheet */
  const ws = await airtable_to_worksheet(table);
  /* create a new workbook */
  const wb = XLSX.utils.book_new();
  /* add worksheet to workbook */
  XLSX.utils.book_append_sheet(wb, ws, "ExportedData");
  return wb;
}

Local files can be created using the SheetJS writeFile method3:

(async() => {
  /* generate SheetJS workbook */
  const wb = await airtable_to_workbook(table);
  /* write to XLSX */
  XLSX.writeFile(wb, "SheetJSAirtableExport.xlsx");
})();

Importing Data​

The Airtable table create method expects an array of record objects. The fields property of each object is expected to contain the raw record data.

Mapping over a standard array of objects can create Airtable-friendly data:

/** Append records from an array of data objects to Airtable table */
async function airtable_load_aoo(table, aoo) {
  /* reshape to be compatible with Airtable API */
  const airtable_rows = aoo.map(fields => ({ fields }));

  /* upload data */
  return await table.create(airtable_rows);
}

Starting from a SheetJS worksheet object4, the sheet_to_json method5 can generate normal arrays of objects:

const XLSX = require("xlsx");

/** Append records from a SheetJS worksheet to Airtable table */
async function airtable_load_worksheet(table, worksheet) {
  /* generate normal array of objects */
  const aoo = XLSX.utils.sheet_to_json(worksheet);

  /* upload data */
  return await airtable_load_aoo(table, aoo);
}

A SheetJS worksheet object can be extracted from a workbook object6:

/** Append records from the first worksheet of a workbook to Airtable table */
async function airtable_load_workbook(table, workbook) {
  /* pull first worksheet from workbook object */
  const first_sheet_name = workbook.SheetNames[0];
  const ws = workbook.Sheets[first_sheet_name];

  /* upload data */
  return await airtable_load_worksheet(table, ws);
}

Local files can be read using the SheetJS readFile method7:

const wb = XLSX.readFile("SheetJSAirtableTest.xlsb");
(async() => {
  await airtable_load_workbook(table, wb);
});

Complete Example​

Tested Deployments

This demo was last tested on 2023 September 03. At the time, free accounts included limited API access.

  1. Create a free Airtable account and verify the email address.

Personal Access Token​

In the past, Airtable offered API keys. They are slated to deprecate API keys in January 2024. They recommend "Personal Access Tokens" for operations.

API actions will require a PAT, which must be created through the developer hub:

  1. Click on account icon (topright area of the page) and select "Developer Hub".

  2. Click "Create Token".

  3. In the form, make the following selections:

  • Name: enter any name (for example, "SheetJS Test")
  • Scopes: data.records:read and data.records:write (adding 2 scopes)
  • Access: "All current and future bases in all current and future workspaces"

The form will look like the screenshot below:

Airtable PAT Form

  1. Click "Create Token" and you will see a popup. Copy the token and save it.

Workspace​

For the purposes of this demo, a sample workspace should be created:

  1. Download https://sheetjs.com/pres.xlsx

  2. Create a project in Airtable using "Quickly upload". Select "Microsoft Excel" and select the downloaded file from step 1. Click "Upload", then "Import".

  3. A workspace will be created. The name will be found in the URL. For example:

https://airtable.com/appblahblah/tblblahblah/blahblah
--------------------/^^^^^^^^^^^/ workspace name

the first part after the .com will be the workspace name.

Exporting Data​

  1. Save the following to SheetJSAirtableRead.js:
SheetJSAirtableRead.js
const Airtable = require("airtable"), XLSX = require("xlsx");
/* replace the value with the personal access token */
const apiKey = "pat...";
/* replace the value with the workspace name */
const base = "app...";
(async() => {
  const conn = new Airtable({ apiKey });
  const table = conn.base(base).table("Sheet1");
  const result = await table.select().all();
  const aoo = result.map(r => r.fields);
  const ws = XLSX.utils.json_to_sheet(aoo);
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
  XLSX.writeFile(wb, "SheetJSAirtable.xlsb");
})();

  1. Replace the values in the highlighted lines with the PAT and workspace name.

  2. Install dependencies:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.2/xlsx-0.20.2.tgz airtable
  1. Run the script:
node SheetJSAirtableRead.js

The script should write SheetJSAirtable.xlsb. The file can be opened in Excel.

Importing Data​

  1. Create a file SheetJSAirpend.xlsx with some new records in sheet Sheet1:

Records to append

npx xlsx-cli SheetJSAirpend.xlsx should print the following data:

Sheet1
Name,Index
Someone Else,47
  1. Save the following to SheetJSAirtableWrite.js:
SheetJSAirtableWrite.js
const Airtable = require("airtable"), XLSX = require("xlsx");
/* replace the value with the personal access token */
const apiKey = "pat...";
/* replace the value with the workspace name */
const base = "app...";
(async() => {
  const conn = new Airtable({ apiKey });
  const table = conn.base(base).table("Sheet1");
  const wb = XLSX.readFile("SheetJSAirpend.xlsx");
  const ws = wb.Sheets["Sheet1"];
  const aoo = XLSX.utils.sheet_to_json(ws);
  await table.create(aoo.map(fields => ({ fields })));
})();

  1. Replace the values in the highlighted lines with the PAT and workspace name.

  2. Install dependencies:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.2/xlsx-0.20.2.tgz airtable
  1. Run the script:
node SheetJSAirtableWrite.js

Open Airtable and verify the new row was added:

Final Result in Airtable

Footnotes​

  1. See json_to_sheet in "Utilities" ↩

  2. See "Workbook Helpers" in "Utilities" for details on book_new and book_append_sheet. ↩

  3. See writeFile in "Writing Files" ↩

  4. See "Sheet Objects" for more details/ ↩

  5. See sheet_to_json in "Utilities" ↩

  6. See "Workbook Object" ↩

  7. See readFile in "Reading Files" ↩