구글시트 창고 랙 배치도 시각화 방법 | Apps Script 자동 색상 + 잠금 처리

// --- 시트 이름, 범위, SETTINGS 키 상수 정의 ---
// 여기만 본인 환경에 맞게 바꾸세요
const SHEET_FLOOR_MAP = 'FLOOR_MAP';       // → 랙 배치도 시트 이름
const SHEET_STOCK     = 'STOCK';           // → 재고 집계 시트 이름
const SHEET_SETTINGS  = 'SETTINGS';        // → 설정값 시트 이름

const FLOOR_RANGE_A1  = 'B18:G24';         // → 랙 범위(열=랙, 행=단)

const KEY_MAX_PER_LOC    = 'MAX_PER_LOCATION';  // → 최대 수량 키
const KEY_WARN_THRESHOLD = 'WARN_THRESHOLD';    // → 경고 비율 키(0~100)
const KEY_FULL_THRESHOLD = 'FULL_THRESHOLD';    // → 포화 비율 키(0~100)

const KEY_COLOR_EMPTY = 'COLOR_EMPTY';     // → 빈칸 색 키
const KEY_COLOR_OK    = 'COLOR_OK';        // → 여유 색 키
const KEY_COLOR_FULL  = 'COLOR_FULL';      // → 포화 색 키

/**
 * SETTINGS 시트(A열 키, B열 값)를 읽어 Map 형태(객체)로 반환
 */
function getSettingsMap() {                             // → SETTINGS를 객체로 읽기
  const ss = SpreadsheetApp.getActive();                // → 현재 파일 가져오기
  const sh = ss.getSheetByName(SHEET_SETTINGS);         // → SETTINGS 시트 가져오기
  if (!sh) {                                            // → 시트 없으면
    throw new Error('SETTINGS 시트를 찾을 수 없습니다'); // → 오류 발생
  }

  const lastRow = sh.getLastRow();                      // → 마지막 행 번호
  if (lastRow < 2) {                                    // → 데이터 없으면
    throw new Error('SETTINGS 시트에 값이 없습니다');     // → 오류 발생
  }

  const range = sh.getRange(2, 1, lastRow - 1, 2);      // → A2:B 마지막 행 범위
  const values = range.getValues();                     // → 키·값 전체 읽기

  const map = {};                                       // → 결과 객체
  values.forEach(function(row) {                        // → 각 행 처리
    const key = String(row[0]).trim();                  // → 키 문자열
    const value = row[1];                               // → 값 그대로
    if (key) {                                          // → 키가 비어 있지 않으면
      map[key] = value;                                 // → 객체에 저장
    }
  });

  return map;                                           // → 전체 설정 반환
}

/**
 * STOCK 시트에서 로케이션별 수량을 읽어 Map 형태(객체)로 반환
 * A열: 로케이션 코드, B열: 현재 수량 기준 예시
 */
function buildStockMap() {                              // → STOCK을 객체로 만들기
  const ss = SpreadsheetApp.getActive();                // → 현재 파일 가져오기
  const sh = ss.getSheetByName(SHEET_STOCK);            // → STOCK 시트 가져오기
  if (!sh) {                                            // → 시트 없으면
    throw new Error('STOCK 시트를 찾을 수 없습니다');    // → 오류 발생
  }

  const lastRow = sh.getLastRow();                      // → 마지막 행 번호
  if (lastRow < 2) {                                    // → 데이터 없으면
    return {};                                          // → 빈 객체 반환
  }

  const range = sh.getRange(2, 1, lastRow - 1, 2);      // → A2:B 범위(로케이션, 수량)
  const values = range.getValues();                     // → 값 읽기

  const stockMap = {};                                  // → 결과 객체
  values.forEach(function(row) {                        // → 각 행 처리
    const loc = String(row[0]).trim();                  // → 로케이션 코드
    const qty = Number(row[1]) || 0;                    // → 수량 숫자로
    if (loc) {                                          // → 로케이션 있으면
      stockMap[loc] = qty;                              // → 객체에 저장
    }
  });

  return stockMap;                                      // → 전체 재고 맵 반환
}

/**
 * FLOOR_MAP 시트의 랙 배치도 영역(B18:G24)을 새로고침하여
 * 로케이션별 수량 텍스트와 배경색을 동시에 갱신.
 * LockService를 사용해 동시 실행(동시 저장) 충돌을 방지.
 */
