[GWSStudio100本ノック] BigQueryにある売上情報をChatからさらに深堀するエージェントを作成してみた(GAS構築編)

はじめに

本記事は、Google Workspace Studio(旧Flows)の実践ノウハウを100本紹介する連載「Google Workspace Studio活用方法100本ノック」の一つとなります。  

今回は、以前ご紹介した「BigQueryにある売上情報を分析した定期レポートをChatに連携するエージェントを作成してみた」をさらに拡張し、Google Chatから特定のキーワードで質問すれば、その場でBigQueryに対して追加クエリを実行し、深堀り結果を返してくれるインタラクティブな仕組みの中のGoogle Apps Script（GAS）の構築を説明する番外編的な内容となります。エージェントから連携されるSQLをもとにして、BigQueryからデータを取得する処理を説明させていただきます。

ユースケース

今回作成するエージェントの代表的なユースケースとしては以下のようなことが考えられると思います。

• 営業会議での追加分析
  • Chatで「売上調査依頼」などの特定の文言を入力すると、BigQueryから最新の売上ランクを取得し、そのまま会議中に共有することができます。
• 地域別の動向を即確認
  • 対象地域の前月比やトレンドグラフ（テキストサマリー）を生成し、マネージャーが判断材料を得られるようになります。
• 予実差分の追跡
  • 想定売上と実績との差をその場で照会し、必要に応じて追加のクエリを投げて深堀りが可能に。Slackへの転送やDocs出力にも派生することも可能です。

前提条件

• 利用環境：Google Workspace Studioにアクセスできるユーザーであること。
• 利用アプリ：BigQueryに接続するための権限が必要となります。そして、通知先のGoogle Chatスペース（またはDM）が事前に用意されていること。
• 社内ルール：情報管理ルールを確認済みであること。

構築手順

今回のブログではGoogle Apps Scriptの構築やGoogle Cloud側のサービスアカウント準備や権限の設定については詳しく説明しません。もしGoogle Apps Scriptの構築やGoogle Cloud側の設定について詳しく知りたい場合はGoogleの公式ドキュメントなどを確認するようにされてください。

まずはGoogle Cloudのサービスアカウントを作成するサイトにアクセスしてください。そして、今回の実行するプロジェクトを選択して、画面上部の「サービスアカウントを作成」のボタンを選択し、適当な名前のサービスアカウントを作成してください。この時に権限を付与することを省略もできますが、今回は省略せずに「BigQueryジョブユーザー」と「BigQueryデータ閲覧者」を付与するようにしてください。

作成したサービスアカウントの詳細画面にアクセスして「キーを追加」ボタンからJSON形式のサービスアカウントキーを作成してください。

ダウンロードしたサービスアカウントキーのファイルの中の以下の項目を後続のGASでは利用するので確認しておいてください。

• project_id
• private_key
• client_email

次に、Google Apps Scriptのサイトにアクセスしてください。そして、新しいプロジェクトを作成して適当な名前をつけてください。今回は「BQクエリラッパー」という名前を付けました。

そして、サイドメニューの歯車ボタンから設定画面にアクセスして、全般設定の中の「「appsscript.json」マニフェスト ファイルをエディタで表示する」のチェックボタンにチェックを入れてください。

設定が完了したらエディタの画面に戻ってください。そして、ファイルの中の「appsscript.json」ファイルを選択して、エディタに以下の内容を入力して保存してください。保存した後にライブラリのところにOAuth2が出てきていれば大丈夫です。

{
  "timeZone": "Asia/Tokyo",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "addOns": {
    "common": {
      "name": "BigQuery Runner",
      "logoUrl": "https://www.gstatic.com/images/icons/material/system/1x/pets_black_48dp.png",
      "useLocaleFromApp": true
    },
    "flows": {
      "workflowElements": [
        {
          "id": "runBigQueryAction",
          "state": "ACTIVE",
          "name": "Run SQL Query",
          "description": "Executes a SQL query on BigQuery and returns the results as JSON.",
          "workflowAction": {
            "inputs": [
              {
                "id": "sqlQuery",
                "description": "Standard SQL Query",
                "cardinality": "SINGLE",
                "dataType": {
                  "basicType": "STRING"
                }
              }
            ],
            "outputs": [
              {
                "id": "resultJson",
                "description": "Query results in JSON format",
                "cardinality": "SINGLE",
                "dataType": {
                  "basicType": "STRING"
                }
              },
              {
                "id": "status",
                "description": "Execution status",
                "cardinality": "SINGLE",
                "dataType": {
                  "basicType": "STRING"
                }
              }
            ],
            "onConfigFunction": "onConfigBQ",
            "onExecuteFunction": "onExecuteBQ"
          }
        }
      ]
    }
  },
  "dependencies": {
    "libraries": [
      {
        "userSymbol": "OAuth2",
        "version": "43",
        "libraryId": "1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF"
      }
    ]
  }
}

