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

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

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

始める前に

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

  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-compute@developer.gserviceaccount.com

    プロジェクト番号は、Google Cloud コンソールの [ようこそ] ページで確認できます。また、次のコマンドでプロジェクト番号を取得することもできます。

    gcloud projects describe PROJECT_ID
    

    本番環境では、新しいサービス アカウントを作成して、必要最小限のアクセス許可を含む、最小権限の原則に従った 1 つ以上の 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. [新規] をクリックします。Plus

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

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

  3. この ID はワークフローの作成時に必要になるため、メモしておいてください。

  4. 前に作成した 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

  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'

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

BigQuery API コネクタを使用して、BigQuery データセットに対してクエリを実行し、Google Sheets API コネクタを使用して結果をシート スプレッドシートに書き込むワークフローをデプロイする

Console

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

    [ワークフロー] に移動

  2. [ 作成] をクリックします。

  3. 新しいフィールドの名前を入力します: read-bigquery-write-sheets

  4. [リージョン] リストで [us-central1 (Iowa)] を選択します。

  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. ワークフローを実行する

    Console

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

      [ワークフロー] に移動

    2. [ワークフロー] ページで [read-bigquery-write-sheets] ワークフローを選択して詳細ページに移動します。

    3. [ワークフローの詳細] ページで [ 実行] を選択します。

    4. もう一度 [Execute] をクリックします。

    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 の値と一致している必要があります。

次のステップ