구글시트 재고 리포트 자동 발송 | Apps Script 5편
도입 – 매일 아침 엑셀 붙잡는 시간을 줄이는 방법
구글시트 재고 리포트 자동 발송 방법을 찾는 분들은 대부분 비슷한 고민을 하고 있습니다. 전날 재고를 마감해 두더라도, 아침이 되면 팀과 관리자에게 전일 기준 입출고 현황을 다시 정리해 이메일로 보내야 합니다. 입출고 담당자는 현장 운영도 처리해야 하는데, 매일 같은 형식의 리포트를 복사·필터·정렬해서 메일에 붙여 넣는 작업에 상당한 시간이 필요합니다.
이 글에서는 그 과정을 구글시트와 Apps Script 이메일 자동 발송 기능으로 줄이는 방법을 실무 기준으로 정리합니다. 전일 기준 입출고 요약과 미처리 입고, 포화 로케이션 현황을 HTML 표 형태로 만들어, Apps Script 시간 기반 트리거로 매일 같은 시각에 자동 발송하는 흐름까지 한 번에 작성합니다. CONFIG만 본인 시트 구조에 맞게 조정하고, 테스트 실행 후 트리거를 등록하면 운영에 바로 쓸 수 있는 구성을 목표로 합니다.
앞에서 다룬 구글시트로 창고 재고관리 시스템 만들기 | Apps Script 자동화, 구글시트 재고 자동 집계 방법 | Apps Script 3편을 통해 입출고 기록과 재고 집계를 준비하셨다면, 이번 글은 그 결과를 “매일 아침 자동으로 도착하는 일일 리포트”로 마무리하는 단계라고 보시면 됩니다.
일일 재고 리포트를 자동으로 보내야 하는 이유
실무에서 일일 재고 리포트를 자동화하면 가장 먼저 줄어드는 것은 반복 작업과 그에 따른 실수입니다. 사람 손으로 매일 날짜 필터를 바꾸고, 피벗을 새로 만들고, 결과를 메일로 옮기는 과정에서는 날짜를 하루 잘못 잡거나, 특정 상태를 필터에서 빼먹는 일이 종종 발생합니다. 전일 기준으로 보고해야 하는데 오늘 기준으로 집계하는 등 기준일이 어긋나는 경우도 자주 생깁니다.
Apps Script로 로직을 한 번 정의해 두면, 기준일 계산과 집계 방식이 항상 같게 유지됩니다. 예를 들어 “오늘 아침 7시에, 전일 0시부터 24시까지의 입출고를 집계한다”라는 규칙을 코드에 박아 두면, 주말과 공휴일이 섞여도 스크립트는 같은 기준으로 계산합니다. 담당자가 교대되거나 휴가를 가더라도, 구글시트 트리거만 유지되면 고객사와 내부 팀은 같은 시각에 같은 형식의 리포트를 받아 볼 수 있습니다.
또한 메일 형식을 HTML 표로 통일해 두면, 모바일이나 웹메일에서도 가독성이 일정하게 유지됩니다. 실제 아침 브리핑에서 자주 다루는 항목은 “전일 입고·출고 건수”, “미처리 입고 목록”, “포화에 가까운 로케이션” 정도인 경우가 많습니다. 이 핵심 정보만 한 화면에 보이도록 구성하면, 관리자는 메일 제목과 첫 화면만 보고도 그날의 작업량과 병목 가능성을 빠르게 파악할 수 있습니다.
전일 기준 집계를 위한 시트 구조와 전제
Apps Script를 넣기 전에, 어떤 시트에서 어떤 데이터를 읽을지 전제를 명확히 하는 것이 중요합니다. 여기서는 다음과 같은 시트 구성을 기준으로 설명합니다. 실제 환경에서는 시트 이름이나 열 제목이 다를 수 있으므로, 코드 상단 CONFIG와 헤더명을 반드시 본인 구조에 맞게 조정해야 합니다.
입출고기록시트: 날짜, 구분(입고/출고), 품목, 수량, 상태(완료/미처리) 등을 행 단위로 기록합니다. 날짜 열은 반드시 “날짜 형식”으로 저장되어 있어야 합니다.로케이션현황시트: 로케이션명과 사용률(0~1 사이 값, 예: 0.87은 87%)이 포함된 현황입니다. 포화 기준 임계치(예: 0.9 이상)를 넘는 로케이션은 리포트에서 따로 강조합니다.
이번 글에서 작성하는 코드는 “기준일 = 실행 시점의 전일”로 통일합니다. 예를 들어 월요일 아침 7시에 트리거가 실행되면, 코드에서는 일요일 날짜를 기준으로 입출고 건수를 집계합니다. 이를 위해 날짜 비교 부분에서 today - 1일을 기준일로 계산하고, HTML과 메일 제목에도 같은 기준일을 표기합니다.
또 하나의 전제는 사용률 값의 범위입니다. 로케이션현황 시트의 “사용률” 열은 0~1 사이 실수라고 가정합니다. 만약 사용률을 이미 퍼센트(예: 95)로 입력하고 있다면, 코드에서 퍼센트 변환 부분을 수정해야 합니다. 이런 전제는 스크립트 상단 주석과 설정 값에 명시해 두면, 나중에 유지보수하는 사람이 구조를 이해하는 데 도움이 됩니다.
일일 요약 HTML 만들기 – buildDailySummary 함수
이 코드는 전일 기준 입고·출고 건수, 미처리 입고, 포화 로케이션 목록을 읽어 HTML 형식의 요약 표로 만들어 줍니다.
붙여넣을 위치는 구글시트 → 상단 메뉴 ‘확장 프로그램’ → ‘Apps Script’ → Code.gs 파일 맨 아래입니다.
붙여넣은 뒤에는 저장(Ctrl+S 또는 ⌘S)을 누르고, 나중에 testBuildDailySummary 함수를 한 번 실행해 로그를 확인합니다.
// → 여기만 본인 환경에 맞게 수정하세요
const CONFIG = { // → 설정 모음
SHEET_LOG: '입출고기록', // → 입출고 기록 시트명
SHEET_LOC: '로케이션현황', // → 로케이션 현황 시트명
REPORT_RECIPIENTS: '[email protected],[email protected]', // → 수신자 이메일(쉼표 구분)
REPORT_SENDER_NAME: '창고 일일 재고 리포트', // → 발신자 이름 표기
LOCATION_FULL_THRESHOLD: 0.9, // → 포화 기준 사용률(90%, 0~1 기준)
TIMEZONE: 'Asia/Seoul' // → 기준 시간대
};
// → 전일 기준 일일 요약 HTML을 만드는 함수
function buildDailySummary() { // → 일일 요약 생성 시작
const tz = CONFIG.TIMEZONE; // → 시간대 지정
const now = new Date(); // → 현재 시각
const yesterday = new Date(now.getTime() - 24 * 60 * 60 * 1000); // → 전일 시각
const ymd = Utilities.formatDate(yesterday, tz, 'yyyy-MM-dd'); // → 전일 날짜 포맷
const ss = SpreadsheetApp.getActiveSpreadsheet(); // → 현재 스프레드시트
const logSheet = ss.getSheetByName(CONFIG.SHEET_LOG); // → 입출고기록 시트
const locSheet = ss.getSheetByName(CONFIG.SHEET_LOC); // → 로케이션현황 시트
if (!logSheet) { // → 시트 없을 때
throw new Error('입출고기록 시트를 찾을 수 없습니다.'); // → 오류 메시지
}
if (!locSheet) { // → 시트 없을 때
throw new Error('로케이션현황 시트를 찾을 수 없습니다.'); // → 오류 메시지
}
const logData = logSheet.getDataRange().getValues(); // → 입출고 전체 데이터
const logHeader = logData[0]; // → 헤더 행
const logRows = logData.slice(1); // → 실제 데이터 행들
const idxDate = logHeader.indexOf('날짜'); // → 날짜 열 위치
const idxType = logHeader.indexOf('구분'); // → 입고/출고 열 위치
const idxStatus = logHeader.indexOf('상태'); // → 상태 열 위치
const idxItem = logHeader.indexOf('품목'); // → 품목 열 위치
const idxQty = logHeader.indexOf('수량'); // → 수량 열 위치
if (idxDate === -1 || idxType === -1 || idxStatus === -1) { // → 필수 열 확인
throw new Error('입출고기록 시트에 날짜/구분/상태 열이 필요합니다.'); // → 안내
}
let inCount = 0; // → 전일 입고 건수
let outCount = 0; // → 전일 출고 건수
let pendingReceipts = []; // → 미처리 입고 목록
logRows.forEach(row => { // → 각 행 반복
const rowDate = row[idxDate]; // → 날짜 값
if (!(rowDate instanceof Date)) { // → 날짜 형식 확인
return; // → 아니면 건너뜀
}
const rowYmd = Utilities.formatDate(rowDate, tz, 'yyyy-MM-dd'); // → yyyy-mm-dd
if (rowYmd !== ymd) { // → 전일과 다르면
return; // → 건너뜀
}
const type = row[idxType]; // → 구분 값
const status = row[idxStatus]; // → 상태 값
if (type === '입고') { // → 입고 행이면
inCount++; // → 입고 건수 +
if (status === '미처리') { // → 미처리면
pendingReceipts.push({ // → 배열에 추가
item: idxItem > -1 ? row[idxItem] : '', // → 품목
qty: idxQty > -1 ? row[idxQty] : '', // → 수량
status: status // → 상태
});
}
} else if (type === '출고') { // → 출고 행이면
outCount++; // → 출고 건수 +
}
});
const locData = locSheet.getDataRange().getValues(); // → 로케이션 전체 데이터
const locHeader = locData[0]; // → 로케이션 헤더
const locRows = locData.slice(1); // → 로케이션 데이터
const idxLocName = locHeader.indexOf('로케이션'); // → 로케이션명 열
const idxUsage = locHeader.indexOf('사용률'); // → 사용률 열
let fullLocations = []; // → 포화 로케이션 목록
if (idxLocName > -1 && idxUsage > -1) { // → 필요한 열 있을 때
locRows.forEach(row => { // → 각 로케이션 반복
const usage = row[idxUsage]; // → 사용률 값(0~1 전제)
if (typeof usage === 'number' && // → 숫자이고
usage >= CONFIG.LOCATION_FULL_THRESHOLD) { // → 임계치 이상
fullLocations.push({ // → 목록 추가
name: row[idxLocName], // → 로케이션명
usage: usage // → 사용률
});
}
});
}
let html = ''; // → HTML 누적 변수
html += '<h2>일일 재고 요약</h2>'; // → 제목
html += '<p>기준일: ' + ymd + '</p>'; // → 기준일 표시
html += '<h3>입출고 건수</h3>'; // → 소제목
html += '<table border="1" cellspacing="0" cellpadding="4">'; // → 표 시작
html += '<tr><th>구분</th><th>건수</th></tr>'; // → 헤더
html += '<tr><td>입고</td><td>' + inCount + '</td></tr>'; // → 입고 행
html += '<tr><td>출고</td><td>' + outCount + '</td></tr>'; // → 출고 행
html += '</table>'; // → 표 끝
html += '<h3>미처리 입고</h3>'; // → 소제목
if (pendingReceipts.length === 0) { // → 없으면
html += '<p>미처리 입고가 없습니다.</p>'; // → 안내 문구
} else {
html += '<table border="1" cellspacing="0" cellpadding="4">'; // → 표 시작
html += '<tr><th>품목</th><th>수량</th><th>상태</th></tr>'; // → 헤더
pendingReceipts.forEach(r => { // → 각 미처리 행
html += '<tr>'; // → 행 시작
html += '<td>' + r.item + '</td>'; // → 품목
html += '<td>' + r.qty + '</td>'; // → 수량
html += '<td>' + r.status + '</td>'; // → 상태
html += '</tr>'; // → 행 끝
});
html += '</table>'; // → 표 끝
}
html += '<h3>포화 로케이션</h3>'; // → 소제목
if (fullLocations.length === 0) { // → 없으면
html += '<p>포화 기준을 넘는 로케이션이 없습니다.</p>'; // → 안내 문구
} else {
html += '<table border="1" cellspacing="0" cellpadding="4">'; // → 표 시작
html += '<tr><th>로케이션</th><th>사용률</th></tr>'; // → 헤더
fullLocations.forEach(l => { // → 각 로케이션 행
const pct = Math.round(l.usage * 100); // → 퍼센트로 변환
html += '<tr>'; // → 행 시작
html += '<td>' + l.name + '</td>'; // → 로케이션명
html += '<td>' + pct + '%</td>'; // → 사용률
html += '</tr>'; // → 행 끝
});
html += '</table>'; // → 표 끝
}
return html; // → HTML 반환
}
// → buildDailySummary 결과를 테스트로 확인하는 함수
function testBuildDailySummary() { // → 테스트용
const html = buildDailySummary(); // → 요약 생성
Logger.log(html); // → 로그 출력
}제대로 됐는지 확인하는 법은 Apps Script 편집기에서 testBuildDailySummary를 실행한 뒤, 실행 로그에 기준일과 표 태그가 포함된 HTML 문자열이 출력되는지 확인하면 됩니다.
MailApp.sendEmail로 리포트 메일 보내기 – sendDailyReport 함수
이 코드는 방금 만든 HTML 요약을 가져와서, Apps Script의 MailApp.sendEmail 기능으로 이메일을 발송합니다.
붙여넣을 위치는 위 buildDailySummary 함수 바로 아래입니다.
붙여넣은 뒤에는 저장 후 testSendDailyReport 함수를 한 번 실행하고, 받은 편지함에 메일이 오는지 확인합니다.
// → 일일 재고 리포트를 이메일로 보내는 함수
function sendDailyReport() { // → 발송 시작
const tz = CONFIG.TIMEZONE; // → 시간대
const now = new Date(); // → 현재 시각
const yesterday = new Date(now.getTime() - 24 * 60 * 60 * 1000); // → 전일
const subjectDate = Utilities.formatDate(yesterday, tz, 'yyyy-MM-dd'); // → 제목용 날짜
const subject = '[창고] 일일 재고 리포트 - ' + subjectDate; // → 메일 제목
const htmlBody = buildDailySummary(); // → 리포트 HTML 생성
const recipients = CONFIG.REPORT_RECIPIENTS // → 수신자 목록
.split(',') // → 쉼표로 분리
.map(s => s.trim()) // → 공백 제거
.filter(s => s); // → 빈 값 제거
if (recipients.length === 0) { // → 수신자 없으면
throw new Error('REPORT_RECIPIENTS 설정에 이메일을 입력해 주세요.'); // → 안내
}
const options = { // → 메일 옵션
name: CONFIG.REPORT_SENDER_NAME, // → 발신자 이름
htmlBody: htmlBody // → HTML 본문
};
recipients.forEach(to => { // → 각 수신자에 대해
MailApp.sendEmail(to, subject, 'HTML을 지원하지 않는 메일입니다.', options); // → 발송
});
}
// → 메일 발송을 수동 테스트하는 함수
function testSendDailyReport() { // → 테스트용
sendDailyReport(); // → 발송 실행
}제대로 됐는지 확인하는 법은 testSendDailyReport를 실행하고, CONFIG에 넣은 이메일 주소의 받은 편지함에 “[창고] 일일 재고 리포트 - 전일날짜” 제목의 메일이 도착하는지 확인하면 됩니다.
매일 같은 시각에 자동 발송 – 시간 기반 트리거 설정
이제 사람이 매번 실행 버튼을 누르지 않아도 되도록 Apps Script 시간 기반 트리거를 설정합니다.
아래 코드는 매일 아침 7시에 sendDailyReport를 자동 실행하는 시간 기반 트리거를 생성합니다.
붙여넣을 위치는 앞 함수들 아래 아무 곳이나 괜찮습니다.
붙여넣은 뒤에는 createDailyTrigger를 한 번만 실행해 두면 됩니다.
// → 매일 일정 시각에 sendDailyReport를 실행하는 트리거 생성
function createDailyTrigger() { // → 트리거 생성 시작
const functionName = 'sendDailyReport'; // → 대상 함수명
const triggers = ScriptApp.getProjectTriggers(); // → 기존 트리거 목록
triggers.forEach(t => { // → 각 트리거 검사
if (t.getHandlerFunction() === functionName) { // → 같은 함수면
ScriptApp.deleteTrigger(t); // → 삭제 후 재생성
}
});
ScriptApp.newTrigger(functionName) // → 새 트리거 생성
.timeBased() // → 시간 기반
.atHour(7) // → 오전 7시
.everyDays(1) // → 매일 실행
.inTimezone(CONFIG.TIMEZONE) // → 지정 시간대
.create(); // → 생성 완료
}제대로 됐는지 확인하는 법은 Apps Script 편집기 오른쪽의 ‘트리거’ 메뉴를 열어, sendDailyReport가 매일 7시에 실행되도록 등록되어 있는지 확인하면 됩니다.
설치·테스트 순서와 구글시트 트리거 설정 팁
실제 운영에 바로 사용하는 대신, 네 단계로 나누어 설치·테스트를 진행하는 것이 안전합니다. 첫째, Apps Script 편집기에서 기존 코드 맨 위에 CONFIG 블록을 추가하고, 시트 이름과 TIMEZONE, REPORT_RECIPIENTS를 실제 환경에 맞게 바꿉니다. 이때 초기 테스트는 본인 1인 이메일만 넣어 두고 검증한 뒤, 검증 이후에 팀 메일링 리스트나 다수 수신자를 추가하는 편이 좋습니다.
둘째, testBuildDailySummary를 실행해 전일 기준 HTML 요약이 올바르게 만들어지는지 확인합니다. 실행 로그에서 기준일, 입출고 건수, 미처리 입고 표 구조 등을 검토합니다. 만약 전일 데이터가 있는데도 건수가 0으로 나온다면, 날짜 열이 텍스트로 들어가 있거나, 열 제목이 코드에서 찾는 이름(날짜/구분/상태)과 다른 경우일 가능성이 큽니다.
셋째, testSendDailyReport를 실행해 Apps Script 이메일 자동 발송이 정상 작동하는지 확인합니다. 회사 계정에서는 보안 정책에 따라 외부 메일 발송이 제한될 수 있으므로, 처음에는 내부 도메인 주소끼리 테스트하고, 스팸함으로 분류되지 않는지도 함께 확인하는 것이 좋습니다. MailApp.sendEmail 예제 그대로이므로, 권한 허용만 완료되면 일반적인 환경에서는 별다른 문제 없이 동작합니다.
마지막으로, createDailyTrigger를 실행해 시간 기반 트리거를 생성합니다. 바로 동작을 보고 싶다면, 스크립트 편집기에서 트리거 메뉴로 들어가 실행 시간을 현재 시각보다 5~10분 뒤로 설정해 두고 실제 자동 발송이 되는지 확인한 뒤, 운영 시간(예: 오전 7시)으로 다시 조정합니다. 이렇게 하면 “스케줄은 걸었는데 실제로 메일이 안 온다”라는 상황을 줄일 수 있습니다.
실무 팁 – 구조를 고정하고, 설정은 CONFIG로 몰아두기
현장에서 구글시트 재고 관리 자동화를 운영해 보면, 코드보다 시트 구조와 규칙 관리가 더 중요하다는 점을 자주 느끼게 됩니다. 이 글의 예제는 열 위치를 indexOf('날짜'), indexOf('구분')처럼 헤더명으로 찾습니다. 이 방식은 중간에 열을 추가해도 안전하지만, 헤더명을 바꾸면 바로 오류가 납니다. 따라서 운영 규칙으로 “입출고기록 시트의 열 제목은 변경하지 않는다. 새로운 정보가 필요하면 오른쪽 끝에 새 열을 추가한다.” 같은 기준을 팀에 공유해 두는 것이 좋습니다.
또 하나는 임계치와 기준일 같은 운영 파라미터를 코드 상단 CONFIG로 모아 두는 것입니다. 포화 기준 사용률(LOCATION_FULL_THRESHOLD), 리포트 발송 시간대(TIMEZONE), 수신자 목록(REPORT_RECIPIENTS) 등을 하나의 객체에 모아 두면, 나중에 다른 창고나 다른 고객에 맞춰 복사·전파할 때도 CONFIG만 수정하면 재사용이 가능합니다. 필요하다면 이후 단계에서 CONFIG 값을 시트의 “설정” 탭에서 읽어 오는 방식으로 바꾸어, 운영자가 코드 수정 없이 임계치를 조정할 수 있도록 확장할 수도 있습니다.
Apps Script 시간 기반 트리거를 여러 개 운영할 때는, 한 계정에 걸린 트리거 목록을 주기적으로 점검하는 것이 좋습니다. 같은 함수에 대해 중복 트리거가 생성되면 동일 리포트가 두 번씩 발송되는 사례가 생길 수 있습니다. 이 글의 createDailyTrigger는 동일 함수에 대한 트리거를 먼저 모두 삭제한 후 새로 만드는 방식으로 이런 중복을 예방하도록 작성했습니다.
자주 발생하는 오류와 해결 방법
이메일 자동 발송과 트리거 설정을 실제로 배포했을 때 실무에서 자주 마주치는 오류는 몇 가지 패턴이 있습니다. 첫 번째는 권한 관련 오류입니다. 처음 testSendDailyReport나 createDailyTrigger를 실행할 때 “이 앱은 확인되지 않았습니다”라는 경고가 뜨는 경우가 있습니다. 이는 회사 내부에서 만든 Apps Script 프로젝트에 대한 일반적인 경고로, ‘고급’ → “프로젝트 이름(안전하지 않음)”을 차례로 클릭해 진행하면 1회 승인 이후에는 동일 계정에서 다시 묻지 않습니다.
두 번째는 헤더명 불일치로 인한 에러입니다. 예를 들어 날짜 열 제목이 실제로는 ‘입출고일자’인데 코드에서는 ‘날짜’를 찾고 있다면, idxDate === -1이 되어 “날짜/구분/상태 열이 필요합니다”라는 오류가 발생합니다. 이때는 시트의 헤더명을 코드에서 사용한 이름으로 바꾸거나, 코드에서 indexOf('날짜')라고 되어 있는 부분을 실제 헤더명으로 수정하면 됩니다. 한 번 구조를 맞춘 뒤에는, 팀 내에서 열 제목 변경을 자제하는 운영 합의가 있어야 안정적으로 유지됩니다.
마지막으로, Apps Script 이메일 발송에는 일일 발송 제한이 있습니다. 일일 재고 리포트 한두 건 수준에서는 제약에 걸린 경험은 많지 않지만, 같은 계정으로 다른 자동 발송 스크립트까지 여러 개 돌리고 있다면, Google Workspace 관리 콘솔이나 공식 문서를 통해 계정별 발송 한도를 미리 확인해 두는 것이 안전합니다. 발송 실패 시 알림을 받도록 트리거 실패 알림 이메일 주소를 설정해 두면, 조기에 문제를 인지하는 데 도움이 됩니다.
맺음말 – 오늘 바로 전일 재고 리포트 자동화를 시도해 보기
전일 재고 리포트는 창고 운영에서 빼기 어려운 반복 업무이지만, 내용과 형식이 매일 거의 동일하다는 점에서 자동화에 특히 잘 맞는 영역입니다. 구글시트와 Apps Script 시간 기반 트리거를 활용하면, 기준일 계산·집계·HTML 변환·이메일 발송까지 한 번 정해 둔 로직으로 반복 실행할 수 있고, 담당자는 데이터 정확도와 현장 운영에 더 많은 시간을 쓸 수 있습니다.
지금 바로 시도해 볼 수 있는 행동은 간단합니다. 먼저 재고 관리 구글시트를 열어 입출고기록 시트의 열 제목이 이 글에서 사용한 이름(날짜, 구분, 상태, 품목, 수량)과 어떻게 다른지 확인한 뒤, Apps Script 편집기에 CONFIG 블록과 buildDailySummary, sendDailyReport, createDailyTrigger 코드를 순서대로 붙여 넣어 보시기 바랍니다. 이후 testBuildDailySummary와 testSendDailyReport를 차례로 실행해 결과를 검증하고, 문제가 없으면 일일 트리거를 등록해 다음 날 아침 받은 편지함에서 전일 재고 리포트가 자동으로 도착하는 것을 직접 확인해 보시기 바랍니다.
