Zenn
🦉

Metabase、SpreadSheetを利用して、Facebookのショップカタログの連携を自動化する

2021/01/28に公開
Facebook
GAS
Instagram
metabase
tech

背景

  • Facebook/Instagram用のカタログを追加したい
  • Facebook/Instagram用のカタログの同期方法は以下の通り
    • Eコマースプラットフォームをリンクする
    • または、以下のフォーマットで商品情報を連携、またはアップロードする
      • CSV、TSVまたはXML (RSS/ATOM) 形式で指定
      • スプレッドシートを指定
  • Eコマースプラットフォームを利用してないため、スプレッドシート経由で連携し、自動化して運用負荷を下げたい

システム構成

  • DB -> Metabase -> SpreadSheet -> Facebook

  • サーバーレスで対応したい

  • MetabaseからSpreadSheetの連携を自動化したい

DB -> Metabase

Metabase -> SpreadSheet (Metabase API)

  • Metabase APIを使用する。curlで動作確認をしてみる
  • Session Idを取得(14日できれる)
curl -X POST \                             
  -H "Content-Type: application/json" \
  -d '{"username": "user@example.com", "password": "pass"}' \
  https://metabase.example.com/api/session
  • Session Idから、カタログを取得
curl -X POST \                             
  -H "Content-Type: application/json" \
  -H "X-Metabase-Session: {Session-Id}" \
  https://metabase.example.com/api/card/22/query/csv

Metabase -> SpreadSheet (GAS)

  • Metabase APIをGoogle Apps Script(GAS)から呼ぶ
  • Session-Idを取得してtableシートに保存
function importMetabaseSessionId() {
  // Load Session-Id
  var headers = {
    'Content-Type': "application/json"
  };
  var options = {
     "method" : "post",
     "headers" : headers,
     "payload" : "{\"username\": \"user@example.com\", \"password\": \"pass\"}",
     "muteHttpExceptions": true
  };
  const url = "https://metabase.example.com/api/session";
  var jsonContent = UrlFetchApp.fetch(url, options).getContentText();
  var jsonData = Utilities.jsonParse(jsonContent);
  var id = jsonData["id"]
  // Save Session-Id
  const sheetName = "table"
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.getRange(1, 1).setValue(id);
}
  • catalogシートをクリア
function clearAllCells() {
  const sheetName = "catalog"
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  sheet.getRange('A:L').clearContent();
}
  • カタログを取得して保存
function importFacebookCatalogFromMetabase() {
  // Load Saved Session-Id
  const idSheetName = "table";
  var tableSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(idSheetName);
  var id = tableSheet.getRange(1, 1).getValue();
  
  // Load CSV from Metabase
  var headers = {
    'Content-Type': "application/json",
    'X-Metabase-Session': id
  };
  var options = {
     "method" : "post",
     "headers" : headers,
     "muteHttpExceptions": true
  };
  const csvUrl = "https://metabase.example.com/api/card/２２/query/csv";
  var csvContent = UrlFetchApp.fetch(csvUrl, options).getContentText();
  var csvData = Utilities.parseCsv(csvContent);

  // Write CSV to sheet
  const sheetName = "catalog";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

Metabase -> SpreadSheet (Securityの考慮)

  • 問題点
    • FacebookからFetchするために、ワークブックのURLを知ってれば誰でも閲覧可にする必要がある
  • 方針
    • 2つのワークブックを作成、データをコピーする
      • GASでデータ取得用ワークブック(限定公開、コピー禁止)
      • Facebook連携用ワークブック(誰でも閲覧可)
function copyCatalogToWorkBookForSync() {
  const sheetName = "catalog";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)

  const outputWorkBookId = "{WorkBook-Id}"
  var outputWorkBook = SpreadsheetApp.openById(outputWorkBookId)

  // Delete same name sheet
  var deleteSheet = outputWorkBook.getSheetByName(sheetName)
  if (deleteSheet) {
    outputWorkBook.deleteSheet(deleteSheet)
  }

  // Copy sheet for syncing to Facebook
  var copiedSheet = sheet.copyTo(outputWorkBook)
  copiedSheet.setName(sheetName)

  // Move copied sheet to first position
  outputWorkBook.setActiveSheet(copiedSheet)
  outputWorkBook.moveActiveSheet(1)
}

GASを定期実行する

  • SessionIdの取得

  • カタログ取得、12時間毎

Facebook側の設定

  • Fetch連携、12時間毎

まとめ

  • FacebookのShop Catalogの連携をMetabaseとSpreadSheet、GASを使って自動化した

Discussion