Skip to main content

Google Sheets 数据交换

该演示重点关注外部数据处理。对于 Google Apps 脚本自定义函数，"谷歌表格" 扩展演示 涵盖了 Apps 脚本集成。

¥This demo focuses on external data processing. For Google Apps Script custom functions, the "Google Sheets" extension demo covers Apps Script integration.

谷歌表格 是一种协作电子表格服务，具有强大的自动化外部 API。

¥Google Sheets is a collaborative spreadsheet service with powerful external APIs for automation.

SheetJS 是一个用于从电子表格读取和写入数据的 JavaScript 库。

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

该演示使用 SheetJS 与电子表格文件正确交换数据。我们将探讨如何在三个数据流中使用 NodeJS 集成库和 SheetJS：

¥This demo uses SheetJS to properly exchange data with spreadsheet files. We'll explore how to use NodeJS integration libraries and SheetJS in three data flows:

  • "导入数据"：NUMBERS 电子表格中的数据将使用 SheetJS 库进行解析并写入 Google Sheets 文档

    ¥"Importing data": Data in a NUMBERS spreadsheet will be parsed using SheetJS libraries and written to a Google Sheets Document

  • "导出数据"：Google Sheets 中的数据将被拉入对象数组中。工作簿将被组装并导出为 Excel 二进制工作簿 (XLSB)。

    ¥"Exporting data": Data in Google Sheets will be pulled into arrays of objects. A workbook will be assembled and exported to Excel Binary workbooks (XLSB).

  • "导出文件"：SheetJS 库将读取 Google Sheets 导出的 XLSX 文件并从每个工作表生成 CSV 行。

    ¥"Exporting files": SheetJS libraries will read XLSX files exported by Google Sheets and generate CSV rows from every worksheet.

强烈建议创建一个新的 Google 账户进行测试。

¥It is strongly recommended to create a new Google account for testing.

一个小错误可能会导致 Google 服务被屏蔽或禁止。

¥One small mistake could result in a block or ban from Google services.

Google Sheets 很快就会弃用 API，并且无法保证所引用的 API 将来可用。

¥Google Sheets deprecates APIs quickly and there is no guarantee that the referenced APIs will be available in the future.

集成详情​

¥Integration Details

此演示通过官方 googleapis 连接器模块使用 Sheets v4 和 Drive v3 API。

¥This demo uses the Sheets v4 and Drive v3 APIs through the official googleapis connector module.

初始设置

为账户启用 Google Sheets API 和 Google Drive API 需要执行多个步骤。完整示例 涵盖了该过程。

¥There are a number of steps to enable the Google Sheets API and Google Drive API for an account. The Complete Example covers the process.

文档二元性​

¥Document Duality

每个 Google Sheets 文档都用唯一的 ID 标识。可以从 Google Sheets 编辑 URL 中找到此 ID。

¥Each Google Sheets document is identified with a unique ID. This ID can be found from the Google Sheets edit URL.

编辑 URL 以 https://docs.google.com/spreadsheets/d/ 开头并包含 /edit。ID 是斜杠之间的字符串。例如：

¥The edit URL starts with https://docs.google.com/spreadsheets/d/ and includes /edit. The ID is the string of characters between the slashes. For example:

https://docs.google.com/spreadsheets/d/a_long_string_of_characters/edit#gid=0
                                      |^^^^^^^^^^^^^^^^^^^^^^^^^^^|--- ID

Google Drive 操作中使用相同的 ID。

¥The same ID is used in Google Drive operations.

此演示涵盖以下操作：

¥The following operations are covered in this demo:

操作API
创建 Google Sheets 文档Sheets
添加和删除工作表Sheets
修改工作表中的数据Sheets
与其他用户共享表格驾驶
生成原始文件导出驾驶

鉴权​

¥Authentication

强烈建议使用服务账户进行 Google API 操作。"服务账户设置" 节 介绍了如何创建服务账户并生成 JSON 密钥文件。

¥It is strongly recommended to use a service account for Google API operations. The "Service Account Setup" section covers how to create a service account and generate a JSON key file.

生成的 JSON 密钥文件包含 client_email 和 private_key 字段。这些字段可用于 JWT 身份验证：

¥The generated JSON key file includes client_email and private_key fields. These fields can be used in JWT authentication:

JWT Authentication using a JSON key file
import { google } from "googleapis";

// adjust the path to the actual key file.
import creds from './sheetjs-test-726272627262.json' assert { type: "json" };

/* connect to google services */
const jwt = new google.auth.JWT({
  email: creds.client_email,
  key: creds.private_key,
  scopes: [
    'https://www.googleapis.com/auth/spreadsheets', // Google Sheets
    'https://www.googleapis.com/auth/drive.file', // Google Drive
  ]
});

连接到服务​

