【作業自動化】MetabaseのAPIのかゆい所に手が届く!

目次

はじめに

僕が現在勤めている会社では基幹のデータ分析にMetabaseを使っています。(アプリはバックエンドRails、フロントVueで作られています。)
しかし、管理職はデータ閲覧には結局スプレットシートで加工したり数値を見たいとのことで、本番DB→metabase→スプレットシートとデータを流しています。(あるあるなのかな??)

結局スプレットシート便利ですよね・・・笑

僕はデータ周りを加工したり分析したりする業務に付いている手前、毎日そのスプレットシートを更新しなくてはいけません
ただ、スプレットシートに毎日新しいデータを持ってくる手作業が嫌で嫌で仕方なく、なんとか自動化したいと思っていました。
(毎日同じことするのってほんと嫌なんです。手が震えてくるくらい。総務系の方本当に尊敬します・・・!)

自動化にあたり、Metabase APIのドキュメントにも詳しくは載っていなかったり、日本語の参考記事が見つからない部分があったので、かなり難航しましたが、最終的にクリアしました。

結構しんどかったので、先駆者(笑)として記録に残します。

2日くらい何しても音沙汰がなく、イライラしては猫に甘える日々を送っていましたね。。。

もし何処かの会社でmetabaseからスプレットシートにデータを定期的に落としたいと思っている方がいたら刺さりますように。

環境

使用言語/ツールバージョン
Metabasev0.36.4
GAS(Google Apps Script)V8

参考記事・文献

基本的なAPIの使い方はMetabase API documentを見れば大体のことは載っています。
英語が苦手な人はGoogle翻訳を!

また、Metabase の API を実行するというQiitaの記事も大変参考になりました!
とてもわかりやすかったのにLGTMが少なくて僕は怒っています!笑
個人的にはかゆい所に手が届かない感じで、その補足をこの記事でできればと思っています。

そもそものMetabaseとはなんぞや?はこちらの記事がとても参考になると思うのでご覧ください!

また、Google Apps Script (以降GAS)でコードを書いていくのでGoogle Apps Script documentも見ます。

一応GASを書いていくにあたり、一応Google Apps Script完全入門も買って読みました。
日本語の方が圧倒的に頭に入りやすいし具体的なコードまで載っているので最初は重宝していましたが、標準組み込み関数の使い方はやっぱりdocumentの方が充実しています。

また、正式なGASのコーディング規約はGoogleから出ていないようだったのでGoogle Apps Scriptコーディングガイドライン【随時更新】を参考にさせていただきました!

また、僕が実際に現場で作成したコードはエラーが起きたら例外処理でslackで通知を送るようにしているのですが、

  • slackや例外処理まで記述していると分量がえげつないことになる。
  • 実際のデータによって例外は変わる

かなと思って一旦省いています。

自動化までのロードマップ

まず、Metabaseに存在するデータをスプレットシートに反映させるまでのロードマップを作成します!

前提

そもそも会社にMetabaseが導入されており、スプレットシートに反映させたいQuestion(欲しいデータを取得するためのクエリ)があるものとします!

MetabaseにはQuestionを作る際に検索ワードや日付を入れてデータを限定して取得できるのですが、今回もその機能をそのまま利用したいと思っています。
※この変数を用いたリクエスト方法の解決策が中々見つからなかったです。

ロードマップ

GASでの自動化手順は以下の通りです!

  1. MetabaseのセッションIDを取得
  2. セッションIDを基にMetabase APIを叩く
  3. スプレットシートのセルに値を格納
  4. スプレットシートの定期更新トリガーを設定

全くの未経験でどういうことですかって方向けへの説明なんですが、

イメージとしては図書館(Metabase)に入るための会員カード(セッションID)をもらい、本(データ)を手に入れます。
その後に手持ちのノート(スプレットシート)に本の内容を写し、毎日本の内容が追加されていくのでアルバイトを雇って定期的に本の内容写してもらう(定期実行のトリガーを設定)ということですね!

処理に関して

MetabaseのセッションIDを取得する

Metabaseのアカウント確認

先ほどのロードマップに従い、まずはMetabaseのセッションIDを取得します。

自分のMetabaseアカウントを確認します。

便宜上以下の形だとします。

mail    : sample@example.com
password: metabase

Google認証でアクセスしている時は、一旦Google認証でMetabaseにログインした後にプロフィールの設定から自分のメールアドレスとパスワードを設定しましょう。

