通过工作流向 Google 表格写入数据

Google 表格是一种基于云的电子表格解决方案，支持实时协作，并提供用于直观呈现、处理和通信数据的工具。

以下示例演示了如何从工作流向 Google 表格写入数据。工作流会查询 BigQuery 数据集，并将结果写入 Google 表格电子表格。它使用 Workflows 连接器来简化 Google Cloud API 的调用。

准备工作

在试用本文档中的示例之前，请确保您已完成以下操作。

  1. 启用 Compute Engine、Google 云端硬盘、Google 表格和 Workflows API。

    控制台

    启用 API

    gcloud

    gcloud services enable \
    compute.googleapis.com \
    drive.googleapis.com \
    sheets.googleapis.com \
    workflows.googleapis.com

  2. 记下 Compute Engine 默认服务帐号服务帐号，因为为了便于测试，您要将该帐号与示例工作流相关联。对于启用了 Compute Engine API 的新项目，系统将使用 IAM 基本 Editor 角色创建此服务帐号，并使用以下电子邮件格式：

    PROJECT_NUMBER-compute@developer.gserviceaccount.com

    您可以在 Google Cloud 控制台的欢迎页面上找到项目编号，也可以检索项目编号：

    gcloud projects describe PROJECT_ID

    对于生产环境，我们强烈建议创建新的服务帐号，并向其授予一个或多个包含所需最低权限并遵循最小权限原则的 IAM 角色。

  3. 在 Google 云端硬盘中创建一个新文件夹。此文件夹用于存储您的电子表格。通过为共享文件夹设置权限，您的工作流可以写入电子表格。

    1. 转至 drive.google.com。
    2. 点击新建 > 新建文件夹。
    3. 为该文件夹输入一个名称。
    4. 点击创建。
    5. 右键点击新文件夹，然后选择共享。

    6. 添加 Compute Engine 默认服务帐号的电子邮件地址。

      这将授予服务帐号访问该文件夹的权限。当您将服务帐号与工作流相关联时，该工作流将拥有对文件夹内任何文件的修改权限。详细了解如何共享文件、文件夹和云端硬盘。

    7. 选择编辑者角色。

    8. 清除通知对方复选框。

    9. 点击分享。

创建电子表格

您可以通过以下任一方式创建电子表格：

无法使用 Google Sheets API 直接在指定文件夹中创建电子表格。 不过，您也可以采用替代方案，例如在创建电子表格后将其移至特定文件夹，如以下示例所示。如需了解详情，请参阅使用 Google 云端硬盘文件夹。

使用 Google 表格创建电子表格

当您通过 Google 表格创建电子表格后，系统会将其保存在 Google 云端硬盘中。默认情况下，电子表格会保存到云端硬盘的根文件夹中。

  1. 访问 sheets.google.com。

  2. 点击 New Plus。

    此操作会创建并打开您的新电子表格。每个电子表格都有一个唯一的 spreadsheetId 值，其中包含字母、数字、连字符或下划线。您可以在 Google 表格网址中找到电子表格 ID：

    https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0

  3. 请记下此 ID，因为您在创建工作流时会用到它。

  4. 将电子表格移至您之前创建的 Google 云端硬盘文件夹：

    1. 在电子表格中，依次选择文件 > 移动。
    2. 导航到您创建的文件夹。
    3. 点击移动。

使用 Google Sheets API 连接器创建电子表格

您可以使用 Google Sheets API 连接器创建电子表格。由于 Workflows 将服务帐号用作触发器身份，因此电子表格会在服务帐号的 Google 云端硬盘根文件夹中创建。然后，您可以将该电子表格移至其他文件夹。

在以下工作流中，系统会从 resp 结果中检索 spreadsheetId：

YAML

main:
  steps:
    - init:
        assign:
          - folder_id: 'FOLDER_ID'
          - drive_url: 'https://www.googleapis.com/drive/v3/files/'
          - drive_auth_scope: 'https://www.googleapis.com/auth/drive'
    - create_sheet:
        call: googleapis.sheets.v4.spreadsheets.create
        args:
          body: null
          connector_params:
            scopes: 'https://www.googleapis.com/auth/drive'
        result: resp
    - get_sheet_info:
        call: http.get
        args:
          url: '${drive_url + resp.spreadsheetId}'
          query:
            fields: parents
          auth:
            type: OAuth2
            scopes: '${drive_auth_scope}'
        result: sheet_info
    - move_sheet:
        call: http.patch
        args:
          url: '${drive_url + resp.spreadsheetId}'
          query:
            addParents: '${folder_id}'
            removeParents: '${sheet_info["body"]["parents"][0]}'
          auth:
            type: OAuth2
            scopes: '${drive_auth_scope}'
        result: resp
    - return:
        return: '${resp}'

JSON

