Google Spreadsheet/GAS で BigQueryのテーブル一覧情報を抜き出す

この記事の趣旨

Google Spreadsheetから呼べるGASで、BigQueryのテーブルメタ情報を引き出し、一覧表示します。
BigQueryのテーブルメタ情報をGASで扱ってみよう。

準備

Google Spreadsheetを開き、こんな感じでセルを埋めて、シート名を「基本情報」に変更してください。

追加メニューの追加

Spreadsheetに機能の呼び出しトリガーとなるメニュー項目を追加します。Spreadsheetを開き、ツールメニューからスクリプトエディタを選ぶと出てくるエディタで次のコードを入力し、保存ボタンをクリックしてください。

コード.gs

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var menu = [{name:'データセット・テーブル一覧',functionName:'onCreateTableList'}
             ];
  spreadsheet.addMenu('追加機能',menu);
}

するとこんな画面でプロジェクト名を聞いてくるのでなんか入れてください。

次に時計のマークをクリックしてトリガーを追加。


スプレッドシート起動時のトリガーを設定します。

保存をクリックすると、権限とか聞いてくるので良しなにOKしてください。

BigQuery APIの有効化

GASエディタの「リソース」メニューから、「Google の拡張サービス...」を選択し、BigQuery API を有効可してください。

BigQueryを有効可すると、ダイアログの下にGoogle Cloud Platformのダッシュボードで有効可してくれ、とメッセージが表示されるので、リンクをクリックし、対象プロジェクトのダッシュボードに飛び、をクリックしてください。

GoogleのAPIはたくさんあって探すのが大変なので検索しましょう。BigQueryで検索するとだいぶ絞り込まれますので、BiqQueryAPIを選んで有効化してください。

実コード

GASエディタに戻って次のコードを追加します。

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var configsheet = spreadsheet.getSheetByName('基本情報');

// BigQueryに問い合わせ
function onCreateTableList() {
  var projectId = configsheet.getRange(1, 2).getValue();
  var dataSets = BigQuery.Datasets.list(projectId);
  if ( dataSets.datasets.length > 0 ) {
    var tablesArray = [];
    // 表示バッファを埋める
    dataSets.datasets.forEach(function(dataset){
      var cols = [];
      cols.push(dataset.datasetReference.datasetId);
      cols.push(dataset.location);
      cols.push("");
      cols.push("");
      cols.push("");
      tablesArray.push(cols);
      var tables = BigQuery.Tables.list(projectId, dataset.datasetReference.datasetId);
      tables.tables.forEach(function(table){
        var tableDetail = BigQuery.Tables.get(projectId, 
                             dataset.datasetReference.datasetId,
                             table.tableReference.tableId);
        var cols = [];

        var date = new Date();
        date.setTime(table.creationTime);
        cols.push("");
        cols.push(tableDetail.tableReference.tableId);
        cols.push(tableDetail.type);
        cols.push(date.toString());
        cols.push(tableDetail.numBytes);
        tablesArray.push(cols);
      });
    });

    // 表示する
    configsheet.getRange(3, 1, tablesArray.length, 5).setValues(tablesArray);
  }
}

シートに戻ってリロードしてください。メニューに「追加機能」が追加されているはずです。B1セルにGCPプロジェクトIDを入れて、データセット・テーブル一覧を選んで動かしてみましょう。

許可が必要、とか聞いてきたら「続行」で、許可を与えてあげてください。

キモなど

• 表示バッファを用意して埋めてから一気にシートに書くとか。スピードが段違い
• Project.Dataset.table の関係性