¥Connecting to Services

google 命名导出包括连接到各种 API 的特殊方法。

¥The google named export includes special methods to connect to various APIs.

谷歌表格​

¥Google Sheets

const sheets = google.sheets({ version: "v4", auth: jwt });

google.sheets 采用包含 API 版本号和身份验证详细信息的选项参数。

¥google.sheets takes an options argument that includes API version number and authentication details.

Google Drive​

const drive = google.drive({ version: "v3", auth: jwt });

google.drive 采用包含 API 版本号和身份验证详细信息的选项参数。

¥google.drive takes an options argument that includes API version number and authentication details.

数组的数组​

¥Array of Arrays

"数组的数组" 是与 Google Sheets 交换的主要数据格式。外部数组对象包含行数组，每个行数组包含数据。

¥"Arrays of Arrays" are the main data format for interchange with Google Sheets. The outer array object includes row arrays, and each row array includes data.

SheetJS 提供了处理数组数组的方法：

¥SheetJS provides methods for working with Arrays of Arrays:

  • aoa_to_sheet[^1] 从数组数组创建 SheetJS 工作表对象

    ¥aoa_to_sheet[^1] creates SheetJS worksheet objects from arrays of arrays

  • sheet_to_json[^2] 可以从 SheetJS 工作表生成数组的数组

    ¥sheet_to_json[^2] can generate arrays of arrays from SheetJS worksheets

导出文档数据​

¥Export Document Data

目标是从 Google 表格创建 XLSB 导出。Google 表格本身不支持 XLSB 格式。SheetJS 填补了这一空白。

¥The goal is to create an XLSB export from a Google Sheet. Google Sheets does not natively support the XLSB format. SheetJS fills the gap.

转换单张表​

¥Convert a Single Sheet

sheets.spreadsheets.values.get 从现有的 Google Sheet 返回数据。该方法需要一个范围。将工作表名称作为标题传递将提取所有行。

¥sheets.spreadsheets.values.get returns data from an existing Google Sheet. The method expects a range. Passing the sheet name as the title will pull all rows.

如果成功，响应对象将具有 data 属性。它将是一个具有 values 属性的对象。这些值将表示为值数组的数组。此数组数组可以转换为 SheetJS 表：

¥If successful, the response object will have a data property. It will be an object with a values property. The values will be represented as an Array of Arrays of values. This array of arrays can be converted to a SheetJS sheet:

async function gsheet_ws_to_sheetjs_ws(id, sheet_name) {
  /* get values */
  const res = await sheets.spreadsheets.values.get({
    spreadsheetId: id,
    range: `'${sheet_name}'`
  });
  const values = res.data.values;

  /* create SheetJS worksheet */
  const ws = XLSX.utils.aoa_to_sheet(values);
  return ws;
}

转换工作簿​

¥Convert a Workbook

sheets.spreadsheets.get 返回有关 Google Sheets 文档的元数据。在结果对象中，data 属性是一个具有 sheets 属性的对象。sheets 属性的值是工作表对象数组。

¥sheets.spreadsheets.get returns metadata about the Google Sheets document. In the result object, the data property is an object which has a sheets property. The value of the sheets property is an array of sheet objects.

SheetJS book_new [^3] 方法创建空白的 SheetJS 工作簿对象。book_append_sheet [^4] 方法将 SheetJS 工作表对象添加到工作簿。

¥The SheetJS book_new [^3] method creates blank SheetJS workbook objects. The book_append_sheet [^4] method adds SheetJS worksheet objects to the workbook.

通过循环遍历工作表，可以转换整个工作簿：

¥By looping across the sheets, the entire workbook can be converted:

async function gsheet_doc_to_sheetjs_wb(doc) {
  /* Create a new workbook object */
  const wb = XLSX.utils.book_new();

  /* Get metadata */
  const wsheet = await sheets.spreadsheets.get({spreadsheetId: id});

  /* Loop across the Document sheets */
  for(let sheet of wsheet.data.sheets) {
    /* Get the worksheet name */
    const name = sheet.properties.title;

    /* Convert Google Docs sheet to SheetJS worksheet */
    const ws = await gsheet_ws_to_sheetjs_ws(id, name);

    /* Append worksheet to workbook */
    XLSX.utils.book_append_sheet(wb, ws, name);
  }

  return wb;
}

此方法返回一个 SheetJS 工作簿对象，可以使用 writeFile 和 write 方法导出。[^5]

¥This method returns a SheetJS workbook object that can be exported with the writeFile and write methods.[^5]

更新文档数据​

¥Update Document Data

目标是将数据从 NUMBERS 文件导入到 Google 表格。Google 表格本身不支持 NUMBERS 格式。SheetJS 填补了这一空白。

