앱스크립트는 처음인데…아직도…성공하지 못한 경험기

/**
 * @OnlyCurrentDoc
 *
 * 마케팅 분석 및 보고서 자동 생성을 위한 Google Apps Script 입니다.
 *
 * 동작 방식:
 * 1. 특정 Google Drive 폴더를 주기적으로 확인합니다.
 * 2. 폴더에 새로 추가된 Google Sheets 파일을 감지합니다.
 * 3. 해당 시트의 데이터를 읽어 OpenAI API로 전송합니다.
 * 4. OpenAI (GPT-4o)는 데이터를 기반으로 마케터 역할을 설정하고, 분석 포인트를 제안하며, 최종 보고서를 생성합니다.
 * 5. 생성된 보고서는 원본 시트와 같은 폴더에 Google Docs 문서로 저장됩니다.
 */

// --- ⚠️ 설정이 필요한 변수들 ---

// 1. 스크립트가 모니터링할 Google Drive 폴더의 ID를 입력하세요.
//    (폴더 URL의 .../folders/ 뒤에 있는 긴 문자열입니다)
const FOLDER_ID = '1y7uK4fmvEG9udNwDZUHi_1Q-nKLXlHkj';

// 2. OpenAI API 키를 입력하세요. (https://platform.openai.com/api-keys 에서 발급)
const OPENAI_API_KEY = 'YOUR_OPENAI_API_KEY'; // 👈 여기에 실제 API 키를 붙여넣으세요.

// --- 스크립트 본문 (이 아래는 수정할 필요 없습니다) ---

/**
 * 자동화를 시작하기 위해 이 함수를 딱 한 번만 직접 실행해주세요.
 * 10분마다 `processNewSurveysInFolder` 함수를 실행하는 시간 기반 트리거를 생성합니다.
 */
function setupTrigger() {
  // 기존에 설정된 트리거가 있다면 삭제하여 중복 실행을 방지합니다.
  const allTriggers = ScriptApp.getProjectTriggers();
  for (const trigger of allTriggers) {
    if (trigger.getHandlerFunction() === 'processNewSurveysInFolder') {
      ScriptApp.deleteTrigger(trigger);
    }
  }

  // 10분 간격으로 실행되는 새 트리거를 설정합니다.
  ScriptApp.newTrigger('processNewSurveysInFolder')
    .timeBased()
    .everyMinutes(10)
    .create();

  Logger.log('✅ 자동 분석 트리거가 성공적으로 설정되었습니다. 10분마다 폴더를 확인합니다.');
}

/**
 * 지정된 폴더를 확인하고, 처리되지 않은 새 설문지에 대해 분석을 실행하는 메인 함수입니다.
 * 이 함수는 트리거에 의해 자동으로 실행됩니다.
 */
function processNewSurveysInFolder() {
  try {
    const targetFolder = DriveApp.getFolderById(FOLDER_ID);
    const sheets = targetFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
    const processedFiles = getProcessedFiles();

    while (sheets.hasNext()) {
      const sheetFile = sheets.next();
      if (!processedFiles[sheetFile.getId()]) {
        Logger.log(`⏳ 새로운 파일 감지: "${sheetFile.getName()}" 분석을 시작합니다.`);

        const spreadsheet = SpreadsheetApp.openById(sheetFile.getId());
        const sheetData = getSheetDataAsString(spreadsheet.getSheets()[0]);

        if (sheetData.trim() === "") {
            Logger.log(`⚠️ 파일 "${sheetFile.getName()}"에 데이터가 없어 건너뜁니다.`);
            markFileAsProcessed(sheetFile.getId());
            continue;
        }

        const reportContent = generateAnalysisReport(sheetData);
        if (reportContent) {
          createReportDocument(targetFolder, sheetFile.getName(), reportContent);
          markFileAsProcessed(sheetFile.getId());
          Logger.log(`✅ "${sheetFile.getName()}" 파일의 분석 보고서 생성이 완료되었습니다.`);
        } else {
            Logger.log(`❌ "${sheetFile.getName()}" 파일의 보고서 생성에 실패했습니다.`);
        }
      }
    }
  } catch (e) {
    Logger.log(`❌ 스크립트 실행 중 오류 발생: ${e.toString()}\n${e.stack}`);
  }
}

/**
 * Google Sheet의 데이터를 문자열 형태로 변환합니다.
 * @param {Sheet} sheet - 읽어올 Google Sheet 객체
 * @return {string} 시트 데이터를 CSV 형태의 문자열로 반환
 */
function getSheetDataAsString(sheet) {
  // 데이터가 있는 마지막 행과 열을 찾습니다.
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();

  if (lastRow === 0 || lastCol === 0) {
      return "";
  }
  // 메모리 사용을 줄이기 위해 최대 1000행만 읽도록 제한합니다.
  const range = sheet.getRange(1, 1, Math.min(lastRow, 1000), lastCol);
  const values = range.getValues();
  return values.map(row => row.join(', ')).join('\n');
}


