透過工作流程寫入 Google 試算表

Google 試算表是雲端試算表解決方案，支援即時協作，並提供可視化、處理及傳達資料的工具。

以下範例說明如何透過工作流程寫入 Google 試算表。這個工作流程會查詢 BigQuery 資料集，並將結果寫入試算表試算表。這個 API 會使用 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 基本編輯者角色建立這個服務帳戶，電子郵件格式如下：

    PROJECT_NUMBER[email protected]

    您可以在 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 雲端硬碟根目錄中建立。然後將試算表移至其他資料夾。

在下列工作流程中，系統會從 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。每個 Google 雲端硬碟資料夾都有專屬 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. 在「Query editor」(查詢編輯器) 文字區域中輸入下列 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'

部署寫入試算表的工作流程

部署工作流程，使用 BigQuery API 連接器查詢 BigQuery 資料集，並使用 Google 試算表 API 連接器將結果寫入 Sheets 試算表。

控制台

  1. 前往 Google Cloud 控制台的「Workflows」頁面：

    前往「Workflows」頁面

  2. 按一下「建立」。

  3. 輸入新工作流程的名稱：read-bigquery-write-sheets。

  4. 在「Region」(區域) 清單中，選取「us-central1 (Iowa)」(us-central1 (愛荷華州))。

  5. 在「服務帳戶」中，選取 Compute Engine 預設服務帳戶 (PROJECT_NUMBER[email protected])。

  6. 點按「Next」。

  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. 按一下 [Deploy] (部署)。

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[email protected]

    將 PROJECT_NUMBER 替換為您的 Google Cloud專案編號。您可以在 Google Cloud 控制台的「歡迎」頁面中找到專案編號。

執行工作流程並驗證結果

執行工作流程會執行與工作流程相關聯的目前工作流程定義。

  1. 執行工作流程：

    控制台

    1. 前往 Google Cloud 控制台的「Workflows」頁面：

      前往「Workflows」頁面

    2. 在「Workflows」頁面中，選取「read-bigquery-write-sheets」工作流程，前往其詳細資料頁面。

    3. 在「Workflow details」(工作流程詳細資料)頁面中，按一下 「Execute」(執行)。

    4. 再次按一下「執行」。

    5. 在「Output」窗格中查看工作流程的結果。

      畫面會顯示如下的輸出內容：

      {
"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 值相符。

後續步驟