사내 도서관 시스템 구현(feat.AppsScript)
0. 개요
평소 데이터 분석 및 업무 자동화에 관심이 많아 링크드인이나 여러 기술 블로그에서 관련 글을 즐겨보곤 합니다.
그러던 중 Joshua님의 사내 도서관 대출/반납 시스템 구축 후기글을 읽게 되었고, 현업에서 자주 사용하던 구글 스프레드시트(Feat. appsScript)와 Slack을 활용해 효율성을 증대시킬 수 있었다는 점에서 큰 흥미를 느끼게 되었어요.
아티클에 정리해 주신 AppsScript 코드를 이해하고 싶어 졌고, 비슷한 환경을 직접 구현해보고 싶어 졌습니다.
나도 할 수 있다!평소 자주 가는 도서관에 AppsScript관련 도서가 있지 않을까 찾던 중 다카하시 노리아키 저자의 '구글 앱스 스크립트 완벽 가이드' 도서를 발견하게 되었고 공부를 하며 AppsScript에 대해 이해도를 높일 수 있었습니다.
혹시 AppsScript에 관심이 있으시다면, 해당 도서를 강추합니다!
나는 왜 구글 스프레드시트와 AppsScript에 관심을 갖게 되었는가?
현업에서 구글 스프레드시트를 자주 사용하곤 했었는데요, 이때 여러 문제를 맞닥뜨릴 수 있었고 해결 방법을 고민하던 때가 있었습니다.
하나의 상황을 예로 들어볼게요.(e.g.출고요청)
샴푸, 바디워시를 판매하는 기업에서 조직원들이 상품 샘플 출고를 저에게 요청합니다.
이때 저는 통일된 양식과 높은 공유성을 가진 구글 스프레드시트로 "출고요청시트"를 제작합니다.
하지만, 구글 스프레드시트를 통해 사내 요청을 받고자 한다면 아래와 같은 문제가 발생할 수 있습니다.
1. 요청자 여러 명이 동시 입력을 한다면 누락 및 오류가 발생할 수 있다.
2. 주소를 오 입력하게 된다면 수기로 걸러야 된다.
김아무개와 준스톤은 다행히 동시 입력을 안 한 듯하다. 하지만 주소에 오기 입을 한 김아무개!실제 현업에서는 위와 같은 문제를 종종 겪곤 했습니다.
당시에 AppsScript를 사용하기는 했었지만 매크로 기능을 통해 반복작업을 자동화할 뿐, 관련 코드에 대한 지식은 미비했습니다.
그래도 복잡해 보이는 매크로 코드를 보며 ‘앱스크립트를 이용한다면 시트를 좀 더 내 입맛에 맞게 커스텀할 수 있지 않을까?’ 하는 생각을 약소하게나마 할 수 있었습니다.
프로그래밍 문맹시절 매크로 코드는 너무 폭력적이었습니다.이제는 AppsScript코드를 조금은(아주 조금입니다.) 이해할 수 있게 되었고, 그 첫 단추로 Joshua님의 사내 도서관 대출/반납 시스템 구축을 저만의 방식으로 구현해보고자 합니다.
1. 대출/반납 시스템 생성
1-1. 도서대출현황 Sheet
먼저 도서대출현황시트의 틀을 구성했습니다.
도서를 기준으로 대출을 희망하는 인원의 성명을 작성하면 대여가 되는 방식입니다.
[주요 컬럼 설명]
[A컬럼]성명 : 대출여부의 기준이 되는 컬럼입니다. [RD_인사] 시트의 데이터를 활용해 드롭다운 형식으로 대출자 성명을 표기합니다.
[RD_인사]시트 예시[B컬럼]대출가능여부 : 내장함수를 활용해 "성명"의 여부에 따라 값을 출력합니다.
[H컬럼]대여일 : "성명"이 입력된 날짜를 기입합니다.
1-2. AppsScript : onEdit 함수
AppsScript를 이용해 [A컬럼]성명의 값이 변경될 때마다 코드가 실행되도록 했어요.
이때, 내장함수인 onEdit 함수를 사용했습니다.
onEdit 함수는 구글 스프레드시트에서 셀을 수정할 때 자동으로 실행되는 아주 유용한 함수예요.
[STEP 1. 변수 선언]
onEdit 함수가 호출되면 구글스프레드시트의 주요 객체를 선언했어요.
https://gist.github.com/junstones/85f71d070745635b416b7376ef512c4b
ss : 현재 구글 시트 객체
loanSheet : 도서대출현황 시트
logSheet : 대출내역 시트
logRange : 대출내역 시트에서 데이터가 존재하는 셀 범위
logValues : 대출내역시트 셀 범위의 셀 값
logLastRow : 대출내역시트 셀 범위 중 가장 마지막 행 번호
[STEP 2. Lock : 시트 동시 수정 방지]
Lock 처리를 통해 여러 사용자로 하여금 onEdit 함수가 동시다발적으로 호출되어 데이터가 꼬이는 일을 사전에 방지했어요.
https://gist.github.com/junstones/0973b8dfca650d46e0ab669009947169
lock : lockService는 AppsScript에서 여러 사용자가 동시에 같은 스크립트를 실행할 때 충돌을 방지하는 기능입니다.
이해하기 쉽게 Lock이 없는 경우의 상황을 예로 들어볼게요.
▶️ e.g.문제 상황 (Lock이 없을 때)
1. A 사용자가 "대출 중"을 입력하여 onEdit 함수가 실행됨.
2. 같은 순간 B 사용자도 "대출 중"을 입력하고 함수가 onEdit 실행됨.
3. 결과 : 두 함수가 동시에 실행되면서 로그가 두 번 추가되거나, 데이터가 꼬일 가능성이 있음.
▶️ e.g.해결 방법 (Lock을 사용할 때)
1. A, B 사용자가 거의 동시에 "대출 중"을 입력(A 사용자가 살짝 빨랐다) -> A 사용자의 onEdit 함수가 실행됨.
2. A 사용자의 onEdit 함수가 lock.waitLock(5000)을 실행함.
- 다른 사용자는 5초 동안 onEdit 실행을 기다려야 돼요.
3. A 사용자의 함수가 먼저 실행되고 끝남 -> lock.releaseLock() 실행.
4. 이제 B 사용자의 함수가 onEdit 실행됨.
[STEP 3. onEdit 이벤트 발생 시나리오]
onEdit 이벤트가 도서대출현황 시트의 [A컬럼]성명에 대해서만 코드가 실행되도록 조건문을 설정했어요.
https://gist.github.com/junstones/e2e3c0d1bd8a572156093f10f59da03d
- range : e는 onEdit 함수의 파라미터로 수정된 셀의 값을 가지고 있습니다. 이때 e는 단인 셀이 될 수도, 범위가 될 수도 있어요.
- if (sheetCheck && cellCheck) { : 수정한 셀이 도서대출현황시트이며 A열인경우
조건에 맞는 경우 변동 전과 후의 로그를 모두 기록했어요.
https://gist.github.com/junstones/4a00e83dab1e039866969e7a4adc8d5a
대출 시에 [A컬럼]성명에 본인 이름을 입력하지만, 반납 시에도 [A컬럼]성명에 "반납완료❤️"를 입력해야 되기 때문에 두 케이스를 나누어 대응할 수 있도록 했어요.
성명에는 이름과 반납완료❤️가 입력됩니다.▶️ case : 반납 시
oldValue는 e 객체의 속성으로 수정 전 값을 가져와요. (참고로, 변경 후 값은 .value 를 사용하면 됩니다.)
해당 객체를 활용해 대출자 이름과 "반납완료❤️"값을 저장합니다.
저장이 완료되면 [A컬럼]성명은 다음 사용자를 위해 빈칸으로 만들었어요.
[STEP 4. 대출 로그 기록]
[대출내역] 시트에 대출 로그를 기록하도록 하였습니다.
https://gist.github.com/junstones/c29a237b1fee7cc74e5dbf27387ba7fa
만약 도서대출현황 시트 [B칼럼] 대출가능여부가 "대출중" 이라면 [i컬럼]대출일과 대출내역 시트에 대출 로그를 기록하도록 했어요.
대출내역 시트의 대출로그[STEP 5. 반납 로그 기록]
마지막으로 반납을 하게 되었을 때 반납일을 기록하도록 하였습니다.
https://gist.github.com/junstones/c73e2fade892ba57984b6dfc3aabeb92
도서대출현황 시트의 [B컬럼]대출가능여부가 "반납완료❤️"로 입력되었을 때, 현재 일자를 저장하고 [i컬럼]대출일을 지웠어요.
이후 logIndex변수를 사용해 대출내역 시트의 "대출자", "도서명", "대출일" 데이터 범위 내에서 반납데이터를 매칭해 알맞은 사용자 로그에 반납일을 입력하도록 작업했어요.
▶️ 날짜 객체를 다룰 때 주의할 점
"도서대출현황 시트 [i컬럼]대출일과 대출내역시트 [D컬럼]대출일 매칭이 안 돼요!"
도서대출현황시트의 대출일을 loanDayCopy 변수에 저장 후, 그대로 대출내역시트 [D컬럼]대출일과 매칭을 진행하고자 했지만 매칭이 안 되는 불상사가 발생했습니다.
시트 상으로 볼 때는 분명 똑같은 날짜 형식인데 말이죠.
▶️왜 그럴까?
Google Sheets에서 getValue()로 날짜를 가져오면 UTC(세계 표준시) 기준으로 불러오게 됩니다.
따라서 new Date(loanDayCopy).toISOString().split('T')[0]처럼 "yyyy-mm-dd"형태로 치환하는 과정이 필요합니다.
아하!예를 들어, 셀에 "2025-02-21"이 저장되어 있다고 가정해 보겠습니다.
이 셀값을 getValue()로 가져오면 내부적으로 "2025-02-20T15:00:00.000Z" (UTC 기준) 값으로 저장이 됩니다.
즉, 형식 차이로 인해 날짜 매칭에 어려움이 있을 수 있으므로 new Date()로 변환 후 toISOString().split('T')[0]을 사용해 "yyyy-mm-dd" 형식으로 맞춰야 합니다.
[코드 총 정리]
https://gist.github.com/junstones/8e341924bb3338aa90170188a4d14c5a
[시연 영상]
1. 대출시
성명을 입력하면 대출내역시트에 Log 자동 입력!2. 반납시
반납완료❤️입력시 반납자에 맞춰 반납일이 잘 매칭되는 모습!2. 마무리하며
데이터를 쌓기전 어디에 활용될지를 미리 생각하고 프레임을 짜는것이 중요하다고 생각합니다.
feat.오늘 점심 뭐먹지그렇기에 이번 프로젝트에서는 단순한 자동화에 그치지 않고, 로그(log) 데이터를 축적하여 다음 단계(STEP)의 가능성을 확장하는 데에도 초점을 맞췄습니다.
예를 들어,
- 반납일과 대출일 사이의 중앙값을 계산하여 적정 대출 기간을 산출할 수 있습니다.
- 도서별 대출량을 분석해 수요에 맞춰 도서 수량을 조정할 수도 있습니다.
사내 도서 대출 프로그램은 현업에서 반드시 필요한 영역은 아니지만, 이처럼 사소한 부분이라도 데이터를 활용해 자동화를 이룬다면 더 나은 의사결정과 운영 최적화에 큰 도움을 줄 수 있을 것이라 느꼈습니다.