{
  "main": {
    "steps": [
      {
        "init": {
          "assign": [
            {
              "folder_id": "FOLDER_ID"
            },
            {
              "drive_url": "https://www.googleapis.com/drive/v3/files/"
            },
            {
              "drive_auth_scope": "https://www.googleapis.com/auth/drive"
            }
          ]
        }
      },
      {
        "create_sheet": {
          "call": "googleapis.sheets.v4.spreadsheets.create",
          "args": {
            "body": null,
            "connector_params": {
              "scopes": "https://www.googleapis.com/auth/drive"
            }
          },
          "result": "resp"
        }
      },
      {
        "get_sheet_info": {
          "call": "http.get",
          "args": {
            "url": "${drive_url + resp.spreadsheetId}",
            "query": {
              "fields": "parents"
            },
            "auth": {
              "type": "OAuth2",
              "scopes": "${drive_auth_scope}"
            }
          },
          "result": "sheet_info"
        }
      },
      {
        "move_sheet": {
          "call": "http.patch",
          "args": {
            "url": "${drive_url + resp.spreadsheetId}",
            "query": {
              "addParents": "${folder_id}",
              "removeParents": "${sheet_info[\"body\"][\"parents\"][0]}"
            },
            "auth": {
              "type": "OAuth2",
              "scopes": "${drive_auth_scope}"
            }
          },
          "result": "resp"
        }
      },
      {
        "return": {
          "return": "${resp}"
        }
      }
    ]
  }
}

将 FOLDER_ID 替换为要将电子表格移到的目标文件夹的 ID。每个云端硬盘文件夹都有一个唯一 ID，该 ID 包含字母、数字、连字符或下划线。您可以在文件夹网址中找到文件夹 ID：

https://drive.google.com/drive/folders/FOLDER_ID/edit#gid=0

如需了解详情，请参阅创建和填充文件夹。

工作流的输出应类似于以下内容，其中 id 值为 spreadsheetId：

"body": {
    "id": "spreadsheetId",
    "kind": "drive#file",
    "mimeType": "application/vnd.google-apps.spreadsheet",
    "name": "Untitled spreadsheet"
  }

探索 BigQuery 公共数据集

BigQuery 托管着许多可供公众查询的公共数据集。

在 BigQuery 中，您可以运行交互式（按需）查询作业。例如，以下查询返回特定数据集中 100 个最常用名称，并将输出写入临时表中。这是您的工作流将运行的查询。

控制台

  1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

    转到 BigQuery

  2. 在查询编辑器文本区域中输入以下 BigQuery SQL 查询：

    SELECT name, gender, SUM(number) AS total
