GASでBigQuery集計術

GAS応用でBigQuery連携を始める

Google Apps Script（GAS）はスプレッドシートやGmailと連携した自動化に優れていますが、データウェアハウスとしてのBigQueryとの連携も可能です。まずはGoogle Cloud ConsoleでBigQuery APIを有効化し、サービスアカウントを作成してJSONキーを取得します。次にApps Scriptのプロジェクトに「Google Cloud BigQuery API」を追加し、認証情報を設定します。これでGASからBigQueryへクエリを投げる準備が整います。

BigQuery.Jobs.queryでSQLを実行

BigQuery.Jobs.queryはApps Scriptから直接SQLを実行できるメソッドです。以下のサンプルコードは、指定したテーブルから最新のレコードを取得し、結果をスプレッドシートに書き込む例です。

function runQuery() {
  var projectId = 'your-project-id';
  var sql = 'SELECT * FROM `dataset.table` ORDER BY created_at DESC LIMIT 10';
  var request = {
    query: sql,
    useLegacySql: false
  };
  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;
  // クエリ完了まで待機
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }
  var rows = queryResults.rows;
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clearContents();
  var headers = queryResults.schema.fields.map(function(f){ return f.name; });
  sheet.appendRow(headers);
  rows.forEach(function(row){
    var values = row.f.map(function(f){ return f.v; });
    sheet.appendRow(values);
  });
}

このように、GASとBigQuery.Jobs.queryを組み合わせることで、スプレッドシート上でSQLを実行し、結果を即座に確認できます。

大量データの集計とレポート作成

BigQueryはペタバイト規模の大量データを高速に処理できるデータウェアハウスです。集計クエリを作成し、GASで結果を取得してレポートを自動生成するワークフローを構築します。例えば、日次売上データを集計し、月次レポートをスプレッドシートに出力する場合、以下のようなSQLを使用します。

SELECT
  DATE_TRUNC('MONTH', order_date) AS month,
  SUM(amount) AS total_sales,
  COUNT(*) AS order_count
FROM `ecommerce.orders`
WHERE order_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH) AND CURRENT_DATE()
GROUP BY month
ORDER BY month;

取得したデータをGASで整形し、グラフやピボットテーブルを作成してレポート化します。これにより、データ分析のサイクルを短縮し、意思決定を迅速化できます。

可視化とデータウェアハウス活用

集計結果を可視化することで、非技術者でもデータの傾向を直感的に把握できます。Google Data StudioやLooker Studioと連携すれば、BigQueryのテーブルを直接データソースとして利用し、ダッシュボードを作成できます。GASからは定期的にレポートを生成し、メールで配信することも可能です。

さらに、BigQueryをデータウェアハウスとして活用することで、複数のデータソースを統合し、統一されたスキーマで分析できます。GASはETL（抽出・変換・ロード）の自動化に最適で、データパイプラインを簡潔に構築できます。

なんでも技術ブログ

このブログを検索