¥The goal is to import data from a NUMBERS file to Google Sheets. Google Sheets does not natively support the NUMBERS format. SheetJS fills the gap.

创建新文档​

¥Create New Document

sheets.spreadsheets.create 创建一个新的 Google Sheets 文档。它可以接受文档标题。它将生成一个带有空白 "Sheet1" 表的新工作簿。响应包括文档 ID，用于后续操作：

¥sheets.spreadsheets.create creates a new Google Sheets document. It can accept a document title. It will generate a new workbook with a blank "Sheet1" sheet. The response includes the document ID for use in subsequent operations:

Create a new document with blank sheet
const res = await sheets.spreadsheets.create({
  requestBody: {
    properties: {
      /* Document Title */
      title: "SheetJS Test"
    }
  }
});
const id = res.data.spreadsheetId;

使用服务工作线程时，主账户默认无权访问新文档。必须使用 Drive API 与主账户共享文档：

¥When using a service worker, the main account does not have access to the new document by default. The document has to be shared with the main account using the Drive API:

Sharing the generated sheet with the main account
await drive.permissions.create({
  fileId: id, // this ID was returned in the response to the create request
  fields: "id",
  requestBody: {
    type: "user",
    role: "writer",
    emailAddress: "YOUR_ADDRESS@gmail.com" // main address
  }
});

删除非初始工作表​

¥Delete Non-Initial Sheets

Google 表格不允许用户删除每个工作表。

¥Google Sheets does not allow users to delete every worksheet.

推荐的方法是删除第一个工作表之后的每个工作表。

¥The recommended approach involves deleting every worksheet after the first.

删除操作需要 Google Sheets 文档中工作表的唯一标识符。这些 ID 在 sheets.spreadsheets.get 响应中找到。

¥The delete operation requires a unique identifier for a sheet within the Google Sheets document. These IDs are found in the sheets.spreadsheets.get response.

以下代码片段使用执行一项批量操作 batchUpdate：

¥The following snippet performs one bulk operation using batchUpdate:

Deleting non-initial sheets
/* get existing sheets */
const wsheet = await sheets.spreadsheets.get({spreadsheetId: id});

/* remove all sheets after the first */
if(wsheet.data.sheets.length > 1) await sheets.spreadsheets.batchUpdate({
  spreadsheetId: id,
  requestBody: { requests: wsheet.data.sheets.slice(1).map(s => ({
    deleteSheet: {
      sheetId: s.properties.sheetId
    }
  }))}
});

重命名第一张工作表​

¥Rename First Sheet

必须重命名第一张工作表，以便附加操作不会与旧名称冲突。由于大多数 SheetJS 支持的文件格式和大多数电子表格应用将工作表名称长度限制为 32 个字符，因此设置超过 33 个字符的名称是安全的。

¥The first sheet must be renamed so that the append operations do not collide with the legacy name. Since most SheetJS-supported file formats and most spreadsheet applications limit worksheet name lengths to 32 characters, it is safe to set a name that exceeds 33 characters.

updateSheetProperties 更新方法可以重命名单个工作表：

¥The updateSheetProperties update method can rename individual sheets:

Rename legacy first sheet
/* rename first worksheet to avoid collisions */
await sheets.spreadsheets.batchUpdate({
  spreadsheetId: id,
  requestBody: { requests: [{
    updateSheetProperties: {
      fields: "title",
      properties: {
        sheetId: wsheet.data.sheets[0].properties.sheetId,
         // the new title is 34 characters, to be exact
        title: "thistitleisatleast33characterslong"
      }
    }
  }]}
});

附加工作表​

¥Append Worksheets

read 和 readFile 方法 从现有工作表文件生成 SheetJS 工作簿对象。

¥The read and readFile methods generate SheetJS workbook objects from existing worksheet files.

从 SheetJS 工作簿开始，SheetNames 属性 [^6] 是工作表名称的数组，Sheets 属性是将工作表名称映射到工作表对象的对象。

¥Starting from a SheetJS workbook, the SheetNames property[^6] is an array of worksheet names and the Sheets property is an object that maps sheet names to worksheet objects.

循环遍历工作表名称，附加工作表有两个步骤：

¥Looping over the worksheet names, there are two steps to appending a sheet:

  1. "附加空白工作表"：通过 sheets.spreadsheets.batchUpdate 方法提交的 addSheet 请求接受新标题并创建新工作表。新的工作表将添加到末尾。

    ¥"Append a blank worksheet": The addSheet request, submitted through the sheets.spreadsheets.batchUpdate method, accepts a new title and creates a new worksheet. The new worksheet will be added at the end.

  2. "将数据写入新表"：带有选项 header: 1[^7] 的 SheetJS sheet_to_json 方法将生成一个数据数组数组。此结构与 sheets.spreadsheets.values.update 操作兼容。

    ¥"Write data to the new sheet": The SheetJS sheet_to_json method with the option header: 1[^7] will generate an array of arrays of data. This structure is compatible with the sheets.spreadsheets.values.update operation.

