経緯
現在働いている会社では、よくGoogleスプレッドシート(以下スプレッドシート)が使われます。
スプレッドシートはExcelに比べて共同編集がしやすい印象です。
会社では日々KPIを追うために、
毎日DBに対してクエリを叩き、スプレッドシートに貼り付けを行う定常業務がありました。
データが多いため、複数のクエリを実行し、CSV形式で出力し、スプレッドシートに貼り付け...
この作業は毎日時間にしておよそ20分ほどかかっていました。
1ヶ月は平日が20日だとして、20×20で一ヶ月400分。
400分が12ヶ月で年間4800分。時間に換算すると400時間
このような作業は時間の無駄なので自動化しましょう！
GASでDBからスプレッドシートにデータを出力
ということでGASを用いてDBからスプレッドシートに出力する方法を解説します。
DBはMySQLを使用します。
（JDBCを使用するので、PostgreSQLでもSQLServerでもOracleでも出来ると思います。）
まず、新しいスプレッドシートを開き、
「ツール」から「スクリプトエディタ」を選択します。
無題のプロジェクトが立ち上がったら、任意のプロジェクト名を入力しましょう。
ここでは「qiitaプロジェクト」と名付けます。
入力したらOKをクリック！
プロジェクト名の入力が終わったら、赤矢印で指している部分にコードを記述していきます。
GASを記述
記述するコードは以下のコードです。
function get_kpi() {
const server = '①DB_HOST';
const port = ②PORT;
const dbName = '③DB_NAME';
const username = '④USERNAME';
const password = '⑤PASSWORD';
const url = 'jdbc:mysql://'+server+':'+port+'/'+dbName;
const conn = Jdbc.getConnection(url, username, password);
const sheet = SpreadsheetApp.openById('スプレッドシートID').getSheetByName('⑦シート名');
sheet.getRange('⑧クリアしたい範囲').clearContent()
const sql = "⑨実行したいクエリ"
const stmt = conn.createStatement();
const results = stmt.executeQuery(sql);
const metaData = results.getMetaData();
const columnCount = metaData.getColumnCount();
while (results.next()) {
arr=[];
for (var col = 0; col < columnCount; col++) {
arr.push(results.getString(col + 1));
}
sheet.appendRow(arr);
}
results.close();
stmt.close();
conn.close();
}
環境に応じた値の入力
①~⑨まで任意の値を入力して頂きます。
①〜⑤は接続したいDB情報
⑥はURLに記載されているスプレッドシートID
例：https://docs.google.com/spreadsheets/d/（スプレッドシートID）/edit#gid=0
赤矢印で指している部分がスプレッドシートID
⑦はクエリ実行結果を反映したいシート名（デフォルトだとシート1）
画像だと赤枠で囲っている部分がシート名です。
⑧はクリアしたいシート範囲（例：A1:AH1000）
毎日自動更新させる場合、上書きしたい範囲をスクリプト実行時に消す必要があります。
このコードを記述しておけば、自動で消去してくれます。
ちなみにMySQL以外のDBを使用する場合は、
const url = 'jdbc:mysql://'+server+':'+port+'/'+dbName;
のjdbc:mysqlの部分を、
PostgreSQLならjdbc:postgresql
Oracleならjdbc:oracle
SQL Serverならjdbc:sqlserver
に変更して下さい
以上を記入したら、赤枠で囲った部分を実行して下さい
▶このマークを押すと、スクリプトが実行されます。
するとこのような警告が出現するので「許可を確認」をクリックします。
スプレッドシートを確認して下さい。
これでクエリの実行ができ、シート上にクエリの実行結果が随時出力されます。
EXCEPTION:Statement cancelled due to timeout or client request
何やら警告が出て、スクリプトが上手く実行出来ていないようです。
（この警告が出現しない場合は 定期実行の設定 まで飛ばして下さい）
警告文を日本語訳すると
「警告：スクリプトはタイムアウトまたはクライアントリクエストが原因でキャンセルされました」
え？なんのこと？
この警告は、クエリの実行時間が長い場合に出現します。
（だいたいクエリ実行時間1.2sくらい？）
これは新しくリリースされたV8エンジンのバグのようです
（原因に気づくまでにすごく時間がかかりました...orz）
https://issuetracker.google.com/issues/149413841#comment3
ただ、原因が分かれば対処は簡単です。
V8以前のエンジンを使用するように変更すればいいのです。
方法は、
「実行」から、
「Chrome V8を搭載した新しいApps Scriptランタイムを無効にする」を選択します。
選択後、
「Future executions of this project will run with Apps Script Legacy.」
が表示されればOKです。（日本語だと違うかも...）
そうしたらまた、
▶ボタンを押して実行すれば、シートに次々とデータが追加されていくと思います。
これで、GASを用いて、DBからデータを取り出しスプレッドシートに出力することができました。
（一旦完了！）
定期実行の設定
次に定期実行の設定を行います。
この作業を行うことで、
あなたが寝ている間に自動でスクリプトを実行してくれるようになります
まず、下の図で赤矢印が指している時計の吹き出しのようなマークをクリックします。
すると、以下の画面が表示されます。
右下の「＋ トリガーを追加」をクリックし、
以下の画面が表示されたら、
定期実行のタイミングを設定します。
例えば、
毎日朝4時にget_kpi（関数名）を実行したい場合には、
1.「イベントのソースを選択」で、「時間主導型」をクリック。
2.「時間ベースのトリガーのタイプを選択」で、「日付ベースのタイマー」をクリック。
3.「時刻を選択」で、「午前4時〜5時」をクリック。
4.右下の「保存」をクリック
上記の手順を行うと、下の図のようにトリガーが追加されていると思います。
ここまで出来たらあとは寝るだけ
翌朝起きて、シートを開いたら最新のデータが反映されているでしょう！
これであなたの睡眠じかn、作業時間が増えるでしょう！！！
記事をご覧下さりありがとうございます。
この記事が参考になったらぜひLGTMボタンを推して下さい！
あとV8エンジンは出来れば使いたいので、バグが直ったら教えて下さい