また、Google認証でアクセスしている時、以降の処理を行うためには管理者権限が必要です

管理者権限を責任者に問い合わせて貰ってください。
※もらえなかった場合も大丈夫です!対応方法を記述します。

セッションID取得

まず以下の形でGASに記述します。

function myFunction(){

  // あなたのアカウント情報を入力
  let email          = "sample@example.com"
  let password       = "metabase"

  // アクセスするための必要な情報をまとめる  
  let sessionData    = {username: email, password: password};
  let sessionUrl     = 'https://{あなたのMetabaseのURL}/api/session';
  let sessionOptions = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(sessionData)
  };

  // Metabaseにまとめた情報を持ってアクセス
  let metabaseSession        = UrlFetchApp.fetch(sessionUrl, sessionOptions);
  let metabaseSessionContent = metabaseSession.getContentText("UTF-8");
  metabaseSessionContent     = JSON.parse(metabaseSessionContent);
  let metabaseSessionId      = metabaseSessionContent["id"]
}

Metabase API Authenticate your requests with a session tokenを参照すると、シェルで取得する方法が記載されています。

curl -X POST \
  -H "Content-Type: application/json" \
  -d '{"username": "person@metabase.com", "password": "fakepassword"}' \
  http://localhost:3000/api/session

ドキュメントではcurlコマンドでリクエストを送っているのですが、その際にjson形式でかつボディにusernameやpasswordを付与しています。

GASでリクエストを送る際にはGASの組み込みオブジェクトのUrlFetchAppクラスを使います
その際、引数にmethodやcontentTypeを指定しなくてはいけません。
また、リクエストのボディとしてJSON形式の文字列変換しなくてはいけないので、JavaScriptの標準組み込みオブジェクトJSONクラスのstringifyメソッドを使ってJSON文字列に整形し、payloadオプションとして渡します。

リクエストを送って返ってきたら、データをUTF-8形式に変換。次にJSON.parseで解析してJSON形式の文字列として展開します。

デバッグしていただくとわかるのですが、解析した結果以下のような形でセッションIDが格納されています。

# 値は例です。
 {"id":"38f4939c-ad7f-4cbe-ae54-30946daf8593"}

このセッションIDを使ってMetabaseに用意したQuestionにアクセスしていくことになります。

管理者権限がもらえなかった場合

管理者権限がないとセッションIDが返ってきません!
これは僕がGoogle認証でのアクセスだったからかわかりませんが、プロフィールで設定してもセッションIDがリクエストを送っても返ってきませんでした。

これを解決するために、ブラウザのデベロッパー機能を使います。
所謂検証ツールです。

検証ツールを開いて画像のようにNetworkタブを開いておきます。

Google認証でアクセスすると、リクエストが走り以下のような形でデータの流れが分かります。

google authのリクエストの中身を確認すると、以下のようにボディにtokenが入っているのがわかると思うのでこちらをコピーして変数に格納しましょう。

注意
注意点としては、セッションIDは2週間ほどで接続が切れてしまうことです。
リクエストを走らせて新しいセッションIDを取得できないと完全な自動化とは言えないので、なんとか管理者権限を手に入れたいところです・・・!

Metabase APIを叩く

セッションIDを取得できたので、Metabaseに用意したデータを取得するためAPIを叩きます。

Metabase APIはダッシュボードにアクセスしたり、コレクションにアクセスしたりと色々できます。

今回は事前に自分でカスタマイズしたクエリにアクセスし、JSON形式でデータを取得したいのでPOST /api/card/:card-id/query/:export-format
のAPIを叩きます。

format指定

このAPIはexport-formatを指定することで

  • api
  • csv
  • json
  • xlsx

の形式でデータを取得することができます。

card-idの指定

Metabaseで質問を作成すると、URLは以下のような形になります。

https://{あなたのmetabase URL}/question/{任意の値}

URLではquestionの後ろの任意の値がAPIを叩く時にはcard-idになります。

parametersの指定

Metabaseでは変数を利用してクエリを直接変えなくても良いようにする機能があります。

変数をリクエストに入れるときは指定のフォーマットをJSON文字列に変換してクエリパラメータに渡す必要があります。

// 指定のフォーマット
 [
   { "target": ["variable", ["template-tag", "{metabaseの変数名}"]],"type": "category", "value": `${{入れたい値}}`}
 ]

ここが第一の難関でめちゃくちゃ難しかったです・・・!

