スプレッドシートとBigQueryだけで定期更新するBIを作る方法

はじめに

• ちょっとした数値管理など、BI作るまででもないようなデータ取得をちゃちゃっとやって何人かで共有したいみたいなことってありませんか？
• そんなときに、GoogleスプレッドシートとBigQueryをうまく活用すると簡単に自動化できます
• 非エンジニアがバッチ処理やろうとすると意外とめんどくさい

前提

• G Suite Enterpriseを導入していること
• スプレッドシートからBigQueryが叩けるようになったのは2019年の1月からのようです
  • G Suiteのアップロードブログ

大まかな流れ

1. スプレッドシートからBigQueryにQueryを投げる
2. その作業をマクロで定期的に実行する
3. Query結果をグラフにする

つまづくポイント

1.Query結果をそのままグラフにすると時系列順になってくれない
→　Query結果を任意順にソートする（ORDER BY dtを最後に付けることが多いです）

2.スプレッドシート上でマクロは動くのにトリガーが上手く起動せず定期実行できない
→　別のタブからQueryを実行するタブに遷移するところから記録させる

詳細手順

1.BigQueryとのコネクト

1-1.データ>データコネクト>BigQuery

1-2.プロジェクトを選択してコネクト

1-3.Queryを書く

1-4.結果を挿入する

2.マクロの設定方法

2-1.一度別タブに遷移しマクロの記録を開始

マクロの記録を開始する際は別のタブからデータシートへ遷移するところからはじめる

2-2.データシートのタブに遷移しQueryの更新をして、記録を保存

2-3.スクリプトエディタに遷移

2-4.現在のプロジェクトのトリガーへ遷移

2-5.トリガーを設定する

僕はだいたいこういう設定をします。毎日朝更新。

3.グラフを作る

最後に

• 毎日やるような作業はどんどん自動化して楽をしていきましょう
• QueryとGAS（この場合はGAS書いてもない）で、業務効率化はどんどんできます
• データ見るの大事です

おまけ

こんなスクリプトです。セルの選択などいらない部分も多いと思います。

/** @OnlyCurrentDoc */

function test() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate(); 
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('データシート 1'), true);
  SpreadsheetApp.enableAllDataSourcesExecution();
  spreadsheet.getCurrentCell().getDataSourceTables()[0].refreshData();
};

function test1() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B24').activate();
};

Queryが重くてtimeoutするときはこちらを参考にしてみてください