そして、ファイルの中の「コード.gs」ファイルを選択して、エディタに以下の内容を入力して保存してください。

// =========================================================
// 設定読み込み
// =========================================================
const PROJECT_ID = "PROJECT_ID";
const SA_PRIVATE_KEY = "SA_PRIVATE_KEY";
const SA_CLIENT_EMAIL = "SA_CLIENT_EMAIL";

// =========================================================
// Workspace Studio Execution (実行処理)
// =========================================================
function onExecuteBQ(event) {
  console.log("Event: " + JSON.stringify(event));

  const inputs = event.workflow.actionInvocation.inputs;
  const sql = getStringValue(inputs["sqlQuery"]);
  
  var resultJsonString = "[]";
  var statusMsg = "";

  try {
    if (!sql) throw new Error("SQL Query is empty.");
    if (!PROJECT_ID || !SA_PRIVATE_KEY || !SA_CLIENT_EMAIL) {
      throw new Error("Service Account configurations are missing in Script Properties.");
    }

    // サービスアカウントでBigQuery実行
    const rows = executeQueryWithServiceAccount(sql);
    
    resultJsonString = JSON.stringify(rows);
    statusMsg = "Success: " + rows.length + " rows returned (via Service Account).";

  } catch (e) {
    console.error(e);
    statusMsg = "Error: " + e.toString();
    resultJsonString = JSON.stringify({ error: e.toString() });
  }

  const variableDataMap = {
    "resultJson": AddOnsResponseService.newVariableData().addStringValue(resultJsonString),
    "status": AddOnsResponseService.newVariableData().addStringValue(statusMsg)
  };
  
  return outputVariables(variableDataMap);
}

// =========================================================
// BigQuery Logic (Service Account Version)
// =========================================================

/**
 * サービスアカウントの認証トークンを使ってSQLを実行する
 */
