Apps Script + 챗GPT로 1,000줄 데이터 1분 만에 자동 정규화 (코드 포함)

구글 시트 + AI 연동 🚀
단순 '노가다'에서 '데이터 파이프라인' 설계자로!

안녕하세요! '넥스트 엔지니어링'입니다. 지난번 'AI 구글 시트 연동 초보편'에서는 Add-on이나 간단한 함수로 '노가다'를 줄이는 법을 다뤘습니다.

하지만 1,000줄이 넘어가면 속도가 느려지고, '요금 폭탄'이 걱정되며, AI가 엉뚱한 분류를 해도 속수무책으로 당하곤 합니다.

이젠 시트가 당신을 부리는 게 아니라, 당신이 시트를 '설계'해야 합니다.

오늘은 '단순 함수'를 넘어, 구글 시트를 'AI 자동화 공장'으로 만드는 '3중 시트 파이프라인' (RAW → STAGING → FINAL)을 설계하고, 'Google Apps Script'와 'AI'를 결합해 '데이터 정규화', '자동 분류', '인간 검수', '자동 개선'까지 수행하는 전문가급 시스템을 구축해 봅니다.

🏭 'AI 자동화 공장' 설계도 (목차)

• 1. [설계] 왜 '3중 시트(RAW-STAGING-FINAL)'가 필요한가?
• 2. [구현] 3가지 경로: Apps Script vs Python vs 노코드
• 3. [핵심] AI의 '계약서': 분류/정규화 프롬프트 (JSON 강제)
• 4. [실전 코드] Google Apps Script로 '자동 분류' 파이프라인 구축
• 5. [검수] 'STAGING' 시트와 '인간-AI 협업' 루프
• 6. [필수] '가드레일'과 '자동 개선' 피드백 루프

1. [설계] 왜 '3중 시트(RAW-STAGING-FINAL)'가 필요한가?

'노가다'의 원인은 모든 데이터를 '한 시트'에서 처리하려 하기 때문입니다. 우리는 데이터의 '흐름(Flow)'을 만들어야 합니다. 이것이 '파이프라인'의 핵심입니다.

[AI 자동화 공장 3-Sheet 시스템]

시트 (역할)	설명	작업 주체
[RAW] (원자재)	구글 폼, 메일, 스크래핑 등 '날것'의 데이터가 쌓이는 곳. (수정 X, 로그 역할)	사람 / 시스템
[STAGING] (품질 검수)	RAW의 데이터를 AI가 '자동 분류/정규화/요약'한 곳. (예: `status="Needs Review"`)	AI (90%) + 사람 (10%)
[FINAL] (완제품)	STAGING에서 사람이 '승인(Approved)'한 깨끗한 데이터만 모이는 곳.	시스템 (자동 복사)

🌟 넥스트 엔지니어링의 조언
'자동화'는 '100% 무인화'가 아닙니다. AI가 90%의 초안을 만들고, 사람은 10%의 '검수'와 '승인'만 하는 'Human-in-the-Loop' 시스템이 가장 현실적이고 안전합니다. 'STAGING' 시트가 바로 그 '검수 데스크'입니다.

2. [구현] 3가지 경로: Apps Script vs Python vs 노코드

RAW ➡️ STAGING으로 AI 처리를 보내는 방법은 3가지입니다.

• 경로 A: Google Apps Script (추천)
  • 특징: 구글 시트 내부에 '심는' 방식. `onEdit()`(셀 수정 시), `onFormSubmit()`(폼 제출 시) 트리거로 '즉시' AI를 호출할 수 있습니다. (오늘 이 방법을 중점적으로 다룹니다.)
• 경로 B: Python 배치 (전문가용)
  • 특징: 내 PC나 서버에서 `gspread` 라이브러리를 이용해 '새벽 3시'에 10,000건을 '한꺼번에(배치)' 처리할 때 유리합니다.
• 경로 C: 노코드 (빠른 구현용)
  • 특징: 'Make.com'이나 'Zapier'로 "Google Form 제출 시 ➡️ OpenAI(분류) ➡️ Google Sheets(STAGING에 추가)"를 레고처럼 조립합니다.

3. [핵심] AI의 '계약서': 분류/정규화 프롬프트 (JSON 강제)

AI가 엉뚱한 답을 하거나 '설명'을 덧붙이면 시트가 엉망이 됩니다. 우리는 AI가 '데이터 입력 로봇'처럼 행동하도록 'JSON 스키마'라는 '계약서'를 줘야 합니다.

이 프롬프트는 AI가 'STAGING' 시트에 채울 모든 열을 정의합니다.

