구글폼 고객 문의 AI 자동 분류: 문의가 오면 알아서 줄 세우는 가장 쉬운 방법

/**
 * what's in your teeth
 * Google Form → Google Sheets → OpenAI inquiry classifier
 * Checked: 2026-07-19
 *
 * Required Script Property:
 *   OPENAI_API_KEY = your OpenAI API key
 */

const WIYT_CONFIG = Object.freeze({
  MODEL: 'gpt-5.6-luna',
  INQUIRY_HEADERS: ['문의 내용', '문의내용', '상담 내용', '상담내용'],
  CATEGORIES: [
    '배송',
    '교환·환불',
    '상품·서비스',
    '예약·일정',
    '결제',
    '불만·클레임',
    '제휴·대량구매',
    '기타'
  ],
  URGENCIES: ['낮음', '보통', '높음', '즉시 확인'],
  OUTPUT_HEADERS: [
    'AI 분류',
    'AI 긴급도',
    'AI 요약',
    'AI 답변 초안',
    'AI 검토 메모',
    '처리 상태',
    'AI 처리 시각',
    'AI 오류'
  ],
  MAX_INQUIRY_LENGTH: 4000
});

/** 스프레드시트를 열 때 전용 메뉴를 만듭니다. */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('🦷 AI 문의 분류')
    .addItem('1. 자동화 초기 설정', 'setupAutomation')
    .addItem('2. 선택한 행 다시 분류', 'reprocessSelectedRow')
    .addItem('3. OpenAI 연결 테스트', 'testOpenAIConnection')
    .addToUi();
}

/**
 * 한 번만 실행하세요.
 * 출력 열을 만들고, 중복 트리거를 지운 뒤 제출 트리거를 새로 만듭니다.
 */
function setupAutomation() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getActiveSheet();
  const apiKey = getApiKey_();

  if (!apiKey) {
    throw new Error(
      'OPENAI_API_KEY가 없습니다. Apps Script의 프로젝트 설정 → 스크립트 속성에 먼저 저장하세요.'
    );
  }

  ensureOutputHeaders_(sheet);

  ScriptApp.getProjectTriggers()
    .filter((trigger) => trigger.getHandlerFunction() === 'handleFormSubmit')
    .forEach((trigger) => ScriptApp.deleteTrigger(trigger));

  ScriptApp.newTrigger('handleFormSubmit')
    .forSpreadsheet(spreadsheet)
    .onFormSubmit()
    .create();

  SpreadsheetApp.getUi().alert(
    '설정 끝',
    '이제 Google Form에 새 문의가 들어오면 AI가 분류·요약·답변 초안을 작성합니다. 답변은 자동 발송되지 않습니다.',
    SpreadsheetApp.getUi().ButtonSet.OK
  );
}

/** Google Form이 제출될 때 설치형 트리거가 호출합니다. */
function handleFormSubmit(event) {
  const lock = LockService.getScriptLock();
  let sheet = null;
  let row = null;

  try {
    lock.waitLock(30000);

    if (!event || !event.range) {
      throw new Error('폼 제출 이벤트 정보가 없습니다. 이 함수는 직접 실행하지 마세요.');
    }

    sheet = event.range.getSheet();
    row = event.range.getRow();
    processRow_(sheet, row);
  } catch (error) {
    console.error(error);
    if (sheet && row) {
      writeError_(sheet, row, error);
    }
  } finally {
    try {
      lock.releaseLock();
    } catch (ignore) {
      // 잠금을 얻지 못한 경우에는 해제할 것도 없습니다.
    }
  }
}

/** 선택한 행을 수동으로 다시 분류합니다. */
function reprocessSelectedRow() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const row = sheet.getActiveRange().getRow();

  if (row <= 1) {
    throw new Error('헤더가 아닌 문의 행을 선택하세요.');
  }

  processRow_(sheet, row);
  SpreadsheetApp.getUi().alert('선택한 문의를 다시 분석했습니다. 결과를 확인하세요.');
}

