King of Time APIをGASで利用して勤怠データを取得する方法

exports.kotProxy = async (req, res) => {
  try {
    const KOT_TOKEN = 'YOUR_KING_OF_TIME_API_TOKEN';
    const base = 'https://api.kingtime.jp/v1.0';
    const path = req.query.path || '/employees';
    const url = new URL(base + path);

    for (const [k, v] of Object.entries(req.query)) {
      if (k !== 'path' && v != null) url.searchParams.set(k, v);
    }

    const r = await fetch(url.toString(), {
      method: 'GET',
      headers: { Authorization: `Bearer ${KOT_TOKEN}` }
    });

    const ab = await r.arrayBuffer();
    const text = new TextDecoder('utf-8').decode(new Uint8Array(ab));

    res.status(r.status)
       .set('Content-Type', 'application/json; charset=utf-8')
       .send(text);
  } catch (e) {
    res.status(500).send(`Proxy error: ${e}`);
  }
};

const API_PROXY_URL = 'https://kot-api-proxy-xxxxxx.a.run.app'; // Cloud RunのURL

function fetchDailyAttendance() {
  const from = '2025-10-01';
  const to   = '2025-10-07';
  const url  = `${API_PROXY_URL}?path=/daily-workings&from=${from}&to=${to}`;
  const res  = UrlFetchApp.fetch(url);
  const data = JSON.parse(res.getContentText('UTF-8'));

  const header = [
    'date', 'employeeKey', 'divisionCode', 'divisionName',
    'isError', 'assigned', 'unassigned', 'overtime',
    'lateNight', 'breakTime', 'totalWork',
    'telework(min)', 'overtime(min)', 'scheduled(min)', 'absence(min)', 'outing(min)'
  ];

  const CUSTOM_MAP = {
    '0001': 'telework(min)',
    '0002': 'overtime(min)',
    '0003': 'scheduled(min)',
    '0004': 'absence(min)',
    '0005': 'outing(min)'
  };

  const rows = [header];

  data.forEach(day => {
    (day.dailyWorkings || []).forEach(d => {
      const custom = {};
      (d.customDailyWorkings || []).forEach(c => {
        const name = CUSTOM_MAP[c.code];
        if (name) custom[name] = c.calculationResult || 0;
      });
      rows.push([
        d.date || day.date, d.employeeKey,
        d.workPlaceDivisionCode, d.workPlaceDivisionName,
        d.isError === true, d.assigned, d.unassigned,
        d.overtime, d.lateNight, d.breakTime, d.totalWork,
        custom['telework(min)'] || 0,
        custom['overtime(min)'] || 0,
        custom['scheduled(min)'] || 0,
        custom['absence(min)'] || 0,
        custom['outing(min)'] || 0
      ]);
    });
  });

  const sheetName = 'AttendanceData';
  const sh = SpreadsheetApp.getActive().getSheetByName(sheetName)
    || SpreadsheetApp.getActive().insertSheet(sheetName);
  sh.clearContents();
  sh.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
  SpreadsheetApp.getActive().toast(`データ出力完了: ${rows.length - 1}件`);
}