経理の二重入力をゼロに。JUST.DB→freee自動連携をGoogle Apps Scriptで月¥0構築

const PROPS = PropertiesService.getScriptProperties();

/** JUST.DB Webhook 受信エンドポイント */
function doPost(e) {
  const startTime = new Date();
  let payload = null;
  let result = { status: 'pending' };

  try {
    payload = JSON.parse(e.postData.contents);
    logToSheet({
      executed_at: startTime, trigger_type: 'webhook', direction: 'push',
      result: 'received', request_summary: summarizePayload(payload),
      response_summary: '', error_detail: ''
    });

    let records = extractRecords(payload);

    // Webhook ペイロードがメタデータのみの場合、recordId から JUST.DB API でフィールド再取得
    if (records.length === 0 && payload.tableName && payload.recordId) {
      const fetched = fetchJustDbRecord(payload.tableName, payload.recordId);
      if (fetched.error) {
        records = [];
      } else {
        records = [fetched.record || fetched];
      }
    }

    const eventType = detectEventType(payload);
    const results = records.map(r => {
      try { return syncPartnerToFreee(r, eventType); }
      catch (err) { return { ok: false, error: err.message }; }
    });

    result = { status: 'ok', processed: results.length, results };

    logToSheet({
      executed_at: new Date(), trigger_type: 'webhook', direction: 'push',
      result: results.every(r => r.ok) ? 'success' : 'partial',
      request_summary: `event=${eventType} records=${records.length}`,
      response_summary: JSON.stringify(results).substring(0, 500),
      error_detail: ''
    });
  } catch (err) {
    result = { status: 'error', message: err.message };
    logToSheet({
      executed_at: new Date(), trigger_type: 'webhook', direction: 'push',
      result: 'error', request_summary: payload ? summarizePayload(payload) : 'parse-failed',
      response_summary: '', error_detail: err.stack || err.message
    });
  }

  return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}

function doGet() {
  return ContentService.createTextOutput(JSON.stringify({
    service: 'JUSTDB-freee Bridge', status: 'alive', now: new Date().toISOString()
  })).setMimeType(ContentService.MimeType.JSON);
}

/** JUST.DB API で recordId から完全なフィールド情報を取得 */
function fetchJustDbRecord(tableName, recordId) {
  const apiKey = PROPS.getProperty('JUSTDB_API_KEY');
  const baseUrl = PROPS.getProperty('JUSTDB_BASE_URL');
  if (!apiKey || !baseUrl) return { error: 'JUSTDB credentials not set' };
  const url = `${baseUrl}/sites/api/services/v1/tables/${tableName}/records/${recordId}`;
  const res = UrlFetchApp.fetch(url, {
    headers: { Authorization: `Bearer ${apiKey}` }, muteHttpExceptions: true
  });
  if (res.getResponseCode() !== 200) return { error: `HTTP ${res.getResponseCode()}` };
  return JSON.parse(res.getContentText());
}

/** 取引先を freee に登録 */
function syncPartnerToFreee(record, eventType) {
  const accessToken = PROPS.getProperty('FREEE_ACCESS_TOKEN');
  const companyId = Number(PROPS.getProperty('FREEE_COMPANY_ID'));
  if (!accessToken || !companyId) return { ok: false, error: 'freee credentials not set' };
  if (eventType === 'deleted') return { ok: true, skipped: 'delete event ignored' };

  const code = pickField(record, ['取引先コード', 'partner_code', 'code']);
  const name = pickField(record, ['取引先名', 'name']);
  if (!code || !name) return { ok: false, error: 'partner_code or name is missing' };

  const body = {
    company_id: companyId, name: name,
    name_kana: pickField(record, ['取引先名カナ', 'name_kana']) || undefined,
    available: pickField(record, ['有効フラグ', 'is_active']) !== false
  };

  const res = UrlFetchApp.fetch(`https://api.freee.co.jp/api/1/partners`, {
    method: 'post', contentType: 'application/json',
    headers: { Authorization: `Bearer ${accessToken}` },
    payload: JSON.stringify(body), muteHttpExceptions: true
  });

  const status = res.getResponseCode();
  if (status >= 200 && status < 300) {
    const respBody = JSON.parse(res.getContentText());
    return { ok: true, freee_id: respBody.partner?.id, code, name };
  }
  return { ok: false, status, body: res.getContentText().substring(0, 300) };
}

function detectEventType(p) { return p.eventType || p.event || p.changeType || 'unknown'; }
function extractRecords(p) { return p.records || (p.record ? [p.record] : (p.data || [])); }
function summarizePayload(p) {
  const j = JSON.stringify(p);
  return j.length > 2000 ? j.substring(0, 2000) + '...' : j;
}
function pickField(record, keys) {
  for (const k of keys) {
    if (record[k] !== undefined) return record[k];
    if (record.fields && record.fields[k] !== undefined) return record.fields[k];
  }
  return undefined;
}

/** ログ用 Sheets 自動作成 */
function setupProperties() {
  let id = PROPS.getProperty('LOG_SHEET_ID');
  if (!id) {
    const ss = SpreadsheetApp.create('JUSTDB-freee Bridge - 連携ログ');
    id = ss.getId();
    PROPS.setProperty('LOG_SHEET_ID', id);
    const sh = ss.getActiveSheet();
    sh.setName('sync_logs');
    sh.appendRow(['executed_at', 'trigger_type', 'direction', 'result',
      'request_summary', 'response_summary', 'error_detail']);
    sh.setFrozenRows(1);
    console.log('Created log spreadsheet:', `https://docs.google.com/spreadsheets/d/${id}`);
  }
}

function logToSheet(entry) {
  const id = PROPS.getProperty('LOG_SHEET_ID');
  if (!id) return;
  SpreadsheetApp.openById(id).getSheetByName('sync_logs').appendRow([
    Utilities.formatDate(entry.executed_at, 'Asia/Tokyo', 'yyyy-MM-dd HH:mm:ss'),
    entry.trigger_type, entry.direction, entry.result,
    entry.request_summary, entry.response_summary, entry.error_detail
  ]);
}

function pullPartnersFromFreee() {
  const token = PROPS.getProperty('FREEE_ACCESS_TOKEN');
  const companyId = Number(PROPS.getProperty('FREEE_COMPANY_ID'));
  const url = `https://api.freee.co.jp/api/1/partners?company_id=${companyId}&limit=100`;
  const res = UrlFetchApp.fetch(url, { headers: { Authorization: `Bearer ${token}` } });
  const data = JSON.parse(res.getContentText());
  // ここではリスト取得のみ。JUST.DB 側のレコード更新は読者の業務要件に合わせて
  // freee 連携 ID（field_xxxxxxxxxx）で照合して PUT する処理を追加してください。
  return data.partners.length;
}