/** API 키와 모델 연결을 확인합니다. 소량의 API 사용료가 발생할 수 있습니다. */
function testOpenAIConnection() {
  const result = classifyInquiry_(
    '어제 받은 화분이 깨져서 왔습니다. 사진은 가지고 있고 교환을 원합니다.'
  );

  SpreadsheetApp.getUi().alert(
    '연결 성공',
    [
      '분류: ' + result.category,
      '긴급도: ' + result.urgency,
      '요약: ' + result.summary
    ].join('\n'),
    SpreadsheetApp.getUi().ButtonSet.OK
  );
}

/** 한 행의 문의를 읽고 AI 결과를 같은 행에 씁니다. */
function processRow_(sheet, row) {
  ensureOutputHeaders_(sheet);

  const headers = getHeaders_(sheet);
  const inquiryColumn = findHeaderColumn_(headers, WIYT_CONFIG.INQUIRY_HEADERS);

  if (inquiryColumn === -1) {
    throw new Error(
      '문의 내용 열을 찾지 못했습니다. Google Form 질문 제목을 “문의 내용”으로 만들거나 WIYT_CONFIG.INQUIRY_HEADERS를 수정하세요.'
    );
  }

  const inquiry = String(sheet.getRange(row, inquiryColumn + 1).getDisplayValue()).trim();

  if (!inquiry) {
    writeOutputs_(sheet, row, {
      '처리 상태': '건너뜀',
      'AI 오류': '문의 내용이 비어 있습니다.'
    });
    return;
  }

  writeOutputs_(sheet, row, {
    '처리 상태': 'AI 분석 중…',
    'AI 오류': ''
  });
  SpreadsheetApp.flush();

  const maskedInquiry = sanitizeInquiry_(inquiry);
  const result = classifyInquiry_(maskedInquiry);

  writeOutputs_(sheet, row, {
    'AI 분류': result.category,
    'AI 긴급도': result.urgency,
    'AI 요약': result.summary,
    'AI 답변 초안': result.draft_reply,
    'AI 검토 메모': result.review_note,
    '처리 상태': result.needs_human_review
      ? '사람 확인 필요'
      : '초안 생성 완료 · 발송 전 확인',
    'AI 처리 시각': new Date(),
    'AI 오류': ''
  });

  colorUrgencyCell_(sheet, row, result.urgency);
}

