使用 Airtable 的 Flying Sheets
Airtable 是一个协作数据集托管服务。
¥Airtable is a collaborative dataset hosting service.
SheetJS 是一个用于从电子表格读取和写入数据的 JavaScript 库。
¥SheetJS is a JavaScript library for reading and writing data from spreadsheets.
该演示使用 SheetJS 与电子表格文件正确交换数据。我们将探讨如何在两个数据流中使用 airtable
NodeJS 库和 SheetJS:
¥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:
-
"导出数据":Airtable 中的数据将被提取到对象数组中,并使用 SheetJS 库导出到 XLSB 电子表格。
¥"Exporting data": Data in Airtable will be pulled into an array of objects and exported to XLSB spreadsheets using SheetJS libraries.
-
"导入数据":XLSX 电子表格中的数据将使用 SheetJS 库进行解析,并附加到 Airtable 中的数据集
¥"Importing data": Data in an XLSX spreadsheet will be parsed using SheetJS libraries and appended to a dataset in Airtable
NodeJS 集成
¥NodeJS Integration
安装
¥Installation
与 Airtable 交互的 NodeJS 脚本中可能需要 SheetJS NodeJS 模块。
¥The SheetJS NodeJS module can be required in NodeJS scripts that interact with Airtable.
Airtable 连接器模块为 airtable
,可与 npm
一起安装:
¥The Airtable connector module is airtable
and can be installed with npm
:
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz airtable
鉴权
¥Authentication
Airtable 建议使用个人访问令牌 ("PAT") 与 API 进行交互。
¥Airtable recommends Personal Access Tokens ("PAT") for interacting with the API.
"个人访问令牌" 部分介绍了创建 PAT 的过程。
¥The "Personal Access Token" section walks through the process of creating a PAT.
连接器构造函数接受一个选项参数。应使用 apiKey
属性传递 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 ... */});
base
方法打开指定的工作区。内部工作区名称是 Airtable URL 中的第一个片段,通常以 "app" 开头:
¥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...");
工作区对象的 table
方法打开指定的表:
¥The table
method of the workspace object opens a specified table:
const table = base.table("tablename...");
导出数据
¥Exporting Data
查询数据时,结果集将是一个简单的 Record 对象数组。每个记录对象的 fields
属性是一个简单的 JS 对象。映射结果集并选择 fields
字段会生成一个标准的对象数组:
¥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;
}
SheetJS json_to_sheet
实用函数 [^1] 可以从对象数组生成工作表对象:
¥The SheetJS json_to_sheet
utility function[^1] 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;
}
不保证结果是排序的。官方 API 包含按字段排序的选项。
¥The results are not guaranteed to be sorted. The official API includes options for sorting by fields.
必须使用 book_new
和 book_append_sheet
辅助函数 [^2] 将工作表对象添加到新的工作簿对象中:
¥The worksheet object must be added to a new workbook object using the book_new
and book_append_sheet
helper functions[^2]:
/** 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;
}
可以使用 SheetJS writeFile
方法 [^3] 创建本地文件:
¥Local files can be created using the SheetJS writeFile
method[^3]:
(async() => {
/* generate SheetJS workbook */
const wb = await airtable_to_workbook(table);
/* write to XLSX */
XLSX.writeFile(wb, "SheetJSAirtableExport.xlsx");
})();
导入数据
¥Importing Data
Airtable 表 create
方法需要一个记录对象数组。每个对象的 fields
属性应包含原始记录数据。
¥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.
映射标准对象数组可以创建 Airtable 友好的数据:
¥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);
}
从 SheetJS 工作表对象 [^4] 开始,sheet_to_json
方法 [^5] 可以生成普通的对象数组:
¥Starting from a SheetJS worksheet object[^4], the sheet_to_json
method[^5] 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);
}
可以从工作簿对象 [^6] 中提取 SheetJS 工作表对象:
¥A SheetJS worksheet object can be extracted from a workbook object[^6]:
/** 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);
}
可以使用 SheetJS readFile
方法 [^7] 读取本地文件:
¥Local files can be read using the SheetJS readFile
method[^7]:
const wb = XLSX.readFile("SheetJSAirtableTest.xlsb");
(async() => {
await airtable_load_workbook(table, wb);
})();
完整示例
¥Complete Example
此演示最后一次测试是在 2025 年 4 月 21 日。在最近的测试中,免费账户包含有限的 API 访问权限。
¥This demo was last tested on 2025-04-21. In the most recent test, free accounts included limited API access.
-
创建一个免费的 Airtable 账户并验证电子邮件地址。
¥Create a free Airtable account and verify the email address.
个人访问令牌
¥Personal Access Token
API 操作需要 PAT,该 PAT 必须通过开发者中心创建:
¥API actions will require a PAT, which must be created through the developer hub:
-
单击账户图标(页面右上角区域)并选择 "Builder Hub"。
¥Click on account icon (topright area of the page) and select "Builder Hub".
在尝试创建令牌之前,必须验证与账户关联的电子邮件地址。
¥The email address associated with the account must be verified before attempting to create a token.
-
点击侧边栏中的 "个人访问令牌",然后点击 "创建令牌"。
¥Click "Personal access tokens" in the sidebar, then click "Create Token".
-
在表单中,进行以下选择:
¥In the form, make the following selections:
-
名称:输入任意名称(例如,"SheetJS 测试")
¥Name: enter any name (for example, "SheetJS Test")
-
适用范围:
data.records:read
和data.records:write
(添加 2 个范围)¥Scopes:
data.records:read
anddata.records:write
(adding 2 scopes) -
使用权:"所有当前和未来工作区中的所有当前和未来基地"
¥Access: "All current and future bases in all current and future workspaces"
该表单将类似于以下屏幕截图:
¥The form will look like the following screenshot:
-
单击 "创建令牌",你将看到一个弹出窗口。复制令牌并保存。
¥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:
-
下载 https://xlsx.nodejs.cn/pres.xlsx
¥Download https://xlsx.nodejs.cn/pres.xlsx
-
点击左上角的向左箭头返回主页。
¥Click the left arrow in the top-left corner to return to the home page.
-
在左侧边栏中,向下滚动并点击 "导入" 链接。
¥In the left sidebar, scroll down and click the "Import" link.
在模态框中,选择 "微软 Excel"。
¥In the modal, select "Microsoft Excel".
在上传模式中,点击 "浏览文件",然后选择步骤 5 中的 pres.xlsx
。
¥In the upload modal, click "browse files" and select pres.xlsx
from Step 5.
单击蓝色的 "上传 1 个文件" 按钮。
¥Click the blue "Upload 1 file" button.
在新的模态框中,点击 "导入"。上次测试此演示时,Airtable 创建了 5 条包含 "名称" 和 "索引" 列标题的记录。
¥In the new modal, click "Import". When this demo was last tested, Airtable created 5 records with "Name" and "Index" column headers.
-
将创建一个工作区。该名称将在 URL 中找到。例如:
¥A workspace will be created. The name will be found in the URL. For example:
https://airtable.com/appblahblah/tblblahblah/blahblah
--------------------/^^^^^^^^^^^/ workspace name
.com
之后的第一部分是工作区名称。复制名字。
¥The first part after the .com
will be the workspace name. Copy the name.
新项目
¥New Project
-
创建一个新项目:
¥Create a new project:
mkdir -p sheetjs-airtable
cd sheetjs-airtable
npm init -y
-
安装依赖:
¥Install dependencies:
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz airtable
导出数据
¥Exporting Data
-
将以下内容保存到
SheetJSAirtableRead.js
:¥Save the following to
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");
})();
-
将高亮的行中的值替换为 PAT 和工作区名称。
¥Replace the values in the highlighted lines with the PAT and workspace name.
-
运行脚本:
¥Run the script:
node SheetJSAirtableRead.js
脚本将数据从 Airtable 导出到 SheetJSAirtable.xlsb
。新的电子表格可以在 Excel 中打开。
¥The script will export the data from Airtable to SheetJSAirtable.xlsb
. The new
spreadsheet can be opened in Excel.
导入数据
¥Importing Data
-
下载
SheetJSAirpend.xlsx
到项目文件夹:¥Download
SheetJSAirpend.xlsx
to the project folder:
curl -LO https://xlsx.nodejs.cn/airtable/SheetJSAirpend.xlsx
-
将以下内容保存到
SheetJSAirtableWrite.js
:¥Save the following to
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 })));
})();
-
将高亮的行中的值替换为 PAT 和工作区名称。
¥Replace the values in the highlighted lines with the PAT and workspace name.
-
运行脚本:
¥Run the script:
node SheetJSAirtableWrite.js
打开 Airtable 并验证已添加新行:
¥Open Airtable and verify the new row was added:
[^1]: 见 json_to_sheet
于 "实用工具"
¥See json_to_sheet
in "Utilities"
[^2]: 有关 book_new
和 book_append_sheet
的详细信息,请参阅 "工作簿助手" 于 "实用工具"。
¥See "Workbook Helpers" in "Utilities" for details on book_new
and book_append_sheet
.
[^3]: 见 writeFile
于 "写入文件"
¥See writeFile
in "Writing Files"
[^4]: 详情请参阅 "Sheet 对象"
¥See "Sheet Objects" for more details
[^5]: 见 sheet_to_json
于 "实用工具"
¥See sheet_to_json
in "Utilities"
[^6]: 见 "工作簿对象"
¥See "Workbook Object"
[^7]: 见 readFile
于 "读取文件"