【非エンジニアでもできる】GASとredashを組み合わせて集計作業を自動化する方法

概要

redashというデータベースを閲覧するシステムとgoogleが提供するプログラミングサービスのGASを利用して、データベースに保存されているデータをspreadsheetに自動で転記し、それを任意の周期で収集する方法について記述します。

簡単にコピペができるようにするためにGASのスクリプトについても公開します。

前提

私の個人的経験からredashのみしか使ったことがありませんが、Googleが用意する関数のimportdata関数を使うことのできるサービスであればredash以外でも適用可能です。

手順

まずredashで一定周期で収集したいクエリを作成します。

この部分はエンジニアの方やSQLを叩くことのできる人に協力してもらうのが良いでしょう

【Tips】定期実行するために予めredashのRefresh Schedule を設定しておきましょう

Neverと青くなっている部分をクリック

その後任意の実行間隔を設定します(今回は毎日00:15に定期実行するという設定にしています)。

以下の画像の赤枠の部分を順番にクリックしていき、Results in CSV formatの部分のURLをコピーして手元に残しておいてください。

APIのURLを取得したら次にspreadsheetを開きます。

Gooogleが用意するimportadaraという関数を使いredasahの内容をspreadsheetに展開します(importdataはおよそ1時間に1回程度URLの内容を参照しにいくので、一度展開してしまえば、自動で最新のデータに更新されるので、手動で展開し直す必要がありません)。

任意のセルに=importdata("先ほどのURL")と入力すると次のようにspreadsheetにredashの実行結果が返ってきます。

次からはGoogle Apps Scriptを操作します。

以下の画像の手順でGoogle Apps Scriptを開きます。

この{}の間に次のコードを貼り付けます。

var book = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxxxxxxx');
 var sheet1 = book.getSheetByName('シート1');
 var date = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyyMMdd');
 var lastRow = sheet1.getLastRow();
 var lastColumn = sheet1.getLastColumn();
 
 book.insertSheet(date,2);
 var sheet2 = book.getSheetByName(date);
 var newData = sheet1.getRange( 1 , 1 , lastRow , lastColumn ).getValues();
 sheet2.getRange( 1 , 1 , lastRow , lastColumn ).setValues(newData);

貼り付ける時の注意点

・xxxxxxxxxxxxxxxxxxxxxxxxxxには自身が利用するspreadsheetのidを入力する

・redashを展開しているspreadsheetのシート名を「シート1」と定義しているの、それ以外の場合には上記が動かないので必ず書き換えてから利用してください。

・コピペを行うとシングルクオーテーションの部分がGASが認識できない形に変換されていることがあるので、シングルクオーテーションを自身で入力しなおしてもらえると良いかもしれないです(認識できている場合シングルクオーテーションで囲まれている部分が赤くなります)。

spreadsheetのidとは、spreadsheetのURLに記載されているもので/d/と/editに挟まれている部分です。下のURLだと太字になっている部分がspreadsheetのidに相当します。

【https://docs.google.com/spreadsheets/d/1jMMKVZyvPCILl0DLs4JVL2jG7jv9q0J72dX3UlTy5VM/edit#gid=1964073521】

では画像のように▶︎を押して実行してみましょう

実行を押すとgoogleから許可が求められます。

全てを許可して実行を行うようにしてください。

実行するとシートが増えます。

シート名は実行した日付が返るようにしてあります(spreadsheetに日付であると認識されると面倒なので、/や-などの日付形式にならないようにしています)。

定期実行を行うためにはトリガーを設置します。

時計のようなマークを押すと下の画面のようなページになります。

右下の「トリガーを追加」からトリガーを設定していきます。

イベントのソースを選択を「スプレッドシートから」ではなく「時間主導型」に変更します。

「時間ベースのトリガータイプを選択」で任意の間隔での実行を選択するようにします(今回はテスト的に1日ごとに実行するタイプを選択しています)。

これで翌日以降毎日シートを増やしながら、任意の間隔のシートを自動で保存していくようになります。

時刻はredashの定期実行よりも1時間以上遅くしておくと良いでしょう。

redashは指定した時間に実行してくれるのですが、spreadsheetは指定した時間の1時間の間のどこかで実行するという仕組みなので、余裕を持たせると良いでしょう。

定期実行を続け、関数などを組み合わせれば、見出し画像のように経過時間ごととの各ユーザーの推移などが追えるようになります。

よろしければぜひご活用ください！