Skip to main content

Google Sheets 脚本自动化

该演示重点介绍 Google Apps 脚本自定义函数。

¥This demo focuses on Google Apps Script custom functions.

"谷歌表格" 云数据演示 涵盖用于外部数据处理的 NodeJS API。

¥The "Google Sheets" cloud data demo covers NodeJS APIs for external data processing.

谷歌表格 是一种协作电子表格服务，具有强大的 JavaScript 自动化和用户定义函数。

¥Google Sheets is a collaborative spreadsheet service with powerful JavaScript automation and user-defined functions.

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

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

Google Sheets 目前不支持使用 Apple Numbers 文件和某些旧文件格式。SheetJS 填补了这一空白。

¥Google Sheets currently does not provide support for working with Apple Numbers files and some legacy file formats. SheetJS fills the gap.

完整演示 定义了 SHEETJS 函数，用于获取远程文件、解析内容并将数据写入工作表：

¥The Complete Demo defines a SHEETJS function that fetches a remote file, parses the contents, and writes data to the sheet:

Screenshot of final result

测试部署

该演示在以下部署中进行了测试：

¥This demo was tested in the following deployments:

Clasp日期
2.4.22024-12-31

集成详情​

¥Integration Details

SheetJS 独立脚本 可以上传到 Apps 脚本项目中。上传后，XLSX 变量可供项目中的其他脚本使用。

¥The SheetJS Standalone scripts can be uploaded into an Apps Script project. Once uploaded, the XLSX variable is available to other scripts in the project.

添加脚本​

¥Adding the script

clasp 命令行工具可用于上传独立脚本：

¥The clasp command line tool can be used to upload the standalone script:

npx @google/clasp clone SCRIPT_ID
curl -LO https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js
npx @google/clasp push

上传后，脚本 xlsx.full.min.gs 将添加到项目中。

¥Once uploaded, the script xlsx.full.min.gs will be added to the project.

完整演示 包含更详细的设置说明。

¥The Complete Demo includes more detailed setup instructions.

获取数据​

¥Fetching data

UrlFetchApp.fetch 执行网络请求并返回 HTTPResponse：

¥UrlFetchApp.fetch performs a network request and returns a HTTPResponse:

const response = UrlFetchApp.fetch("https://xlsx.nodejs.cn/pres.numbers");

HTTPResponse#getContent 以有符号字节数组的形式返回文件数据：

¥HTTPResponse#getContent returns the file data as an array of signed bytes:

const content = response.getContent();

SheetJS read 方法 [^1] 可以读取无符号字节数组。幸运的是，数组中的值可以通过按位运算进行更正：

¥The SheetJS read method[^1] can read arrays of unsigned bytes. Fortunately, the values in the array can be corrected with bitwise operations:

for(var i = 0; i < content.length; ++i) content[i] &= 0xFF;

将每个有符号字节转换为无符号字节后，可以使用 read 方法解析数组：

¥After converting each signed byte to unsigned byte, the array can be parsed with the read method:

const wb = XLSX.read(content, { type: "array" });

返回数据​

¥Returning data

带有选项 header: 1 的 SheetJS sheet_to_json 方法 [^2] 返回数据数组的数组 [^3]：

¥The SheetJS sheet_to_json method[^2] with the option header: 1 returns arrays of arrays of data[^3]:

const first_worksheet = wb.Sheets[wb.SheetNames[0]];
const aoa = XLSX.utils.sheet_to_json(first_worksheet, {header: 1});

Google Sheets 会将数组的数组分布在行和列中。下面的 AOA 函数返回一个包含两个数组的数组。屏幕截图显示了将单元格 A1 设置为公式 =AOA() 的结果：

¥Google Sheets will spread arrays of arrays across rows and columns. The AOA function below returns an array that contains two arrays. The screenshot shows the result of setting cell A1 to the formula =AOA():

Custom FunctionGoogle Sheets
function AOA(url) {
  return [
    ["Sheet", "JS"], 
    [     72,   62]
  ];
}

Google Sheets result for AOA function

完整演示​

¥Complete Demo

此演示创建一个函数 SHEETJS(url)，用于获取指定的 URL、从第一个工作表中提取数据并写入数据

¥This demo creates a function SHEETJS(url) that fetches the specified URL, extracts data from the first worksheet, and writes the data

初始设置​

¥Initial Setup

  1. 登录 Google 账户（或创建一个新账户）

    ¥Sign into a Google account (or create a new one)

  2. 在终端窗口中，运行

    ¥In a terminal window, run

npx @google/clasp login

浏览器窗口应定向到账户选择页面。

¥A browser window should direct to an account selection page.

  1. 从步骤 0 中选择账户。

    ¥Select the account from step 0.

下一页将包含以下标题：

¥The next page will include the following title:

登录 clasp – Apps Script CLI

¥Sign in to clasp – The Apps Script CLI

clasp sign-in

  1. 在屏幕底部，单击 "继续"。

    ¥At the bottom of the screen, click "Continue".

  2. 在下一个屏幕中，选中提到 "Google Apps Script" 的每个框。上次测试演示时，需要以下内容：

    ¥In the next screen, check every box that mentions "Google Apps Script". When the demo was last tested, the following were required:

  • 创建和更新 Google Apps Script 部署。

    ¥Create and update Google Apps Script deployments.

  • 创建和更新 Google Apps Script 项目。

    ¥Create and update Google Apps Script projects.

