Google Apps Script で BigQuery のテーブル定義をスプレッドシートに自動で出力してみた

こんにちは、みかみです。

やりたいこと

• Google Apps Script（ GAS ） から BigQuery を操作するにはどうすればいいのか知りたい
• GAS で BigQuery のテーブル定義書をスプレッドシートに出力したい
• GAS をスケジュール実行して、定期的にテーブル定義書を更新したい

Step1：BigQuery テーブルデータをスプレッドシートに出力

とりあえず、Google Apps Script を動かして、BigQuery のテーブルデータをスプレッドシートに出力してみたいと思います。

新しいスプレッドシートを開いたら、「ツール」タブから「スクリプトエディタ」をクリックします。

Apps Script 画面が開くので、「サービス」から「BigQuery API」を追加します。

エディタに以下のスクリプトを入力しました。

function runQuery() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  var projectId = 'cm-da-mikami-yuki-258308';

  var request = {
    query: 'SELECT JANCD, maker, name, price_avg FROM dataset_1.pos_partition_date;',
    useLegacySql: false
  };
  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  if (rows) {
    var spreadsheet = SpreadsheetApp.create('BiqQuery Table Data');
    var sheet = spreadsheet.getActiveSheet();
    sheet.setName('pos_partition_date');

    // Append the headers.
    var headers = queryResults.schema.fields.map(function(field) {
      return field.name;
    });
    sheet.appendRow(headers);

    // Append the results.
    var data = new Array(rows.length);
    for (var i = 0; i < rows.length; i++) {
      var cols = rows[i].f;
      data[i] = new Array(cols.length);
      for (var j = 0; j < cols.length; j++) {
        data[i][j] = cols[j].v;
      }
    }
    sheet.getRange(2, 1, rows.length, headers.length).setValues(data);

    Logger.log('Results spreadsheet created: %s',
        spreadsheet.getUrl());
  } else {
    Logger.log('No rows returned.');
  }
}

下記、GitHub で公開されている GAS のサンプルコードをベースに、プロジェクト ID と実行する SQL、出力するスプレッドシートのファイル名とシート名を変更しました。

• apps-script-samples/advanced/bigquery.gs ｜ GitHub

「プロジェクトを保存」のアイコンからスクリプトを保存したら、「実行」ボタンで実行します。

初回実行時にはプロジェクトへのアクセス承認ダイアログが表示されるので、アクセスする BigQuery プロジェクトへのアクセス権がある Google アカウントを選択してアクセス許可を実行します。

スクリプト実行が正常に終了したようなので、実行ログに表示されているスプレッドシートの URL にアクセスしてみます。

見やすいようにセルの幅を調整しましたが、SQL で取得した BigQuery のテーブルデータが無事スプレッドシートに出力されたことが確認できました。

Step2：テーブル情報を取得してテーブル定義書を作成

指定したデータセットのテーブル情報をBigQuery API で取得して、スプレッドシートにテーブル一覧とテーブル定義を出力する、以下のスクリプトを準備しました。

function createDDL() {
  var projectId = 'cm-da-mikami-yuki-258308';
  var datasetId = 'dataset_1';
  var name_book = 'BiqQuery Table DDL';

  var table_info = get_table_info(projectId, datasetId);
  Logger.log('table_info: %s', table_info);
  if (table_info) {
    var tables = new Array(table_info.length);
    var columns = new Array(table_info.length);
    for(var i = 0; i < table_info.length; i++) {
        tables[i] = table_info[i][0]
        columns[i] = table_info[i][1]
    }
    Logger.log('tables: %s', tables);
    Logger.log('========');

    var spreadsheet = get_book(name_book);
    var sheet = get_sheet('テーブル一覧', spreadsheet);
    sheet.clear()
    sheet.appendRow(['データセット', 'テーブル', '説明', 'ロケーション']);
    sheet.getRange(2, 1, tables.length, tables[0].length).setValues(tables);
    sheet.getRange(1, 1, 1, tables[0].length).setBackground("#FFFFCC");
    sheet.getRange(1, 1, tables.length+1, tables[0].length).setBorder(true, true, true, true, true, true);
    for (var i = 0; i < tables.length; i++) {
      Logger.log('column: %s', columns[i]);
      sheet = get_sheet(tables[i][1], spreadsheet);
      sheet.clear()
      sheet.appendRow(['#', 'カラム名', 'データ型', 'モード', 'description']);
      sheet.getRange(2, 1, columns[i].length, columns[i][0].length).setValues(columns[i]);
      sheet.getRange(1, 1, 1, columns[i][0].length).setBackground("#FFFFCC");
      sheet.getRange(1, 1, columns[i].length+1, columns[i][0].length).setBorder(true, true, true, true, true, true);
    }
    Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  }
}

