从工作流写入 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 表格 API 直接在指定文件夹中创建电子表格。不过,您可以使用其他方法,包括在创建电子表格后将其移至特定文件夹(如以下示例所示)。如需了解详情,请参阅使用 Google 云端硬盘文件夹

使用 Google 表格创建电子表格

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

  1. 前往 sheets.google.com

  2. 点击新建图标 Plus

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

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

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

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

    1. 在电子表格中,依次选择文件 > 移动
    2. 前往您创建的文件夹。
    3. 点击移动

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

您可以使用 Google 表格 API 连接器创建电子表格。由于 Google 工作流会使用服务账号作为触发器身份,因此电子表格会在服务账号的 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:

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 表格 API 连接器将结果写入表格电子表格。

控制台

  1. 在 Google Cloud 控制台中,前往 Workflows 页面:

    进入 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. Workflows 页面上,选择 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. 验证工作流是否已将查询结果写入到您的电子表格中。例如,电子表格中的列数和行数应与 updatedColumnsupdatedRows 值一致。

后续步骤