Metabase APIにはJSON文字列化しろとは書いてあるんですが、指定のフォーマットでなんて書いてなくて探すのに苦労しました。

Note that this expects the parameters as serialized JSON in the ‘parameters’ parameter

parameters value may be nil, or if non-nil, value must be a valid JSON string.

Metabase API

通常のリクエストを送った後のURLは上のような複雑な形ではなく、結構シンプルなクエリパラメータだったので、JSON文字列化ができていないんじゃないかって悩んで時間を浪費。

MetabaseのDiscussionやstackoverflowを読み漁り、検証ツールを開いてリクエストの中身を確認してクリア!

第二の難関としてはドキュメントにはparameterを渡せとしか書いてなかったことですね。
どうやってパラメータを付与すればいいのかを試行錯誤しました。

GASのリクエストのボディに入れたり、JSON文字列化がうまくいっていないのかデバッグしまくったり・・・
最終的に

  1. Discussionに書いてあったようにクエリパラメーターで渡す。
  2. GASだとJSON文字列化したものをさらにencodeURIで整形する必要がある。

ということに気づきクリアしました。

POST /api/card/:card-id/query/:export-formatの罠

POST /api/card/:card-id/query/:export-formatのAPIを叩くと、何故かデータの順番などがぐちゃぐちゃになって返ってきます。

// POST /api/card/:card-id/query/:export-formatで返ってくるデータ
{data:[
  0:{},
  1:{},
  ...
]}


POST /api/card/:card-id/queryのAPIだとデータがこのような形で返ってくるのですが・・・

{data:
  rows:[],
  cols:[],
  native_form:{},
  ...以下略
}

Metabaseの質問の通りにデータを並べたいので、データを整形します。
色々な方法があると思いますが、僕は以下の手順で行いました。

  1. 元々のMetabaseのデータの並び順通りにデータを落としたいので、POST /api/card/:card-id/queryを叩いてカラムのデータを取得し、id情報を付与。
  2. POST /api/card/:card-id/query/:export-formatを叩いて素データを取得する。
  3. idが付与されているカラムデータと素データを紐付け。
  4. id順になるようにソート。

APIを余分に叩く手間とコード実行時間のリソースを使ってしまうのが難点で、順番をそもそもコードに書いておく方がいいんじゃないかと悩みました。

DBの情報が変更されてしまった場合の更新の手間と、僕の後釜の管理者が来た時に仕様を把握したり、変更のミスなどが起きないように配慮した方がいいかなと思い、なるべくコードの修正をしないで済むようにしたいと思ってこのような形を取っています。

持ってくる情報が5列くらいのデータであればカラム用のAPIを叩く必要はないです!
※僕の場合は20列ほどのデータで今後増減する可能性があったためカラム用のAPIを叩いています。

// リクエストを送るための初期設定
const HEADERS      = {
                   "Content-Type": "application/json",
                   "X-Metabase-Session": metabaseSessionId,
                 };

const OPTIONS        = {
                        "method": "POST",
                        "headers": HEADERS,
                        "muteHttpExceptions": true
                        };
// ①カラム情報を取得/扱いやすいようにオブジェクトに加工
let getColumnURL     = "https://{あなたのmetabase URL}/card/{任意の値}/query"
let columnData        = UrlFetchApp.fetch(url, OPTIONS);
content               = columnData.getContentText("UTF-8");
columnData            = JSON.parse(columnData);

// name情報だけ必要なので抽出。
columnData            = columnData.data.cols.map(col => col.name);

// idを付与。
columnData            = columnData.map((col, index) => {return {name: col, id: index+1}});

// ②欲しいデータを取得/扱いやすいようにオブジェクトに加工
let template          = [
                         { "target": ["variable", ["template-tag", "{metabaseの変数名}"]],"type": "category", "value": "{入れたい値}"},
                         { "target": ["variable", ["template-tag", "{metabaseの変数名}"]],"type": "category", "value": "{入れたい値}"}
                        ]
let encodedTemplate   =  encodeURI(JSON.stringify(template));
let endpointUrl       = https://{あなたのmetabase URL}/card/{任意の値} +  
                        "/query/json" +
                        "?parameters=" + encodedTemplate 
let originData        = UrlFetchApp.fetch(endpointUrl, OPTIONS);
originData            = originData.getContentText("UTF-8");
originData            = JSON.parse(originData);