以下代码片段将 SheetJS 工作簿中的所有工作表推送到 Google Sheets 文档中：

¥The following snippet pushes all worksheets from a SheetJS workbook into a Google Sheets document:

Push data from a SheetJS workbook to a Google Sheets Document
/* add sheets from file */
for(let name of wb.SheetNames) {
  /* (1) Create a new Google Sheets sheet */
  await sheets.spreadsheets.batchUpdate({
    spreadsheetId: id,
    requestBody: { requests: [
      /* add new sheet */
      { addSheet: { properties: { title: name } } },
    ] }
  });

  /* (2) Push data */
  const aoa = XLSX.utils.sheet_to_json(wb.Sheets[name], {header:1});
  await sheets.spreadsheets.values.update({
    spreadsheetId: id,
    range: `'${name}'!A1`,
    valueInputOption: "USER_ENTERED",
    resource: { values: aoa }
  });
}

删除初始工作表​

¥Delete Initial Sheet

添加新工作表后，最后一步是删除初始工作表。

¥After adding new worksheets, the final step involves removing the initial sheet.

初始工作表 ID 可以从 非初始工作表已被删除 时获取的工作表元数据中提取：

¥The initial sheet ID can be pulled from the worksheet metadata fetched when the non-initial sheets were removed:

Deleting the Initial sheet
/* remove first sheet */
await sheets.spreadsheets.batchUpdate({
  spreadsheetId: id,
  requestBody: { requests: [
    /* remove old first sheet */
    { deleteSheet: { sheetId: wsheet.data.sheets[0].properties.sheetId } }
  ] }
});

原始文件导出​

¥Raw File Exports

在网络界面中，Google 表格可以将文档导出到 XLSX 或 ODS。

¥In the web interface, Google Sheets can export documents to XLSX or ODS.

原始文件导出通过 Drive API 中的 files.export 方法公开：

¥Raw file exports are exposed through the files.export method in the Drive API:

Export XLSX workbook
const drive = google.drive({ version: "v3", auth: jwt });

/* Request XLSX export */
const file = await drive.files.export({
  /* XLSX MIME type */
  mimeType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
  fileId: id
});

mimeType 属性应为受支持的格式 [^8] 之一。上次测试演示时，支持以下工作簿转换：

¥The mimeType property is expected to be one of the supported formats[^8]. When the demo was last tested, the following workbook conversions were supported:

格式MIME 类型
XLSXapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet
ODSapplication/x-vnd.oasis.opendocument.spreadsheet

响应对象有一个 data 字段，其值将是 Blob 对象。数据可以拉入 ArrayBuffer 并传递给 SheetJS read[^9] 方法：

¥The response object has a data field whose value will be a Blob object. Data can be pulled into an ArrayBuffer and passed to the SheetJS read[^9] method:

/* Obtain ArrayBuffer */
const ab = await file.data.arrayBuffer();

/* Parse */
const wb = XLSX.read(buf);

代码片段适用于 XLSX 和 ODS。Google Sheets 支持具有不同集成逻辑的其他格式。

¥The code snippet works for XLSX and ODS. Google Sheets supports other formats with different integration logic.

纯文本

¥Plaintext

以下格式被视为 "plaintext"：

¥The following formats are considered "plaintext":

格式MIME 类型
CSV（第一张表）text/csv
TSV（第一张表）text/tab-separated-values

对于这些格式，file.data 是一个可以直接解析的 JS 字符串：

¥For these formats, file.data is a JS string that can be parsed directly:

/* Request CSV export */
const file = await drive.files.export({ mimeType: "text/csv", fileId: id });

/* Parse CSV string*/
const wb = XLSX.read(file.data, {type: "string"});

HTML

Google Sheets 有一种相关的 HTML 类型：

¥Google Sheets has one relevant HTML type:

格式MIME 类型
HTML（所有工作表）application/zip

Google Sheets 工作表的 HTML 导出包括一行列标签（A、B、...）和一列行标签（1、2、...）。

¥The HTML export of a Google Sheets worksheet includes a row for the column labels (A, B, ...) and a column for the row labels (1, 2, ...).

完整的包是一个包含一系列 .html 文件的 ZIP 文件。文件按制表符顺序写入。每个文件的名称与 Google Sheets 中的名称匹配。

¥The complete package is a ZIP file that includes a series of .html files. The files are written in tab order. The name of each file matches the name in Google Sheets.

可以使用嵌入式 CFB 库提取此 ZIP：

¥This ZIP can be extracted using the embedded CFB library:

import { read, utils, CFB } from 'xlsx';
// -------------------^^^-- `CFB` named import

