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

Google Apps Script 入門

#Google Apps Script

#Google BigQuery

#Google Cloud (GCP)

みかみ

2021.02.04

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

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

やりたいこと

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 で１テーブルずつカラム定義などの詳細情報を取得します。

（省略）
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」という名前のスプレッドシートファイルの「テーブル一覧」シートと各テーブル毎のシートに分けてテーブル定義を出力します。同名のファイルやシートがあれば上書きし、なければ新規で作成します。見やすくするため、表には枠線とヘッダー行の色付けも行っています。

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 にはテーブルを１つ追加しました。