Metabase x GAS 俺的メモ

//プロパティを利用する(旧エディタで手動入力)
const scriptProp = PropertiesService.getScriptProperties();
//metabase接続アカウント・URL情報
const BASE_URL = scriptProp.getProperty("BASE_URL");
const USER_NAME = scriptProp.getProperty("USER_NAME");
const PASSWORD = scriptProp.getProperty("PASSWORD");

function metabaseToSpreadsheet() {
  //取得したいIDと反映するシートをここで記述して渡す(ここは作る内容によって変更する)
  //欲しい情報が都度違うので現状手動で作っているが、スプレッドシートから入力できるようにしてもよさげ
  //https://metabase.xxx.xxx/question/XXX のXXXを入れる
  const questionList = [{
    id: 1,
    sheetName: "シート1",
    param: 1,
  },];

  //metabase認証
  const token = fetchToken();

  //metabaseからデータを取得してスプレッドシートに反映する
  questionList.map((question) => {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
      question.sheetName
    );
    //存在しない場合、シートを作成
    if (!sheet) createSheet(question.sheetName);
    //metabaseから情報を取得
    const response = fetchQuestion(token, question.id, question.param);
    //取得した情報をスプレッドシートに展開する
    const statusCode = response.getResponseCode();
    const result =
      statusCode == 200 || statusCode == 201 ?
        fillSheet(response, question.sheetName) : {
          success: false,
          error: "データの取得に失敗しました",
        };
    console.log(`
      id:${question.id}(${question.sheetName})
      success:${result.success}
      status:${statusCode}
      error:${result.error}
    `);
  });
}

//metabaseのセッション切れや初回接続時 tokenを返す
function fetchToken() {
  const sessionUrl = BASE_URL + "api/session";
  const options = {
    method: "post",
    headers: {
      "Content-Type": "application/json",
    },
    payload: JSON.stringify({
      username: USER_NAME,
      password: PASSWORD,
    }),
  };

  let response;
  try {
    response = UrlFetchApp.fetch(sessionUrl, options);
  } catch (e) {
    sendSlack(e)
    throw e;
  }
  const token = JSON.parse(response).id;
  return token;
}

//metabaseからデータを取得してレスポンスを返す
function fetchQuestion(token, id, param) {
  //※jsonで欲しい場合は/csvを/jsonにするとjson形式で取得可能
  questionUrl =
    BASE_URL + "api/card/" + id + "/query/csv?parameters=" + getParam(param);
  console.log("接続先:" + questionUrl);
  const options = {
    method: "post",
    headers: {
      "Content-Type": "application/json",
      "X-Metabase-Session": token,
    },
    muteHttpExceptions: true,
  };

  let response;
  try {
    response = UrlFetchApp.fetch(questionUrl, options);
  } catch (e) {
    return {
      success: false,
      error: e,
    };
  }
  return response;
}

//取得したmetabaseのデータを指定のシートに展開する
function fillSheet(response, name) {
  const values = Utilities.parseCsv(response.getContentText());
  try {
    sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
    const beforeMemberCount = sheet.getLastRow();
    sheet.clear({
      contentsOnly: true,
    });
    sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
    const afterMemberCount = sheet.getLastRow();
    count = afterMemberCount - beforeMemberCount
    if (count !== 0) {
      let body = "";
      for (let i = 0; i < count; i++) {
        body = `${body}\n${values[values.length - 1 - i]}`;
      }
      sumsup = ":+1:"

      sendSlack(`${count}件 会員登録が増えました！${sumsup.repeat(count)} 【計${afterMemberCount - 1}件】\n${body}`);
    }
    return {
      success: true,
    };
  } catch (e) {
    console.log(e);
    return {
      success: false,
      error: e,
    };
  }
}

function getParam(param) {
  const template = [{
    target: ["variable", ["template-tag", "client_id"]],
    type: "category",
    value: param,
  },];
  return encodeURI(JSON.stringify(template));
}

//起動時にメニューを手動項目で追加する
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("Metabase")
    .addItem("手動更新", "metabaseToSpreadsheet")
    .addToUi();
  //必要があれば起動時に取得するようにも可
  //metabaseToSpreadsheet();
}

//シートが存在しない場合は作成する
function createSheet(name) {
  //コンテナバインドで紐付いたスプレッドシートを読み込み
  const mySheet = SpreadsheetApp.getActiveSpreadsheet();
  //スプレッドシートに新しいシートを追加挿入し、アクティブ化
  const newSheet = mySheet.insertSheet().activate();
  newSheet.setName(name);
  mySheet.moveActiveSheet(1);
}

function sendSlack(slackText) {
  var webHookUrl = "https://hooks.slack.com/services/xxxx/xxxx/xxxx";

  var jsonData = {
    text: "<!subteam^xxxxx>\n" + slackText,
    link_names: 1,
    username: "Bot",
  };
  var payload = JSON.stringify(jsonData);

  var options = {
    method: "post",
    contentType: "application/json",
    payload: payload,
    muteHttpExceptions: true,
  };

  UrlFetchApp.fetch(webHookUrl, options);
}