고객 문의 자동 응답 시스템 구축: Google과 ChatGPT 활용

function processNewFormResponses() {
  const SPREADSHEET_ID = '1b2CoGKbqvi9tj0cmRR3PPiohMM4yQtNipq_-B7g_0sM';
  const SHEET_NAME = 'sheet4';
  const STATUS_COLUMN_INDEX = 7;
  const STATUS_COMPLETE = "완료";
  
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = ss.getSheetByName(SHEET_NAME);
  if (!sheet) return;
  
  const lastRow = sheet.getLastRow();
  if (lastRow <= 1) return;
  
  const dataRange = sheet.getRange(2, 1, lastRow - 1, STATUS_COLUMN_INDEX);
  const responses = dataRange.getValues();
  
  for (let i = 0; i < responses.length; i++) {
    const currentRow = i + 2;
    const email = responses[i][2];
    const phoneNumber = responses[i][3];
    const inquiry = responses[i][5];
    
    if (responses[i][STATUS_COLUMN_INDEX - 1] === STATUS_COMPLETE) continue;
    
    const chatGPTResponse = generateChatGPTResponse(inquiry);
    if (!chatGPTResponse) continue;
    
    const pdfAttachment = getCompanyPdfAttachment();
    sendCustomerEmail(email, "문의 답변 및 회사 소개", chatGPTResponse, pdfAttachment);
    
    sendSMSNotification(phoneNumber, chatGPTResponse);
    sendKakaoNotification(phoneNumber, chatGPTResponse);
    
    sheet.getRange(currentRow, STATUS_COLUMN_INDEX).setValue(STATUS_COMPLETE);
  }
}

/**
 * Google Forms의 새로운 응답을 처리하는 메인 함수
 * - 트리거 설정: Forms 제출 시 또는 시간 기반 트리거로 실행
 */
function processNewFormResponses() {
  // 스프레드시트 ID를 상수로 지정 (실제 스프레드시트 ID로 변경)
  const SPREADSHEET_ID = '1nAdWTuJvIJV4eSa_WfDSnLEo_7eYw2rUHxaccurDjTw';
  
  // 시트 및 열 관련 상수 정의
  const SHEET_NAME = 'sheet4';
  const STATUS_HEADER = "처리상태";
  const STATUS_COLUMN_INDEX = 7; // G열
  
  // 처리 상태 상수
  const STATUS_COMPLETE = "완료";
  const STATUS_EMAIL_ERROR = "이메일 오류";
  const STATUS_ERROR = "오류";
  
  // 응답 데이터 열 인덱스 (0부터 시작하는 배열 인덱스)
  const COL_TIMESTAMP = 0; // A열
  const COL_NAME      = 1; // B열
  const COL_EMAIL     = 2; // C열
  const COL_PHONE     = 3; // D열
  const COL_NEEDS_PDF = 4; // E열
  const COL_INQUIRY   = 5; // F열

  try {
    // 트리거 실행 시 활성 스프레드시트가 없으므로, 스프레드시트 ID를 통해 직접 엽니다.
    const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
    const sheet = ss.getSheetByName(SHEET_NAME);
    if (!sheet) {
      Logger.log(`시트 '${SHEET_NAME}'를 찾을 수 없습니다.`);
      return;
    }
    
    // 데이터가 있는 마지막 행 가져오기
    const lastRow = sheet.getLastRow();
    if (lastRow <= 1) {
      Logger.log("처리할 응답이 없습니다.");
      return; // 헤더만 있는 경우 종료
    }
    
    // 상태 열 헤더 확인 및 설정
    const headerRange = sheet.getRange(1, STATUS_COLUMN_INDEX, 1, 1);
    if (headerRange.getValue() !== STATUS_HEADER) {
      headerRange.setValue(STATUS_HEADER);
    }
    
    // 모든 응답 데이터 가져오기 (A열부터 상태 열까지)
    const dataRange = sheet.getRange(2, 1, lastRow - 1, STATUS_COLUMN_INDEX);
    const responses = dataRange.getValues();
    
    // 각 응답 처리
    for (let i = 0; i < responses.length; i++) {
      const currentRow = i + 2; // 실제 시트의 행 번호 (헤더 다음부터)
      const response = responses[i];
      
      // 이미 처리된 응답은 건너뛰기
      if (response[STATUS_COLUMN_INDEX - 1] === STATUS_COMPLETE) {
        continue;
      }
      
      // 응답 데이터 추출
      const responseData = {
        timestamp: response[COL_TIMESTAMP],
        name: response[COL_NAME],
        email: response[COL_EMAIL],
        phone: response[COL_PHONE],
        needsPDF: String(response[COL_NEEDS_PDF]).toLowerCase().includes('네'),
        inquiry: response[COL_INQUIRY]
      };
      
      // 이메일 유효성 검사
      if (!responseData.email || !validateEmail(responseData.email)) {
        sheet.getRange(currentRow, STATUS_COLUMN_INDEX).setValue(STATUS_EMAIL_ERROR);
        Logger.log(`행 ${currentRow} - 이메일 오류: ${responseData.email}`);
        continue;
      }
      
      try {
        // PDF 첨부 파일 준비 (필요한 경우에만)
        let pdfAttachment = null;
        if (responseData.needsPDF) {
          pdfAttachment = preparePDFAttachment(); // 별도 구현 필요
        }
        
        // ChatGPT 응답 생성
        const chatGPTResponse = generateChatGPTResponse(responseData.inquiry); // 별도 구현 필요
        
        // 이메일 내용 생성
        const emailContent = createEmailContent(responseData.name, chatGPTResponse); // 별도 구현 필요
        
        // 이메일 전송
        sendCustomerEmail(
          responseData.email,
          "회사 소개 PDF 및 문의 답변",
          emailContent,
          pdfAttachment
        ); // 별도 구현 필요
        
        // 처리 상태 업데이트
        sheet.getRange(currentRow, STATUS_COLUMN_INDEX).setValue(STATUS_COMPLETE);
        
        // 처리 로그 기록
        logProcessResult(currentRow, "성공", "이메일 전송 완료");
      } catch (processError) {
        // 개별 응답 처리 중 발생한 오류 처리
        sheet.getRange(currentRow, STATUS_COLUMN_INDEX).setValue(STATUS_ERROR);
        logProcessResult(currentRow, "실패", processError.message);
        Logger.log(`행 ${currentRow} 처리 중 오류 발생: ${processError.message}`);
      }
    }
    
  } catch (error) {
    // 전체 프로세스 실행 중 발생한 오류 처리
    Logger.log("프로세스 실행 중 오류 발생: " + error.message);
    throw error;
  }
}

/**
 * 이메일 주소 유효성 검사 헬퍼 함수
 */
function validateEmail(email) {
  const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  return emailRegex.test(email);
}