구글시트 예약 KPI 집계 방법: 정시율·체류시간 자동 계산

const KPI_CONFIG = {                              // → KPI용 설정 모음
  TZ: 'America/New_York',                        // → 고정 시간대
  APPT_SHEET_NAME: 'APPT_MAIN',                  // → 예약 원본 시트 이름
  KPI_SHEET_NAME: 'KPI_DAILY',                   // → 일일 KPI 시트 이름
  ONTIME_MINUTES_THRESHOLD: 15                   // → 정시 판정 기준(분)
};                                               

// 여기만 본인 시트 구조에 맞게 바꾸세요
const KPI_COL_APPT_DATE = 1;                     // → APPT_MAIN: 예약일(열 A)
const KPI_COL_APPT_TIME = 2;                     // → APPT_MAIN: 예약 시작시간(열 B)
// 도착·완료 시각은 체크인/체크아웃 구현에 맞게 사용
// 아직 APPT_MAIN 에 체크인/체크아웃 열을 만들지 않았다면 그대로 0으로 두고,
// 나중에 열을 추가했을 때 실제 열 번호(1부터)를 넣어 주세요.
const KPI_COL_CHECKIN_AT = 0;                    // → 도착시각 열 번호(없으면 0)
const KPI_COL_CHECKOUT_AT = 0;                   // → 완료시각 열 번호(없으면 0)

const KPI_COL_KPI_DATE = 1;                      // → KPI_DAILY: 기준일(열 A)
const KPI_COL_KPI_TOTAL = 2;                     // → KPI_DAILY: 전체 예약 건수(열 B)
const KPI_COL_KPI_ARRIVED = 3;                   // → KPI_DAILY: 도착 기록 있는 건수(열 C)
const KPI_COL_KPI_ONTIME = 4;                    // → KPI_DAILY: 정시 도착 건수(열 D)
const KPI_COL_KPI_ONTIME_RATE = 5;               // → KPI_DAILY: 정시율 % (열 E)
const KPI_COL_KPI_AVG_DWELL = 6;                 // → KPI_DAILY: 평균 체류시간 분(열 F)
const KPI_COL_KPI_SKIPPED = 7;                   // → KPI_DAILY: 계산 제외 건수(열 G)
const KPI_COL_KPI_CALC_AT = 8;                   // → KPI_DAILY: 계산 시각(열 H)

