ワークフローから Google スプレッドシートに書き込む

Google スプレッドシートは、リアルタイムでの共同編集が可能な、データの可視化、処理、通信のためのツールを提供するクラウドベースのスプレッドシートソリューションです。

次の例は、ワークフローからスプレッドシートに書き込む方法を示しています。このワークフローは、BigQuery データセットに対してクエリを実行し、結果を Google スプレッドシートに書き込みます。Workflows コネクタを使用して、Google Cloud APIs の呼び出しを簡素化します。

始める前に

このドキュメントの例を試す前に、次のことが完了していることを確認してください。

Compute Engine、Google ドライブ、Google スプレッドシート、Workflows API を有効にします。
コンソール
API を有効にする
gcloud
```
gcloud services enable \
    compute.googleapis.com \
    drive.googleapis.com \
    sheets.googleapis.com \
    workflows.googleapis.com
```
テスト用にサンプルワークフローに関連付けるため、Compute Engine のデフォルトのサービスアカウントのサービスアカウントをメモします。Compute Engine API が有効になっている新しいプロジェクトには、IAM 基本編集者ロールや、以下のメール形式を持つサービスアカウントが作成されます。
```
PROJECT_NUMBER-compute@developer.gserviceaccount.com
```
プロジェクト番号は、Google Cloud コンソールの [ようこそ] ページで確認できます。また、次のコマンドでプロジェクト番号を取得することもできます。
```
gcloud projects describe PROJECT_ID
```
本番環境では、新しいサービスアカウントを作成して、必要最小限のアクセス許可を含む、最小権限の原則に従った 1 つ以上の IAM ロールを付与することを強くおすすめします。
注:
iam.automaticIamGrantsForDefaultServiceAccounts 組織のポリシーの制約により、編集者のロールはデフォルトのサービスアカウントに自動的に付与されません。2024 年 5 月 3 日より後に組織を作成した場合、この制約はデフォルトで適用されます。

この制約を適用して、ロールの自動付与を無効にすることを強くおすすめします。ロールの自動付与を無効にする場合は、デフォルトのサービスアカウントに付与するロールを決定し、ご自身でこれらのロールを付与する必要があります。

デフォルトのサービスアカウントにすでに編集者ロールが設定されている場合は、編集者ロールを権限の低いロールに置き換えることをおすすめします。サービスアカウントのロールを安全に変更するには、Policy Simulator を使用して変更の影響を確認してから、適切なロールを付与または取り消す操作を行います。
Google ドライブに新しいフォルダを作成します。このフォルダはスプレッドシートの保存に使用されます。共有フォルダの権限を設定することで、ワークフローによるスプレッドシートへの書き込みができるようになります。
1. drive.google.com にアクセスする。
2. [新規] > [新しいフォルダ] をクリックします。
3. フォルダ名を入力します。
4. [作成] をクリックします。
5. 新しいフォルダを右クリックし、[共有] を選択します。
6. Compute Engine のデフォルトサービスアカウントのメールアドレスを追加します。
  
  これにより、サービスアカウントにフォルダへのアクセスが許可されます。サービスアカウントをワークフローに関連付けると、そのワークフローにはフォルダ内の任意のファイルに対する編集権限が付与されます。ファイル、フォルダ、ドライブの共有の詳細。
7. 編集者のロールを選択します。
8. [通知] チェックボックスをオフにします。
9. [共有] をクリックします。

スプレッドシートを作成する

スプレッドシートは、次のいずれかの方法で作成できます。

Google スプレッドシートを使用してスプレッドシートを作成する
Google Sheets API コネクタを使用してスプレッドシートを作成する

Google Sheets API を使用して、指定したフォルダ内に直接スプレッドシートを作成するオプションはありません。ただし、次の例に示すように、スプレッドシートを作成した後に、特定のフォルダに移動するなど、別の方法もあります。詳細については、Google ドライブフォルダを操作するをご覧ください。

