AppScriptを使ってBigQueryの出力結果を定期的にGoogleスプレッドシートに保存する方法

こんにちはピリカの冨田です。

今回は、BigQueryの出力結果をAppScriptを使って定期的にGoogle スプレッドシートに保存する方法です。

背景
嬉しいところ
使用するものと処理の流れ
実装方法
参考

背景

今まで同様のニーズがあり、OWOX BI BIgQuery Reportsという無料のプラグインを使用していました。

これは無料+任意のタイミングで実行できていて重宝していたのですが、ソースコードの管理が難しかったり（プラグインで管理＆スプレッドシートのシートごとに管理）、追記ができなかったり（全件洗い替え）等といった難点がありました。

そこで今回は、より多くのクエリを、保守しやすく、追記できるようにしたいと思いAppScriptを使用するようにしました。

嬉しいところ

BigQueryの出力結果を定期的にGoogle スプレッドシートに保存する方法はいくつかあります。

例えば、私の知る限りだと、下記のようなものがあります。

今回の方法(GoogleAppScript)
Googleコネクテッドシート
OWOX BI BIgQuery Reports

その中でも、本記事でご紹介するこのAppScriptを使う方法のメリットは、

データの上書き・追記・更新などが自由に行える: データをAppScriptで整形するので、自由にロジックを組むことができます。
比較的安価: 定期実行に設定しているBigQueryの実行費用だけで済みます。スプレッドシートを開いただけで費用がかかってしまうことはありません。
保守性: プラグインに頼らずAppScriptで一元管理できます。AppScriptをGithubと連携させると尚良いと思います。

使用するものと処理の流れ

「BigQuery - GoogleAppScript - Google スプレッドシート」を使います。

実装方法

1. スプレッドシートに紐づくAppScriptを作成

今回は特定のスプレッドシートに出力するので、スプレッドシートに紐づくAppScriptを用意します。

2. サービスにBigQueryを追加

BigQueryをAppScriptから利用できるようにするために、BigQueryをAppScriptに追加します。

3. スクリプトの作成

スクリプトは公式を参考にすると簡単に書けました。

ざっくりとした処理の流れば、

クエリを実行・パース
データの整形：追記や更新の場合は、スプレッドシートと比較して書き込むデータのみを抽出する
スプレッドシートにデータを書き込む

という流れです。

このときに、追記にしたい時は、1で更新データのみをリクエストするか、2で追記するデータのみを抽出すれば良いです。

今回は、1のクエリ実行・パースに関する処理のみ下記に記載しています。

クエリ実行部分

事前にBigQueryのエディタで実行できるクエリ文を用意しておきます。

BigQuery.Jobs.queryメソッドでクエリを実行します。

ちなみに、このメソッドはBigQuery APIのjobs.queryを使用しており、レスポンスの詳細は公式のAPIのドキュメントが参考になりました。

class QueryHandler {
    /**
   * BigQueryから直近の立ち上げ日数を取得する。
   * 取得したQueryResultsクラスを返す
   * @param queryString クエリ文字列
   */
  runQuery(queryString) {
    const projectId = GCPのプロジェクトID;
    const request = {
      query: queryString,
      useLegacySql: false
    };
    
    // クエリ実行
    let queryResults = BigQuery.Jobs.query(request, projectId);
    const jobId = queryResults.jobReference.jobId;

    // クエリ完了まで待機
    let sleepTimeMs = 500;
    while (!queryResults.jobComplete) {
      Utilities.sleep(sleepTimeMs);
      sleepTimeMs *= 2;
      queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
    }

    return queryResults
  };
}

クエリ結果処理部分

既に述べたように、レスポンスは、APIの公式ドキュメントを参考にします。

例えば、私はヘッダーとデータのみが必要だったので、下記のように実装しました。

ヘッダーはschema.fieldsに、データはrowsに含まれています。

class QueryHandler {
  /**
   * クエリを行とヘッダーにする。クエリが空の場合はnullを返す。
   @param query クエリ結果クラス
   */
  parseQueryResult(query) {
    if (query === null || query === undefined) {
      Logger.log("Query is null");
      return;
    }

    // ヘッダー取得
    const header = query.schema.fields.map(function (field) {
      return field.name;
    })

    // データ取得
    let rows = query.rows;
    while (query.pageToken) {
      query = BigQuery.Jobs.getQueryResults(projectId, jobId, {
        pageToken: query.pageToken
      });
      rows = rows.concat(query.rows);
    }
    return [rows, header];
  }