구글 시트 데이터 정리 자동화 — 중복 제거·형식 통일·빈칸 채우기

정리할 시트와 요청 프롬프트

2장에서 요청 프롬프트의 다섯 요소를 익혔습니다. 이번에는 그 템플릿으로 실제 업무에 가까운 일을 시킵니다. 행사 신청자 명단처럼 여러 사람이 입력한 시트에는 늘 같은 문제가 생깁니다. 앞뒤 공백, 대소문자가 섞인 이메일, 제각각인 전화번호, 같은 사람의 중복 등록입니다.

정리 전 시트

탭 이름은 '명단'이고 1행이 머리글입니다. 열은 A=이름, B=전화, C=이메일, D=가입일입니다.

첫 줄과 세 번째 줄은 같은 사람인데 이메일 대소문자와 전화번호 표기가 다르다는 이유로 다른 줄처럼 보입니다. 사람이 눈으로 찾으면 오래 걸리고 놓치기 쉽습니다.

요청 프롬프트

구글 시트 '명단' 탭을 정리하는 앱스 스크립트를 써 줘.

• 1행은 머리글이고 A열 이름, B열 전화번호, C열 이메일, D열 가입일이야.
• 모든 칸의 앞뒤 공백을 지우고, 이메일은 소문자로 바꿔 줘.
• 전화번호는 숫자만 뽑아 010-1234-5678 형식으로 통일해. 01012345678, "010 2222 3333", 10-1234-5678이 모두 이 형식이 되어야 해. 자릿수가 안 맞는 번호(예: 02-123-4567)는 그대로 둬.
• 가입일이 날짜면 yyyy-MM-dd 문자열로 바꿔 줘(Asia/Seoul).
• 이메일이 같으면 중복으로 보고 먼저 나온 행만 남겨. 이메일이 비어 있으면 전화번호로 비교해.
• 제약: 시트를 한 번에 읽고 한 번에 쓰는 방식으로, 외부 서비스는 쓰지 마.
• 환경: 앱스 스크립트 V8. 시트를 열 때 '자동 정리' 메뉴가 생기고 거기서 실행할 수 있게 해 줘. 결과는 Logger.log로 남겨 줘.

이 프롬프트를 ChatGPT나 Claude 어디에 보내도 됩니다. 2장의 다섯 요소가 모두 들어갔습니다. 예시 값을 여러 형태로 적은 것이 핵심입니다. AI가 정규식(문자열에서 규칙에 맞는 부분을 찾는 표현식)을 짤 때 이 예시를 기준으로 삼습니다.

받은 코드와 실행 결과

코드 전체

// 시트 메뉴에 '자동 정리'를 추가합니다.
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('자동 정리')
    .addItem('명단 정리 실행', 'cleanSheet')
    .addToUi();
}

// 1행은 머리글, A열=이름, B열=전화번호, C열=이메일, D열=가입일
function cleanSheet() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getDataRange().getValues();
  const header = values[0];
  const seen = new Set();
  const result = [header];
  let removed = 0;

  for (let i = 1; i < values.length; i++) {
    const row = values[i].map(v => (typeof v === 'string' ? v.trim() : v));
    row[1] = normalizePhone(row[1]);
    row[2] = String(row[2]).toLowerCase();
    if (row[3] instanceof Date) {
      row[3] = Utilities.formatDate(row[3], 'Asia/Seoul', 'yyyy-MM-dd');
    }
    const key = row[2] || row[1];          // 이메일, 없으면 전화번호로 중복 판단
    if (key && seen.has(key)) { removed++; continue; }
    if (key) seen.add(key);
    result.push(row);
  }

  sheet.clearContents();
  sheet.getRange(1, 1, result.length, header.length).setValues(result);
  Logger.log('정리 완료: ' + (result.length - 1) + '행 남김, 중복 ' + removed + '행 삭제');
}

