Edited at

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


はじめに


  • ちょっとした数値管理など、BI作るまででもないようなデータ取得をちゃちゃっとやって何人かで共有したいみたいなことってありませんか?

  • そんなときに、GoogleスプレッドシートとBigQueryをうまく活用すると簡単に自動化できます

  • 非エンジニアがバッチ処理やろうとすると意外とめんどくさい


前提


大まかな流れ


  1. スプレッドシートからBigQueryにQueryを投げる

  2. その作業をマクロで定期的に実行する

  3. Query結果をグラフにする


つまづくポイント

1.Query結果をそのままグラフにすると時系列順になってくれない

→ Query結果を任意順にソートする(ORDER BY dtを最後に付けることが多いです)

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


詳細手順


1.BigQueryとのコネクト


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

image.png


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

スクリーンショット 2019-05-29 12.49.42.png


1-3.Queryを書く

image.png


1-4.結果を挿入する

スクリーンショット 2019-05-29 12.54.36.png


2.マクロの設定方法


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

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

image.png


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

スクリーンショット 2019-05-29 13.59.42.png


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

スクリーンショット 2019-05-29 14.01.40.png


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

スクリーンショット 2019-05-29 14.02.00.png


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

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

image.png


3.グラフを作る

スクリーンショット 2019-05-30 9.08.35.png


最後に


  • 毎日やるような作業はどんどん自動化して楽をしていきましょう

  • 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();
};