Skip to main content

Google Sheets 脚本自动化

该演示重点介绍 Google Apps 脚本自定义函数。对于外部数据处理，"谷歌表格" 云数据演示 涵盖了 NodeJS 脚本的 API

¥This demo focuses on Google Apps Script custom functions. For external data processing, the "Google Sheets" cloud data demo covers the API for NodeJS scripts

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.

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

测试部署

该演示的上次测试时间为 2024 年 3 月 11 日。

¥This demo was last tested on 2024 March 11.

集成详情​

¥Integration Details

添加脚本​

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

XLSX.read 的 "array" 类型需要一个无符号字节数组。幸运的是，可以通过按位运算来纠正内容：

¥The "array" type for XLSX.read expects an array of unsigned bytes. Fortunately, the content can be corrected with bitwise operations:

for(var i = 0; i < content.length; ++i) content[i] &= 0xFF;
const wb = XLSX.read(content, { type: "array" });

返回数据​

¥Returning data

带有选项 header: 1 的 XLSX.utils.sheet_to_json 返回与 Google Sheets 配合良好的数组数组：

¥XLSX.utils.sheet_to_json with the option header: 1 returns arrays of arrays that play nice with Google Sheets:

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

完整演示​

¥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. Select the account from the previous step. In the next page, there will be a title like

clasp – Apps 脚本 CLI 想要访问你的 Google 账户

¥clasp – The Apps Script CLI wants to access your Google Account

在屏幕底部，单击 "允许"。

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

终端窗口现在应该显示

¥The terminal window should now state

Authorization successful.

创建工作表​

¥Creating a Sheet

  1. 使用同一账户登录 Google 表格并创建新的空白表格

    ¥Sign into Google Sheets with the same account and create a new blank sheet

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

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

  3. 单击齿轮图标（项目设置）并复制脚本 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

创建自定义函数​

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

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

¥Click the "Save Project" 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.