구글시트 대시보드 만들기 Apps Script: 창고 현황 한눈에 보는 방법

// → 이 편에서만 쓰는 설정 모음. 시트·열 이름은 1~7편에서 만든 그대로다.
const DASHBOARD_CONFIG = {
  SHEET_DASHBOARD: 'DASHBOARD',                 // → 이 편에서 새로 만드는 시트
  SHEET_INBOUND:   'INBOUND',                   // → 1편에서 만든 입고 예정 시트
  SHEET_OUTBOUND:  'OUTBOUND',                  // → 2편에서 만든 출고 이력 시트
  SHEET_STOCK:     'STOCK',                     // → 3편에서 만든 재고 집계 시트
  SHEET_LOADS:     'LOADS',                     // → 7편에서 만든 Load 관리 시트

  COL_INBOUND:  { ETA_DATE: 4, STATUS: 6 },     // → D열 도착예정일, F열 상태
  COL_OUTBOUND: { QTY: 4, TIMESTAMP: 5 },       // → D열 수량, E열 처리시각
  COL_STOCK:    { LOCATION: 1, MODEL: 2, STOCK: 5 },  // → A열 로케이션, B열 모델, E열 재고
  COL_LOADS:    { STATUS: 4 },                  // → D열 상태

  DONE_STATUS: 'PUTAWAY_DONE',                  // → 1편이 적치 완료에 쓰는 값
  OPEN_STATUS: 'OPEN',                          // → 7편이 미마감 Load에 쓰는 값

  ROW_FIRST_KPI: 2,                             // → 지표가 시작하는 행(A2)
  KPI_COUNT: 4,                                 // → 지표 개수
  TOP_N: 10                                     // → 재고 상위 몇 개를 보여줄지
};

// 표가 시작하는 행을 여기 한 곳에서만 계산한다.
// 만드는 함수와 채우는 함수가 각자 계산하면 한 줄씩 어긋나 머리글이 지워진다.
function dashboardRows_() {                     // → 대시보드 행 배치
  const first = DASHBOARD_CONFIG.ROW_FIRST_KPI; // → 지표 첫 행
  const title = first + DASHBOARD_CONFIG.KPI_COUNT + 1;  // → 한 줄 띄우고 재고 표 제목
  return {
    kpiFirst: first,                            // → 지표 첫 행 (A2)
    stockTitle: title,                          // → '재고 상위 10개' 제목 행 (A7)
    stockHeader: title + 1,                     // → 표 머리글 행 (A8)
    stockData: title + 2                        // → 표 데이터 첫 행 (A9)
  };
}

function createDashboardSheet_() {              // → DASHBOARD 시트 만들기·초기화
  const ss = SpreadsheetApp.getActiveSpreadsheet();      // → 현재 스프레드시트
  let sheet = ss.getSheetByName(DASHBOARD_CONFIG.SHEET_DASHBOARD);  // → 기존 시트
  if (!sheet) {                                 // → 없으면
    sheet = ss.insertSheet(DASHBOARD_CONFIG.SHEET_DASHBOARD);       // → 새로 만든다
  }
  const rows = dashboardRows_();                // → 행 배치 가져오기

  sheet.getRange('A1').setValue('창고 대시보드');          // → 제목
  sheet.getRange('A1').setFontSize(16).setFontWeight('bold');       // → 제목 스타일

  const labels = [                              // → A열 지표 이름 (순서 고정)
    ['오늘 입고 예정 건수'],                    // → B2
    ['미처리 적치 건수'],                       // → B3
    ['오늘 출고 수량'],                         // → B4
    ['미마감 Load 수']                          // → B5
  ];
  sheet.getRange(rows.kpiFirst, 1, labels.length, 1).setValues(labels);  // → 라벨 쓰기
  sheet.getRange(rows.kpiFirst, 2, labels.length, 1).clearContent();     // → 값은 새로고침이 채운다

  // C열 기준값은 운영자가 손으로 넣는 값이다. 이미 숫자가 있으면 절대 지우지 않는다.
  const thRange = sheet.getRange(rows.kpiFirst, 3, labels.length, 1);    // → 기준값 범위
  const kept = thRange.getValues().map(function (r) {                    // → 한 줄씩 확인
    const n = Number(r[0]);                     // → 숫자로 바꿔 보고
    return (r[0] !== '' && Number.isFinite(n)) ? [r[0]] : [''];          // → 숫자면 유지
  });
  thRange.setValues(kept);                      // → 되돌려 쓰기

  sheet.getRange(rows.stockTitle, 1)            // → 재고 표 제목
    .setValue('재고 상위 ' + DASHBOARD_CONFIG.TOP_N + '개').setFontWeight('bold');
  sheet.getRange(rows.stockHeader, 1, 1, 3)     // → 표 머리글
    .setValues([['로케이션', '모델', '재고 수량']]).setFontWeight('bold');
  sheet.getRange(rows.stockData, 1, DASHBOARD_CONFIG.TOP_N, 3).clearContent();  // → 데이터 영역 비우기

  sheet.setColumnWidths(1, 3, 150);             // → A~C열 너비
}

