구글시트 재고 자동 집계 방법 | Apps Script 3편
도입 — 수식 없이 재고 현황을 한 번에 갱신하는 방법
구글시트 재고 자동 집계 방법을 찾다 보면 대부분 SUMIFS, QUERY 수식을 이용한 예시가 나옵니다. 입고·출고 데이터는 잘 쌓이는데, 로케이션별·모델별 현재 재고를 보려고 하면 수식이 복잡해지고 시트가 느려지는 문제가 생깁니다. 이 글에서는 구글시트 Apps Script 재고관리 방식으로, 스크립트 한 번 실행만으로 STOCK 시트를 전부 다시 계산해 갱신하는 방법을 정리합니다.
이번 글은 구글시트로 창고 재고관리 시스템 만들기 | Apps Script 자동화와 구글시트 출고 자동화 방법: Apps Script 2편에 이어지는 3편입니다. 앞 글에서 입고 적치 기록 시트(LOCATIONS)와 출고 기록 시트(OUTBOUND)가 자동으로 쌓이는 구조까지 만들었습니다. 이제 이 데이터를 바탕으로, 수식을 깔지 않고 Apps Script 한 번으로 로케이션(Location)·모델(Model)별 현재 재고를 STOCK 시트에 자동 집계하는 부분을 완성합니다.
STOCK 시트 구조 — 최소 정보만 뽑아서 단순하게
실무에서 재고 현황 시트는 “중요한 정보만 한눈에 보이는가”가 핵심입니다. 너무 많은 열을 한 번에 넣으면 보기는 좋지만, 관리와 유지 보수가 어렵습니다. 이 글에서 만드는 구글시트 STOCK 시트는 로케이션·모델·입고 수량·출고 수량·현재 고 정도만 먼저 두고, 필요할 때 열을 추가하는 방식으로 설계합니다.
STOCK 시트의 기본 열 구조 예시는 다음과 같습니다.
- Location
- Model
- InQty(입고 합계)
- OutQty(출고 합계)
- Stock(현재 고 = InQty - OutQty)
입고 데이터는 LOCATIONS 시트에, 출고 데이터는 OUTBOUND 시트에 이미 쌓이고 있다고 가정합니다. 각각 최소한 다음과 같은 열이 있어야 합니다.
- LOCATIONS 시트
- Location 열
- Model 열
- Qty 열(입고 수량)
- OUTBOUND 시트
- Location 열
- Model 열
- Qty 열(출고 수량)
현장에서 자주 나오는 질문은 “재고를 모델별로만 볼지, 로케이션까지 함께 볼지”입니다. 실제 창고 운영에서는 로케이션까지 함께 보는 경우가 더 많습니다. 같은 모델이라도 어느 랙, 어느 구역에 있는지가 중요하기 때문입니다. 이 글에서는 (Location, Model) 조합별로 집계해서 STOCK 시트에 표시합니다. 전체 모델별 합계를 보고 싶다면 이후에 피벗 테이블이나 별도 요약 시트를 만드는 것이 안전합니다.
또 하나 중요한 원칙은 “STOCK 시트는 항상 스크립트가 전량 덮어쓴다”입니다. 중간에 사람이 수식을 추가하거나 값을 직접 수정하면, 다음 재집계 시점에 꼬일 수 있습니다. 그래서 STOCK 시트는 한 번 실행할 때마다 내용을 모두 지우고 다시 쓰는 구조로 설계합니다. 구글 스프레드시트 재고 현황 스크립트를 이렇게 만들어 두면, 중간에 잘못된 입력이 있더라도 재계산 한 번으로 정합성을 다시 맞출 수 있습니다.
Apps Script로 재고 집계 구현 — 전체 흐름 먼저 이해하기
코드로 들어가기 전에, 이번에 만드는 “구글시트 입고 출고 현재고 자동화”의 흐름을 간단히 정리합니다. 구글시트 Apps Script 재고관리에서 하는 일은 크게 다섯 단계입니다.
- LOCATIONS 시트에서 모든 입고 데이터를 읽어
(Location, Model)별 입고 합계를 Map 형태로 만듭니다. - OUTBOUND 시트에서 모든 출고 데이터를 읽어 같은 키로 출고 합계를 만듭니다.
- 두 Map을 합쳐
(Location, Model)별 InQty, OutQty, Stock(현재 고)을 계산합니다. - STOCK 시트를 비운 뒤, 헤더를 다시 쓰고 계산한 데이터를 전량 덮어씁니다.
- 여러 사용자가 동시에 실행해도 안전하도록 스크립트 잠금(LockService)을 사용합니다.
이 방식을 실제 업무에 적용하면 다음과 같은 장점이 있습니다.
- 시트에
SUMIFS,QUERY같은 복잡한 수식을 깔지 않아도 됩니다. - 로케이션·모델 열 위치를 바꾸더라도, 코드 상단의 상수(CONFIG)만 수정하면 대응할 수 있습니다.
- STOCK 시트는 조회 전용으로 두고, 입력·수정은 LOCATIONS, OUTBOUND 시트에서만 처리하므로 데이터 흐름이 명확해집니다.
반대로 단점도 있습니다. 코드 수정을 어려워하는 분들에게는 진입 장벽이 있고, 구조를 변경할 때마다 Apps Script를 함께 손봐야 합니다. 실무에서 사용해 본 경험으로는, 일 단위 입·출고 라인이 수백~수천 건 수준부터는 수식보다 Apps Script SUMIFS 재고 계산 방식이 유지 보수와 속도 면에서 더 안정적이었습니다. 이 글에서는 코드 상단에 시트 이름과 열 번호를 한 곳에 모아 두어, 나중에 구조를 바꿀 때 수정 범위를 최소화하는 형태로 작성합니다.
Apps Script로 재고 집계 구현
1단계 — 설정 상수와 기본 도우미 함수 작성
이 단계에서는 시트 이름·열 번호를 CONFIG 상수에 모아 두고, 데이터를 읽어오는 기본 도우미 함수를 만듭니다.
1) 하는 일: 입고·출고 시트 이름과 열 번호를 한 번에 관리하고, 시트와 데이터 범위를 안전하게 가져옵니다.
2) 붙여넣을 위치: 확장 프로그램 → Apps Script → Code.gs 파일 맨 위.
3) 붙여넣은 뒤 할 일: 저장(⌘S 또는 Ctrl+S) 후 오류가 없는지 확인합니다.
// → 여기만 본인 시트에 맞게 바꾸세요
const CONFIG = { // → 설정 모음
STOCK_SHEET_NAME: 'STOCK', // → 재고 시트 이름
LOCATIONS_SHEET_NAME: 'LOCATIONS', // → 입고 시트 이름
OUTBOUND_SHEET_NAME: 'OUTBOUND', // → 출고 시트 이름
// LOCATIONS 시트 열 번호(1부터 시작)
LOCATIONS_COL_LOCATION: 1, // → Location 열 번호
LOCATIONS_COL_MODEL: 2, // → Model 열 번호
LOCATIONS_COL_QTY: 3, // → 입고 수량 열 번호
// OUTBOUND 시트 열 번호(1부터 시작)
OUTBOUND_COL_LOCATION: 1, // → Location 열 번호
OUTBOUND_COL_MODEL: 2, // → Model 열 번호
OUTBOUND_COL_QTY: 3, // → 출고 수량 열 번호
HEADER_ROW: 1 // → 헤더가 있는 행 번호
};
// → 시트 이름으로 시트 객체 가져오기(없으면 생성)
function getOrCreateSheet_(name) { // → 시트 가져오기 함수
const ss = SpreadsheetApp.getActiveSpreadsheet(); // → 현재 스프레드시트
let sheet = ss.getSheetByName(name); // → 이름으로 시트 찾기
if (!sheet) { // → 없다면
sheet = ss.insertSheet(name); // → 새 시트 만들기
}
return sheet; // → 시트 반환
}
// → 특정 시트의 데이터 범위를 2차원 배열로 가져오기
function getDataRangeValues_(sheet, headerRow) { // → 데이터 읽기 함수
const lastRow = sheet.getLastRow(); // → 마지막 행 번호
const lastCol = sheet.getLastColumn(); // → 마지막 열 번호
if (lastRow <= headerRow || lastCol === 0) { // → 데이터 없을 때
return []; // → 빈 배열 반환
}
return sheet // → 시트에서
.getRange(headerRow + 1, 1, lastRow - headerRow, lastCol) // → 헤더 아래 범위
.getValues(); // → 값 배열로 가져오기
}제대로 됐는지 확인하는 법: 저장 버튼을 눌렀을 때 빨간 오류 표시가 없으면 1단계가 완료된 것입니다.
2단계 — 입·출고 데이터를 (Location, Model)별 합계로 모으기
이 단계에서는 LOCATIONS, OUTBOUND 시트를 읽어 (Location, Model)별 입고·출고 합계를 Map으로 만듭니다.
1) 하는 일: 로케이션·모델 조합별로 InQty, OutQty 합계를 미리 계산합니다.
2) 붙여넣을 위치: 1단계 코드 바로 아래에 이어서 붙여넣습니다.
3) 붙여넣은 뒤 할 일: 저장 후 testBuildStockMaps() 함수 실행으로 로그를 확인합니다.
// → (Location, Model) 키를 만드는 도우미
function makeKey_(location, model) { // → 키 생성 함수
return location + '||' + model; // → 구분자 포함 문자열
}
// → LOCATIONS, OUTBOUND를 Map 형태로 합계 집계
function buildStockMaps_() { // → 집계 함수
const locSheet = getOrCreateSheet_(CONFIG.LOCATIONS_SHEET_NAME); // → 입고 시트
const outSheet = getOrCreateSheet_(CONFIG.OUTBOUND_SHEET_NAME); // → 출고 시트
const locValues = getDataRangeValues_(locSheet, CONFIG.HEADER_ROW); // → 입고 데이터
const outValues = getDataRangeValues_(outSheet, CONFIG.HEADER_ROW); // → 출고 데이터
const inMap = {}; // → 입고 합계 Map
const outMap = {}; // → 출고 합계 Map
// → 입고 데이터 집계
locValues.forEach(row => { // → 행마다 반복
const location = String(row[CONFIG.LOCATIONS_COL_LOCATION - 1]); // → Location 값
const model = String(row[CONFIG.LOCATIONS_COL_MODEL - 1]); // → Model 값
const qty = Number(row[CONFIG.LOCATIONS_COL_QTY - 1]) || 0; // → 수량 숫자
if (!location || !model) { // → 필수값 없으면
return; // → 건너뛰기
}
const key = makeKey_(location, model); // → 키 만들기
inMap[key] = (inMap[key] || 0) + qty; // → 합계 누적
});
// → 출고 데이터 집계
outValues.forEach(row => { // → 행마다 반복
const location = String(row[CONFIG.OUTBOUND_COL_LOCATION - 1]); // → Location 값
const model = String(row[CONFIG.OUTBOUND_COL_MODEL - 1]); // → Model 값
const qty = Number(row[CONFIG.OUTBOUND_COL_QTY - 1]) || 0; // → 수량 숫자
if (!location || !model) { // → 필수값 없으면
return; // → 건너뛰기
}
const key = makeKey_(location, model); // → 키 만들기
outMap[key] = (outMap[key] || 0) + qty; // → 합계 누적
});
return { inMap, outMap }; // → 두 Map 반환
}
// → 집계 결과를 콘솔에서 테스트 확인
function testBuildStockMaps() { // → 테스트 함수
const { inMap, outMap } = buildStockMaps_(); // → 집계 실행
Logger.log('IN:' + JSON.stringify(inMap)); // → 입고 로그
Logger.log('OUT:' + JSON.stringify(outMap)); // → 출고 로그
}제대로 됐는지 확인하는 법: Apps Script 편집기 상단에서 함수 목록에서 testBuildStockMaps를 선택한 뒤 실행 버튼을 누르고, 실행 → 실행 로그를 열었을 때 IN/OUT 로그에 (Location||Model) 키와 합계 수량이 찍혀 있으면 정상입니다.
3단계 — STOCK 시트에 현재 재고 쓰기 및 메뉴 연결
3단계에서는 합계 Map을 이용해 현재 재고를 계산하고 STOCK 시트에 전량 덮어씁니다. 동시에 LockService를 통해 동시 실행 충돌을 막고, 메뉴에 “재고 새로고침” 버튼을 추가합니다.
#### 3-1단계 — 재고 계산 후 STOCK 시트 전량 갱신
1) 하는 일: InQty, OutQty를 이용해 Stock(현재 고)을 계산하고 STOCK 시트를 초기화 후 다시 채웁니다.
2) 붙여넣을 위치: 2단계 코드 아래에 이어서 붙여넣습니다.
3) 붙여넣은 뒤 할 일: 저장 후 buildStock() 함수를 실행해 STOCK 시트를 생성합니다.
// → (Location, Model)별 재고를 STOCK 시트에 쓰기
function buildStock() { // → 재고 집계 메인
const lock = LockService.getScriptLock(); // → 잠금 객체
if (!lock.tryLock(30000)) { // → 최대 30초 대기
throw new Error('다른 사용자가 재고 집계 중입니다. 잠시 후 다시 시도하세요.'); // → 잠금 실패
}
try { // → 예외 처리 시작
const { inMap, outMap } = buildStockMaps_(); // → 합계 가져오기
const stockSheet = getOrCreateSheet_(CONFIG.STOCK_SHEET_NAME); // → 재고 시트
stockSheet.clearContents(); // → 내용 전체 삭제
// → 헤더 작성
const header = ['Location', 'Model', 'InQty', 'OutQty', 'Stock']; // → 헤더 배열
stockSheet.getRange(1, 1, 1, header.length).setValues([header]); // → 1행에 쓰기
const rows = []; // → 본문 데이터 배열
// → 입고와 출고 키 목록 합치기
const keys = new Set(); // → 키 모음
Object.keys(inMap).forEach(k => keys.add(k)); // → 입고 키 추가
Object.keys(outMap).forEach(k => keys.add(k)); // → 출고 키 추가
// → 키마다 재고 계산
keys.forEach(key => { // → 각 키 반복
const [location, model] = key.split('||'); // → 값 복구
const inQty = inMap[key] || 0; // → 입고 합계
const outQty = outMap[key] || 0; // → 출고 합계
const stock = inQty - outQty; // → 현재 재고
rows.push([location, model, inQty, outQty, stock]); // → 한 행 추가
});
// → 데이터가 있을 때만 작성
if (rows.length > 0) { // → 행 존재 확인
stockSheet.getRange(2, 1, rows.length, header.length) // → 2행부터 범위
.setValues(rows); // → 값 쓰기
}
// → 보기 좋게 정렬(옵션)
stockSheet.sort(1); // → 1열(Location) 기준
} finally { // → 항상 실행
lock.releaseLock(); // → 잠금 해제
}
}제대로 됐는지 확인하는 법: Apps Script에서 buildStock 함수를 선택해 실행한 뒤, 스프레드시트로 돌아가 STOCK 시트가 새로 생기고 Location·Model별로 InQty, OutQty, Stock 값이 채워져 있으면 정상입니다. LOCATIONS, OUTBOUND에 수량을 몇 건 수정하고 다시 실행했을 때 STOCK 값이 그에 맞게 바뀌는지도 함께 확인합니다.
#### 3-2단계 — 메뉴에 ‘재고 새로고침’ 버튼 추가하기
1) 하는 일: 스프레드시트를 열었을 때 상단 메뉴에 “재고 새로고침”을 추가해 클릭만으로 buildStock()을 실행합니다.
2) 붙여넣을 위치: 같은 Code.gs 파일 맨 아래.
3) 붙여넣은 뒤 할 일: 저장 후 시트를 새로고침합니다.
// → 시트 열릴 때 메뉴 추가
function onOpen() { // → 시트 열릴 때 실행
const ui = SpreadsheetApp.getUi(); // → UI 객체
ui.createMenu('재고 관리') // → 메뉴 이름
.addItem('재고 새로고침', 'buildStock') // → 메뉴 항목 추가
.addToUi(); // → UI에 붙이기
}제대로 됐는지 확인하는 법: 시트를 새로고침했을 때 상단 메뉴에 ‘재고 관리’가 보이고, 그 안에 ‘재고 새로고침’을 눌렀을 때 STOCK 시트의 값이 최신 데이터 기준으로 다시 채워지면 성공입니다.
수식 방식과 스크립트 방식 비교 — 언제 스크립트가 유리한가
구글시트 STOCK 시트를 만드는 방법은 Apps Script만 있는 것은 아닙니다. 단순한 구조라면 SUMIFS, QUERY만으로도 충분합니다. 예를 들어, 모델별 현재 고만 보고 싶을 때는 다음과 같은 구성을 쓸 수 있습니다.
- 입고 합계 예시:
=SUMIFS(LOCATIONS!C:C, LOCATIONS!B:B, A2)
- 출고 합계 예시:
=SUMIFS(OUTBOUND!C:C, OUTBOUND!B:B, A2)
- 현재 고 계산:
=C2-D2
또는 QUERY 함수로 LOCATIONS와 OUTBOUND 각각을 모델별로 그룹핑한 뒤, 두 집계를 다시 VLOOKUP으로 합치는 방식도 가능합니다. 데이터량이 적고, 로케이션을 고려하지 않아도 된다면 이 정도 구성으로도 충분히 운영할 수 있습니다.
다만 현장에서 구글시트 재고 자동 집계 방법을 수식으로만 운영하다 보면 다음과 같은 문제가 자주 발생합니다.
- 데이터 행이 수천 건으로 늘어나면 시트 자동 계산 속도가 눈에 띄게 느려집니다.
- 수식을 추가·복사하는 과정에서 참조 범위가 일부 어긋나, 특정 행만 잘못 계산되는 오류가 생깁니다.
- 로케이션까지 합치면 기준 열이 두 개 이상이 되고, 여러
SUMIFS와 보조열이 얽혀 구조가 금방 복잡해집니다.
이 글에서 구현한 Apps Script 기반 구글시트 STOCK 시트 만들기 방식은, 계산은 스크립트에 몰아두고 시트는 결과만 보여 주도록 단순화하는 방식입니다. 재고가 이상하다고 느껴질 때마다 “재고 새로고침”을 한 번 누르면 LOCATIONS, OUTBOUND 원본 데이터를 기준으로 다시 계산되므로, 중간에 잘못된 수식이나 수동 수정으로 인한 오류를 최소화할 수 있습니다. 실제 소규모 창고와 사무실에서 사용해 본 경험으로, 하루 수백 건 수준의 입·출고까지는 체감 속도 문제 없이 안정적으로 운영되었습니다.
실무에서 써 본 팁 — 구조 변경과 오류 점검 습관
실제 창고·사무실에서 이 구조를 운영하면서 느낀 실무 팁을 정리합니다. 구글시트 Apps Script 재고관리를 처음 도입하는 분께 도움이 될 수 있는 부분들입니다.
첫째, STOCK 시트는 철저히 “조회 전용”으로 둡니다. 누군가가 STOCK 시트에서 수량을 손으로 바꾸기 시작하면, 다음 자동 집계에서 다시 덮어써지기 때문에 현장에서 혼란이 발생합니다. 실제로는 STOCK 시트 상단 A1 셀에 굵은 글씨로 “이 시트는 자동 생성됩니다. 직접 수정하지 마십시오.”라고 적어 두고, 셀 보호 기능으로 STOCK 시트를 보호하는 것이 도움이 되었습니다.
둘째, LOCATIONS나 OUTBOUND 시트의 열 구조를 바꾸는 순간, 반드시 CONFIG 상수부터 확인합니다. 예를 들어 입고 수량 열 앞에 보조 열을 하나 추가하면, 기존에 3이던 열 번호가 4로 밀립니다. 이 상태에서 코드를 수정하지 않으면, 재고 스크립트는 다른 열의 값을 수량으로 인식해 잘못된 현재 고를 계산하게 됩니다. 실무에서는 구조 변경 시 체크리스트에 “Apps Script CONFIG 열 번호 확인” 항목을 반드시 넣어 두고 처리했습니다.
셋째, 여러 명이 동시에 “재고 새로고침”을 눌러도 문제 없도록 잠금을 사용하는 것이 중요합니다. 초기에는 단순히 buildStock()만 만들어 두었다가, 드물게 STOCK 시트가 비어 있는 상태로 남는 현상을 겪었습니다. 확인해 보니 두 사용자가 거의 동시에 실행하면서 범위 삭제 타이밍이 겹친 경우였습니다. 현재 코드처럼 LockService.getScriptLock()과 try/finally 구조로 잠금을 잡아 주고, 잠금 실패 시 오류를 던지도록 바꾼 이후에는 이런 문제가 사라졌습니다.
넷째, 오류가 의심될 때는 먼저 testBuildStockMaps()로 원천 합계를 확인하는 습관을 들입니다. 실행 로그에서 IN/OUT Map 값이 실제 입·출고 집계와 일치하는지 보면, 데이터 문제인지 STOCK 시트 쓰는 부분 문제인지 바로 구분할 수 있습니다. 실무에서는 재고가 이상하게 느껴질 때마다, ① 원천 데이터 확인 → ② testBuildStockMaps() 로그 확인 → ③ buildStock() 재실행 순서로 점검했습니다.
자주 발생하는 오류와 해결 방법
구글 스프레드시트 재고 현황 스크립트를 처음 적용할 때, 현장에서 반복적으로 나왔던 오류와 해결 방법을 정리합니다.
- 스크립트 권한 관련 경고
처음 Apps Script를 실행하면 “이 앱은 확인되지 않았습니다”라는 경고가 뜨는 경우가 많습니다. 이때는
- 경고 창에서 “고급”을 클릭한 뒤
- “(프로젝트 이름)으로 이동”을 선택합니다.
내부에서만 사용하는 자동화 스크립트라면 작성자 계정으로 한 번만 권한을 승인해 주면 이후에는 다시 묻지 않습니다.
- 열 번호 불일치로 인한 이상한 재고 값
LOCATIONS나 OUTBOUND 시트에 열을 추가하거나 순서를 바꾼 뒤 CONFIG 상수를 수정하지 않으면, Stock 값이 현실과 전혀 맞지 않게 나옵니다. 이럴 때는 다음 순서로 확인합니다.
- 각 시트에서 Location, Model, Qty 열의 실제 위치(1부터 시작)를 확인합니다.
- CONFIG 객체의
LOCATIONS_COL_LOCATION,LOCATIONS_COL_MODEL,LOCATIONS_COL_QTY,OUTBOUND_COL_*값이 이 위치와 일치하는지 비교합니다. - 수정 후
testBuildStockMaps()로 합계를 한 번 확인하고, 마지막으로buildStock()을 실행해 STOCK 시트를 재생성합니다.
- 데이터가 없어 STOCK 시트가 비어 보이는 경우
테스트 환경에서 LOCATIONS, OUTBOUND 시트에 데이터가 거의 없으면 STOCK 시트에는 헤더만 보이거나 빈 시트처럼 보일 수 있습니다. 이 경우에는 입고·출고 데이터를 2~3건만이라도 입력한 뒤 buildStock()을 다시 실행해 보는 것이 좋습니다. 특히 처음 도입할 때는 예시 수량을 사용해 구조부터 검증하는 편이 안정적이었습니다.
이런 몇 가지 포인트만 익혀 두면, 구글시트 STOCK 시트 만들기 과정에서 막히는 부분을 대부분 현장에서 바로 해결할 수 있습니다.
맺음말
지금까지 입고·출고 데이터를 바탕으로, 구글시트 재고 자동 집계 방법을 Apps Script로 구현하는 과정을 단계별로 살펴보았습니다. 핵심은 LOCATIONS, OUTBOUND 시트에는 기록만 쌓고, STOCK 시트는 스크립트가 매번 전량 다시 계산해 쓰도록 설계하는 것입니다. 이렇게 해 두면 수식 꼬임과 수동 수정으로 인한 오류를 크게 줄일 수 있습니다.
이 글을 다 읽으셨다면, 지금 바로 할 수 있는 행동은 사용하는 스프레드시트에서 Apps Script 편집기를 열고, CONFIG 상수 부분을 본인 시트 구조에 맞게 수정한 뒤 buildStock()을 한 번 실행해 보는 일입니다. 한 번 정상 동작을 확인해 두면, 이후에는 상단 메뉴의 “재고 새로고침”만 눌러도 로케이션·모델별 현재 고가 항상 최신 상태로 유지됩니다. 다음 글에서는 이렇게 만들어진 STOCK 데이터를 활용해 최소 재고 알림이나 간단한 대시보드까지 확장하는 방법을 다룰 예정입니다.