function get_table_info(projectId, datasetId) {
  var tables = BigQuery.Tables.list(projectId, datasetId).tables;
  var table_info = new Array(tables.length);
  var table_cnt = 0
  for (var i = 0; i < tables.length; i++) {
    if (tables[i].type != 'TABLE')
      continue;
    table_cnt ++;
    table = BigQuery.Tables.get(projectId, datasetId, tables[i].tableReference.tableId);

    var schema = table.schema.fields;
    var columns = new Array(schema.length);
    for (var j = 0; j < schema.length; j++) {
      columns[j] = [j+1, schema[j].name, schema[j].type, schema[j].mode, schema[j].description];
    }
    table_info[i] = [[table.tableReference.datasetId, table.tableReference.tableId, table.description, table.location], columns]
  }
  table_info.length = table_cnt;
  return table_info;
}

function get_book(name) {
  var files = DriveApp.getFilesByName(name);
  if(files.hasNext()) {
    return SpreadsheetApp.openById(files.next().getId());
  }
  return SpreadsheetApp.create(name);
}

function get_sheet(name, spreadsheet){
  var sheet = spreadsheet.getSheetByName(name)
  if(sheet)
    return sheet

  sheet = spreadsheet.getActiveSheet();
  if(sheet.getName() != 'シート1') {
    sheet = spreadsheet.insertSheet();
  }
  sheet.setName(name);
  return sheet;
}

テーブル情報は BigQuery.Tables.list でテーブル一覧を取得した後、BigQuery.Tables.get で１テーブルずつカラム定義などの詳細情報を取得します。

• BigQueryサービス ｜ Google Apps Script ドキュメント
• BigQuery API ｜ BigQuery ドキュメント

（省略）
function get_table_info(projectId, datasetId) {
  var tables = BigQuery.Tables.list(projectId, datasetId).tables;
  var table_info = new Array(tables.length);
  var table_cnt = 0
  for (var i = 0; i < tables.length; i++) {
    if (tables[i].type != 'TABLE')
      continue;
    table_cnt ++;
    table = BigQuery.Tables.get(projectId, datasetId, tables[i].tableReference.tableId);

    var schema = table.schema.fields;
    var columns = new Array(schema.length);
    for (var j = 0; j < schema.length; j++) {
      columns[j] = [j+1, schema[j].name, schema[j].type, schema[j].mode, schema[j].description];
    }
    table_info[i] = [[table.tableReference.datasetId, table.tableReference.tableId, table.description, table.location], columns]
  }
  table_info.length = table_cnt;
  return table_info;
}
（省略）

また、取得したテーブル情報は「BiqQuery Table DDL」という名前のスプレッドシートファイルの「テーブル一覧」シートと各テーブル毎のシートに分けてテーブル定義を出力します。 同名のファイルやシートがあれば上書きし、なければ新規で作成します。 見やすくするため、表には枠線とヘッダー行の色付けも行っています。

• Drive Service ｜ Google Apps Script ドキュメント
• Spreadsheet Service ｜ Google Apps Script ドキュメント

function createDDL() {
（省略）
  var name_book = 'BiqQuery Table DDL';
（省略）
    var spreadsheet = get_book(name_book);
    var sheet = get_sheet('テーブル一覧', spreadsheet);
    sheet.clear()
    sheet.appendRow(['データセット', 'テーブル', '説明', 'ロケーション']);
    sheet.getRange(2, 1, tables.length, tables[0].length).setValues(tables);
    sheet.getRange(1, 1, 1, tables[0].length).setBackground("#FFFFCC");
    sheet.getRange(1, 1, tables.length+1, tables[0].length).setBorder(true, true, true, true, true, true);
    for (var i = 0; i < tables.length; i++) {
      Logger.log('column: %s', columns[i]);
      sheet = get_sheet(tables[i][1], spreadsheet);
      sheet.clear()
      sheet.appendRow(['#', 'カラム名', 'データ型', 'モード', 'description']);
      sheet.getRange(2, 1, columns[i].length, columns[i][0].length).setValues(columns[i]);
      sheet.getRange(1, 1, 1, columns[i][0].length).setBackground("#FFFFCC");
      sheet.getRange(1, 1, columns[i].length+1, columns[i][0].length).setBorder(true, true, true, true, true, true);
    }
（省略）
}