// 取得したデータが配列で返ってくるので、あとで扱いやすいようにオブジェクトにする。
originData            = originData.map((data) => { 
                          return Object.entries(data).map((x) => { 
                            return {name: x[0], value: x[1]}
                          });
                        });

// ③カラム情報と欲しい情報を合致
for(var ds of originData){
  for (var d of ds){
    for (var col of cols_list){
      if(d.name == col.name){
        Object.assign(d,col)
      };
    };
  };
};

// ④id順になるようにソート
let data              = originData.map((d) => {
                          d.sort(function(a, b){
                            if(a.id < b.id) return -1;
                            if(a.id > b.id) return 1;
                            return 0;
                          })
                       });

スプレットシートのセルに値を格納

次に値をスプレットシートに格納します。

まずはスプレットシートをGASの標準組み込み標準オブジェクトのSpreadsheetAppクラスopenById関数を用いて指定します。指定したら返り値がGASの標準組み込み標準オブジェクトのSpreadsheetクラスになっていて、その組み込み関数であるgetSheetByName関数を使ってタブまで特定します。

また、1つ1つのセルに値を入れていくと実行時間オーバーになってしまうので、一気にデータを格納します。
GASの標準組み込み標準オブジェクトのSpreadsheetクラスgetRange関数でセル範囲を指定し、GASの標準組み込みオブジェクトRangeクラスsetValues関数を使うと2次配列をそのままセルに格納してくれます。

let ss = SpreadsheetApp.openById("{スプレットシートのID}")
ss     = ss.getSheetByName("{スプレットシートのタブの名前}")
ss     = ss.getRange({開始列}, {開始行}, {終了列}, {終了行})
ss.setValues({格納したいデータ配列})     

データを格納するときの注意点

Metabaseから取得できるデータは基本的にJSON形式で2次配列になっているのでそのままsetValues関数が使えます。

iddateuseremail
12021/01/01samplesample@example.com
22021/02/01testtest@example.com

データ数に関しての注意点
基本的にデータが増えていくはずで、今回のコードでは上書きされていくのですが、以前のデータの方が多い時は差分のデータが残ってしまいます。
その場合は以前のデータ範囲を取得して、RangeクラスのclearContent関数で消しましょう。

まとめの手順としては

  1. 取得したデータの範囲(列数・行数)を確認
  2. 格納したいスプレットシートを指定
  3. ヘッダーとしてカラム情報を1行目に格納
  4. データのまとまりを2行目以降に格納

とします。

// 前提
// cols_listはカラムデータ
// dataは前出で取得した格納したいデータ

let lastColumn  = cols_list.length
let lastRow     = data.length;   
let columns     = cols_list.map(col => col.name)
let values      = data.map(ds => ds.map(d => d.value))

let targetSheet =  SpreadsheetApp.openById("{スプレットシートのID}").getSheetByName("{スプレットシートのタブの名前}")
targetSheet.getRange(1, 1, 1,      lastColumn).setValues([columns]);
targetSheet.getRange(2, 1, lastRow,lastColumn).setValues(values);

これまでのまとめ

以上をまとめると以下のようなコードになります。

初期設定だとmyFunctionという名前の関数になっていますが、わかりやすいように関数名をautoUpdateと変更します。

