유학원 등록부터 PDF 발송까지 한 번에 처리하는 자동화 시스템
Google Sheets · Apps Script Web App · PDF 자동 생성 · Google Drive · Gmail
보호자와 학생의 등록 정보를 한 번 입력하면 학생용 인보이스, 입학허가서, Agent 문서, 픽업확인서를 자동 생성하고 고객별 폴더 저장과 이메일 발송까지 이어지도록 구축한 프로젝트입니다.
신청 정보를 여러 문서에 다시 옮겨 적고 계신가요?
유학원 등록 업무에서는 같은 정보가 여러 번 사용됩니다.
보호자와 학생의 영문명, 생년월일, 수강 과정, 숙소, 연수 기간을 먼저 정리한 뒤 학생용 인보이스에 입력합니다. 같은 내용을 입학허가서와 Agent 문서에도 다시 옮기고, 픽업확인서에는 가족 구성과 항공 관련 정보를 별도로 작성합니다.
문서를 다 만든 뒤에는 PDF로 저장하고, 고객별 폴더를 만들고, 필요한 파일을 이메일에 다시 첨부해야 합니다.
각 단계는 어렵지 않습니다.
하지만 같은 정보를 여러 번 확인하고 복사하는 과정에서 시간이 들고, 이름이나 날짜가 문서마다 다르게 입력될 가능성도 생깁니다.
자동화 전에는
담당자는 신청 한 건을 처리할 때 아래 업무를 반복해야 했습니다.
•
보호자와 학생 정보를 시트에 정리
•
문서별 양식에 이름과 생년월일을 다시 입력
•
학생 수에 맞춰 표 행을 추가하거나 삭제
•
연수 시작일과 기간을 문서마다 다시 확인
•
학생용 인보이스, 입학허가서, Agent 문서, 픽업확인서를 각각 PDF로 저장
•
고객별 Google Drive 폴더를 생성
•
생성된 PDF를 찾아 이메일에 첨부
•
어떤 문서를 생성하고 발송했는지 별도로 기록
학생이나 보호자가 늘어나면 입력해야 할 칸도 함께 늘어납니다.
문서를 수정한 뒤 기존 PDF와 새 PDF가 한 폴더에 함께 남으면 어떤 파일이 최종본인지 다시 확인해야 하는 문제도 있었습니다.
우리가 해결하고 싶었던 문제
이번 프로젝트의 목표는 단순히 PDF를 빠르게 만드는 것이 아니었습니다.
한 번 입력한 신청 정보가 등록 관리, 문서 생성, 파일 보관, 이메일 발송까지 끊기지 않고 이어지는 구조를 만드는 것이 목표였습니다.
담당자는 문서의 셀 위치나 파일 저장 경로를 기억하지 않아도 되고, 신청 내용을 확인한 뒤 필요한 작업만 선택할 수 있어야 했습니다.
즉,
문서마다 사람을 이동시키는 것이 아니라, 신청 한 건을 중심으로 모든 문서가 움직이도록 설계했습니다.
자동화 구조
사용자는 Google Sheets를 직접 편집하는 대신 Apps Script 웹앱에서 신청 정보를 등록하고 관리합니다.
Apps Script Web App
신규 신청 등록
↓
신청데이터
보호자 · 학생 · 숙소 · 연수 정보 저장
↓
Google Sheets 문서 양식 4종
학생용 인보이스 · 입학허가서 · Agent · 픽업확인서
↓
PDF 자동 생성 + 워터마크 합성
↓
고객별 Google Drive 폴더
↓
Gmail 첨부 발송 + 생성 로그 기록
Plain Text
복사
담당자는 웹앱 한 화면에서 다음 작업을 처리할 수 있습니다.
•
신규 신청 등록
•
최근 신청 목록 확인
•
이름 또는 신청키 검색
•
신청 상태 확인
•
필요한 문서만 선택하여 PDF 생성
•
PDF 생성 후 즉시 이메일 발송
•
생성된 문서와 고객 폴더 바로 열기
입력 화면을 업무 흐름에 맞춰 다시 만들었습니다
기존에는 정해진 칸에 맞춰 정보를 입력해야 했지만, 웹앱에서는 실제 등록 순서에 맞춰 필요한 항목만 보여줍니다.
기본 등록 정보
•
일반 / 체험단 구분
•
신규 / 재등록 구분
•
숙소와 기타 숙소명
•
연수 시작일
•
연수 기간
보호자 정보
•
보호자 인원 선택
•
여권상 영문명
•
생년월일
•
대표 보호자의 카카오톡 ID
학생 정보
•
학생 인원 선택
•
한글 이름과 여권상 영문명
•
성별과 생년월일
•
수강 과정
보호자와 학생 수를 선택하면 필요한 입력 영역이 자동으로 늘어납니다.
한 명을 등록할 때는 한 명의 입력란만 보이고, 가족 구성이 커지면 그 수에 맞춰 입력란이 만들어집니다. 사용자는 사용하지 않는 수십 개의 빈 칸을 볼 필요가 없습니다.
데스크톱뿐 아니라 모바일 화면에서도 한 열 구조로 바뀌도록 반응형으로 구성했습니다.
신청데이터를 하나의 기준점으로 만들었습니다
웹앱에서 저장된 정보는 신청데이터 시트에 한 건씩 기록됩니다.
이 시트에는 다음 정보가 함께 보관됩니다.
•
접수일시와 신청키
•
현재 처리 상태
•
체험단 및 재등록 여부
•
숙소와 연수 일정
•
보호자와 학생 수
•
보호자별 정보
•
학생별 이름, 성별, 생년월일, 과정
•
요청사항
•
최근 수신 이메일
•
최근 생성 폴더 주소
•
원본 입력 JSON
원본 JSON도 함께 저장하기 때문에 화면이나 문서 구조가 확장되더라도 최초 입력 내용을 다시 확인할 수 있습니다.
기존 문서 양식과의 호환을 위해 과거 데이터 구조에도 필요한 값을 함께 기록하도록 연결했습니다. 시스템을 새로 만들면서 기존 시트와 문서 계산식을 모두 버리지 않고, 새 웹앱과 기존 양식이 함께 움직이도록 만든 것입니다.
Google Sheets를 문서 제작 엔진으로 사용했습니다
이번 프로젝트에서는 문서 디자인을 코드로 매번 새로 그리지 않았습니다.
학생용 인보이스, 입학허가서, Agent 문서, 픽업확인서를 각각 Google Sheets 양식으로 구성하고, 선택된 신청키에 따라 셀의 내용이 자동으로 바뀌도록 만들었습니다.
이 방식의 장점은 분명합니다.
•
문서 디자인을 시트에서 직접 확인할 수 있음
•
문구와 색상, 표 크기를 코드 수정 없이 조정 가능
•
인쇄 범위를 정확히 지정할 수 있음
•
기존 문서와 비슷한 배치를 유지하기 쉬움
•
운영자가 문서 내용을 눈으로 검수할 수 있음
웹앱에서 신청 건을 선택하면 문서 양식의 선택키가 변경되고, 연결된 수식이 해당 가족의 정보로 문서를 다시 구성합니다.
PDF 4종을 한 번에 생성합니다
현재 자동 생성되는 문서는 다음과 같습니다.
•
학생용 인보이스: 학생과 보호자 정보, 숙소, 과정 및 비용 안내
•
입학허가서: 연수 기간, 학생 영문명, 생년월일과 수강 과정
•
Agent 문서: 유학원 및 협력사 확인용 문서
•
픽업확인서: 가족명, 픽업 인원, 항공편과 도착 정보
사용자는 필요한 문서만 체크해서 생성할 수 있습니다.
신청 저장과 동시에 PDF 4개를 생성할 수도 있고, 신청만 먼저 저장한 뒤 검토를 마치고 문서를 만들 수도 있습니다.
워터마크는 PDF 생성 후 합성합니다
문서 안에 이미지를 직접 넣는 방식은 표와 본문의 위치를 밀어낼 수 있습니다.
그래서 이번 시스템에서는 시트를 먼저 A4 PDF로 변환한 뒤, PDF의 각 페이지 중앙에 PNG 워터마크를 반투명으로 합성하도록 구성했습니다.
Google Sheets 문서 양식
↓
A4 PDF 변환
↓
PNG 워터마크 합성
투명도 18%
↓
최종 PDF 저장
Plain Text
복사
워터마크는 문서 내용과 별도로 처리되기 때문에 표의 크기나 글자 배치에 영향을 주지 않습니다. 문서가 여러 장이어도 모든 페이지에 같은 비율로 적용됩니다.
파일 관리도 신청 건을 기준으로 자동화했습니다
PDF는 지정된 Google Drive 루트 폴더 아래에 고객별 폴더를 만들어 저장됩니다.
같은 날짜에 같은 이름의 문서를 다시 생성하면 이전 파일은 휴지통으로 이동하고 새 파일만 남도록 처리했습니다. 수정 후 재생성했을 때 비슷한 이름의 PDF가 계속 쌓이지 않도록 하기 위한 구조입니다.
문서가 생성되면 다음 정보가 로그에 남습니다.
•
생성일시
•
신청키
•
보호자 영문명
•
문서종류
•
PDF 파일명
•
파일 주소
•
고객 폴더 주소
•
이메일 발송 상태
웹앱에서는 생성된 문서와 고객 폴더를 버튼으로 바로 열 수 있습니다.
이메일 발송까지 같은 화면에서 처리합니다
받는 이메일 주소를 입력하고 발송 버튼을 누르면 선택한 문서가 자동으로 첨부됩니다.
이메일 제목과 본문은 별도의 시트에서 관리할 수 있고, 보호자 이름은 발송 시 자동으로 치환됩니다.
사용자는 다음 세 가지 방식 중 필요한 작업을 선택할 수 있습니다.
•
PDF만 생성
•
기존 PDF를 이메일로 발송
•
PDF를 새로 생성한 뒤 바로 이메일 발송
발송이 완료되면 신청 상태와 최근 수신 이메일도 자동으로 갱신됩니다.
동시에 작업해도 데이터가 섞이지 않도록 했습니다
웹앱에서는 저장과 PDF 생성 같은 작업이 동시에 요청될 수 있습니다.
이를 위해 LockService를 사용해 한 작업이 신청데이터를 저장하거나 문서를 생성하는 동안 다른 작업이 같은 영역을 덮어쓰지 않도록 처리했습니다.
또한 서버와 화면 양쪽에서 필수 항목을 검증합니다.
•
숙소, 시작일, 연수 기간 확인
•
보호자 1의 영문명과 생년월일 확인
•
학생 1의 영문명 확인
•
이메일 형식 확인
•
신청 건과 문서 선택 여부 확인
작업 중에는 전체 화면 로딩 표시가 나타나 중복 클릭도 방지합니다.
자동화 후 달라진 점
등록 담당자
•
시트의 여러 열을 직접 찾지 않고 웹앱에서 신청 등록
•
보호자와 학생 수에 맞는 입력란만 사용
•
신청 목록과 처리 상태를 한 화면에서 확인
•
문서 생성과 이메일 발송을 같은 화면에서 처리
문서 담당자
•
이름과 생년월일을 문서마다 다시 입력하지 않음
•
동일한 신청 정보로 PDF 4종 생성
•
수정 후 재생성해도 이전 파일과 혼동되지 않음
•
워터마크가 포함된 동일한 품질의 문서 출력
시스템
•
신청데이터와 원본 JSON 동시 보관
•
기존 시트 양식과 호환
•
고객별 폴더 자동 생성
•
문서 생성 이력 및 발송 상태 기록
•
데스크톱과 모바일 대응
이번 프로젝트의 찐 포인트
이 프로젝트의 핵심은 PDF를 많이 만드는 것이 아닙니다.
신청 정보를 받은 뒤 담당자가 여러 시트와 파일을 오가던 흐름을 신청 한 건 중심의 작업 화면으로 바꾼 것이 핵심입니다.
웹앱은 입력과 실행을 담당하고, Google Sheets는 데이터를 보관하고 문서를 구성하며, Apps Script는 PDF 생성과 파일 관리, 이메일 발송을 연결합니다.
각 도구가 잘하는 역할을 나눠 맡도록 구성했기 때문에 운영자는 복잡한 내부 구조를 몰라도 사용할 수 있습니다.
좋은 자동화는 기능을 많이 보여주는 것이 아니라, 한 번 입력한 정보가 마지막 업무까지 다시 입력되지 않게 만드는 것이라고 생각합니다.
이런 업무에 적합합니다
•
가족연수 및 유학원 등록 문서를 반복 작성하는 경우
•
한 신청 건으로 여러 종류의 문서를 만들어야 하는 경우
•
보호자와 학생 수가 신청마다 달라지는 경우
•
Google Sheets 문서 양식을 유지하면서 웹 입력 화면이 필요한 경우
•
PDF 저장과 고객별 폴더 정리를 자동화하고 싶은 경우
•
문서 생성 후 이메일 발송까지 한 번에 처리하고 싶은 경우
•
기존 시트 구조를 유지한 채 업무 화면만 개선하고 싶은 경우