function executeQueryWithServiceAccount(sql) {
  const service = getBigQueryService();
  
  if (!service.hasAccess()) {
    throw new Error('Authorization failed: ' + service.getLastError());
  }

  // BigQuery REST API Endpoint
  // https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
  const url = `https://bigquery.googleapis.com/bigquery/v2/projects/${PROJECT_ID}/queries`;
  
  const payload = {
    query: sql,
    useLegacySql: false,
    // 必要に応じてロケーションを指定
    // location: "asia-northeast1" 
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      Authorization: 'Bearer ' + service.getAccessToken()
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(url, options);
  const json = JSON.parse(response.getContentText());

  if (response.getResponseCode() !== 200) {
    throw new Error("BigQuery API Error: " + (json.error ? json.error.message : response.getContentText()));
  }

  // jobCompleteのチェック（簡単なクエリなら即時返るが、念の為ループ処理が必要な場合も）
  // ※このAPI(queries)は同期実行を試みますが、タイムアウトした場合はjobIdが返るため
  // 本格的な実装ではjobIdを使って getQueryResults をポーリングする必要があります。
  // 今回は簡易版として、即時完了しなかった場合のWarningのみログに出します。
  if (!json.jobComplete) {
    console.warn("Job not complete yet. Pagination/Polling logic required for long queries.");
  }

  return parseBigQueryResults(json);
}

/**
 * OAuth2サービスを設定する
 */
function getBigQueryService() {
  return OAuth2.createService('BigQuerySA')
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setPrivateKey(SA_PRIVATE_KEY)
    .setIssuer(SA_CLIENT_EMAIL)
    .setPropertyStore(PropertiesService.getScriptProperties())
    .setScope('https://www.googleapis.com/auth/bigquery');
}

/**
 * BigQueryのレスポンス(JSON)を整形する
 * (前回のロジックを流用可能ですが、REST APIの構造に合わせて微調整)
 */
function parseBigQueryResults(results) {
  if (!results.schema || !results.rows) {
    return [];
  }

  const headers = results.schema.fields.map(function(field) {
    return field.name;
  });

  const rows = results.rows;
  
  return rows.map(function(row) {
    const rowObject = {};
    row.f.forEach(function(cell, i) {
      rowObject[headers[i]] = cell.v;
    });
    return rowObject;
  });
}

// =========================================================
// Helper Functions
// =========================================================

function onConfigBQ() {
  var card = {
    "sections": [
      {
        "header": "BigQuery Settings (Service Account)",
        "widgets": [
          {
            "textInput": {
              "name": "sqlQuery",
              "label": "SQL Query",
              "multiline": true, 
              "hostAppDataSource" : { "workflowDataSource" : { "includeVariables" : true } }
            }
          }
        ]
      }
    ]
  };
  return pushCard(card);
}

function getStringValue(inputItem) {
  if (!inputItem) return "";
  if (inputItem.stringValues && inputItem.stringValues.length > 0) return inputItem.stringValues[0];
  return "";
}

function pushCard(card) {
  return { "action": { "navigations": [{ "push_card": card }] } };
}

function outputVariables(variableDataMap) {
 const workflowAction = AddOnsResponseService.newReturnOutputVariablesAction().setVariableDataMap(variableDataMap);
 const hostAppAction = AddOnsResponseService.newHostAppAction().setWorkflowAction(workflowAction);
 return AddOnsResponseService.newRenderActionBuilder().setHostAppAction(hostAppAction).build();
}

そして、コードの冒頭にある設定読み込みのところをGoogle Cloudで発行したサービスアカウントキーの中の情報に置き換えてください。

// =========================================================
// 設定読み込み
// =========================================================
const PROJECT_ID = "PROJECT_ID"; // project_idに置き換える
const SA_PRIVATE_KEY = "SA_PRIVATE_KEY"; // private_keyに置き換える
const SA_CLIENT_EMAIL = "SA_CLIENT_EMAIL"; // client_emailに置き換える

すべての保存が完了したら画面右上にある「デプロイ」ボタンを選択した後に、「デプロイをテスト」を選択してください。

デプロイをテストのポップアップ画面が出てくると思いますので、この画面の中の「Application(s): Workspace Studio」の右側に出てくる「インストール」ボタンを選択してください。はじめてインストールする時には認証画面が出てくると思いますがその時は画面の表示に従って認証を実施してください。

インストールが完了するとWorkspace Studio側の中に作成した処理が追加されます。この時にWorkspace Studioに表示されない場合は画面の再読み込みを試してみてください。

初めてこのStepを設定するとこちらのブログのような認証を要求する画面が出てきます。「Grant permission」を選択して認証を順番に実施してください。

認証がすべて完了すると以下のような入力フォームのある画面に更新されます。

実行テスト

実行テストについてはこちらのブログの内容を確認してください。一連のエージェントの実行の流れを記載させていただいております。

BigQuery側では実行履歴を確認することでどのようなSQLが実行されたのかやどれくらいの処理量となったかを確認することが可能となっています。

まとめ

定期レポートだけでは見えない突発的な分析ニーズに応えるには、Chatから直接データを深堀りできる仕組みが有効です。Workspace StudioとBigQueryを組み合わせて、キーワードに応じたSQLを実行し、Geminiで読みやすい要約に整えて返すことで、営業チームが気になる数字をその場で確認できるようになりました。

将来的な展望としてはコマンドのバリエーションを増やしたり、権限管理・ログ記録を組み込むことで、さらに安全で便利な「対話型データ分析エージェント」へ進化させていきましょう。

メールの署名から連絡先を手作業で登録していると、更新し忘れや表記ゆれが発生しがちです。Workspace Studioで署名抽出とGASを組み合わせれば、受信と同時にGoogle Contactsを最新化する仕組みを構築できます。

本記事ではContacts登録ロジックをGASで登録する処理とWorkspace Studioにインストールする方法を解説しました。エージェントの構築と合わせて試してみて、連絡先管理の自動化による業務効率化を体感してみてください。