function refreshFloorMap() {                                      // → FLOOR_MAP 시각화
  // --- LockService로 동시 실행 방지 ---
  const lock = LockService.getDocumentLock();                     // → 문서 단위 잠금
  let hasLock = false;
  try {
    // 최대 3초 동안 잠금 획득을 시도
    hasLock = lock.tryLock(3000);
    if (!hasLock) {
      throw new Error('다른 사용자가 랙 배치도를 업데이트 중입니다. 잠시 후 다시 시도하세요.');
    }

    const ss = SpreadsheetApp.getActive();                        // → 현재 파일 가져오기
    const shMap = ss.getSheetByName(SHEET_FLOOR_MAP);             // → FLOOR_MAP 시트
    if (!shMap) {                                                 // → 시트 없으면
      throw new Error('FLOOR_MAP 시트를 찾을 수 없습니다');       // → 오류 발생
    }

    const settings = getSettingsMap();                            // → 설정값 가져오기
    const maxPerLoc = Number(settings[KEY_MAX_PER_LOC]) || 0;     // → 최대 수량
    const warnRatio  = Number(settings[KEY_WARN_THRESHOLD]) || 0; // → 경고 비율(%)
    const fullRatio  = Number(settings[KEY_FULL_THRESHOLD]) || 0; // → 포화 비율(%)

    const colorEmpty = String(settings[KEY_COLOR_EMPTY] || '#eeeeee'); // → 빈칸 색
    const colorOk    = String(settings[KEY_COLOR_OK]    || '#fff2cc');  // → 여유 색
    const colorFull  = String(settings[KEY_COLOR_FULL]  || '#f4cccc');  // → 포화 색

    if (!maxPerLoc || !warnRatio || !fullRatio) {                 // → 기준값 누락 시
      throw new Error('SETTINGS 시트의 MAX_PER_LOCATION, WARN_THRESHOLD, FULL_THRESHOLD 값을 확인하세요'); // → 오류 발생
    }

    const stockMap = buildStockMap();                             // → 재고 맵 가져오기

    const range = shMap.getRange(FLOOR_RANGE_A1);                 // → 랙 범위 가져오기
    const values = range.getValues();                             // → 현재 셀 값
    const newValues = [];                                         // → 새 값 배열
    const bgColors  = [];                                         // → 배경색 배열

    for (let r = 0; r < values.length; r++) {                     // → 행 반복
      const rowValues = values[r];                                // → 한 행 값
      const newRowValues = [];                                    // → 새 행 값
      const rowColors = [];                                       // → 새 행 색

      for (let c = 0; c < rowValues.length; c++) {                // → 열 반복
        const cellVal = String(rowValues[c]).trim();              // → 셀 내용
        if (!cellVal) {                                           // → 로케이션 없으면
          newRowValues.push('');                                  // → 빈값 유지
          rowColors.push(colorEmpty);                             // → 빈칸 색
          continue;                                               // → 다음 셀
        }

        const loc = cellVal;                                      // → 로케이션 코드
        const qty = Number(stockMap[loc]) || 0;                   // → 재고 수량

        const text = loc + '\n' + qty;                            // → 코드+수량
        newRowValues.push(text);                                  // → 셀 값 넣기

        let ratio = 0;                                            // → 사용 비율
        if (maxPerLoc > 0) {                                      // → 기준 있으면
          ratio = qty / maxPerLoc;                                // → 비율 계산
        }

        let color = colorEmpty;                                   // → 기본 색
        if (qty === 0) {                                          // → 수량 0이면
          color = colorEmpty;                                     // → 빈칸 색
        } else if (ratio >= fullRatio / 100) {                    // → 포화 이상
          color = colorFull;                                      // → 포화 색
        } else if (ratio >= warnRatio / 100) {                    // → 경고 이상
          color = colorOk;                                        // → 여유 색
        } else {                                                  // → 경고 미만
          color = colorOk;                                        // → 여유 색(초기 여유 구간)
        }

        rowColors.push(color);                                    // → 색 추가
      }

      newValues.push(newRowValues);                               // → 새 값 행 추가
      bgColors.push(rowColors);                                   // → 새 색 행 추가
    }

    range.setValues(newValues);                                   // → 값 일괄 반영
    range.setBackgrounds(bgColors);                               // → 색 일괄 반영

  } finally {
    // 잠금을 획득했으면 반드시 해제
    if (hasLock) {
      lock.releaseLock();
    }
  }
}

/**
 * 시트가 열릴 때 상단 메뉴에 "창고 도구" 메뉴를 추가
 */
function onOpen() {                                              // → 시트 열 때 실행
  const ui = SpreadsheetApp.getUi();                             // → UI 객체
  ui.createMenu('창고 도구')                                      // → 새 메뉴 이름
    .addItem('랙 배치도 새로고침', 'refreshFloorMap')             // → 메뉴 항목
    .addToUi();                                                  // → 메뉴 추가
}