BigQueryのデータを定期的にスプレッドシートにエクスポートするくん

4345 ワード
GoogleSpreadSheet スプレットシート BigQuery GoogleAppsScript gas GoogleAppsScript テキストリンク

これはなに

BigQueryを使っている方なら、定期的に決められたクエリを流してデータを取得したいみたいな運用を経験したことある人は多いと思います。
かくゆう私もそのひとりなわけですが、Google様のお力を使ってこの作業をなんとか自動化してみることにしたのでまとめる。

やり方

①スプレッドシートをBigQueryに接続
②スプレッドシートからBigQueryのクエリを実行してデータを取得
③マクロとGASで自動化の設定

①スプレッドシートをBigQueryに接続

1. スプレッドシートを起動 -> データコネクタ -> BigQueryに接続

②スプレッドシートからBigQueryのクエリを実行してデータを取得

  1. データを取得するprojectを選択する
  2. BigQueryで使っているクエリを書く

3. データシートというシートができあがる

③マクロとGASで自動化の設定

  1. ツール -> マクロ -> マクロを記録

※マクロとはGASで書いたプログラムをスクリプトエディタ側でなく、スプレッドシート画面から実行できるようにすること
参考：https://tanuhack.com/macro/

  1. マクロを保存

  2. GASをいじる
    ツール -> スクリプトエディタ -> GAS（GoogleAppScript）をいじる

  3. GASからトリガーを設定
    時計のマークのところから遷移

    この画面で設定

GASについて

私の場合は、決められたクエリを毎月指定した日に実行して、新しいシートとしてアウトプットしてほしかった。
GASで下記のコードを書いて、トリガーの getqueryresult と createsheet の実行時間をずらして設定した。

// BigQueryを叩く
function getqueryresult() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate(); 
  //クエリをセットしているシート名を指定
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('sheetname'), true); 
  SpreadsheetApp.enableAllDataSourcesExecution();
  spreadsheet.getCurrentCell().getDataSourceTables()[0].refreshData();
}

// シートの名前を前の月にする（yyyy年m月分）
function sheetmonth() {
  //今日の日付を取得
  var today = new Date();
  //1ヶ月後の日付にする
  var thismonth = today.setMonth(today.getMonth() - 1);
  //表示用に整形
  var formatDate = Utilities.formatDate(today, 'JST', 'yyyy年M月分');
  return formatDate;
}

// シートを自動生成する
function createsheet() {
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = source.getSheets()[0];
  // スプレッドシートのidを入力
  var destination = SpreadsheetApp.openById('xxxxxxxxx');
  // 上記で取得したシートを、そのスプレッドシートにコピー
  var month = sheetmonth()
  var copysheet = sheet.copyTo(destination).setName(month);
};

slack通知もしたかったら

上記自動化によって新しいシートが作成されたことをトリガーとして、zapierとか使ってslackに通知したらいいとおもう！
またはGASでslackのAPI叩きに行くのもよし！

補足

最初BigQuery->スプレッドシートへのエクスポートを自動化はできないの？？って思った。
BigQueryってクエリの結果をcsvやスプレッドシートに落とす機能がある。
この機能を使って自動化できればとてもええやんって思ったけど、それはできないっぽかった。