使用Node.js通过googleapis查询Google表格数据-js教程-PHP中文网

使用Node.js通过googleapis查询Google表格数据

碧海醫心

发布： 2025-10-03 13:45:01

原创

590人浏览过

使用node.js通过googleapis查询google表格数据

本文探讨了在使用Node.js的googleapis库时，直接通过Sheets API执行SQL-like查询的局限性。针对这一挑战，文章提供了一种有效的解决方案：利用Google Visualization API的gviz/tq端点。通过服务账户获取访问令牌，并结合request库向gviz/tq端点发送带认证的HTTP请求，即可实现对Google表格数据的灵活查询，并对返回的CSV数据进行解析处理。

Google Sheets API查询的局限性

在使用Node.js的googleapis包与Google Sheets API交互时，开发者通常会使用sheets.spreadsheets.values.batchGet等方法来获取数据。这些方法允许按范围（ranges）获取数据，但它们不直接支持SQL-like的查询语句，例如select * where B = '9831'。当需要根据特定条件筛选大量数据时，如果每次都获取所有数据并在客户端进行过滤，效率会非常低下，尤其是在处理包含数千行甚至更多数据的表格时。

虽然Google Charts提供了一套查询语言，但其直接集成并非通过标准的Sheets API客户端库接口实现，而是需要通过特定的端点进行访问。

解决方案：利用Google Visualization API的gviz/tq端点

要实现对Google表格的SQL-like查询，我们需要绕过googleapis库中Sheets API的直接限制，转而使用Google Visualization API提供的gviz/tq端点。这个端点允许我们传入查询语言（Query Language）语句，并返回查询结果。

核心思路是：

认证：使用服务账户（Service Account）获取有效的访问令牌（Access Token）。
构建请求URL：构造一个指向gviz/tq端点的URL，其中包含表格ID、工作表ID以及经过编码的查询语句。
发送HTTP请求：使用HTTP客户端库（如request或axios）发送带有访问令牌的GET请求到该URL。
解析响应：gviz/tq端点通常以CSV或其他格式返回数据，需要对响应进行解析以获取结构化的数据。

详细实现步骤

1. 服务账户认证与获取访问令牌

首先，确保你已经配置了Google服务账户凭据，并且该账户拥有访问目标Google表格的权限。googleapis库可以方便地帮助我们进行认证并获取访问令牌。

const { google } = require("googleapis");
const request = require("request"); // 或者使用更现代的axios

// 假设 googleCredentials 已经通过环境变量或配置文件加载
const googleCredentials = require('./path/to/your/service-account-key.json'); 

const auth = new google.auth.GoogleAuth({
  credentials: googleCredentials,
  scopes: "https://www.googleapis.com/auth/spreadsheets", // 访问Google表格的权限
});

// 获取访问令牌
async function getAccessToken() {
  try {
    const accessToken = await auth.getAccessToken();
    return accessToken.token; // 返回令牌字符串
  } catch (err) {
    console.error("获取访问令牌失败:", err);
    throw err;
  }
}

登录后复制

2. 构建查询URL

gviz/tq端点的URL结构如下：

https://docs.google.com/spreadsheets/d/{spreadsheetId}/gviz/tq?tqx=out:csv&gid={sheetId}&tq={encodedQuery}

{spreadsheetId}：你的Google表格的ID。
tqx=out:csv：指定输出格式为CSV。你也可以选择out:json等。
gid={sheetId}：工作表的ID。这个ID可以在Google表格的URL中找到，例如https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit#gid=YOUR_SHEET_ID。
tq={encodedQuery}：经过URL编码的查询语句。

注意事项：查询语句的语法遵循Google Visualization API Query Language。例如，字符串值需要用单引号括起来，如'9831'。

const spreadsheetId = "YOUR_SPREADSHEET_ID"; // 替换为你的表格ID
const sheetId = "YOUR_SHEET_GID"; // 替换为你的工作表GID
const query = "select * where B='9831'"; // 你的查询语句

const encodedQuery = encodeURIComponent(query);
const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?tqx=out:csv&gid=${sheetId}&tq=${encodedQuery}`;

登录后复制

3. 发送带认证的HTTP请求

获取到访问令牌和构建好URL后，即可使用request库发送GET请求。请求头中需要包含Authorization: Bearer YOUR_ACCESS_TOKEN。

async function queryGoogleSheet() {
  try {
    const accessToken = await getAccessToken();
    const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?tqx=out:csv&gid=${sheetId}&tq=${encodedQuery}`;

    request(
      {
        url: url,
        method: "GET",
        headers: { authorization: `Bearer ${accessToken}` },
      },
      (err, res, result) => {
        if (err) {
          console.error("请求失败:", err);
          return;
        }
        if (result) {
          // 处理CSV结果
          console.log("原始CSV结果:\n", result);
          parseCsvResult(result);
        } else {
          console.log("未获取到结果。");
        }
      }
    );
  } catch (err) {
    console.error("查询过程中发生错误:", err);
  }
}

