ワークフローから 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 API が有効になっている新しいプロジェクトには、IAM 基本編集者ロールや、以下のメール形式を持つ Compute Engine のデフォルトのサービスアカウントが作成されます。
```
PROJECT_NUMBER-compute@developer.gserviceaccount.com
```
プロジェクト番号は、Google Cloud コンソールの [ようこそ] ページで確認できます。

このドキュメントの例のテストに使用するため、このサービスアカウントをメモします。

重要: 本番環境では、新しいサービスアカウントを作成し、必要最小限のアクセス許可を含み、最小権限の原則に従う 1 つ以上の IAM ロールを付与することを強くお勧めします。
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）を選択します。
[Next（次へ）] をクリックします。

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

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

次のステップ