[AI 데이터 정규화 프롬프트 (Apps Script용)]

역할: 당신은 데이터를 정규화하고 분류하는 AI 어시스턴트입니다.
입력된 'RAW 텍스트'를 분석하여, 아래 [스키마]에 맞춰 JSON 객체만 반환하세요. 다른 설명은 절대 덧붙이지 마세요.

[스키마]
{ "title": "내용을 10단어 이내로 요약한 제목", "desc": "핵심 설명 (최대 300자, 민감정보 제거)", "category": "Bug|Feature|Ops|Research|Finance|Other", "tags": ["추출한 핵심 태그1", "태그2"], "owner": "담당자 이름 (언급 시) 또는 null", "due": "YYYY-MM-DD (마감일 언급 시) 또는 null", "confidence": 0.0, // AI의 분류 신뢰도 (0.0 ~ 1.0) "reason": "이렇게 분류/추론한 한 줄 근거" }

# 규칙:
1. 모르면 추정 금지. 확실하지 않으면 `null` 또는 `Other`로 표기.
2. '마감', '까지', 'by' 등의 표현은 ISO 날짜(YYYY-MM-DD)로 정규화.
3. 개인/민감정보(전화번호, 계좌)는 `desc`에서 제거하거나 익명화.

`confidence`(신뢰도)와 `reason`(근거)은 AI가 왜 그렇게 분류했는지 '디버깅'하고, 신뢰도가 0.7 이하면 '무조건 검수'하도록 만드는 핵심 장치입니다.

4. [실전 코드] Google Apps Script로 '자동 분류' 파이프라인 구축

'경로 A'를 구현하는 코드입니다. [확장 프로그램] > [Apps Script]를 열고, `Code.gs`에 아래 코드를 붙여넣습니다.

// --- 1. 설정 (API 키는 '스크립트 속성'에 저장!) ---
// (주의: 코드에 키를 박지 말고, [파일] > [프로젝트 속성] > [스크립트 속성]에 'OPENAI_API_KEY'로 저장하세요)
const CFG = {
  MODEL_URL: "https://api.openai.com/v1/chat/completions",
  MODEL: "gpt-4o-mini",
  OPENAI_KEY: PropertiesService.getScriptProperties().getProperty("OPENAI_API_KEY"),
  SHEETS: { RAW: "RAW", STAGING: "STAGING", FINAL: "FINAL" }
};

// --- 2. AI 호출 함수 (범용) ---
function callLLM_(system, user, jsonMode = true) {
  const payload = {
    model: CFG.MODEL,
    messages: [
      { role: "system", content: system },
      { role: "user", content: user }
    ]
  };
  if (jsonMode) payload.response_format = { type: "json_object" };
  
  const res = UrlFetchApp.fetch(CFG.MODEL_URL, {
    method: "post",
    headers: {
      "Authorization": `Bearer ${CFG.OPENAI_KEY}`,
      "Content-Type": "application/json"
    },
    muteHttpExceptions: true,
    payload: JSON.stringify(payload),
  });
  
  const body = JSON.parse(res.getContentText());
  const content = body.choices?.[0]?.message?.content || "{}";
  return JSON.parse(content);
}

// --- 3. 프롬프트 (위 3번 섹션의 프롬프트를 여기에 넣습니다) ---
function getClassifierPrompt_() {
  return `
역할: 데이터 정규화/분류기.
입력(raw_text)을 아래 스키마로 변환해 JSON만 반환.

스키마:
{
 "title": "...", "desc": "...", "category": "Bug|Feature|Other",
 "tags": ["..."], "owner": null, "due": null,
 "confidence": 0.0, "reason": "..."
}
(규칙 생략...)
`;
}

// --- 4. 메인 파이프라인 (RAW -> STAGING) ---
// (이 함수를 'onFormSubmit' 트리거에 연결합니다)
function processRawRow_(row) {
  const ss = SpreadsheetApp.getActive();
  const rawSh = ss.getSheetByName(CFG.SHEETS.RAW);
  const stagingSh = ss.getSheetByName(CFG.SHEETS.STAGING);

  // RAW 시트에서 데이터 읽기 (3번째 열이 raw_text라고 가정)
  const raw_text = rawSh.getRange(row, 3).getValue();
  if (rawSh.getRange(row, 4).getValue() == "DONE") return; // 4번째 열(상태)이 DONE이면 스킵

  const sys = getClassifierPrompt_();
  const user = `raw=${raw_text}\n\n요구: 스키마 JSON만 반환`;
  let out = {};
  
  try {
    out = callLLM_(sys, user, true);
  } catch (err) {
    out = { title: "(AI 파싱 실패)", category: "Other", confidence: 0, reason: String(err) };
  }

  // STAGING 시트에 결과 쓰기
  const id = `S-${new Date().toISOString()}-${row}`;
  stagingSh.appendRow([
    id,
    out.title || "",
    out.desc || "",
    out.category || "Other",
    (out.tags || []).join(","),
    out.owner || "",
    out.due || "",
    out.confidence || 0,
    out.reason || "",
    "Needs Review", // ★핵심: '검수 대기' 상태로 추가
    row // 원본 RAW 시트 행 번호
  ]);
  
  // RAW 시트에 '처리 완료' 표시
  rawSh.getRange(row, 4).setValue("DONE");
}
            

5. [검수] 'STAGING' 시트와 '인간-AI 협업' 루프

이제 여러분의 일은 'RAW' 시트를 보는 것이 아닙니다. 오직 'STAGING' 시트만 보면 됩니다.

'STAGING' 시트의 'J열(status)'에 "Needs Review"라고 되어있는 행들이 AI가 처리한 '초안'입니다.

여러분의 새 업무:

1. 'STAGING' 시트 J열에 '데이터 확인' 드롭다운 메뉴를 만듭니다. (선택지: 'Approved', 'Rejected')
2. AI가 분류한 'category', 'tags' 등을 훑어봅니다. (AI가 써준 'reason' 열 참고)
3. (AI가 잘했다면) J열을 'Approved'로 바꿉니다.
4. (AI가 틀렸다면) 'category'를 직접 수정한 뒤, J열을 'Approved'로 바꿉니다.

이제 'FINAL' 시트는 'STAGING' 시트에서 J열이 'Approved'인 행만 '자동으로' 가져오도록 `QUERY` 함수나 별도 스크립트를 설정합니다.

[FINAL 시트 A1 셀에 입력]
=QUERY(STAGING!A:K, "SELECT * WHERE J = 'Approved'")
            

이것이 AI가 90% 처리하고 인간이 10% 검수하는 'Human-in-the-Loop' 시스템입니다.

6. [필수] '가드레일'과 '자동 개선' 피드백 루프

시스템은 '실패'를 대비해야 합니다.

A. 가드레일 (안전장치)

• 비용: 'Apps Script'의 `PropertiesService`에 '오늘 사용한 횟수'를 기록, 하루 100회(예시)가 넘으면 AI 호출을 중단시킵니다. (주의 3 참고)
• 민감정보 (PII): AI 프롬프트에 '민감정보는 desc에서 제거/익명화' 규칙을 넣었지만, '법률/가격/계약' 카테고리로 분류되면 `confidence`를 0.6으로 낮춰 '무조건 수동 검수'하게 만듭니다.

B. 자동 개선 (피드백 루프)

여러분이 'STAGING' 시트에서 '수정한 내역'은 AI를 훈련시킬 최고의 '정답지'입니다.

별도의 'Feedback' 시트에 [AI 제안]과 [사람 수정본]을 로그로 쌓습니다. 주 1회, 이 '에러 패턴'을 분석하여 `getClassifierPrompt_()` 프롬프트에 '운영 보정 규칙'을 추가합니다. (예: "AI가 '버그'를 자꾸 '기능'으로 분류함" ➡️ "프롬프트에 '오류, 실패, 멈춤' 단어는 'Bug'로 분류하라는 규칙 추가")

⚡️ 잠깐! 복습 퀴즈!

오늘 설계한 '자동화 공장', 잘 이해하셨나요? (정답은 맨 아래에!)

Q1. AI가 분류한 초안을 '사람이 검수'하는 단계가 일어나는 시트의 이름은 무엇인가요?

(1) `RAW` (원자재)
(2) `STAGING` (품질 검수)
(3) `FINAL` (완제품)

Q2. AI가 엉뚱한 '설명' 없이, 우리가 원하는 '데이터 형식(JSON)'으로만 답하게 만드는 '핵심 계약서'는?

(1) AI 모델 (`gpt-4o-mini`)
(2) 프롬프트 (JSON 스키마와 규칙 정의)
(3) `FINAL` 시트의 `QUERY` 함수

🔑 'AI 시트 파이프라인' 6가지 핵심

1. 목표: 시스템화

'함수'가 아닌 '파이프라인'을 설계한다.

2. 구조: 3중 시트

`RAW`(입력) → `STAGING`(검수) → `FINAL`(출력)로 데이터 흐름을 분리한다.

3. 구현: Apps Script

'Add-on'보다 'Apps Script'가 보안, 비용, 커스텀에 유리하다. (복붙 가능)

4. 핵심: JSON 계약서

'JSON 스키마' 프롬프트로 AI의 출력을 '강제'하여 데이터 품질을 확보한다.

5. 협업: '검수' 루프

'STAGING' 시트는 AI(90%)와 사람(10%)이 협업하는 'Human-in-the-Loop' 공간이다.

6. 개선: '피드백'

사람이 '수정한' 데이터를 AI가 재학습(튜닝)하여 시스템을 '자동 개선'한다.

❓ 'AI 구글 시트' 전문가 FAQ

Q1. Apps Script에 API 키를 넣으면 안전한가요?

A. 코드에 '그대로' 박아넣는 것은 위험합니다. 코드 예시처럼 [파일] > [프로젝트 속성] > [스크립트 속성]에 키를 저장하고 `PropertiesService.getScriptProperties()`로 불러오는 것이 훨씬 안전합니다. 이 속성값은 시트 공유 시 노출되지 않습니다.

Q2. 10,000건을 처리하려니 Apps Script가 6분 제한(Timeout)에 걸려요.

A. 맞습니다. Apps Script는 '대용량 배치'에 적합하지 않습니다. 이 경우 '경로 B (Python 배치)'가 정답입니다. 내 PC나 서버에서 `gspread` 라이브러리로 100개씩 묶어(배치) 처리하면 시간제한 없이 안정적으로 실행할 수 있습니다.

Q3. AI가 자꾸 JSON 형식을 무시하고 "요청하신 JSON입니다:" 같은 설명을 붙여요.

A. AI 모델(GPT-4o, Claude 3 Sonnet 등)이 'JSON 모드'를 지원하는지 확인하세요. 지원한다면 코드의 `response_format={"type": "json_object"}` 옵션을 사용하는 것이 100% 확실합니다. 이 옵션이 없다면 프롬프트에 "JSON 객체만 반환해. 다른 모든 텍스트, 설명, 서론, 결론을 절대 포함하지 마."라고 매우 '강력하게' 지시해야 합니다.

Q4. 'confidence' 점수는 AI가 어떻게 계산하나요?

A. 프롬프트 스키마에 `confidence: 0.0` (0.0~1.0)을 넣어달라고 AI에게 '요청'한 것입니다. AI는 자신의 분류에 대해 스스로 '자신감'을 메타인지하여 점수로 반환합니다. "배송 문의"처럼 명확하면 0.9, "칭찬인지 비꼬는 건지 애매함" 같은 내용은 0.5 등으로 낮게 나옵니다.

Q5. `onEdit` 트리거를 쓰니 셀 하나 바꿀 때마다 AI가 호출돼서 느려요.

A. '실시간'이 꼭 좋은 것은 아닙니다. `onEdit` 트리거 대신, (1) 'Google Form 제출 시'(`onFormSubmit`) 실행하거나, (2) '매시간' 또는 '매일 밤' 실행하는 '시간 기반 트리거'로 설정하는 것이 비용과 성능 면에서 훨씬 효율적입니다.

Q6. '피드백 루프'는 어떻게 만드나요?

A. 'STAGING' 시트의 `onEdit` 트리거를 하나 더 만듭니다. 만약 'J열(status)'이 'Approved'로 바뀌고, 'C열(category)'이 AI의 제안과 '다르다면', [원본 텍스트], [AI 제안], [사람 수정본]을 'Feedback' 시트에 자동으로 복사하는 스크립트를 만듭니다. 이 'Feedback' 시트가 AI를 튜닝할 정답지가 됩니다.

'데이터 입력자'에서 '파이프라인 관리자'로

'구글 시트 노가다'는 기술이 없어서가 아니라 '시스템'이 없어서 발생합니다.

오늘 우리는 'RAW-STAGING-FINAL'이라는 데이터 공장을 설계했습니다. 이 시스템이 구축되면, 여러분은 더 이상 '데이터 입력자'가 아닙니다. AI가 처리한 'STAGING'을 검수하고, 'FINAL'로 나가는 품질을 책임지며, '피드백'으로 AI를 튜닝하는 '데이터 파이프라인 관리자'가 되는 것입니다.

지금 당장, 여러분의 시트에 'RAW', 'STAGING', 'FINAL' 3개의 탭을
만들어보는 것부터 시작하세요.

여러분의 '시스템 구축'을 '넥스트 엔지니어링'이 응원합니다!

(퀴즈 정답: Q1 - (2) / Q2 - (2))