function testCreateDashboardSheet_() {          // → 편집기에서 실행할 확인용 함수
  createDashboardSheet_();                      // → 시트 만들기
  Logger.log(JSON.stringify(dashboardRows_())); // → 어떤 행에 무엇이 들어갔는지 로그로 확인
}

// 시트 값이 날짜인지 확인하고 yyyy-MM-dd 문자열로 바꾼다. 날짜가 아니면 빈 문자열.
function ymd_(v, tz) {                          // → 날짜 정규화
  if (v === '' || v === null || v === undefined) return '';   // → 빈 칸
  const d = (v instanceof Date) ? v : new Date(v);            // → 날짜로 해석
  if (isNaN(d.getTime())) return '';            // → 날짜가 아니면 빈 값
  return Utilities.formatDate(d, tz, 'yyyy-MM-dd');           // → 비교용 문자열
}

// 머리글을 뺀 데이터 행만 돌려준다. 시트가 없거나 비어 있으면 빈 배열.
function sheetRows_(ss, name) {                 // → 데이터 행 읽기
  const sh = ss.getSheetByName(name);           // → 시트 찾기
  if (!sh || sh.getLastRow() < 2) return [];    // → 없거나 머리글뿐이면 빈 배열
  return sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
}

function refreshDashboard() {                   // → 대시보드 전체 갱신
  const lock = LockService.getScriptLock();     // → 동시 실행 잠금
  lock.waitLock(30000);                         // → 최대 30초 대기
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();         // → 현재 스프레드시트
    const tz = ss.getSpreadsheetTimeZone();     // → 시트에 설정된 시간대
    const dash = ss.getSheetByName(DASHBOARD_CONFIG.SHEET_DASHBOARD);   // → DASHBOARD 시트
    if (!dash) {                                // → 없으면
      throw new Error('DASHBOARD 시트가 없습니다. testCreateDashboardSheet_ 를 먼저 실행하세요.');
    }
    const rows = dashboardRows_();              // → 행 배치
    const today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');   // → 오늘 날짜
    const skipped = [];                         // → 건너뛴 행 기록

    // 1)·2) INBOUND — 오늘 도착 예정 건수와, 그중 아직 적치되지 않은 건수
    const ci = DASHBOARD_CONFIG.COL_INBOUND;    // → 입고 열 위치
    let inboundToday = 0;                       // → 오늘 입고 예정
    let pendingPutaway = 0;                     // → 그중 미처리 적치
    sheetRows_(ss, DASHBOARD_CONFIG.SHEET_INBOUND).forEach(function (row, i) {
      const day = ymd_(row[ci.ETA_DATE - 1], tz);              // → 도착 예정일
      if (!day) {                               // → 날짜로 못 읽으면
        skipped.push('INBOUND ' + (i + 2) + '행: ETA_DATE를 날짜로 읽을 수 없음');
        return;                                 // → 이 행은 세지 않는다
      }
      if (day !== today) return;                // → 오늘이 아니면 건너뜀
      inboundToday++;                           // → 오늘 예정 +1
      const status = String(row[ci.STATUS - 1] || '').trim();  // → 상태값
      if (status !== DASHBOARD_CONFIG.DONE_STATUS) {           // → 적치 완료가 아니면
        pendingPutaway++;                       // → 미처리 +1
      }
    });

    // 3) OUTBOUND — 오늘 처리된 출고 수량 합계
    const co = DASHBOARD_CONFIG.COL_OUTBOUND;   // → 출고 열 위치
    let outboundQty = 0;                        // → 수량 합계
    sheetRows_(ss, DASHBOARD_CONFIG.SHEET_OUTBOUND).forEach(function (row, i) {
      if (ymd_(row[co.TIMESTAMP - 1], tz) !== today) return;   // → 오늘 것만
      const qty = Number(row[co.QTY - 1]);      // → 수량을 숫자로
      if (!Number.isFinite(qty)) {              // → 글자가 섞였으면
        skipped.push('OUTBOUND ' + (i + 2) + '행: 수량이 숫자가 아님');
        return;                                 // → 합계를 NaN으로 만들지 않는다
      }
      outboundQty += qty;                       // → 합산
    });

    // 4) LOADS — 아직 마감되지 않은 Load 수
    const cl = DASHBOARD_CONFIG.COL_LOADS;      // → Load 열 위치
    let openLoads = 0;                          // → 미마감 Load
    sheetRows_(ss, DASHBOARD_CONFIG.SHEET_LOADS).forEach(function (row) {
      const status = String(row[cl.STATUS - 1] || '').trim().toUpperCase();  // → 상태
      if (status === DASHBOARD_CONFIG.OPEN_STATUS) openLoads++;              // → OPEN이면 +1
    });

    dash.getRange(rows.kpiFirst, 2, DASHBOARD_CONFIG.KPI_COUNT, 1).setValues([
      [inboundToday],                           // → B2
      [pendingPutaway],                         // → B3
      [outboundQty],                            // → B4
      [openLoads]                               // → B5
    ]);

    // 5) STOCK — 재고가 많은 순으로 상위 N개
    const cs = DASHBOARD_CONFIG.COL_STOCK;      // → 재고 열 위치
    const picked = [];                          // → 뽑아낸 행
    sheetRows_(ss, DASHBOARD_CONFIG.SHEET_STOCK).forEach(function (row, i) {
      const qty = Number(row[cs.STOCK - 1]);    // → 재고 수량
      if (!Number.isFinite(qty)) {              // → 숫자가 아니면
        skipped.push('STOCK ' + (i + 2) + '행: 재고 수량이 숫자가 아님');
        return;                                 // → 순위에서 제외
      }
      if (qty <= 0) return;                     // → 0 이하는 보여줄 필요가 없다
      picked.push({ loc: row[cs.LOCATION - 1], model: row[cs.MODEL - 1], qty: qty });
    });
    picked.sort(function (a, b) { return b.qty - a.qty; });    // → 많은 순 정렬

    const table = [];                           // → 항상 TOP_N줄을 쓴다
    for (let i = 0; i < DASHBOARD_CONFIG.TOP_N; i++) {         // → 모자라면 빈 줄로
      const r = picked[i];                      // → i번째 행
      table.push(r ? [r.loc, r.model, r.qty] : ['', '', '']);  // → 없으면 빈 줄
    }
    dash.getRange(rows.stockData, 1, DASHBOARD_CONFIG.TOP_N, 3).setValues(table);

    applyDashboardColors_(dash, rows, [inboundToday, pendingPutaway, outboundQty, openLoads]);

    if (skipped.length) {                       // → 건너뛴 행이 있으면
      Logger.log('건너뛴 행 ' + skipped.length + '건: ' + skipped.join(' / '));
    }
  } finally {
    lock.releaseLock();                         // → 성공·실패와 무관하게 잠금 해제
  }
}