/**
 * OpenAI API를 호출하여 마케팅 분석 보고서를 생성합니다.
 * @param {string} data - 분석할 설문 데이터 문자열
 * @return {string | null} 생성된 보고서 내용 또는 실패 시 null
 */
function generateAnalysisReport(data) {
  const apiEndpoint = 'https://api.openai.com/v1/chat/completions';

  // GPT-4o 모델에게 전달할 프롬프트(명령)
  const systemPrompt = `당신은 19년차 경력의 최고 마케팅 전략가입니다. 주어진 설문조사 데이터를 바탕으로 다음의 과업을 한국어로 수행해 주세요.
    1.  설문 내용(특히 1열 헤더)을 분석하여, 이 데이터를 분석하기에 가장 적합한 마케터의 역할(예: 브랜드 마케터, 퍼포먼스 마케터, CRM 스페셜리스트 등)을 지정하세요.
    2.  지정된 역할의 관점에서, 이 데이터에서 분석해야 할 가장 유의미한 포인트 10가지를 제안하세요.
    3.  제안된 10가지 포인트를 바탕으로, 19년차 전문가의 시각에서 심도 있는 마케팅 분석 보고서를 작성하세요.
    4.  보고서는 반드시 개조식(bullet points)으로, 간결하고 명확하게 작성해야 합니다.
    5.  최종 결과물은 아래와 같은 마크다운 코드 블록 형식으로만 출력해주세요. 다른 설명은 절대 추가하지 마세요.

    \`\`\`markdown
    # [설문지 이름] 마케팅 분석 보고서

    -   **분석 담당자 역할:** [여기에 1번에서 지정한 역할 입력]
    -   **작성일:** [오늘 날짜 YYYY-MM-DD 형식으로 입력]
    -   **핵심 요약:** [보고서 전체 내용을 한두 문장으로 요약]

    ---

    ## 주요 분석 포인트 및 인사이트

    ### 1. [여기에 2번에서 제안한 첫 번째 포인트]
    -   (분석 내용)
    -   (인사이트 및 제언)

    ### 2. [여기에 2번에서 제안한 두 번째 포인트]
    -   (분석 내용)
    -   (인사이트 및 제언)

    ... (10번까지 반복) ...

    ## 종합 결론 및 추천 액션 플랜

    -   **결론:** [분석을 통해 도출된 최종 결론]
    -   **추천 액션:** [결론에 따른 구체적인 실행 방안 1~3가지 제안]
    \`\`\``;


  const payload = {
    model: 'gpt-4o',
    messages: [
      { role: 'system', content: systemPrompt },
      { role: 'user', content: `다음은 구글 시트에서 추출한 설문조사 데이터입니다. 분석을 시작해주세요:\n\n${data}` }
    ],
    temperature: 0.5,
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + OPENAI_API_KEY
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  try {
    const response = UrlFetchApp.fetch(apiEndpoint, options);
    const responseCode = response.getResponseCode();
    const responseBody = response.getContentText();

    if (responseCode === 200) {
      const jsonResponse = JSON.parse(responseBody);
      return jsonResponse.choices[0].message.content;
    } else {
      Logger.log(`API Error: ${responseCode} - ${responseBody}`);
      return null;
    }
  } catch (e) {
    Logger.log(`API 호출 중 예외 발생: ${e.toString()}`);
    return null;
  }
}

/**
 * 분석 보고서 내용을 담은 Google Docs 파일을 생성합니다.
 * @param {Folder} folder - 파일이 생성될 Google Drive 폴더
 * @param {string} sheetName - 원본 Google Sheet 파일의 이름
 * @param {string} reportContent - 문서에 기록될 보고서 내용
 */
function createReportDocument(folder, sheetName, reportContent) {
  const date = Utilities.formatDate(new Date(), 'Asia/Seoul', 'yyyy-MM-dd');
  const docName = `[${sheetName}] 마케팅 분석 보고서 ${date}`;

  const doc = DocumentApp.create(docName);
  const docFile = DriveApp.getFileById(doc.getId());
  
  // 생성된 문서를 지정된 폴더로 이동
  folder.addFile(docFile);
  // 내 드라이브(루트)에 남은 사본은 삭제
  DriveApp.getRootFolder().removeFile(docFile);

  // 보고서 내용을 문서에 삽입
  doc.getBody().setText(reportContent);
  doc.saveAndClose();
}


// --- 처리 완료 파일 관리를 위한 헬퍼 함수들 ---

/**
 * 스크립트 속성에서 처리 완료된 파일 목록을 가져옵니다.
 * @return {Object} 파일 ID를 키로 갖는 객체
 */
function getProcessedFiles() {
  const properties = PropertiesService.getScriptProperties();
  const processed = properties.getProperty('PROCESSED_FILES');
  return processed ? JSON.parse(processed) : {};
}

/**
 * 파일을 처리 완료 목록에 추가합니다.
 * @param {string} fileId - 처리 완료된 파일의 ID
 */
function markFileAsProcessed(fileId) {
  const processedFiles = getProcessedFiles();
  processedFiles[fileId] = true;
  PropertiesService.getScriptProperties().setProperty('PROCESSED_FILES', JSON.stringify(processedFiles));
}