매일 20분씩 하던 엑셀 정리, 버튼 한 번으로 끝내기
매일 아침 전날 실적 파일을 열어 다음과 같은 작업을 반복하는 직장인이 많습니다.
- 이름과 부서명 앞뒤의 불필요한 공백 삭제
- 휴대전화 번호 형식 통일
- 금액에 포함된 쉼표와 ‘원’ 문자 제거
- 처리 상태가 비어 있는 행에 ‘확인 필요’ 입력
- 중복된 접수번호 표시
데이터가 20행이라면 손으로 처리할 수 있습니다. 하지만 매일 300행, 500행씩 쌓이면 단순 정리에만 상당한 시간이 들어갑니다. 더 큰 문제는 복사와 붙여넣기를 반복하다가 행을 잘못 지우거나 숫자 형식을 바꾸는 실수가 생긴다는 점입니다.
이 글에서는 엑셀 파일을 Google 스프레드시트로 열고, ( ( (자세히 보기)자세히 보기)자세히 보기)Google Apps Script로 ‘데이터 정리’ 메뉴를 만들어 한 번의 클릭으로 수백 행을 정리하는 과정을 실제 예시와 함께 설명합니다. 코드를 이해하기 위해 프로그래밍을 따로 공부할 필요는 없습니다. 예제의 열 이름만 현재 업무 파일에 맞게 바꾸면 바로 시험할 수 있습니다.
먼저 자동화할 업무 장면을 정해보겠습니다
아래와 같은 일일 접수 현황을 매일 전달받는 상황을 가정하겠습니다.
정리 전 원본 데이터
| 접수번호 | 담당자 | 부서 | 연락처 | 처리금액 | 상태 |
|---|---|---|---|---|---|
| A-1001 | 박지훈 | 영업1팀 | 01012345678 | 1,250,000원 | 완료 |
| A-1002 | 이서연 | 고객 지원팀 | 010-9876-5432 | 850000 | |
| A-1002 | 최민수 | 영업1팀 | 010 5555 1111 | 320,000 원 | 처리중 |
이 표에는 눈으로는 쉽게 보이지만 집계와 필터링을 방해하는 문제가 섞여 있습니다.
- 담당자와 부서명에 공백이 포함되어 있습니다.
- 연락처 형식이 세 가지로 다릅니다.
- 처리금액이 숫자가 아닌 문자로 저장될 수 있습니다.
- 상태가 비어 있는 행이 있습니다.
- 접수번호 A-1002가 중복되었습니다.
목표는 이 데이터를 다음과 같이 바꾸는 것입니다.
버튼 클릭 후 결과
| 접수번호 | 담당자 | 부서 | 연락처 | 처리금액 | 상태 | 중복 여부 |
|---|---|---|---|---|---|---|
| A-1001 | 박지훈 | 영업1팀 | 010-1234-5678 | 1250000 | 완료 | 정상 |
| A-1002 | 이서연 | 고객 지원팀 | 010-9876-5432 | 850000 | 확인 필요 | 중복 |
| A-1002 | 최민수 | 영업1팀 | 010-5555-1111 | 320000 | 처리중 | 중복 |
1단계: 엑셀 파일을 Google 스프레드시트로 열기
Google Apps Script는 Google 스프레드시트 안에서 작동합니다. 기존 엑셀 파일이 있다면 다음 순서로 변환합니다.
- Google Drive에 접속합니다.
- 엑셀 파일을 Drive에 업로드합니다.
- 업로드한 파일을 마우스 오른쪽 버튼으로 클릭합니다.
- 연결 앱 → Google 스프레드시트를 선택합니다.
- 원본과 구분할 수 있도록 파일명을 ‘일일접수현황_자동정리’처럼 변경합니다.
처음부터 원본 파일에 적용하지 말고, 반드시 복사본에서 시험하는 편이 안전합니다. 자동화 코드에 오류가 있더라도 원본을 보존할 수 있기 때문입니다.
실무 적용 전 확인: 회사에서 Google Drive 사용을 허용하지 않거나 외부 클라우드 업로드를 제한한다면, 실제 고객·직원 데이터를 올리지 마십시오. 이 경우 가상의 이름과 번호로 만든 테스트 파일에서만 구조를 검증한 뒤 사내 보안 담당 부서의 승인을 받아야 합니다.
2단계: Apps Script 편집기 열기
- 자동화할 Google 스프레드시트를 엽니다.
- 상단 메뉴에서 확장 프로그램 → Apps Script를 누릅니다.
- 새 창에 기본으로 표시된 코드를 모두 지웁니다.
- 아래 스크립트를 복사해 붙여넣습니다.
바로 사용할 수 있는 데이터 정리 스크립트
function onOpen() { SpreadsheetApp.getUi() .createMenu('업무 자동화') .addItem('데이터 정리 실행', 'cleanDailyData') .addToUi(); } function cleanDailyData() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const lastRow = sheet.getLastRow(); const lastColumn = sheet.getLastColumn(); if (lastRow < 2) { SpreadsheetApp.getUi().alert('정리할 데이터가 없습니다.'); return; } const range = sheet.getRange(1, 1, lastRow, lastColumn); const values = range.getValues(); const headers = values[0].map(header => String(header).trim()); const receiptIndex = headers.indexOf('접수번호'); const nameIndex = headers.indexOf('담당자'); const departmentIndex = headers.indexOf('부서'); const phoneIndex = headers.indexOf('연락처'); const amountIndex = headers.indexOf('처리금액'); const statusIndex = headers.indexOf('상태'); const requiredHeaders = { '접수번호': receiptIndex, '담당자': nameIndex, '부서': departmentIndex, '연락처': phoneIndex, '처리금액': amountIndex, '상태': statusIndex }; const missingHeaders = Object.entries(requiredHeaders) .filter(([name, index]) => index === -1) .map(([name]) => name); if (missingHeaders.length > 0) { SpreadsheetApp.getUi().alert( '필수 열을 찾을 수 없습니다: ' + missingHeaders.join(', ') ); return; } let duplicateIndex = headers.indexOf('중복 여부'); if (duplicateIndex === -1) { duplicateIndex = headers.length; headers.push('중복 여부'); for (let i = 1; i < values.length; i++) { values[i].push(''); } } values[0] = headers; const receiptCounts = {}; for (let i = 1; i < values.length; i++) { const receiptNumber = String(values[i][receiptIndex]).trim(); if (receiptNumber) { receiptCounts[receiptNumber] = (receiptCounts[receiptNumber] || 0) + 1; } } for (let i = 1; i < values.length; i++) { values[i][receiptIndex] = String(values[i][receiptIndex]).trim(); values[i][nameIndex] = String(values[i][nameIndex]).trim(); values[i][departmentIndex] = String(values[i][departmentIndex]) .trim() .replace(/\s+/g, ' '); const phoneDigits = String(values[i][phoneIndex]).replace(/\D/g, ''); if (phoneDigits.length === 11) { values[i][phoneIndex] = phoneDigits.replace( /(\d{3})(\d{4})(\d{4})/, '$1-$2-$3' ); } else if (phoneDigits.length === 10) { values[i][phoneIndex] = phoneDigits.replace( /(\d{3})(\d{3})(\d{4})/, '$1-$2-$3' ); } const amountText = String(values[i][amountIndex]).replace(/[^\d.-]/g, ''); values[i][amountIndex] = amountText === '' ? '' : Number(amountText); if (String(values[i][statusIndex]).trim() === '') { values[i][statusIndex] = '확인 필요'; } else { values[i][statusIndex] = String(values[i][statusIndex]).trim(); } const receiptNumber = values[i][receiptIndex]; values[i][duplicateIndex] = receiptNumber && receiptCounts[receiptNumber] > 1 ? '중복' : '정상'; } sheet .getRange(1, 1, values.length, values[0].length) .setValues(values); sheet .getRange(2, amountIndex + 1, lastRow - 1, 1) .setNumberFormat('#,##0'); sheet.setFrozenRows(1); sheet.autoResizeColumns(1, values[0].length); SpreadsheetApp.getUi().alert( (lastRow - 1) + '개 행의 데이터 정리가 완료되었습니다.' ); }붙여넣은 뒤 상단의 저장 아이콘을 누르고 프로젝트 이름을 ‘일일 데이터 정리’로 지정합니다.
3단계: 스크립트를 처음 실행하고 권한 승인하기
처음 한 번은 스크립트 실행 권한을 승인해야 합니다.
- Apps Script 편집기 상단의 함수 선택란에서 cleanDailyData를 선택합니다.
- 실행 버튼을 누릅니다.
- 권한 확인 창이 나타나면 사용 중인 Google 계정을 선택합니다.
- 스크립트가 현재 스프레드시트를 수정하도록 승인합니다.
권한 승인 후 스프레드시트 화면으로 돌아와 새로고침합니다. 상단 메뉴에 업무 자동화가 추가됩니다.
이제 매일 데이터를 붙여넣은 뒤 다음 두 번의 클릭만 하면 됩니다.
업무 자동화 → 데이터 정리 실행
정리가 끝나면 ‘500개 행의 데이터 정리가 완료되었습니다’와 같은 안내 창이 표시됩니다.
스크립트가 실제로 처리하는 작업
코드 전체를 해석할 필요는 없지만, 어떤 기준으로 원본이 바뀌는지는 알고 있어야 합니다.
| 대상 열 | 입력 예시 | 정리 결과 |
|---|---|---|
| 담당자 | “ 박지훈 ” | “박지훈” |
| 부서 | “고객 지원팀 ” | “고객 지원팀” |
| 연락처 | “010 1234 5678” | “010-1234-5678” |
| 처리금액 | “1,250,000원” | 숫자 1,250,000 |
| 상태 | 빈칸 | “확인 필요” |
| 접수번호 | 같은 번호가 두 번 등장 | 중복 여부 열에 “중복” 표시 |
특히 처리금액을 단순히 보기 좋은 문자로 바꾸는 것이 아니라 실제 숫자 형식으로 변환한다는 점이 실무에서 유용합니다. 그래야 합계, 평균, 피벗 테이블, 차트에서 정상적으로 계산됩니다.
대부분 놓치는 부분: 열 순서가 바뀌어도 작동하게 만들기
간단한 자동화 예제는 흔히 “B열은 담당자, C열은 부서”처럼 열 위치를 고정합니다. 하지만 실무 파일은 작성자나 시스템에 따라 열 순서가 바뀔 수 있습니다.
예를 들어 이번 주에는 연락처가 D열에 있었지만, 다음 주 파일에서는 신규 항목이 추가되어 E열로 밀릴 수 있습니다. 열 번호를 기준으로 만든 코드는 이때 엉뚱한 데이터를 수정합니다.
위 스크립트는 열 번호가 아니라 첫 번째 행의 제목을 읽습니다.
const phoneIndex = headers.indexOf('연락처'); const amountIndex = headers.indexOf('처리금액');따라서 ‘연락처’ 열이 D열에서 F열로 이동해도 제목이 같다면 정상적으로 찾아갑니다. 반대로 필수 제목이 빠졌다면 자동 정리를 중단하고 누락된 열 이름을 안내합니다. 잘못된 열을 조용히 수정하는 것보다, 실행을 멈추고 문제를 알려주는 방식이 업무용 자동화에 더 안전합니다.
현재 업무 파일에 맞게 수정하는 방법
열 이름이 다를 때
회사 파일에서 ‘담당자’ 대신 ‘담당자명’을 사용한다면 아래 한 줄을 찾습니다.
const nameIndex = headers.indexOf('담당자');다음과 같이 제목만 바꿉니다.
const nameIndex = headers.indexOf('담당자명');아래의 필수 열 목록에서도 같은 이름으로 변경합니다.
'담당자명': nameIndex빈 상태를 다른 문구로 표시할 때
빈칸을 ‘미처리’로 표시하려면 다음 부분을 찾습니다.
values[i][statusIndex] = '확인 필요';아래처럼 바꿉니다.
values[i][statusIndex] = '미처리';ChatGPT에 수정 요청하는 실제 입력 예시
코드를 직접 수정하기 어렵다면 회사의 실제 데이터가 아닌 가상의 열 구조를 사용해 ChatGPT에 요청할 수 있습니다.
입력:
아래 Google Apps Script에서 ‘담당자’ 열을 ‘검수자’로 바꾸고, ‘상태’가 비어 있으면 ‘검수 대기’가 입력되도록 수정해 주세요. 다른 기능은 변경하지 말고, 수정된 전체 코드를 출력해 주세요.
이처럼 변경할 열 이름과 원하는 결과를 구체적으로 적으면 전체 코드를 다시 받을 수 있습니다. 고객명, 전화번호, 주문번호처럼 실제 업무 데이터는 함께 입력하지 않아도 됩니다.
실무 적용 전 10행으로 먼저 시험하기
자동화는 수백 행에 바로 적용하기보다 작은 표로 검증하는 것이 안전합니다. 아래 여섯 가지 상황을 포함한 테스트 데이터를 만들어 보십시오.
- 정상적으로 입력된 행
- 담당자 앞뒤에 공백이 있는 행
- 연락처에 하이픈이 없는 행
- 금액에 ‘원’과 쉼표가 포함된 행
- 상태가 비어 있는 행
- 접수번호가 중복된 두 행
실행 후에는 단순히 화면만 보지 말고 다음 항목도 확인합니다.
- 금액 셀을 선택했을 때 숫자로 인식되는지
- 원래 정상인 값이 바뀌지 않았는지
- 빈 행이나 제목 행까지 수정되지 않았는지
- 중복 기준이 실제 업무 규칙과 일치하는지
- 같은 스크립트를 두 번 실행해도 결과가 망가지지 않는지
예를 들어 접수번호가 같더라도 접수일자가 다르면 정상으로 봐야 하는 회사가 있습니다. 이런 경우에는 접수번호만으로 중복을 판단하면 안 됩니다. ‘접수번호+접수일자’를 묶어 비교하도록 기준을 수정해야 합니다. 자동화의 정확도는 코드 길이보다 회사에서 실제로 사용하는 판단 기준을 얼마나 정확하게 반영했는지에 달려 있습니다.
엑셀 파일로 다시 내려받는 방법
정리한 결과를 사내 시스템에 업로드하거나 동료에게 엑셀 형식으로 전달해야 한다면 다음 순서로 저장합니다.
- Google 스프레드시트에서 파일을 누릅니다.
- 다운로드 → Microsoft Excel(.xlsx)을 선택합니다.
- 내려받은 파일을 열어 날짜, 금액, 수식 형식을 확인합니다.
- 원본과 구분되는 파일명으로 저장합니다.
매크로가 포함된 엑셀 파일로 변환되는 것은 아닙니다. Apps Script는 Google 스프레드시트에서 실행되고, 내려받은 엑셀 파일에는 정리된 결과만 들어갑니다. 다음 날 새 데이터를 처리하려면 원래의 Google 스프레드시트에서 다시 실행해야 합니다.
FAQ
1. 고객 이름과 연락처가 포함된 파일을 Google 스프레드시트에 올려도 되나요?
회사의 보안 규정과 개인정보 처리 기준을 먼저 확인해야 합니다. 고객명, 연락처, 주소, 사번, 급여 정보가 포함된 파일은 개인 판단으로 외부 클라우드에 업로드하지 않는 것이 안전합니다. 회사가 승인한 Google Workspace 계정과 저장 정책이 있는 경우에만 정해진 범위에서 사용하십시오. 승인 여부가 불분명하다면 가상의 데이터로 기능만 검증하고, 실제 적용 전 보안 담당자나 관리자에게 확인하는 방식이 적절합니다.
2. 스크립트를 실행했는데 ‘필수 열을 찾을 수 없습니다’라는 메시지가 나옵니다.
첫 번째 행의 제목이 코드와 정확히 일치하는지 확인하십시오. 코드가 ‘처리금액’을 찾도록 되어 있는데 파일에는 ‘처리 금액’ 또는 ‘결제금액’으로 적혀 있으면 다른 제목으로 인식합니다. 파일의 제목을 코드에 맞추거나, 코드의 headers.indexOf('처리금액') 부분을 실제 제목으로 변경하면 됩니다. 제목 앞뒤의 공백은 자동으로 제거하지만 띄어쓰기와 단어 자체의 차이는 구분합니다.
3. 회사 엑셀 파일의 수식과 서식도 그대로 유지되나요?
이 예제는 셀의 값을 읽어 정리한 뒤 다시 입력하는 방식입니다. 대상 범위 안에 복잡한 수식이 있으면 계산 결과가 값으로 바뀌거나, 업무 파일의 구조에 따라 예상하지 못한 변경이 생길 수 있습니다. 따라서 원본 복사본에서 먼저 실행하고, 자동화 대상 시트에는 가능한 한 원본 데이터 값만 두는 편이 안전합니다. 수식 열을 유지해야 한다면 데이터 입력 영역과 계산 영역을 분리하거나, 스크립트가 특정 열만 수정하도록 범위를 제한해야 합니다.