Airtable

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

测试部署

该演示的上次测试时间为 2024 年 5 月 4 日。当时，免费账户包含有限的 API 访问权限。

¥This demo was last tested on 2024 May 04. At the time, free accounts included limited API access.

0. 创建一个免费的 Airtable 账户并验证电子邮件地址。

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

个人访问令牌

¥Personal Access Token

过去，Airtable 提供 API 密钥。它们于 2024 年 2 月 1 日正式弃用。他们推荐 "个人访问令牌" 进行操作。

¥In the past, Airtable offered API keys. They were officially deprecated on 2024 February 1. They recommend "Personal Access Tokens" for operations.

API 操作需要 PAT，该 PAT 必须通过开发者中心创建：

¥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".

在尝试创建令牌之前，必须验证与账户关联的电子邮件地址。

¥The email address associated with the account must be verified before attempting to create a token.

2. 单击蓝色的 "创建令牌" 按钮。

   ¥Click the blue "Create Token" button.

3. 在表单中，进行以下选择：

   ¥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 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 following screenshot:

4. 单击 "创建令牌"，你将看到一个弹出窗口。复制令牌并保存。

   ¥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:

5. 下载 https://xlsx.nodejs.cn/pres.xlsx

   ¥Download https://xlsx.nodejs.cn/pres.xlsx

6. 点击 "返回首页" 返回主页。

   ¥Click "Back to Home" to return to the home page.

7. 使用 "快速上传" 在 Airtable 中创建一个项目。选择 "微软 Excel"，然后选择步骤 1 中下载的文件。单击 "上传"，然后单击 "导入"。

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

8. 将创建一个工作区。该名称将在 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

9. 创建一个新项目：

   ¥Create a new project:

mkdir -p sheetjs-airtable
cd sheetjs-airtable
npm init -y

10. 安装依赖：

    ¥Install dependencies:

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

导出数据

¥Exporting Data

11. 将以下内容保存到 SheetJSAirtableRead.js：

    ¥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");
})();

12. 将高亮的行中的值替换为 PAT 和工作区名称。

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

13. 运行脚本：

    ¥Run the script:

node SheetJSAirtableRead.js

脚本应该写 SheetJSAirtable.xlsb。该文件可以在 Excel 中打开。

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

导入数据

¥Importing Data

14. 下载 SheetJSAirpend.xlsx 到项目文件夹：

    ¥Download SheetJSAirpend.xlsx to the project folder:

curl -LO https://xlsx.nodejs.cn/airtable/SheetJSAirpend.xlsx

15. 将以下内容保存到 SheetJSAirtableWrite.js：

    ¥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 })));
})();

16. 将高亮的行中的值替换为 PAT 和工作区名称。

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

17. 运行脚本：

    ¥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 于 "读取文件"

¥See readFile in "Reading Files"