function autoUpdate(){

  // あなたのアカウント情報を入力
  let email          = "{Metabaseにログインするためのメールアドレス}"
  let password       = "{Metabaseにログインするためのパスワード}"

  // アクセスするための必要な情報をまとめる  
  let sessionData    = {username: email, password: password};
  let sessionUrl     = 'https://{MetabaseのURL}/api/session';
  let sessionOptions = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(sessionData)
  };

  // Metabaseにまとめた情報を持ってアクセス
  // 管理者権限がない場合は直接 metabaseSessionIdに値を格納する。
  let metabaseSession        = UrlFetchApp.fetch(sessionUrl, sessionOptions);
  let metabaseSessionContent = metabaseSession.getContentText("UTF-8");
  metabaseSessionContent     = JSON.parse(metabaseSessionContent);
  let metabaseSessionId      = metabaseSessionContent["id"]

  // Metabaseのクエリに設定した変数を記述。
  // 変数に値を入れなくてよければvalueを""とする
  let template          = [
                            { "target": ["variable", ["template-tag", "{metabaseの変数名}"]],"type": "category", "value": "{入れたい値}"}
                          ]

  // JSON文字列化 + encode化
  let encodedTemplate   =  encodeURI(JSON.stringify(template));

  // クエリパラメーターに渡す
  let endpointUrl       = https://{あなたのmetabase URL}/card/{任意の値} +  
                        "/query/json" +
                        "?parameters=" + encodedTemplate 

  // リクエストを送るための初期設定
  const HEADERS      = {
                     "Content-Type": "application/json",
                     "X-Metabase-Session": metabaseSessionId,
                   };

  const OPTIONS        = {
                          "method": "POST",
                          "headers": HEADERS,
                          "muteHttpExceptions": true
                          };
  // ①カラム情報を取得/扱いやすいようにオブジェクトに加工
  let getColumnURL     = "https://{あなたのmetabase URL}/card/{任意の値}/query"
  let columnData        = UrlFetchApp.fetch(url, OPTIONS);
  content               = columnData.getContentText("UTF-8");
  columnData            = JSON.parse(columnData);

  // name情報だけ必要なので抽出。
  columnData            = columnData.data.cols.map(col => col.name);

  // idを付与。
  columnData            = columnData.map((col, index) => {return {name: col, id: index+1}});

  // ②欲しいデータを取得/扱いやすいようにオブジェクトに加工
  let template          = [
                           { "target": ["variable", ["template-tag", "{metabaseの変数名}"]],"type": "category", "value": "{入れたい値}"},
                           { "target": ["variable", ["template-tag", "{metabaseの変数名}"]],"type": "category", "value": "{入れたい値}"}
                          ]
  let encodedTemplate   =  encodeURI(JSON.stringify(template));
  let endpointUrl       = https://{あなたのmetabase URL}/card/{任意の値} +  
                          "/query/json" +
                          "?parameters=" + encodedTemplate 
  let originData        = UrlFetchApp.fetch(endpointUrl, OPTIONS);
  originData            = originData.getContentText("UTF-8");
  originData            = JSON.parse(originData);

  // 取得したデータが配列で返ってくるので、あとで扱いやすいようにオブジェクトにする。
  originData            = originData.map((data) => { 
                            return Object.entries(data).map((x) => { 
                              return {name: x[0], value: x[1]}
                            });
                          });

  // ③カラム情報と欲しい情報を合致
  for(var ds of originData){
    for (var d of ds){
      for (var col of cols_list){
        if(d.name == col.name){
          Object.assign(d,col)
        };
      };
    };
  };

  // ④id順になるようにソート
  let data              = originData.map((d) => {
                            d.sort(function(a, b){
                              if(a.id < b.id) return -1;
                              if(a.id > b.id) return 1;
                              return 0;
                            })
                         });

  let lastColumn  = cols_list.length
  let lastRow     = data.length;   
  let columns     = cols_list.map(col => col.name)
  let values      = data.map(ds => ds.map(d => d.value))

  let targetSheet =  SpreadsheetApp.openById("{スプレットシートのID}").getSheetByName("{スプレットシートのタブの名前}")
  targetSheet.getRange(1, 1, 1,      lastColumn).setValues([columns]);
  targetSheet.getRange(2, 1, lastRow,lastColumn).setValues(values);
};

スプレットシートの定期更新トリガーを設定

GASではトリガーを設定することで毎日定期的にコードを実行することができます!
左にあるサイドボードの時計マークをクリックするとトリガー設定画面が出てきます。

右下のトリガーを追加ボタンをクリックして詳細を設定します。

  1. 実行する関数名をautoUpdateで選択
  2. 実行するデプロイをHEAD
  3. イベントのソースを時間帯主導型
  4. 時間ベースのトリガーのタイプを日付ベースに
  5. 時刻を自分の設定したい時間帯に(朝出勤する前に更新されていて欲しいので8~9時で設定しています。)

まとめ

いかがでしたでしょうか!
特殊な事例だと思いますが、Metabaseを使用している方に少しでも見ていただければ幸いです!

実際に現場では再利用できそうなコードは関数にまとめてモジュール化しています。
GASだと全部がグローバル関数/グローバル変数になってしまうため、大規模な実装だとコンフリクトが起きそうで怖いです汗

最近調べていたらGASでもGithubのようにバージョン管理できたりJestでテストができたりするみたいですね。

今後社内でもGASでの運用が続きそうなので、色々勉強したいと思います!

紹介したい書籍

GASを書くにあたり、読んでいた技術書があるので、一応貼っておきます!
JavaScriptの基本さえあれば1〜2日ですぐコードが書けると思うので、手元にあってもいいかと思います。

よかったらシェアしてね!
目次
閉じる