// ...

/* Parse Google Sheets ZIP file */
const cfb = CFB.read(new Uint8Array(ab), {type: "array"});

/* Create new SheetJS workbook */
const wb = utils.book_new();

/* Scan through each entry in the ZIP */
cfb.FullPaths.forEach((n, i) => {
  /* only process HTML files */
  if(n.slice(-5) != ".html") return;

  /* Extract worksheet name */
  const name = n.slice(n.lastIndexOf("/")+1).slice(0,-5);

  /* parse HTML */
  const htmlwb = read(cfb.FileIndex[i].content);

  /* add worksheet to workbook */
  utils.book_append_sheet(wb, htmlwb.Sheets.Sheet1, name);
});

此时 wb 就是一个 SheetJS 工作簿对象 [^10]。

¥At this point wb is a SheetJS workbook object[^10].

完整示例​

¥Complete Example

测试部署

此演示最后一次于 2024 年 6 月 8 日使用 googleapis 版本 140.0.0 进行测试。演示使用 Sheets v4 和 Drive v3 API。

¥This demo was last tested on 2024 June 08 using googleapis version 140.0.0. The demo uses Sheets v4 and Drive v3 APIs.

Google Cloud Web 界面经常更改！

¥The Google Cloud web interface changes frequently!

屏幕截图和详细描述可能已过时。请报告任何问题 到文档存储库 或联系 SheetJS Discord 服务器。

¥The screenshots and detailed descriptions may be out of date. Please report any issues to the docs repo or reach out to the SheetJS Discord server.

账户设置​

¥Account Setup

  1. 创建新的 Google 账户或登录现有账户。

    ¥Create a new Google account or log into an existing account.

可能需要有效的调用号码（用于短信验证）。

¥A valid phone number (for SMS verification) may be required.

  1. 在网络浏览器中打开 https://console.cloud.google.com。

    ¥Open https://console.cloud.google.com in a web browser.

如果这是第一次访问 Google Cloud 资源，则会显示服务条款模式。点击 "Google Cloud Platform 服务条款" 链接查看 Google Cloud Platform 服务条款。

¥If this is the first time accessing Google Cloud resources, a terms of service modal will be displayed. Review the Google Cloud Platform Terms of Service by clicking the "Google Cloud Platform Terms of Service" link.

你必须同意 Google Cloud Platform 服务条款才能使用 API。

¥You must agree to the Google Cloud Platform Terms of Service to use the APIs.

选中 "服务条款" 下的复选框并单击 "AGREE AND CONTINUE"。

¥Check the box under "Terms of Service" and click "AGREE AND CONTINUE".

项目设置​

¥Project Setup

本节的目标是创建一个新项目。

¥The goal of this section is to create a new project.

  1. 打开项目选择器。

    ¥Open the Project Selector.

在顶部栏中，在 "Google Cloud" 徽标和搜索栏之间，将有一个选择框。单击 ▼ 图标显示模式。

¥In the top bar, between the "Google Cloud" logo and the search bar, there will be a selection box. Click the ▼ icon to show the modal.

Project Selector

如果缺少选择框，请展开浏览器窗口。

¥If the selection box is missing, expand the browser window.

  1. 单击模式右上角的 "新项目"。

    ¥Click "NEW PROJECT" in the top right corner of the modal.

  2. 在“新建项目”屏幕中，在“项目名称”文本框中输入 "SheetJS 测试"，然后在“位置”框中选择 "无组织"。单击 "CREATE"。

    ¥In the New Project screen, enter "SheetJS Test" in the Project name textbox and select "No organization" in the Location box. Click "CREATE".

通知将确认项目已创建：

¥A notification will confirm that the project was created:

Project notification

API 设置​

¥API Setup

本部分的目标是启用 Google Sheets API 和 Google Drive API。

¥The goal of this section is to enable Google Sheets API and Google Drive API.

  1. 打开项目选择器（▼ 图标）并选择 "SheetJS 测试"

    ¥Open the Project Selector (▼ icon) and select "SheetJS Test"

  2. 在搜索栏中，输入 "已启用" 并选择 "已启用 API 和服务"。此项将位于搜索结果的 "PRODUCTS & PAGES" 部分。

    ¥In the search bar, type "Enabled" and select "Enabled APIs & services". This item will be in the "PRODUCTS & PAGES" part of the search results.

启用 Google Sheets API​

¥Enable Google Sheets API

  1. 在页面顶部附近，单击 "* ENABLE APIS AND SERVICES"。

    ¥Near the top of the page, click "+ ENABLE APIS AND SERVICES".

  2. 在页面中间附近的搜索栏中（不是顶部的搜索栏），输入 "Sheets" 并按 Enter。

    ¥In the search bar near the middle of the page (not the search bar at the top), type "Sheets" and press Enter.

