通过 gcloud CLI 认证从本地脚本写入 Google Sheets

最近我需要从GitHub API获取数据并发布到Google表格中，以便分享一些关于代码审查工作量的图表。本文将介绍我是如何完成身份验证的。

我编写了一个Node.js脚本，因为我的用例对于BASH来说太复杂了，而用Go又显得过于临时。最初，脚本生成了临时的CSV数据，我可以手动复制到Google Sheets中，使用“粘贴为CSV”功能。经过几轮手动复制后，我想明智地利用时间：我估计可以在几个小时内完成Sheets集成，如果能做到这一点，那么
几个月内就能看到回报。

备注: Sheets 不是我的数据库。它是我的数据报表界面。不要将 Sheets 用作数据库。

花了我将近4个小时才搞定这个认证问题，因为认证真的很复杂。这篇文章展示了通向解决方案的更直接路径。

在创建OAuth应用时，很容易被其复杂性所分散注意力，而我特别喜欢从我的云服务中使用服务账户，但在本地运行时，这不太方便。

我学到的技巧是，gcloud CLI 的 应用默认凭据 设置可以作为 OAuth 代理为 Google Workspace 代码工作，通过扩展其与 Google 的身份验证方式来包含一些额外的权限（OAuth 作用域）。

🔐 使用 gcloud CLI 进行本地认证

要向 Google Sheets 发出 API 请求，需要在您的 Cloud 项目中启用 Sheets API：

    $> gcloud services enable sheets.googleapis.com

    操作 "operations/acat.p2-480745230567-02564c8d-c6ba-4f60-90bd-13f33e41f0fe" 成功完成。

进入全屏模式 退出全屏模式

设置你的 应用默认凭据，并声明一些非默认的OAuth范围，以便该凭据可以与Google Sheets一起使用：

    $> gcloud auth application-default login --scopes \
       'https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/spreadsheets'

进入全屏模式 退出全屏模式

这将触发一个OAuth流程，涉及在您的浏览器中访问一个网页。根据终端配置的不同，这可能会显示一个URL，甚至会自动打开该页面。

当 gcloud 被授予此范围时，可以访问您本地凭证的代码将对您的所有 Google Sheets 数据具有读写权限。您可以在需要时通过重新运行此命令（不带自定义范围）来切换此权限的开启和关闭。

🗝️ 初始化一个用于 Google Sheets 的 Node 客户端

    import {google} from 'googleapis';

    function sheetsClient() {
        const authConfig = new google.auth.GoogleAuth({
            scopes: [
                // 仅代码中需要 'spreadsheets' 权限。
                // gcloud CLI 还需要 'cloud-platform' 和 'drive' 权限。
                'https://www.googleapis.com/auth/spreadsheets'
            ],
        });
        const auth = await authConfig.getClient();
        return google.sheets({version: 'v4', auth});
    }

进入全屏模式 退出全屏模式

🖇 向表格追加数据

文档中关于如何向表格追加数据的示例（点击Node.js选项卡）运行得很好。然而，我不明白如何从那里进行身份验证。在我理解了上述技巧，将缺少的OAuth范围添加到我的开发环境凭据后，示例才得以运行。

我对客户端的重用进行了几处修改，以使其更易于重用，以不同的方式参数化请求，并强调了我希望如何处理Sheets中的数据。

我的代码用于将数据追加到表格中，利用上述客户端初始化代码：

    let client;

    async function appendDataToSheet(spreadsheetId, tab, values) {
        if (!client) {
            client = sheetsClient();
        }

        try {
            const result = await client.spreadsheets.values.append({
                spreadsheetId,
                range: `${tab}!A2:AG`,
                // 使用提供的数据。
                valueInputOption: 'RAW',
                // 通过插入行来减少覆盖。
                insertDataOption: 'INSERT_ROWS',
                requestBody: { values },
            });
            console.log(`${result.data.updates.updatedCells} 个单元格已追加。`);
        } catch(e) {
            // 显示错误，不停止。将错误与终端输出进行比较，然后根据情况决定重新运行脚本或手动复制数据。
            console.error(e);
        }
    }

进入全屏模式 退出全屏模式

我使用"追加"是因为我的脚本收集月度指标，追加允许我在不删除早期行的情况下添加新行。

这里是一个调用 appendDataToSheet() 函数的例子：

    const values = [
      // 每个嵌套数组代表电子表格中的一行。
      [1, 2, 3, 4, '行李'],
      [4, 5, 6, '不可用', '棍子'],
    ];
    appendDataToSheet(
      'HPDkfqdu6rfIq5-4uTGDqz2tvmPxDZMul27JFexample',
      '导出数据标签',
      values
    );

进入全屏模式 退出全屏模式

文档中有关于使用Sheets API的一些好建议，例如建议不要每秒对同一个表格发送超过一个API请求。我则是从惨痛的经验中学到了这一点：用第二个请求的数据覆盖第一个请求的数据。

🚀 发布它！

如果我要将这个项目推进到生产环境，我可能会切换到使用Cloud Scheduler和Cloud Run Jobs。如果您想了解这方面的内容，请告诉我。

封面照片由 Glib Albovsky 在 Unsplash 提供