/** OpenAI Responses API를 호출해 구조화된 JSON 결과를 받습니다. */
function classifyInquiry_(maskedInquiry) {
  const apiKey = getApiKey_();

  if (!apiKey) {
    throw new Error('OPENAI_API_KEY가 설정되지 않았습니다.');
  }

  const developerInstruction = [
    '당신은 대한민국의 1인 사업자와 소상공인을 돕는 고객 문의 분류 보조자입니다.',
    '문의 내용을 분류하고 긴급도를 판단하며, 사람이 검토할 짧고 정중한 답변 초안을 작성하세요.',
    '확인되지 않은 주문 상태, 환불 가능 여부, 보상, 재고, 배송일을 만들어내지 마세요.',
    '답변 초안에서 고객에게 민감한 개인정보를 공개 댓글이나 일반 이메일로 보내라고 요구하지 마세요.',
    '안전사고, 법적 위협, 개인정보 유출, 중복 결제, 심각한 폭언·분쟁은 “즉시 확인”으로 분류하세요.',
    '단순 문의는 “낮음”, 일반적인 확인이 필요한 문의는 “보통”, 빠른 사람 확인이 필요한 불만·결제·파손은 “높음”으로 분류하세요.',
    '카테고리는 다음 중 하나만 사용하세요: ' + WIYT_CONFIG.CATEGORIES.join(', '),
    '답변 초안은 2~4문장으로 작성하고, 사실 확인이 필요하면 그 점을 분명히 밝히세요.',
    'review_note에는 사람이 확인해야 할 사실을 한 문장으로 적으세요.'
  ].join('\n');

  const payload = {
    model: WIYT_CONFIG.MODEL,
    input: [
      {
        role: 'system',
        content: developerInstruction
      },
      {
        role: 'user',
        content: maskedInquiry
      }
    ],
    text: {
      format: {
        type: 'json_schema',
        name: 'customer_inquiry_classification',
        strict: true,
        schema: {
          type: 'object',
          properties: {
            category: {
              type: 'string',
              enum: WIYT_CONFIG.CATEGORIES
            },
            urgency: {
              type: 'string',
              enum: WIYT_CONFIG.URGENCIES
            },
            summary: {
              type: 'string'
            },
            draft_reply: {
              type: 'string'
            },
            needs_human_review: {
              type: 'boolean'
            },
            review_note: {
              type: 'string'
            }
          },
          required: [
            'category',
            'urgency',
            'summary',
            'draft_reply',
            'needs_human_review',
            'review_note'
          ],
          additionalProperties: false
        }
      }
    },
    reasoning: { effort: 'low' },
    max_output_tokens: 700,
    store: false
  };

  const response = UrlFetchApp.fetch('https://api.openai.com/v1/responses', {
    method: 'post',
    contentType: 'application/json; charset=utf-8',
    headers: {
      Authorization: 'Bearer ' + apiKey
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });

  const statusCode = response.getResponseCode();
  const responseText = response.getContentText();
  let body;

  try {
    body = JSON.parse(responseText);
  } catch (error) {
    throw new Error('OpenAI 응답을 JSON으로 읽지 못했습니다. HTTP ' + statusCode);
  }

  if (statusCode < 200 || statusCode >= 300) {
    const apiMessage = body && body.error && body.error.message
      ? body.error.message
      : responseText.slice(0, 500);
    throw new Error('OpenAI API 오류 ' + statusCode + ': ' + apiMessage);
  }

  const outputText = extractOutputText_(body);
  let result;

  try {
    result = JSON.parse(outputText);
  } catch (error) {
    throw new Error('AI 결과 JSON을 읽지 못했습니다: ' + outputText.slice(0, 300));
  }

  return validateResult_(result);
}

/** Responses API 결과에서 최종 텍스트를 찾습니다. */
function extractOutputText_(body) {
  if (body && typeof body.output_text === 'string' && body.output_text) {
    return body.output_text;
  }

  const output = body && Array.isArray(body.output) ? body.output : [];

  for (const item of output) {
    const content = item && Array.isArray(item.content) ? item.content : [];

    for (const part of content) {
      if (part.type === 'output_text' && typeof part.text === 'string') {
        return part.text;
      }

      if (part.type === 'refusal') {
        throw new Error('AI가 요청 처리를 거부했습니다: ' + (part.refusal || '사유 없음'));
      }
    }
  }

  throw new Error('OpenAI 응답에서 출력 텍스트를 찾지 못했습니다.');
}

/** 결과값을 한 번 더 검사합니다. */
function validateResult_(result) {
  if (!result || typeof result !== 'object') {
    throw new Error('AI 결과 형식이 올바르지 않습니다.');
  }

  const category = WIYT_CONFIG.CATEGORIES.includes(result.category)
    ? result.category
    : '기타';
  const urgency = WIYT_CONFIG.URGENCIES.includes(result.urgency)
    ? result.urgency
    : '보통';

  return {
    category: category,
    urgency: urgency,
    summary: String(result.summary || '').trim().slice(0, 300),
    draft_reply: String(result.draft_reply || '').trim().slice(0, 1200),
    needs_human_review: Boolean(result.needs_human_review),
    review_note: String(result.review_note || '').trim().slice(0, 500)
  };
}

/** API로 보내기 전에 흔한 개인정보 패턴을 기본 마스킹합니다. */
function sanitizeInquiry_(text) {
  return String(text)
    .replace(/[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}/gi, '[이메일 삭제]')
    .replace(/(?:\+82[-.\s]?)?0?1[016789][-.\s]?\d{3,4}[-.\s]?\d{4}/g, '[휴대전화 삭제]')
    .replace(/\b\d{6}[-.\s]?[1-4]\d{6}\b/g, '[주민등록번호 삭제]')
    .replace(/\b(?:\d[ -]*?){13,19}\b/g, '[카드번호 가능성 삭제]')
    .replace(/\b\d{2,4}[-.\s]?\d{2,4}[-.\s]?\d{4,8}\b/g, '[식별번호 삭제]')
    .slice(0, WIYT_CONFIG.MAX_INQUIRY_LENGTH)
    .trim();
}

/** 필요한 출력 열이 없으면 응답 시트 오른쪽에 추가합니다. */
function ensureOutputHeaders_(sheet) {
  const lastColumn = Math.max(sheet.getLastColumn(), 1);
  const headers = sheet.getRange(1, 1, 1, lastColumn).getDisplayValues()[0];
  const missingHeaders = WIYT_CONFIG.OUTPUT_HEADERS.filter(
    (header) => !headers.includes(header)
  );

  if (!missingHeaders.length) {
    return;
  }

  const startColumn = lastColumn + 1;
  sheet.getRange(1, startColumn, 1, missingHeaders.length)
    .setValues([missingHeaders])
    .setFontWeight('bold')
    .setBackground('#C8FF4D')
    .setFontColor('#151515');

  sheet.setFrozenRows(1);
}

/** 첫 행의 헤더를 가져옵니다. */
function getHeaders_(sheet) {
  return sheet
    .getRange(1, 1, 1, Math.max(sheet.getLastColumn(), 1))
    .getDisplayValues()[0]
    .map((header) => String(header).trim());
}

/** 여러 후보명 중 실제 시트에 있는 열 번호를 찾습니다. */
function findHeaderColumn_(headers, aliases) {
  const normalizedAliases = aliases.map(normalizeHeader_);
  return headers.findIndex((header) => normalizedAliases.includes(normalizeHeader_(header)));
}

function normalizeHeader_(text) {
  return String(text).replace(/\s+/g, '').toLowerCase();
}

/** 지정한 헤더의 셀에 결과를 씁니다. */
function writeOutputs_(sheet, row, valuesByHeader) {
  const headers = getHeaders_(sheet);

  Object.keys(valuesByHeader).forEach((header) => {
    const columnIndex = headers.indexOf(header);
    if (columnIndex === -1) {
      return;
    }

    const cell = sheet.getRange(row, columnIndex + 1);
    const value = valuesByHeader[header];
    cell.setValue(value);

    if (header === 'AI 처리 시각' && value instanceof Date) {
      cell.setNumberFormat('yyyy-mm-dd hh:mm:ss');
    }
  });
}

/** 오류가 생겨도 문의 원문은 남겨 두고 오류 열에 이유를 기록합니다. */
function writeError_(sheet, row, error) {
  try {
    ensureOutputHeaders_(sheet);
    writeOutputs_(sheet, row, {
      '처리 상태': '오류 · 다시 확인',
      'AI 오류': error && error.message ? error.message : String(error)
    });
  } catch (secondaryError) {
    console.error(secondaryError);
  }
}

/** 긴급도 셀에 가벼운 색 표시를 넣습니다. */
function colorUrgencyCell_(sheet, row, urgency) {
  const headers = getHeaders_(sheet);
  const columnIndex = headers.indexOf('AI 긴급도');

  if (columnIndex === -1) {
    return;
  }

  const colors = {
    '낮음': '#E5FFF2',
    '보통': '#FFF7CC',
    '높음': '#FFE7D6',
    '즉시 확인': '#FFE8EE'
  };

  sheet.getRange(row, columnIndex + 1)
    .setBackground(colors[urgency] || '#FFFFFF')
    .setFontWeight('bold')
    .setFontColor('#151515');
}

/** 스크립트 속성에서 API 키를 읽습니다. */
function getApiKey_() {
  return PropertiesService
    .getScriptProperties()
    .getProperty('OPENAI_API_KEY');
}