在结果页面中，查找 "谷歌表格 API"。点击卡片

¥In the results page, look for "Google Sheets API". Click the card

  1. 在“产品详细信息”屏幕中，单击蓝色的 "ENABLE" 按钮。

    ¥In the Product Details screen, click the blue "ENABLE" button.

  2. 单击 "API/服务详细信息" 旁边的左箭头（<-）。

    ¥Click the left arrow (<-) next to "API/Service details".

启用 Google Drive API​

¥Enable Google Drive API

  1. 在页面顶部附近，单击 "* ENABLE APIS AND SERVICES"。

    ¥Near the top of the page, click "+ ENABLE APIS AND SERVICES".

  2. 在页面中间附近的搜索栏中（不是顶部的搜索栏），输入 "驾驶" 并按 Enter。

    ¥In the search bar near the middle of the page (not the search bar at the top), type "Drive" and press Enter.

在结果页面中，查找 "谷歌云端硬盘 API"。点击卡片

¥In the results page, look for "Google Drive API". Click the card

  1. 在“产品详细信息”屏幕中，单击蓝色的 "ENABLE" 按钮。

    ¥In the Product Details screen, click the blue "ENABLE" button.

服务账户设置​

¥Service Account Setup

本部分的目标是创建服务账户并生成 JSON 密钥。

¥The goal of this section is to create a service account and generate a JSON key.

  1. 转到 https://console.cloud.google.com 或单击顶部栏中的 "Google Cloud" 图片。

    ¥Go to https://console.cloud.google.com or click the "Google Cloud" image in the top bar.

创建服务账户​

¥Create Service Account

  1. 单击项目选择器（:· 图标）并选择 "SheetJS 测试"。

    ¥Click the Project Selector (:· icon) and select "SheetJS Test".

  2. 在搜索栏中，输入 "证书" 并选择带有副标题 "API 和服务" 的 "证书" 项目。此项将位于 "PRODUCTS & PAGES" 组中：

    ¥In the search bar, type "Credentials" and select the "Credentials" item with subtitle "APIs & Services". This item will be in the "PRODUCTS & PAGES" group:

Credentials

  1. 单击 "* CREATE CREDENTIALS"。在下拉列表中，选择 "服务帐号"

    ¥Click "+ CREATE CREDENTIALS". In the dropdown, select "Service Account"

  2. 输入 "SheetJService" 作为服务账户名称。点击 "创建并继续"

    ¥Enter "SheetJService" for Service account name. Click "CREATE AND CONTINUE"

服务账户 ID 是自动生成的。

¥The Service account ID is generated automatically.

  1. 在步骤 2 "授予此服务账户对项目的访问权限" 中，单击继续

    ¥In Step 2 "Grant this service account access to project", click CONTINUE

  2. 在步骤 3 中单击 "DONE"。你将返回到凭据屏幕

    ¥In Step 3 click "DONE". You will be taken back to the credentials screen

创建 JSON 键​

¥Create JSON Key

  1. 在 "服务账户" 表中查找 "SheetJService"，然后单击行中的电子邮件地址。

    ¥Look for "SheetJService" in the "Service Accounts" table and click the email address in the row.

  2. 单击页面顶部附近水平栏中的 "KEYS"。

    ¥Click "KEYS" in the horizontal bar near the top of the page.

  3. 单击 "添加密钥" 并在下拉列表中选择 "创建新密钥"。

    ¥Click "ADD KEY" and select "Create new key" in the dropdown.

  4. 在弹出窗口中，选择 "JSON" 单选按钮并单击 "CREATE"。

    ¥In the popup, select the "JSON" radio button and click "CREATE".

该页面将下载一个 JSON 文件。如果出现提示，请允许下载。

¥The page will download a JSON file. If prompted, allow the download.

  1. 点击 "CLOSE"

    ¥Click "CLOSE"

创建文档​

¥Create Document

本部分的目标是从服务账户创建文档并与主账户共享。

¥The goal of this section is to create a document from the service account and share with the main account.

  1. 创建 SheetJSGS 文件夹并初始化：

    ¥Create a SheetJSGS folder and initialize:

mkdir SheetJSGS
cd SheetJSGS
npm init -y

  1. 将步骤 24 中的 JSON 文件复制到项目文件夹中。

    ¥Copy the JSON file from step 24 into the project folder.

  2. 安装依赖：

    ¥Install dependencies:

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

  1. 下载 init.mjs：

    ¥Download init.mjs:

curl -LO https://xlsx.nodejs.cn/gsheet/init.mjs

编辑文件顶部附近的标记行：

¥Edit the marked lines near the top of the file:

init.mjs (edit highlighted lines)
/* Change this import statement to point to the credentials JSON file */
import creds from './sheetjs-test-726272627262.json' assert { type: "json" };

