BigQueryとスプレッドシートの連携でよくtimeoutするのでGASでどうにかしてみた

2348 ワード
pm GoogleAppsScript BigQuery gas GoogleAppsScript テキストリンク

目的

  • スプレッドシートとBigQueryの連携は便利だが、期間が長いなど重いQueryはタイムアウトしてしまうことがある
  • そこでBigQueryでは最新1日分のみのデータを取得し、取得したデータをスプレッドシートに蓄積する形で実現する

大まかな流れ

1.BigQueryで前日分のみデータを取得
2.取得したデータをコピー
3.コピーしたデータを別シートの既存数値の下に貼り付ける
4.上記の流れをトリガーで毎日更新するように設定

GAS詳細

function copy(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet_copyFrom = ss.getSheetByName('シート1'); //コピー元のシートを指定

　// コピー範囲取得
  var lr = sheet_copyFrom.getLastRow();
  var lc = sheet_copyFrom.getLastColumn();
  var copyRange = sheet_copyFrom.getRange(1,1,lr,lc); //取得開始位置を指定（1.1）=A1,(2,1）=A2となる。開始位置のみ指定したら、それ以降を全て自動で取得する。

  var sheet_copyTo = ss.getSheetByName('シート2'); //コピー先のシートを指定
  var lastRow = sheet_copyTo.getLastRow();
  var lastRow1 = lastRow + 1;

　// コピー範囲の値を取得し、対象範囲へ転記
　var copyValues = copyRange.getValues();
  sheet_copyTo.getRange(lastRow1,1,lr,lc).setValues(copyValues);

}

流れ

1.BigQueryと連携して前日分のみのデータを取得

2.GASを記載する

以下3点を変更する
- コピー元のシート名
- コピー開始位置
- ペーストするシート名

// コピー開始位置は取得開始位置を指定。（1.1）=A1,(2,1）=A2となる。開始位置のみ指定しそれ以降を全て取得する。
var copyRange = sheet_copyFrom.getRange(1,1,lr,lc);

3.この値がシート2の一番下にコピペされていることを確認する

4.トリガー設定をする

  • 割愛

おわりに

  • GASが使えるとコスト削減にもなりますね