clasp permissions

  1. 滚动到屏幕底部并单击 "继续"。

    ¥Scroll to the bottom of the screen and click "Continue".

浏览器将显示以下消息：

¥The browser will show the following message:

已登录！你可以关闭此页面。

¥Logged in! You may close this page.

终端窗口将显示以下消息：

¥The terminal window will show the following message:

Authorization successful.

创建工作表​

¥Creating a Sheet

  1. 使用同一账户登录 Google Sheets。

    ¥Sign into Google Sheets with the same account.

  2. 创建一个新的空白电子表格。

    ¥Create a new Blank spreadsheet.

  3. 打开应用脚本窗口（扩展 > 应用脚本）

    ¥Open the apps script window (Extensions > Apps Script)

extensions - apps script

  1. 单击齿轮图标（项目设置）并复制脚本 ID

    ¥Click the gear icon (Project Settings) and copy the Script ID

克隆 Apps 脚本​

¥Cloning the Apps Script

  1. 在终端窗口中，为你的项目创建一个新文件夹：

    ¥In the terminal window, create a new folder for your project:

mkdir SheetJSGAS
cd SheetJSGAS

  1. 克隆 Apps 脚本项目。官方命令是：

    ¥Clone the Apps Script project. The official command is:

npx @google/clasp clone PASTE_YOUR_ID_HERE

在终端中键入 npx @google/clasp clone ，并在尾随空格（不要按 Enter 键！），然后从 Apps 脚本设置页面复制脚本 ID 并粘贴到终端中。粘贴 ID 后按 Enter。

¥Type npx @google/clasp clone in the terminal with a trailing space (do not press Enter yet!), then copy the Script ID from the Apps Script settings page and paste in the terminal. Press Enter after pasting the ID.

添加 SheetJS 库​

¥Adding the SheetJS Library

  1. 下载 SheetJS Standalone 脚本并移至项目目录：

    ¥Download the SheetJS Standalone script and move to the project directory:

curl -LO https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js

  1. 将项目推送到 Apps 脚本：

    ¥Push the project to Apps Script:

npx @google/clasp push

如果未启用 Google Apps 脚本 API，该命令将显示带有 code: 403 的对象以及有关 Apps 脚本 API 的错误消息：

¥If the Google Apps Script API is not enabled, the command will display an object with code: 403 and an error message about the Apps Script API:

{ // ...
  code: 403,
  errors: [
    {
      message: 'User has not enabled the Apps Script API. Enable it by ...',
      domain: 'global',
      reason: 'forbidden'
    }
  ]
}

该消息包含一个 URL（上次测试演示时为 https://script.google.com/home/usersettings）。访问该网址。

¥The message includes a URL (https://script.google.com/home/usersettings when the demo was last tested). Visit that URL.

如果 Google Apps 脚本 API 是 "离开"，请单击 "Google Apps 脚本 API"，然后单击滑块以启用该 API。

¥If the Google Apps Script API is "Off", click on "Google Apps Script API" and click on the slider to enable the API.

启用 API 后，再次运行 npx @google/clasp push。

¥After enabling the API, run npx @google/clasp push again.

  1. 重新打开 Google 表格和 Apps 脚本编辑器（扩展 > Apps 脚本）。

    ¥Reopen the Google Sheet and Apps Script editor (Extensions > Apps Script).

在文件列表中，应该有一个新条目 xlsx.full.min.gs

¥In the Files listing, there should be a new entry xlsx.full.min.gs

xlsx.full.min.gs in Apps Script

创建自定义函数​

¥Creating a Custom Function

  1. 在 Apps 脚本编辑器中，选择 Code.gs 并删除编辑器中的代码。替换为以下函数：

    ¥In Apps Script editor, select Code.gs and erase the code in the editor. Replace with the following function:

Code.gs
function SHEETJS(url) {
  /* fetch data */
  const res = UrlFetchApp.fetch(url || "https://xlsx.nodejs.cn/pres.numbers");
  const content = res.getContent();

  /* fix data */
  for(var i = 0; i < content.length; ++i) content[i] &= 0xFF;

  /* parse */
  const wb = XLSX.read(content, {type: "array"});

  /* generate array of arrays from worksheet */
  const ws = wb.Sheets[wb.SheetNames[0]];
  const aoa = XLSX.utils.sheet_to_json(ws, {header: 1});
  return aoa;
}

单击 "将项目保存到 Drive" 图标 (💾) 保存项目。

¥Click the "Save project to Drive" icon (💾) to save the project.

  1. 在 Google 表格窗口中，选择单元格 A1 并输入公式

    ¥In the Google Sheets window, select cell A1 and enter the formula

=SHEETJS("https://xlsx.nodejs.cn/pres.numbers")

将获取文件并将内容写入工作表。

¥The file will be fetched and the contents will be written to the sheet.

[^1]: 详细信息请参见 "输入类型" 于 "读取文件"。

¥See "Input Type" in "Reading Files" for more details.

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

¥See sheet_to_json in "Utilities"

[^3]: 详细信息请参见 "数组的数组" 于 "实用工具"。

¥See "Array of Arrays" in "Utilities" for more details.