FROM `bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY name, gender
ORDER BY total DESC
LIMIT 100

  3. 点击运行。

bq

在终端中，输入以下 bq query 命令，以使用标准 SQL 语法运行交互式查询：

    bq query \
    --use_legacy_sql=false \
    'SELECT
      name, gender, SUM(number) AS total
    FROM
      `bigquery-public-data.usa_names.usa_1910_2013`
    GROUP BY
      name, gender
    ORDER BY
      total DESC
    LIMIT 100'

部署向 Google 表格写入内容的工作流

部署一个工作流，该工作流使用 BigQuery API 连接器查询 BigQuery 数据集，并使用 Google Sheets API 连接器将结果写入 Google 表格电子表格。

控制台

  1. 在 Google Cloud 控制台中，转到工作流页面：

    进入 Workflows

  2. 点击 创建。

  3. 输入新工作流的名称：read-bigquery-write-sheets。

  4. 在区域列表中，选择 us-central1（爱荷华）。

  5. 对于服务帐号，选择 Compute Engine 默认服务帐号 (PROJECT_NUMBER-compute@developer.gserviceaccount.com)。

  6. 点击下一步。

  7. 在工作流编辑器中，为工作流输入以下定义：

    main:
    steps:
    - init:
        assign:
        # Replace with your sheetId and make sure the service account
        # for the workflow has write permissions to the sheet
        - sheetId: "1D8n7uoU8kGwQvR4rcLkF10CdAfnUKE2o0yl6P-Z7nfM"
        - limit: 100
    - runQuery:
        call: googleapis.bigquery.v2.jobs.query
        args:
            projectId: ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
            body:
                useLegacySql: false
                # Query name and gender of most popular names
                query: ${"SELECT name, gender, SUM(number) AS total
                    FROM `bigquery-public-data.usa_names.usa_1910_2013`
                    GROUP BY name, gender
                    ORDER BY total DESC
                    LIMIT " + limit}
        result: queryResult
    - init_header_row:
        assign:
        - rows:
            - ["Name", "Gender", "Total"]
    - process_query_result:
        for:
            value: row
            in: ${queryResult.rows}
            steps:
            - process_each_row:
                assign:
                - name: ${row.f[0].v}
                - gender: ${row.f[1].v}
                - total: ${row.f[2].v}
                - row: ["${name}", "${gender}", "${total}"]
                - rows: ${list.concat(rows, row)}
    - clear_existing_values:
        call: googleapis.sheets.v4.spreadsheets.values.clear
        args:
            range: "Sheet1"
            spreadsheetId: ${sheetId}
        result: clearResult
    - update_sheet:
        call: googleapis.sheets.v4.spreadsheets.values.update
        args:
            range: ${"Sheet1!A1:C" + (limit + 1)}
            spreadsheetId: ${sheetId}
            valueInputOption: RAW
            body:
                majorDimension: "ROWS"
                values: ${rows}
        result: updateResult
    - returnResult:
        return: ${updateResult}

  8. 将占位符 sheetId 值替换为您的 spreadsheetId。

  9. 点击部署。

gcloud

  1. 为工作流创建源代码文件：

    touch read-bigquery-write-sheets.yaml

  2. 在文本编辑器中，将以下工作流复制到源代码文件中：

    main:
    steps:
    - init:
        assign:
        # Replace with your sheetId and make sure the service account
        # for the workflow has write permissions to the sheet
        - sheetId: "1D8n7uoU8kGwQvR4rcLkF10CdAfnUKE2o0yl6P-Z7nfM"
        - limit: 100
    - runQuery:
        call: googleapis.bigquery.v2.jobs.query
        args:
            projectId: ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
            body:
                useLegacySql: false
                # Query name and gender of most popular names
                query: ${"SELECT name, gender, SUM(number) AS total
                    FROM `bigquery-public-data.usa_names.usa_1910_2013`
                    GROUP BY name, gender
                    ORDER BY total DESC
                    LIMIT " + limit}
        result: queryResult
    - init_header_row:
        assign:
        - rows:
            - ["Name", "Gender", "Total"]
    - process_query_result:
        for:
            value: row
            in: ${queryResult.rows}
            steps:
            - process_each_row:
                assign:
                - name: ${row.f[0].v}
                - gender: ${row.f[1].v}
                - total: ${row.f[2].v}
                - row: ["${name}", "${gender}", "${total}"]
                - rows: ${list.concat(rows, row)}
    - clear_existing_values:
        call: googleapis.sheets.v4.spreadsheets.values.clear
        args:
            range: "Sheet1"
            spreadsheetId: ${sheetId}
        result: clearResult
    - update_sheet:
        call: googleapis.sheets.v4.spreadsheets.values.update
        args:
            range: ${"Sheet1!A1:C" + (limit + 1)}
            spreadsheetId: ${sheetId}
            valueInputOption: RAW
            body:
                majorDimension: "ROWS"
                values: ${rows}
        result: updateResult
    - returnResult:
        return: ${updateResult}

  3. 将占位符 sheetId 值替换为您的 spreadsheetId。

  4. 输入以下命令以部署工作流：

    gcloud workflows deploy read-bigquery-write-sheets \
    --source=read-bigquery-write-sheets.yaml \
    --location=us-central1 \
    --service-account=PROJECT_NUMBER-compute@developer.gserviceaccount.com

    将 PROJECT_NUMBER 替换为您的 Google Cloud 项目编号。您可以在 Google Cloud 控制台的欢迎页面上找到项目编号。

执行工作流并验证结果

执行某个工作流会运行与该工作流关联的当前工作流定义。

  1. 运行工作流：

    控制台

    1. 在 Google Cloud 控制台中，转到 Workflows 页面：

      进入 Workflows

    2. 在工作流页面上，选择 read-bigquery-write-sheets 工作流，进入其详情页面。

    3. 在工作流详情页面上，点击 执行。

    4. 再次点击执行。

    5. 在输出窗格中查看工作流的结果。

      输出应类似如下所示：

      {
"spreadsheetId": "1pgkNY7mWICA6o6INUNLHGnmk0Tdioh0nUghZPOYPpZA",
"updatedCells": 303,
"updatedColumns": 3,
"updatedRange": "Sheet1!A1:C101",
"updatedRows": 101
}

    gcloud

    1. 打开终端。

    2. 执行工作流：

      gcloud workflows run read-bigquery-write-sheets

      执行结果应类似于以下内容：

      Waiting for execution [4dcf737b-69d9-4081-b8d9-86d39ae86bd1] to complete...done.     
argument: 'null'
duration: 3.131912897s
endTime: '2023-01-25T14:59:46.818828242Z'
name: projects/918619793306/locations/us-central1/workflows/read-bigquery-write-sheets/executions/4dcf737b-69d9-4081-b8d9-86d39ae86bd1
result: '{"spreadsheetId":"1pgkNY7mWICA6o6INUNLHGnmk0Tdioh0nUghZPOYPpZA","updatedCells":303,"updatedColumns":3,"updatedRange":"Sheet1!A1:C101","updatedRows":101}'
startTime: '2023-01-25T14:59:43.686915345Z'
state: SUCCEEDED

  2. 验证工作流是否已将查询结果写入电子表格。例如，电子表格中的列数和行数应与 updatedColumns 和 updatedRows 值一致。

后续步骤