// 01012345678, 010 1234 5678, 10-1234-5678 → 010-1234-5678
function normalizePhone(value) {
  let digits = String(value).replace(/\D/g, '');
  if (digits.length === 10 && digits.startsWith('10')) digits = '0' + digits;
  if (digits.length !== 11) return value;  // 형식이 다르면 손대지 않음
  return digits.slice(0, 3) + '-' + digits.slice(3, 7) + '-' + digits.slice(7);
}

실행하는 법

1. 시트에서 확장 프로그램 → Apps Script를 열고 기본 코드를 지운 뒤 위 코드를 붙여 넣고 저장합니다.
2. 편집기에서 onOpen을 한 번 직접 실행해 권한을 승인합니다. 이후부터는 시트를 열 때마다 메뉴가 자동으로 생깁니다.
3. 시트로 돌아와 새로고침하면 상단에 자동 정리 메뉴가 보입니다. 명단 정리 실행을 누르면 됩니다.

메뉴를 만드는 onOpen은 시트를 열 때 구글이 자동으로 부르는 이름의 함수입니다. 함수 이름을 바꾸면 메뉴가 생기지 않습니다.

정리 결과

모의 환경에서 위 데이터로 실제 실행한 결과입니다. 실행 로그에는 정리 완료: 3행 남김, 중복 1행 삭제가 남았고, 메뉴는 '자동 정리/명단 정리 실행'으로 등록됐습니다.

세 번째 행(두 번째 김철수)은 이메일이 소문자로 바뀌면서 첫 행과 같아져 삭제됐습니다. 지역번호 전화번호 02-123-4567은 11자리가 아니어서 그대로 남았습니다. 중복이 발견되면 나중에 나온 행의 내용은 사라진다는 점도 알아 두세요. 어느 행을 남길지는 요청 프롬프트에서 바꿀 수 있습니다.

일괄 처리가 빠른 이유

getValues와 setValues

이 코드는 getDataRange().getValues()로 시트 전체를 2차원 배열로 한 번에 읽고, 메모리에서 정리한 뒤 setValues로 한 번에 씁니다. 셀을 하나씩 읽고 쓰는 방식은 호출 횟수만큼 서버를 왕복해서 느립니다.

구글 공식 Best practices 문서에 따르면 셀마다 배경색을 설정하면 약 70초가 걸리지만, setBackgrounds()로 한 번에 설정하면 약 1초면 끝납니다(developers.google.com/apps-script/guides/support/best-practices).

같은 문서는 "모든 데이터를 한 명령으로 배열에 읽고, 배열에서 작업한 뒤, 한 명령으로 쓰라"고 권합니다. 스크립트 한 번의 실행 한도가 6분이므로(구글 공식 할당량 문서), 데이터가 수천 행으로 늘어도 일괄 처리를 지키면 한도에 걸릴 일이 줄어듭니다.

AI가 for 반복문 안에서 sheet.getRange(i, 1).setValue(...)를 쓴 코드를 주면 의심하세요. "셀 단위 호출을 쓰지 말고 getValues·setValues로 일괄 처리해 줘"라고 되물으면 됩니다.

내장 removeDuplicates도 있습니다

중복 제거만 필요하다면 더 짧은 길이 있습니다. 앱스 스크립트에는 Range.removeDuplicates()라는 내장 메서드가 있고, 이전 행과 값이 같은 행을 범위에서 지워 줍니다(공식 레퍼런스). 이메일 열만 비교하는 옵션도 있습니다.

그런데 이번 예제처럼 이메일 대소문자를 먼저 통일하고, 이메일이 비면 전화번호로 비교하는 식의 규칙은 내장 메서드만으로는 어렵습니다. 단순 중복 제거는 내장 메서드, 정리와 중복 판단이 섞인 규칙은 직접 코드로 짜는 것이 맞습니다.

오류 수정 대화

열 수가 맞지 않을 때