function computeKpiForDate_(dateObj) {                          // → 특정 일자의 KPI 계산
  if (!(dateObj instanceof Date) || isNaN(dateObj.getTime())) { // → 입력이 유효한 날짜인지 확인
    throw new Error('computeKpiForDate_: 유효한 Date 객체가 아닙니다.'); 
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();             // → 현재 스프레드시트 가져오기
  const apptSheet = ss.getSheetByName(KPI_CONFIG.APPT_SHEET_NAME); // → 예약 시트 찾기
  if (!apptSheet) {                                             // → 시트 없으면 오류
    throw new Error('예약 시트(APPT_MAIN)를 찾을 수 없습니다.'); 
  }

  const targetYmd = Utilities.formatDate(                       // → 기준일을 yyyy-MM-dd 문자열로
    dateObj,
    KPI_CONFIG.TZ,
    'yyyy-MM-dd'
  );                                                            

  const lastRow = apptSheet.getLastRow();                       // → 예약 시트 마지막 행
  if (lastRow < 2) {                                            // → 헤더만 있으면
    const emptyResult = {                                       // → KPI 계산할 데이터 없음
      date: targetYmd,
      total: 0,
      arrived: 0,
      ontime: 0,
      ontimeRate: 0,
      avgDwellMinutes: 0,
      skipped: 0
    };
    saveDailyKpi_(emptyResult);                                 // → 빈 결과도 시트에 반영
    Logger.log(JSON.stringify(emptyResult));                    // → 로그 남기기
    return emptyResult;                                         // → 결과 반환
  }

  const lastCol = apptSheet.getLastColumn();                    // → 마지막 열 번호
  const range = apptSheet.getRange(2, 1, lastRow - 1, lastCol); // → 데이터 범위
  const values = range.getValues();                             // → 2차원 배열로 읽기

  let total = 0;                                                // → 대상 날짜 예약 건수
  let arrived = 0;                                              // → 도착 기록 있는 건수
  let ontime = 0;                                               // → 정시 도착 건수
  let dwellSum = 0;                                             // → 체류시간 합(분)
  let dwellCount = 0;                                           // → 체류시간 계산된 건수
  let skipped = 0;                                              // → 계산에서 제외된 건수

  const tz = KPI_CONFIG.TZ;                                     // → 시간대 상수 사용
  const threshold = KPI_CONFIG.ONTIME_MINUTES_THRESHOLD;        // → 정시 기준(분)

  for (let i = 0; i < values.length; i++) {                     // → 각 예약 행 반복
    const row = values[i];                                      // → 현재 행 데이터
    const apptDateCell = row[KPI_COL_APPT_DATE - 1];            // → 예약일 값

    if (!apptDateCell) {                                        // → 예약일이 비어 있으면
      skipped++;                                                // → 제외 건수 증가
      continue;                                                 // → 다음 행
    }

    let apptYmd;                                                // → 예약일 문자열
    try {
      const apptDateObj = new Date(apptDateCell);               // → 날짜로 변환
      if (isNaN(apptDateObj.getTime())) {                       // → Invalid Date면
        skipped++;                                              // → 제외 건수 증가
        continue;                                               // → 다음 행
      }
      apptYmd = Utilities.formatDate(                           // → 예약일을 문자열로
        apptDateObj,
        tz,
        'yyyy-MM-dd'
      );
    } catch (e) {                                               // → 변환 실패 시
      skipped++;                                                // → 제외 건수 증가
      continue;                                                 // → 다음 행
    }

    if (apptYmd !== targetYmd) {                                // → 대상 날짜가 아니면
      continue;                                                 // → 건너뛰기
    }

    total++;                                                    // → 대상 날짜 예약 건수 증가

    const apptTimeCell = row[KPI_COL_APPT_TIME - 1];            // → 예약 시각 셀
    const checkinCell = KPI_COL_CHECKIN_AT > 0                  // → 도착시각 열 설정
      ? row[KPI_COL_CHECKIN_AT - 1]                             // → 설정된 열이면 읽기
      : null;                                                   // → 아니면 null
    const checkoutCell = KPI_COL_CHECKOUT_AT > 0                // → 완료시각 열 설정
      ? row[KPI_COL_CHECKOUT_AT - 1]                            // → 설정된 열이면 읽기
      : null;                                                   // → 아니면 null

    // 예약·도착 시각이 모두 있는 경우에만 정시/지각 판정
    if (apptTimeCell && checkinCell) {                          // → 두 값 다 있으면
      let apptDateTime;                                         // → 예약 DateTime
      try {
        apptDateTime = new Date(apptDateCell);                  // → 예약일 시각 00:00 기준
        if (apptDateTime instanceof Date && !isNaN(apptDateTime.getTime())) {
          // 예약 시각이 Date 로 들어온 경우와 문자열로 들어온 경우만 단순 처리
          let apptHours = 0;
          let apptMinutes = 0;

          if (apptTimeCell instanceof Date) {
            apptHours = apptTimeCell.getHours();
            apptMinutes = apptTimeCell.getMinutes();
          } else if (typeof apptTimeCell === 'string') {
            const parts = apptTimeCell.split(':');
            if (parts.length >= 2) {
              apptHours = Number(parts[0]);
              apptMinutes = Number(parts[1]);
            }
          }

          if (!Number.isFinite(apptHours) || !Number.isFinite(apptMinutes)) {
            throw new Error('잘못된 예약 시간 값입니다.');
          }

          apptDateTime.setHours(apptHours, apptMinutes, 0, 0);
        } else {
          throw new Error('잘못된 예약 날짜 값입니다.');
        }
      } catch (e) {                                             // → 변환 오류
        skipped++;                                              // → 제외 건수 증가
        continue;                                               // → 다음 행
      }

      const checkinDateTime = new Date(checkinCell);            // → 도착 DateTime
      const ciMs = checkinDateTime.getTime();                   // → 도착 ms

      if (isFinite(apptDateTime.getTime()) &&                   // → 예약 시각 유효
          isFinite(ciMs)) {                                     // → 도착 시각 유효
        arrived++;                                              // → 도착 건수 증가
        const diffMs = ciMs - apptDateTime.getTime();           // → 차이(ms)
        const diffMinutes = diffMs / 1000 / 60;                 // → 차이(분)
        if (Number.isFinite(diffMinutes)) {                     // → 숫자인 경우만
          if (Math.abs(diffMinutes) <= threshold) {             // → 기준 이내면
            ontime++;                                           // → 정시 건수 증가
          }
        } else {                                                // → 숫자가 아니면
          skipped++;                                            // → 제외 건수 증가
        }
      } else {                                                  // → 유효하지 않은 시각
        skipped++;                                              // → 제외 건수 증가
      }
    }

    // 도착·완료 시각이 모두 있는 경우에만 체류시간 계산
    if (checkinCell && checkoutCell) {                          // → 두 값 다 있으면
      const checkinDateTime = new Date(checkinCell);            // → 도착 DateTime
      const checkoutDateTime = new Date(checkoutCell);          // → 완료 DateTime
      const ciMs = checkinDateTime.getTime();                   // → 도착 ms
      const coMs = checkoutDateTime.getTime();                  // → 완료 ms

      if (isFinite(ciMs) && isFinite(coMs) && coMs >= ciMs) {   // → 정상 범위인지
        const dwellMinutes = (coMs - ciMs) / 1000 / 60;         // → 체류시간(분)
        if (Number.isFinite(dwellMinutes)) {                    // → 숫자인 경우
          dwellSum += dwellMinutes;                             // → 합계에 더하기
          dwellCount++;                                         // → 건수 증가
        } else {                                                // → 숫자 아님
          skipped++;                                            // → 제외 건수 증가
        }
      } else {                                                  // → 잘못된 시각
        skipped++;                                              // → 제외 건수 증가
      }
    }
  }

  const ontimeRate = arrived > 0                                // → 정시율 계산
    ? (ontime / arrived) * 100                                  // → % 계산
    : 0;                                                        // → 분모 0이면 0

  const avgDwellMinutes = dwellCount > 0                        // → 평균 체류시간
    ? dwellSum / dwellCount                                     // → 평균 값
    : 0;                                                        // → 분모 0이면 0

  const result = {                                              // → 결과 객체
    date: targetYmd,
    total,
    arrived,
    ontime,
    ontimeRate,
    avgDwellMinutes,
    skipped
  };

  Logger.log(JSON.stringify(result));                           // → 요약 로그 출력
  saveDailyKpi_(result);                                        // → KPI_DAILY 시트에 저장
  return result;                                                // → 결과 반환
}

function saveDailyKpi_(result) {                               // → 일일 KPI 저장
  const ss = SpreadsheetApp.getActiveSpreadsheet();            // → 현재 스프레드시트
  let sheet = ss.getSheetByName(KPI_CONFIG.KPI_SHEET_NAME);    // → KPI_DAILY 시트 찾기
  if (!sheet) {                                                // → 없으면
    sheet = ss.insertSheet(KPI_CONFIG.KPI_SHEET_NAME);         // → 새 시트 만들기
    sheet.getRange(1, KPI_COL_KPI_DATE, 1, KPI_COL_KPI_CALC_AT) // → 헤더 범위
      .setValues([[
        'DATE',                                               // → 기준일
        'TOTAL_APPTS',                                        // → 전체 예약 건수
        'ARRIVED_APPTS',                                      // → 도착 기록 건수
        'ONTIME_APPTS',                                       // → 정시 도착 건수
        'ONTIME_RATE_PCT',                                    // → 정시율(%)
        'AVG_DWELL_MIN',                                      // → 평균 체류시간(분)
        'SKIPPED_APPTS',                                      // → 제외 건수
        'CALCULATED_AT'                                       // → 계산 시각
      ]]);                                                    
  }

  const lastRow = sheet.getLastRow();                          // → 마지막 행
  let targetRow = 0;                                           // → 쓸 행 번호

  if (lastRow >= 2) {                                          // → 데이터가 있으면
    const range = sheet.getRange(2, KPI_COL_KPI_DATE, lastRow - 1, 1); // → 날짜 열 범위
    const values = range.getValues();                          // → 2차원 배열
    for (let i = 0; i < values.length; i++) {                  // → 각 행 검사
      const ymd = values[i][0];                                // → 날짜 값
      if (ymd === result.date) {                               // → 같은 날짜 찾으면
        targetRow = i + 2;                                     // → 실제 행 번호
        break;                                                 // → 반복 종료
      }
    }
  }

  if (!targetRow) {                                            // → 기존 행 없으면
    targetRow = lastRow + 1;                                   // → 새 행 번호
  }

  const now = new Date();                                      // → 현재 시각
  const rowValues = [                                          // → 기록할 값 배열
    result.date,                                               // → 기준일
    result.total,                                              // → 전체 예약 수
    result.arrived,                                            // → 도착 기록 수
    result.ontime,                                             // → 정시 도착 수
    result.ontimeRate,                                         // → 정시율(%)
    result.avgDwellMinutes,                                    // → 평균 체류시간
    result.skipped,                                            // → 제외 건수
    now                                                        // → 계산 시각
  ];

  sheet.getRange(targetRow, KPI_COL_KPI_DATE, 1, rowValues.length) 
    .setValues([rowValues]);                                   // → 행에 쓰기

  // 날짜 문자열과 숫자 값은 시트에서 포맷으로 보정하는 걸 권장합니다.
  // 여기서는 계산 시각 열만 기본적인 날짜-시간 포맷을 설정합니다.
  sheet.getRange(2, KPI_COL_KPI_CALC_AT, sheet.getLastRow() - 1, 1) 
    .setNumberFormat('yyyy-MM-dd HH:mm');                      // → 시각 포맷
}

function KPI_testComputeToday_() {                         // → 오늘 KPI 테스트 함수
  const today = new Date();                               // → 오늘 날짜
  const result = computeKpiForDate_(today);               // → KPI 계산 호출
  Logger.log('KPI_testComputeToday_ 결과: ' +             // → 결과 로그 출력
    JSON.stringify(result));                              
}