Google スプレッドシートを使用してスプレッドシートを作成する

Google スプレッドシートを使用して作成したスプレッドシートは、Google ドライブに保存されます。デフォルトでは、スプレッドシートはドライブのルートフォルダに保存されます。

sheets.google.com にアクセスします。
[新規] をクリックします。

新しいスプレッドシートが作成され、開きます。各スプレッドシートには一意の spreadsheetId 値があり、これには文字、数字、ハイフン、アンダースコアが含まれます。スプレッドシート ID は Google スプレッドシートの URL で確認できます。

https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0
この ID はワークフローの作成時に必要になるため、メモしておいてください。
前に作成した Google ドライブフォルダにスプレッドシートを移動します。
1. スプレッドシートで、[ファイル] > [移動] を選択します。
2. 作成したフォルダに移動します。
3. [移動] をクリックします。

Google Sheets API コネクタを使用してスプレッドシートを作成する

Google Sheets API コネクタを使用してスプレッドシートを作成できます。Workflows はサービスアカウントをトリガー ID として使用するため、サービスアカウントの 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 は、フォルダの URL で確認できます。

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 個の名前を返し、出力を一時テーブルに書き込みます。これは、ワークフローで実行されるクエリです。

Console

Google Cloud コンソールで BigQuery ページに移動します。

BigQuery に移動

[クエリエディタ] のテキスト領域に、次の 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

[実行] をクリックします。

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 Sheets API コネクタを使用して結果をシートスプレッドシートに書き込むワークフローをデプロイする

Console

Google Cloud コンソールの [ワークフロー] ページに移動します。

[ワークフロー] に移動
[ 作成] をクリックします。
新しいフィールドの名前を入力します: read-bigquery-write-sheets。
[リージョン] リストで [us-central1 (Iowa)] を選択します。
[サービスアカウント] で、Compute Engine のデフォルトのサービスアカウント（PROJECT_NUMBER-compute@developer.gserviceaccount.com）を選択します。
[次へ] をクリックします。

ワークフローエディタで、次のワークフローの定義を入力します。

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}

プレースホルダ sheetId 値を spreadsheetId に置き換えます。
[デプロイ] をクリックします。

gcloud

ワークフローのソースコードファイルを作成します。
```
touch read-bigquery-write-sheets.yaml
```

テキストエディタで、次のワークフローをソースコードファイルにコピーします。

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}

プレースホルダ sheetId 値を spreadsheetId に置き換えます。
次のコマンドを入力してワークフローをデプロイします。
```
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 コンソールの [ようこそ] ページで確認できます。

ワークフローを実行して結果を確認する

ワークフローを実行すると、そのワークフローに関連付けられた現在のワークフロー定義が実行されます。

ワークフローを実行する

Console

Google Cloud コンソールの [ワークフロー] ページに移動します。

[ワークフロー] に移動
[ワークフロー] ページで [read-bigquery-write-sheets] ワークフローを選択して詳細ページに移動します。
[ワークフローの詳細] ページで [ 実行] を選択します。
もう一度 [Execute] をクリックします。

ワークフローの結果が [出力] ペインに表示されます。

出力例を以下に示します。

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

gcloud

ターミナルを開きます。

ワークフローを実行します。

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

ワークフローによってクエリの結果がスプレッドシートに書き込まれていることを確認します。たとえば、スプレッドシート内の列と行数は、updatedColumns と updatedRows の値と一致している必要があります。

ワークフローから Google スプレッドシートに書き込む

始める前に

コンソール

gcloud

スプレッドシートを作成する

Google スプレッドシートを使用してスプレッドシートを作成する

Google Sheets API コネクタを使用してスプレッドシートを作成する

YAML

JSON

BigQuery の一般公開データセットを探索する

Console

bq

スプレッドシートへの書き込みを行うワークフローをデプロイする

Console

gcloud

ワークフローを実行して結果を確認する

Console

gcloud

次のステップ