function testRefreshDashboard_() {              // → 편집기에서 실행할 확인용 함수
  refreshDashboard();                           // → 갱신 실행
  const dash = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(DASHBOARD_CONFIG.SHEET_DASHBOARD);        // → DASHBOARD 시트
  const rows = dashboardRows_();                // → 행 배치
  const kpi = dash.getRange(rows.kpiFirst, 1, DASHBOARD_CONFIG.KPI_COUNT, 2).getValues();
  Logger.log(JSON.stringify(kpi));              // → 라벨과 값을 함께 로그로 출력
}

// C열에 기준값이 들어 있는 지표만 색을 칠한다.
// 기준의 2배부터 붉은색, 기준부터 노란색, 그 아래는 흰색 — 한 값에는 한 색만 정해진다.
function applyDashboardColors_(dash, rows, values) {         // → 임계치 색상
  const n = DASHBOARD_CONFIG.KPI_COUNT;         // → 지표 개수
  const thresholds = dash.getRange(rows.kpiFirst, 3, n, 1).getValues();  // → C열 기준값
  const colors = [];                            // → 칠할 색 목록

  for (let i = 0; i < n; i++) {                 // → 지표마다
    const th = Number(thresholds[i][0]);        // → 기준값
    const v = Number(values[i]);                // → 현재 값
    let color = '#ffffff';                      // → 기본은 흰색
    if (Number.isFinite(th) && th > 0 && Number.isFinite(v)) {            // → 기준이 있을 때만
      if (v >= th * 2) {                        // → 기준의 두 배 이상
        color = '#f4cccc';                      // → 붉은색(즉시 대응)
      } else if (v >= th) {                     // → 기준 이상
        color = '#fff2cc';                      // → 노란색(주의)
      }
    }
    colors.push([color]);                       // → 목록에 추가
  }

  dash.getRange(rows.kpiFirst, 2, n, 1).setBackgrounds(colors);          // → 한 번에 칠하기
}

function onOpen() {                             // → 시트를 열 때 자동 실행
  const menu = SpreadsheetApp.getUi().createMenu('창고 도구');  // → 시리즈 공용 메뉴
  addDashboardMenu_(menu);                      // → 이 편 항목 붙이기
  menu.addToUi();                               // → 시트에 붙이기
}

function addDashboardMenu_(menu) {              // → 합칠 때는 이 함수만 부른다
  menu.addItem('대시보드 새로고침', 'refreshDashboard');       // → 메뉴 항목
}

function createDashboardTrigger_() {                     // → 트리거 생성 함수
  const funcName = 'refreshDashboard';

  // 기존 같은 함수용 시간 트리거 삭제
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(tr => {
    if (tr.getHandlerFunction() === funcName &&
        tr.getEventType() === ScriptApp.EventType.TIME_BASED) {
      ScriptApp.deleteTrigger(tr);
    }
  });

  // 30분마다 실행되는 새 트리거 생성
  ScriptApp.newTrigger(funcName)
    .timeBased()
    .everyMinutes(30)
    .create();

  Logger.log('대시보드 자동 갱신 트리거 생성 완료');
}