시트에 열이 늘어나 A~I열 9개로 쓰고 있는데, AI가 쓴 코드는 앞의 6개 열만 정리해서 쓰도록 되어 있었다고 해 보겠습니다. 쓰는 줄이 아래처럼 열 수를 숫자로 박은 상태입니다.

// 문제 상황 예시: 범위의 열 수(9)와 배열의 열 수(6)가 다름
sheet.getRange(1, 1, result.length, 9).setValues(result);

실행하면 이런 오류가 납니다.

Exception: The number of columns in the data does not match the number of columns in the range. The data has 6 but the range has 9.

이 오류는 setValues에 넘긴 2차원 배열의 열 수와 getRange로 잡은 범위의 열 수가 다를 때 납니다. 배열의 행 길이를 맞추거나 범위의 열 수를 data[0].length로 맞추면 해결됩니다(yagisanatode.com 해설).

오류 원문을 붙여 다시 묻기

2장에서 익힌 대로 원문, 한 일, 기대 결과를 한 번에 붙입니다.

정리 스크립트를 실행했더니 아래 오류가 났어.

Exception: The number of columns in the data does not match the number of columns in the range. The data has 6 but the range has 9.

시트는 A~I열 9개를 쓰고, 1행 머리글도 9칸이야. cleanSheet 함수에서 마지막 setValues 줄이 문제 같아. 9열 전체가 그대로 남은 채 이름·전화·이메일·가입일 4개 열만 정리되면 돼. 열 수를 숫자로 적지 말고 데이터에서 읽어 오도록 고쳐 줘.

AI는 대개 header.length 또는 result[0].length를 쓰도록 바꿔 줍니다. 3장의 코드가 getRange(1, 1, result.length, header.length)로 쓰는 이유가 이것입니다. 열이 늘어나도 코드를 건드릴 필요가 없습니다.

한 가지 더 확인하세요. 코드는 row[1], row[2], row[3]을 열 위치로 고정해서 씁니다. 열 순서가 바뀌면 엉뚱한 열이 정리되니, 머리글 순서를 바꿨다면 코드의 열 번호도 함께 확인해야 합니다.

실행 전 복사본을 만드는 습관

이 스크립트는 clearContents()로 시트를 비운 뒤 다시 씁니다. 중간에 오류가 나면 원본이 비어 버릴 수 있습니다. 실무 데이터에 처음 돌릴 때는 반드시 복사본에서 시험해 보세요.

복사본 만들기

1. 시트 아래 탭 '명단'에서 오른쪽 클릭 → 복사를 선택합니다.
2. 이름이 '명단의 사본'으로 생기면 이것으로 코드를 먼저 돌립니다. 코드 속 시트 이름을 사본에 맞추거나, 사본 탭을 연 채 getActiveSheet()로 실행합니다.
3. 결과가 맞는지 눈으로 확인한 뒤에 원본에 실행합니다.

파일 전체를 복사해 두고 싶다면 파일 → 사본 만들기를 쓰세요. 구글 시트는 버전 기록(파일 → 버전 기록)도 남기지만, 복사본이 가장 확실한 안전장치입니다. 요청 프롬프트에 "실행 전에 원본 탭을 백업 탭으로 복사해 줘"라고 넣을 수도 있습니다.

정리

시트를 한 번에 읽고 한 번에 쓰는 구조로 공백 제거, 이메일 소문자화, 전화번호 형식 통일, 중복 삭제를 한 번에 처리했고, 열 수 오류는 header.length로 해결했습니다. 단순 중복 제거라면 내장 removeDuplicates()도 선택지입니다.

다음 챕터에서는 '메일 대량 발송 자동화 — 시트 명단으로 개인화 메일 보내기'를 다루겠습니다. 이번에 정리한 명단을 바탕으로 이름과 금액이 들어간 메일을 보내고, 하루 발송 한도와 테스트 발송 절차를 익힙니다.