登录后复制

4. 解析CSV响应数据

由于gviz/tq端点返回的是CSV格式的数据，我们需要对其进行解析。一个简单的解析函数可以处理基本的CSV格式，但对于复杂的CSV（如包含逗号的字段），建议使用专门的CSV解析库（如csv-parse）。

以下是一个简单的解析示例：

function parseCsvResult(csvString) {
  if (!csvString) {
    console.log("CSV字符串为空。");
    return [];
  }

  // 移除首行（通常是标题行，如果不需要）
  const [, ...dataRows] = csvString.split("\n");

  const parsedData = dataRows.map((row) =>
    row.split(",").map((cell) => {
      // 移除双引号，并尝试转换为数字
      const trimmedCell = cell.replace(/"/g, "");
      return isNaN(trimmedCell) ? trimmedCell : Number(trimmedCell);
    })
  );
  console.log("解析后的数据:", parsedData);
  return parsedData;
}

登录后复制

完整示例代码

将上述片段整合，形成一个完整的查询脚本：

const { google } = require("googleapis");
const request = require("request");

// 替换为你的服务账户凭据路径
const googleCredentials = require('./path/to/your/service-account-key.json'); 

const auth = new google.auth.GoogleAuth({
  credentials: googleCredentials,
  scopes: "https://www.googleapis.com/auth/spreadsheets",
});

const spreadsheetId = "1_YOUR_SPREADSHEET_ID_HERE"; // 替换为你的Google表格ID
const sheetId = "0"; // 替换为你的工作表GID (通常第一个工作表GID是0)
const query = "select * where B='9831'"; // 你的查询语句，例如查询B列值为'9831'的行

async function queryGoogleSheetWithVisualizationAPI() {
  try {
    const accessTokenResponse = await auth.getAccessToken();
    const accessToken = accessTokenResponse.token;

    if (!accessToken) {
      console.error("未能获取到访问令牌。");
      return;
    }

    const encodedQuery = encodeURIComponent(query);
    const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?tqx=out:csv&gid=${sheetId}&tq=${encodedQuery}`;

    console.log("正在发送查询请求到:", url);

    request(
      {
        url: url,
        method: "GET",
        headers: { authorization: `Bearer ${accessToken}` },
      },
      (err, res, result) => {
        if (err) {
          console.error("请求失败:", err);
          return;
        }
        if (result) {
          console.log("原始CSV结果:\n", result);

          // 简单的CSV解析
          const [, ...dataRows] = result.split("\n");
          const parsedData = dataRows.map((row) =>
            row.split(",").map((cell) => {
              const trimmedCell = cell.replace(/"/g, "");
              return isNaN(trimmedCell) ? trimmedCell : Number(trimmedCell);
            })
          );
          console.log("解析后的数据:", parsedData);
        } else {
          console.log("未获取到任何结果。");
        }
      }
    );
  } catch (err) {
    console.error("查询过程中发生错误:", err);
  }
}

// 执行查询
queryGoogleSheetWithVisualizationAPI();

登录后复制

注意事项

查询语言语法：确保你的查询语句符合Google Visualization API Query Language的规范。例如，字符串值必须用单引号'括起来。
CSV解析的健壮性：上述示例中的CSV解析是一个简化版本。如果你的表格数据包含逗号、换行符或引号等特殊字符，或者需要更复杂的类型转换，强烈建议使用成熟的Node.js CSV解析库（如csv-parse或fast-csv）来确保数据的正确解析。
错误处理：在实际生产环境中，需要更完善的错误处理机制，包括网络请求失败、认证失败、API返回错误等情况。
性能：虽然gviz/tq端点提供了强大的查询能力，但对于超大规模的数据集，仍然需要考虑其性能限制和Google API的配额。
request库：request库已经进入维护模式，建议在新项目中考虑使用更现代的HTTP客户端，如axios或Node.js内置的fetch API（Node.js v18+）。

总结

尽管googleapis的Sheets API客户端库不直接支持SQL-like查询，但通过巧妙地利用Google Visualization API的gviz/tq端点，并结合服务账户认证和HTTP请求，我们可以在Node.js环境中实现对Google表格数据的强大且灵活的查询功能。这种方法有效地解决了处理大型表格数据时，按条件筛选数据的效率问题，为开发者提供了更高级的数据操作能力。

以上就是使用Node.js通过googleapis查询Google表格数据的详细内容，更多请关注php中文网其它相关文章！