학습 목표
데이터의 양이 폭발적으로 증가하는 2026년 현재, 실무 현장에서 가장 많이 활용되는 도구는 여전히 구글 시트입니다. 하지만 여러 사람이 동시에 입력하는 데이터는 공백, 서로 다른 날짜 형식, 통일되지 않은 전화번호 포맷 등 수많은 오류를 포함하고 있습니다. 본 강의의 목표는 Google Apps Script(GAS)를 활용하여 구글 시트에 입력된 데이터를 자동으로 정제하고 형식을 통일하는 자동화 시스템을 구축하는 것입니다. 이를 통해 수작업으로 소요되던 데이터 클렌징 시간을 99% 이상 단축하고 데이터의 신뢰도를 확보할 수 있습니다.
사전 준비 사항
본 실습은 구글 시트 내에서 작동하는 스크립트 작성뿐만 아니라, 외부 환경과의 연동 및 자동화를 위해 다음과 같은 개발 환경 세팅을 권장합니다. 특히 대규모 프로젝트 관리를 위해 VS Code와 Python을 활용한 API 연동 방식까지 다룹니다.
- 운영체제(OS): Windows 11, macOS Sequoia, 또는 Linux(Ubuntu 24.04 이상)
- 코드 에디터: Visual Studio Code (VS Code) 최신 버전
- 언어 및 런타임: Node.js (clasp 사용용), Python 3.12.x 이상
- 필수 설치 라이브러리 (Terminal 입력):
# Google API 및 자동화를 위한 라이브러리 설치
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
# Google Apps Script 로컬 관리를 위한 clasp 설치 (선택 사항)
npm install -g @google/clasp단계별 실습 과정
1단계: 구글 시트 데이터 구조 정의 및 Apps Script 에디터 접속
먼저 정제할 데이터가 담긴 구글 시트를 생성합니다. 시트의 A열에는 ‘성함’, B열에는 ‘전화번호’, C열에는 ‘등록일자’가 있다고 가정합니다. 데이터는 공백이 섞여 있거나 날짜 형식이 제각각인 상태여야 실습의 효과를 확인할 수 있습니다.
- 구글 시트 메뉴에서 [확장 프로그램] > [Apps Script]를 클릭합니다.
- 새 프로젝트의 이름을
DataAutoCleaner로 변경합니다.
2단계: 데이터 자동 정제 핵심 로직 작성 (JavaScript/GAS)
Apps Script 에디터에 아래의 코드를 작성합니다. 이 코드는 시트 전체를 순회하며 텍스트 양 끝의 공백을 제거하고, 전화번호에서 하이픈(-)을 통일하며, 날짜 형식을 YYYY-MM-DD로 고정합니다.
/
* 구글 시트 데이터 자동 정제 함수
*/
function cleanSheetData() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const range = sheet.getDataRange();
const values = range.getValues();
// 첫 번째 행은 헤더이므로 i=1부터 시작
for (let i = 1; i < values.length; i++) {
// 1. 이름 정제 (공백 제거)
values[i][0] = values[i][0].toString().trim();
// 2. 전화번호 정제 (숫자만 추출 후 형식 재구성)
let phone = values[i][1].toString().replace(/[^0-9]/g, '');
if (phone.length === 11) {
values[i][1] = phone.replace(/(\d{3})(\d{4})(\d{4})/, '$1-$2-$3');
}
// 3. 날짜 형식 통일
let dateVal = values[i][2];
if (dateVal instanceof Date) {
values[i][2] = Utilities.formatDate(dateVal, Session.getScriptTimeZone(), "yyyy-MM-dd");
} else if (dateVal !== "") {
let d = new Date(dateVal);
if (!isNaN(d.getTime())) {
values[i][2] = Utilities.formatDate(d, Session.getScriptTimeZone(), "yyyy-MM-dd");
}
}
}
// 정제된 데이터를 시트에 다시 기록
range.setValues(values);
SpreadsheetApp.getUi().alert('데이터 정제가 완료되었습니다!');
}
3단계: Python을 활용한 원격 데이터 제어 및 자동화
단순히 시트 내부에서 실행하는 것을 넘어, 로컬 환경(Python)에서 구글 시트 API를 통해 데이터를 조작하거나 스크립트를 트리거할 수 있습니다. 아래는 Python을 사용하여 특정 시트의 데이터를 읽어와 유효성을 검사하는 예시 코드입니다.
import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
# 권한 범위 설정
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
def main():
creds = None
# token.json에 인증 정보 저장
if os.path.exists('token.json'):
creds = Credentials.from_authorized_user_file('token.json', SCOPES)
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file('credentials.json', SCOPES)
creds = flow.run_local_server(port=0)
with open('token.json', 'w') as token:
token.write(creds.to_json())
service = build('sheets', 'v4', credentials=creds)
# 구글 시트 ID와 범위 지정
SPREADSHEET_ID = '여러분의_시트_ID_입력'
RANGE_NAME = 'Sheet1!A2:C10'
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME).execute()
rows = result.get('values', [])
print(f"{len(rows)}개의 데이터를 확인했습니다.")
for row in rows:
print(f"이름: {row[0]}, 연락처: {row[1]}")
if __name__ == '__main__':
main()4단계: 트리거 설정으로 완전 자동화 구현
코드 작성이 완료되었다면, 사용자가 시트를 수정할 때마다 또는 매일 특정 시간에 스크립트가 실행되도록 설정해야 합니다.
- Apps Script 에디터 왼쪽 메뉴에서 [트리거(시계 아이콘)]를 클릭합니다.
- [+ 트리거 추가] 버튼을 누릅니다.
- 실행할 함수로
cleanSheetData를 선택합니다. - 이벤트 소스를 ‘시간 기반’ 또는 ‘스프레드시트에서 – 수정 시’로 설정하여 자동화를 완성합니다.
결과 확인 및 유지보수
모든 설정이 완료되었습니다. 이제 구글 시트에 제각각으로 데이터를 입력해 보십시오. 예를 들어 이름 뒤에 공백을 넣거나, 전화번호를 01012345678처럼 하이픈 없이 입력한 뒤 스크립트를 실행(혹은 트리거 작동 대기)하면, 정해진 규칙에 따라 데이터가 즉시 정제되는 것을 확인할 수 있습니다.
이 시스템은 데이터 규모가 커질수록 빛을 발합니다. 특히 정규표현식(RegEx)을 활용한 로직을 추가하면 이메일 주소 유효성 검사, 주소지 자동 분리 등 더욱 고도화된 데이터 처리가 가능해집니다. 주기적으로 token.json의 만료 여부를 확인하고, API 할당량을 모니터링하여 안정적인 자동화 환경을 유지하시기 바랍니다.
