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

mail    : sample@example.com
password: metabase

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"]
}

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

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

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

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

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

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

// リクエストを送るための初期設定
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 ss = SpreadsheetApp.openById("{スプレットシートのID}")
ss     = ss.getSheetByName("{スプレットシートのタブの名前}")
ss     = ss.getRange({開始列}, {開始行}, {終了列}, {終了行})
ss.setValues({格納したいデータ配列})     

// 前提
// 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);

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