/* Change this to the primary account address, NOT THE SERVICE ACCOUNT */
const acct = "YOUR_ADDRESS@gmail.com";

  • './sheetjs-test-726272627262.json' 应替换为步骤 27 中 JSON 文件的名称。需要 ./ 前缀！

    ¥'./sheetjs-test-726272627262.json' should be replaced with the name of the JSON file in step 27. The ./ prefix is required!

  • 'YOUR_ADDRESS@gmail.com' 应替换为第 0 步中的 Google 账户电子邮件地址。

    ¥'YOUR_ADDRESS@gmail.com' should be replaced with the Google Account email address from step 0.

  1. 运行脚本：

    ¥Run the script:

node init.mjs

脚本将打印三行：

¥The script will print three lines:

Created Google Workbook a-long-string-of-characters
Created Google Worksheets "SheetJS1" and "SheetJS2"
Shared a-long-string-of-characters with YOUR_ACCOUNT@gmail.com

"创建 Google Workbook" 后面的长字符串是 ID。记下此 ID。

¥The long string of characters after "Created Google Workbook" is the ID. Take note of this ID.

  1. 登录 Google 表格。表中应显示共享文档 "SheetJS 测试"。它将由服务账户拥有。

    ¥Sign into Google Sheets. A shared document "SheetJS Test" should be displayed in the table. It will be owned by the service account.

  2. 打开第 31 步中的共享文档并确认该文档有两个名为 "SheetJS1" 和 "SheetJS2" 的工作表。

    ¥Open the shared document from step 31 and confirm that the document has two worksheets named "SheetJS1" and "SheetJS2".

确认工作表数据与以下屏幕截图匹配：

¥Confirm the worksheet data matches the following screenshots:

SheetDataScreenshot
SheetJS1
[
  [ "Sheet", "JS" ],
  [ 72, 62 ]
]

SheetJS1 data

SheetJS2
[
  [ "Area Code", "Part 1", "Part 2" ],
  [ 201, 867, 5309 ],
  [ 281, 330, 8004 ]
]

SheetJS2 data

  1. 复制 URL 并提取文档 ID。

    ¥Copy the URL and extract the document ID.

文档的 URL 看起来像

¥The URL of the document will look like

https://docs.google.com/spreadsheets/d/a_long_string_of_characters/edit#gid=0
---------------------------------------^^^^^^^^^^^^^^^^^^^^^^^^^^^--- ID

ID 是一长串由字母、数字和下划线字符 (_) 组成的字符串，位于 URL 的 /edit 部分之前。

¥The ID is a long string of letters and numbers and underscore characters (_) just before the /edit part of the URL.

确认此 ID 与步骤 30 中打印的 ID 匹配。

¥Confirm that this ID matches the ID printed in step 30.

从 NUMBERS 加载数据​

¥Load Data from NUMBERS

本部分的目标是使用示例 NUMBERS 文件中的数据更新新文档。

¥The goal of this section is to update the new document with data from a sample NUMBERS file.

  1. 下载 测试文件 pres.numbers：

    ¥Download the test file pres.numbers:

curl -LO https://xlsx.nodejs.cn/pres.numbers

  1. 下载 load.mjs：

    ¥Download load.mjs:

curl -LO https://xlsx.nodejs.cn/gsheet/load.mjs

编辑文件顶部附近的标记行：

¥Edit the marked lines near the top of the file:

load.mjs (edit highlighted lines)
/* Change this import statement to point to the credentials JSON file */
import creds from './sheetjs-test-726272627262.json' assert { type: "json" };

/* Change this to the spreadsheet ID */
const id = "SOME-SPREADSHEETJS-ID";

  • './sheetjs-test-726272627262.json' 应替换为步骤 27 中 JSON 文件的名称。需要 ./ 前缀！

    ¥'./sheetjs-test-726272627262.json' should be replaced with the name of the JSON file in step 27. The ./ prefix is required!

  • "SOME-SPREADSHEETJS-ID" 应替换为步骤 33 中的文档 ID。

    ¥"SOME-SPREADSHEETJS-ID" should be replaced with the Document ID from step 33.

  1. 运行脚本：

    ¥Run the script:

node load.mjs

  1. 登录 Google 表格并打开 "SheetJS 测试" 共享文档。它应该显示与测试文件的内容相匹配的总统列表。

    ¥Sign into Google Sheets and open the "SheetJS Test" shared document. It should show a list of Presidents, matching the contents of the test file.

将数据导出到 XLSB​

¥Export Data to XLSB

本部分的目标是将原始数据从 Google Sheets 导出到 XLSB。

¥The goal of this section is to export the raw data from Google Sheets to XLSB.

  1. 下载 dump.mjs：

    ¥Download dump.mjs:

