通过工作流向 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

    您可以在以下位置找到项目编号: 欢迎 页面,或者您可以检索项目编号:

    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 云端硬盘根文件夹。然后,您可以将该电子表格移至 文件夹中。

在以下工作流中,spreadsheetId 为 从 resp 结果中检索到的数据:

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. 查询编辑器文本区域:

    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 连接器 并使用 Google Sheets 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 项目编号。您可以在以下位置找到项目编号: 欢迎 页面

执行工作流并验证结果

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

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

后续步骤