GASでBigQueryから毎日自動データ取得し、スプレッドシートに反映する

はじめに

このエントリは非エンジニア・GAS初心者によるものです。

毎日BigQueryで複数の決まったクエリを実行して事業数値を集計し、スプレッドシートに反映していたのが地味に負担だったので、GASによる自動化を目指しました。

また、スプレッドシートからBigQueryを呼び出すスクリプトは@satoru_magさんの「BigQueryとスプレッドシートとGASと」のものを使わせていただきました。

それを元に、
・ループ処理で複数のクエリが実行されるスクリプト
・毎日自動で実行して更新するスクリプト
を足したものになります。

普段プログラミングになじみのない方でも再現できる内容になってます。
ぜひ非エンジニアの方もチャレンジしてみてください！

スプレッドシートからBigQueryを呼び出す

こちらの項目は前出の@satoru_magさんの「BigQueryとスプレッドシートとGASと」をご参考ください。

①GASでAPI連携が実行できるように設定をします。
②文中にあるスクリプトを「スクリプトエディタ」にコピペしておきます。

こちらを大元に、以下のループ処理により複数のクエリが実行されるように編集していきます。

ループ処理で複数のクエリを実行する

ではさっそくループ処理が実行されるようにしていきます。

//SQLの結果を出力するシート
︙
︙
var start_column = master_sheet.getRange('C4').getValue();

の部分を以下のように変更します。

//SQLの結果を出力するシート
var sheet_name = ['①','②','③','④','⑤','⑥','⑦','⑧','⑨','⑩','⑪','⑫']
var query = ['B2','B3','B4','B5','B6','B7','B8','B9','B10','B11','B12','B13']
var row = ['C2','C3','C4','C5','C6','C7','C8','C9','C10','C11','C12','C13']
var column= ['D2','D3','D4','D5','D6','D7','D8','D9','D10','D11','D12','D13']

function onRunQuery() {
  for(var k = 0; k < sheet_name.length ; k++){

  var result_sheet = ss.getSheetByName(sheet_name[k]);

  var sql = master_sheet.getRange(query[k]).getValue();
  var start_row = master_sheet.getRange(row[k]).getValue();
  var start_column = master_sheet.getRange(column[k]).getValue();

以下のような意味合いになっています。
・sheet_name　=>　出力先のシート名
・query　=>　実行するクエリが書かれているセル
・row　=>　出力する行が書かれているセル
・column => 　出力する列が書かれているセル

同時にスプレッドシートも以下の画像のように整えておきましょう。
(※シート名、セルの指定はスクリプトの記載と対応させてさえいればもちろん任意のものにしていただいて大丈夫です。)

毎日自動で実行して更新する

こちらは「いつも隣にITのお仕事」の以下2つの記事を参考にさせていただきました。

①Google Apps Script で毎日決まった時刻にスクリプトを実行するトリガー設定
②Google Apps Scriptで使用済みのトリガーを削除する方法

まずは下記を同じファイルの上部か、あるいは別ファイルを作成して記載してください。(※時間は任意に指定)

function setTrigger(){

  const time = new Date();
  time.setHours(08);
  time.setMinutes(00);
  ScriptApp.newTrigger('onRunQuery').timeBased().at(time).create();

}

function delTrigger() {

  const triggers = ScriptApp.getProjectTriggers();
  for(const trigger of triggers){
    if(trigger.getHandlerFunction() == "onRunQuery"){
      ScriptApp.deleteTrigger(trigger);
    }
  }

}

そして

function onRunQuery()

の最初に、「delTrigger();」を記載して呼び出せるようにしましょう。

function onRunQuery() {
  delTrigger();

そしてスクリプトエディタの「トリガー」作成画面で以下の設定を行います。

ここまで完了したら、指定した時刻に更新されるはずです！！

(おまけ)手動でも更新できるようにしたい

別ファイルにて以下のように記述してonOpenの関数を実行してみてください。
スプレッドシートのメニューバーにボタンが誕生します。
更新したいタイミングでボタンを押せば手動でも更新できるようになります。

// メニューバーに追加
function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('更新ボタン')
      .addItem('更新', 'onRunQuery')
      .addToUi();
}

まとめ

いかがでしょうか、正しく実行されましたか？
これできっと毎日の数値更新作業から解放されます！

あとは自動取得したシートを、ダッシュボードで使用しているシートにimportrange関数やvlookup関数などを使って参照してやれば、ダッシュボードが毎日自動で最新数値に更新されるようになります。