function get_book(name) {
  var files = DriveApp.getFilesByName(name);
  if(files.hasNext()) {
    return SpreadsheetApp.openById(files.next().getId());
  }
  return SpreadsheetApp.create(name);
}

function get_sheet(name, spreadsheet){
  var sheet = spreadsheet.getSheetByName(name)
  if(sheet)
    return sheet

  sheet = spreadsheet.getActiveSheet();
  if(sheet.getName() != 'シート1') {
    sheet = spreadsheet.insertSheet();
  }
  sheet.setName(name);
  return sheet;
}

先ほど同様、Apps Script のエディタに入力して「実行」します。

正常に実行できたようなので、スプレッドシートが出力されているか確認してみます。

見やすくするために一部セルの幅を調整しましたが、期待通り、テーブル一覧と各テーブルのテーブル定義が出力できました！

なお、Apps Script ではブラウザ上でデバッグも可能です。 エラーが発生した場合、別途環境準備などの手間なくステップ実行で変数を確認しながら修正できるので便利ですね。

Step3：テーブル定義書作成スクリプトをスケジュール実行

BigQuery を使っていると、テーブルの追加や更新など、ドキュメントと実体がかけ離れていくことも多々あるかと思います。 とはいえ、テーブルを作成・更新するたびにドキュメントも合わせて更新するのは手間がかかりますし、更新を忘れてしまうこともあると思います。 テーブル定義書作成・更新スクリプトを定期的に自動実行して、ドキュメントのメンテナンスはスクリプトにお任せできたら便利ではないでしょうか？

先ほど作成したテーブル定義作成スクリプトを、スケジュール実行する設定をしてみます。

スケジュール実行結果を確認するために、BigQuery にはテーブルを１つ追加しました。

Apps Script 画面左のメニューから「トリガー」を選択し、トリガー画面右下に表示されている「トリガーを追加」ボタンをクリックします。

トリガー追加ダイアログ「イベントのソースを選択」項目で「時間主導型」を選択し、とりあえず5 分おきに実行するようプルダウンから各項目を選択し「保存」します。

トリガーが追加されたのを確認して、しばらく待ちます。

スケジュール実行が行われたどうかは、Apps Script 画面左の「実行数」メニューまたはトリガー名右端メニューの「実行数」から、実行数画面に遷移すれば確認できます。

実行数画面では、スクリプトが２回自動実行済みであることが確認できました。

スプレッドシートにも新しいテーブルが追加されれているか確認してみます。

期待通り、テーブル一覧に新しいテーブルが追加され、テーブル定義のシートも追加されていることが確認できました。

まとめ（所感）

Google Apps Script にはまったく触ったことがなく実行方法も知りませんでしたが、ブラウザから簡単に実行することができました。 実行するスクリプトはサンプルコードもあり、JavaScript ベースなので、プログラムを書いたことがない方にもとっつきやすいのではないかと思います。

BigQuery に接続するための「サービス」の追加やスケジュール実行設定も画面 UI から直感的に操作できましたし、もし何か分からないことが出てきた場合には、Web 検索すればいろいろな方が書いたたくさんの記事を見つけることもできます。

さすが Google さんのサービスなだけあって、Apps Script からは BigQuery 以外にも Gmail や YouTube など、様々な API も利用できるようです。 工夫次第で他にもいろいろ楽しいものが作れそうです！

BigQuery と合わせて Apps Script を使うことで、業務もより効率的になるのではないかと思いました！

参考

• BigQueryサービス ｜ Google Apps Script ドキュメント
• Drive Service ｜ Google Apps Script ドキュメント
• Spreadsheet Service ｜ Google Apps Script ドキュメント
• apps-script-samples/advanced/bigquery.gs ｜ GitHub
• BigQuery API ｜ BigQuery ドキュメント