curl -LO https://xlsx.nodejs.cn/gsheet/dump.mjs

编辑文件顶部附近的标记行：

¥Edit the marked lines near the top of the file:

dump.mjs (edit highlighted lines)
/* Change this import statement to point to the credentials JSON file */
import creds from './sheetjs-test-726272627262.json' assert { type: "json" };

/* Change this to the spreadsheet ID */
const id = "SOME-SPREADSHEETJS-ID";

  • './sheetjs-test-726272627262.json' 应替换为步骤 27 中 JSON 文件的名称。需要 ./ 前缀！

    ¥'./sheetjs-test-726272627262.json' should be replaced with the name of the JSON file in step 27. The ./ prefix is required!

  • "SOME-SPREADSHEETJS-ID" 应替换为步骤 33 中的文档 ID。

    ¥"SOME-SPREADSHEETJS-ID" should be replaced with the Document ID from step 33.

  1. 运行脚本：

    ¥Run the script:

node dump.mjs

该脚本应在项目文件夹中创建一个文件 SheetJSExport.xlsb。此文件可以在 Excel 中打开。

¥The script should create a file SheetJSExport.xlsb in the project folder. This file can be opened in Excel.

导出原始文件​

¥Export Raw Files

本部分的目标是解析 Google Sheets XLSX 导出并为每个工作表生成 CSV 文件。

¥The goal of this section is to parse the Google Sheets XLSX export and generate CSV files for each worksheet.

  1. 登录 Google 表格并打开 "SheetJS 测试" 共享文档。

    ¥Sign into Google Sheets and open the "SheetJS Test" shared document.

  2. 单击左下角的加号 (+) 图标以创建新工作表。

    ¥Click the Plus (+) icon in the lower left corner to create a new worksheet.

  3. 在新工作表中，将单元格 A1 设置为公式 =SEQUENCE(3,5)。这将分配一个值网格

    ¥In the new worksheet, set cell A1 to the formula =SEQUENCE(3,5). This will assign a grid of values

  4. 下载 raw.mjs：

    ¥Download raw.mjs:

curl -LO https://xlsx.nodejs.cn/gsheet/raw.mjs

编辑文件顶部附近的标记行：

¥Edit the marked lines near the top of the file:

raw.mjs (edit highlighted lines)
/* Change this import statement to point to the credentials JSON file */
import creds from './sheetjs-test-726272627262.json' assert { type: "json" };

/* Change this to the spreadsheet ID */
const id = "SOME-SPREADSHEETJS-ID";

  • './sheetjs-test-726272627262.json' 应替换为步骤 27 中 JSON 文件的名称。需要 ./ 前缀！

    ¥'./sheetjs-test-726272627262.json' should be replaced with the name of the JSON file in step 27. The ./ prefix is required!

  • "SOME-SPREADSHEETJS-ID" 应替换为步骤 33 中的文档 ID。

    ¥"SOME-SPREADSHEETJS-ID" should be replaced with the Document ID from step 33.

  1. 运行脚本：

    ¥Run the script:

node raw.mjs

脚本将显示两个工作表的工作表名称和 CSV 行：

¥The script will display the sheet names and CSV rows from both worksheets:

#### Sheet1
Name,Index
Bill Clinton,42
GeorgeW Bush,43
Barack Obama,44
Donald Trump,45
Joseph Biden,46

#### Sheet14
1,2,3,4,5
6,7,8,9,10
11,12,13,14,15

[^1]: 见 aoa_to_sheet 于 "实用工具"

¥See aoa_to_sheet in "Utilities"

[^2]: 见 sheet_to_json 于 "实用工具"

¥See sheet_to_json in "Utilities"

[^3]: 见 book_new 于 "实用工具"

¥See book_new in "Utilities"

[^4]: 见 book_append_sheet 于 "实用工具"

¥See book_append_sheet in "Utilities"

[^5]: 见 writeFile 于 "写入文件"

¥See writeFile in "Writing Files"

[^6]: 详细信息请参见 "工作簿对象"。

¥See "Workbook Object" for more details.

[^7]: 见 sheet_to_json 于 "实用工具"

¥See sheet_to_json in "Utilities"

[^8]: 有关受支持的文件类型的完整列表，请参阅 Google 开发者文档中的 "导出 Google Workspace 文档的 MIME 类型"。

¥See "Export MIME types for Google Workspace documents" in the Google Developer documentation for the complete list of supported file types.

[^9]: 见 read 于 "读取文件"

¥See read in "Reading Files"

[^10]: 有关工作簿对象的说明，请参阅 "工作簿对象"；有关使用工作簿和工作表对象的各种方法，请参阅 "API 参考"。

¥See "Workbook Object" for a description of the workbook object or "API Reference" for various methods to work with workbook and sheet objects.