Editing Openai/691c9bd6-70c4-8009-9802-3a9e356e3130


{{DISPLAYTITLE:CRM 스터디 실습 코드 분석}}

''This conversation was exported from ChatGPT''<ref>{{cite web|title=CRM 스터디 실습 코드 분석|url=https://chatgpt.com/share/691c9bd6-70c4-8009-9802-3a9e356e3130|publisher=ChatGPT|access-date=2025-11-20}}</ref>

== gpt-5-1-thinking ==
=== User: // API 키 설정 Script Properties에서 가져오기 ===
// API 키 설정 (Script Properties에서 가져오기)
// 스크립트 속성에 'GEMINI_API_KEY' 이름으로 API 키를 저장해야 합니다.
const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');

/**
 * 메뉴를 생성합니다. (시트 열 때 자동 실행)
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('CRM 자동화')
    .addItem('1. 이메일 내용 생성 (C6, C7 -> C8, C9)', 'generateEmail')
    .addItem('2. 이메일 디자인 생성 및 테스트 발송 (C9, C14, C13 -> C15, 발송)', 'generateEmailDesign')
    .addSeparator()
    .addItem('3. 단체 이메일 발송', 'sendBulkEmail')
    .addToUi();
}

/**
 * 디버깅용: onOpen이 작동하지 않을 때 수동으로 메뉴를 만듭니다.
 * 이 함수를 Apps Script 편집기에서 한번 실행하면 메뉴가 생성됩니다.
 */
function createCustomMenuManually() {
  SpreadsheetApp.getUi()
    .createMenu('CRM 자동화')
    .addItem('1. 이메일 내용 생성 (C6, C7 -> C8, C9)', 'generateEmail')
    .addItem('2. 이메일 디자인 생성 및 테스트 발송 (C9, C14, C13 -> C15, 발송)', 'generateEmailDesign')
    .addSeparator()
    .addItem('3. 단체 이메일 발송', 'sendBulkEmail')
    .addToUi();
}

/**
 * C6(핵심내용)과 C7(내용 프롬프트 독스)을 기반으로
 * Gemini를 호출하여 C8(제목), C9(본문)을 생성합니다.
 */
function generateEmail() {
  // ... (기존 코드와 동일)
  const sheet = SpreadsheetApp.getActiveSheet();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // C6: 이메일 핵심 내용
  const emailCore = sheet.getRange('C6').getValue();
  
  // C7: 스마트칩에서 구글 독스 URL 가져오기 (Sheets API 사용)
  const sheetName = sheet.getName();
  const a1Notation = '${sheetName}'!C7;
  
  let obj;
  try {
    obj = Sheets.Spreadsheets.get(ss.getId(), {
      ranges: [a1Notation],
      fields: "sheets(data(rowData(values(userEnteredValue,chipRuns))))"
    });
  } catch(e) {
    Logger.log('Sheets API 호출 에러: ' + e.toString());
    SpreadsheetApp.getUi().alert('Google Sheets API 고급 서비스가 활성화되어 있는지 확인하세요. 에러: ' + e.toString());
    return;
  }
  
  // chipRuns에서 URL 추출
  let docUrl = null;
  try {
    const rowData = obj.sheets[0].data[0].rowData[0];
    const chipRuns = rowData.values[0].chipRuns;
    
    if (chipRuns && chipRuns.length > 0) {
      for (const run of chipRuns) {
        if (run.chip && run.chip.richLinkProperties && run.chip.richLinkProperties.uri) {
          docUrl = run.chip.richLinkProperties.uri;
          break;
        }
      }
    }
  } catch (e) {
    Logger.log('C7 chipRuns 파싱 에러: ' + e.toString());
    // 칩이 없거나 파싱 실패 시 docUrl은 null로 유지됩니다.
  }
  
  // 입력값 체크
  if (!emailCore) {
    SpreadsheetApp.getUi().alert('C6에 이메일 핵심 내용을 입력해주세요!');
    return;
  }
  
  if (!docUrl) {
    SpreadsheetApp.getUi().alert('C7에 구글 독스 스마트칩이 없습니다!');
    return;
  }
  
  // 구글 독스에서 프롬프트 가져오기
  const userPrompt = getDocContent(docUrl);
  
  if (!userPrompt) {
    SpreadsheetApp.getUi().alert('C7의 구글 독스에서 프롬프트를 가져올 수 없습니다! (문서 접근 권한 등 확인)');
    return;
  }
  
  // Gemini에게 보낼 최종 프롬프트 구성
  const finalPrompt = ${userPrompt}

이메일 핵심 내용:
${emailCore}

다음 형식으로 정확하게 응답해주세요:
제목: [여기에 이메일 제목]
---
[여기에 이메일 본문];

  // Gemini API 호출
  const payload = {
    contents: [
      {
        parts: [
          { text: finalPrompt },
        ],
      },
    ],
  };

  const url = 'https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent';
  const options = {
    method: 'POST',
    contentType: 'application/json',
    headers: {
      'x-goog-api-key': apiKey,
    },
    payload: JSON.stringify(payload)
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const data = JSON.parse(response.getContentText());
    const content = data['candidates'][0]['content']['parts'][0]['text'];
    
    // 응답 파싱 (제목과 본문 분리)
    // ... (기존 코드와 동일)
    const lines = content.split('\n');
    let subject = '';
    let body = '';
    let foundSeparator = false;
    
    for (let i = 0; i < lines.length; i++) {
      const line = lines[i].trim();
      
      if (line.startsWith('제목:')) {
        subject = line.replace('제목:', '').trim();
      } else if (line === '---') {
        foundSeparator = true;
      } else if (foundSeparator) {
        body += (body ? '\n' : '') + lines[i];
      }
    }
    
    // C8에 제목, C9에 본문 입력
    sheet.getRange('C8').setValue(subject.trim());
    sheet.getRange('C9').setValue(body.trim());
    
    SpreadsheetApp.getUi().alert('이메일 내용이 생성되었습니다! (C8, C9)');
    
  } catch (error) {
    Logger.log('Gemini API 에러: ' + error.toString());
    SpreadsheetApp.getUi().alert('내용 생성 중 에러가 발생했습니다: ' + error.toString());
  }
}

/**
 * C9(이메일 본문), C14(디자인 프롬프트 독스)를 기반으로 C15(HTML 디자인)를 생성하고,
 * C13에 입력된 테스트 주소로 메일을 발송합니다.
 */
function generateEmailDesign() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ui = SpreadsheetApp.getUi();

  // 1. C9에서 이메일 텍스트 내용 가져오기
  const emailText = sheet.getRange('C9').getValue();

  // 2. C14에서 스마트칩 구글 독스 URL 가져오기 (Sheets API 사용)
  const sheetName = sheet.getName();
  const a1Notation = '${sheetName}'!C14; 

  let obj;
  try {
    obj = Sheets.Spreadsheets.get(ss.getId(), {
      ranges: [a1Notation],
      fields: "sheets(data(rowData(values(userEnteredValue,chipRuns))))"
    });
  } catch(e) {
    Logger.log('Sheets API 호출 에러: ' + e.toString());
    ui.alert('Google Sheets API 고급 서비스가 활성화되어 있는지 확인하세요. 에러: ' + e.toString());
    return;
  }

  // chipRuns에서 URL 추출
  let designPromptDocUrl = null;
  try {
    const rowData = obj.sheets[0].data[0].rowData[0];
    const chipRuns = rowData.values[0].chipRuns;
    
    if (chipRuns && chipRuns.length > 0) {
      for (const run of chipRuns) {
        if (run.chip && run.chip.richLinkProperties && run.chip.richLinkProperties.uri) {
          designPromptDocUrl = run.chip.richLinkProperties.uri;
          break;
        }
      }
    }
  } catch (e) {
    Logger.log('C14 chipRuns 파싱 에러: ' + e.toString());
  }

  // 3. 입력값 체크
  if (!emailText) {
    ui.alert('C9에 이메일 텍스트 내용이 없습니다. 먼저 내용을 생성해주세요!');
    return;
  }

  if (!designPromptDocUrl) {
    ui.alert('C14에 디자인 프롬프트가 담긴 구글 독스 스마트칩을 연결해주세요!');
    return;
  }

  // 4. 구글 독스에서 디자인 프롬프트 가져오기
  const designPrompt = getDocContent(designPromptDocUrl); 
  
  if (!designPrompt) {
    ui.alert('C14의 구글 독스에서 프롬프트를 가져올 수 없습니다! (문서 접근 권한 등 확인)');
    return;
  }

  // 5. Gemini에게 보낼 최종 프롬프트 구성
  const finalPrompt = 
당신은 전문 이메일 HTML 디자이너입니다.
다음은 디자이너에게 전하는 디자인 지침(프롬프트)입니다:
---[디자인 지침 시작]---
${designPrompt}
---[디자인 지침 끝]---

다음은 이메일에 들어갈 실제 텍스트 내용입니다:
---[이메일 텍스트 내용 시작]---
${emailText}
---[이메일 텍스트 내용 끝]---

[요청 사항]
디자인 지침에 따라, 이메일 텍스트 내용을 사용하여 완전한 이메일 HTML 코드를 생성해주세요.
* 모든 스타일은 반드시 인라인(inline)으로 적용해야 합니다 (Gmail 등에서의 호환성 보장).
* 반응형 디자인을 고려하여 <style> 태그 내에 미디어 쿼리를 포함할 수 있습니다.
* 다른 설명이나 주석 없이, 순수한 HTML 코드(<html>...</html>)만 응답으로 반환해주세요.
* 텍스트 인코딩은 UTF-8로 설정해주세요. (예: <meta charset="UTF-8">)
;

  // 6. Gemini API 호출
  const payload = {
    contents: [
      {
        parts: [
          { text: finalPrompt },
        ],
      },
    ],
  };

  const url = 'https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent';
  const options = {
    method: 'POST',
    contentType: 'application/json',
    headers: {
      'x-goog-api-key': apiKey,
    },
    payload: JSON.stringify(payload)
  };

  let htmlResult = '';

  try {
    const response = UrlFetchApp.fetch(url, options);
    const data = JSON.parse(response.getContentText());
    const content = data['candidates'][0]['content']['parts'][0]['text'];
    
    // 응답 정리
    htmlResult = content.trim();
    if (htmlResult.startsWith('html')) {
      htmlResult = htmlResult.substring(7);
    }
    if (htmlResult.endsWith('')) {
      htmlResult = htmlResult.substring(0, htmlResult.length - 3);
    }
    htmlResult = htmlResult.trim();
    
    // 7. C15에 결과(HTML 코드) 입력
    sheet.getRange('C15').setValue(htmlResult);
    
  } catch (error) {
    Logger.log('Gemini API 에러 (디자인): ' + error.toString());
    ui.alert('디자인 생성 중 에러가 발생했습니다: ' + error.toString());
    return;
  }

  // --- [추가된 로직: 테스트 메일 발송] ---
  const testEmail = sheet.getRange('C13').getValue().trim(); // C13에서 테스트 주소 가져오기
  const subject = sheet.getRange('C8').getValue(); // C8에서 제목 가져오기
  
  if (testEmail) {
    Logger.log(Sending test email to: ${testEmail});
    const emailOptions = {
      htmlBody: htmlResult, // C15에 저장된 HTML 내용
      name: 'AI CRM 시스템 (테스트)', // 발신자 이름 (테스트용)
    };
    
    try {
      // 제목에 [테스트] prefix 추가
      GmailApp.sendEmail(testEmail, [테스트] ${subject}, "", emailOptions);
      
      // 최종 성공 알림 (저장 + 발송)
      ui.alert(이메일 디자인이 생성되어 C15에 입력되었으며, 테스트 메일이 ${testEmail}로 발송되었습니다!);
    } catch (sendError) {
      Logger.log('Test Email Send Error: ' + sendError.toString());
      // HTML 저장은 성공했으므로, 발송 실패만 알림
      ui.alert([경고] 이메일 디자인은 C15에 저장되었으나, 테스트 메일 발송에 실패했습니다. (주소/권한 확인) 에러: ${sendError.toString()});
    }
  } else {
    // C13이 비어있는 경우
    ui.alert('이메일 디자인이 생성되어 C15에 입력되었습니다! (C13에 테스트 주소가 없어 발송되지 않았습니다)');
  }
}

/**
 * [NEW] B19:C24 설정 및 주소록을 기반으로 단체 메일을 발송합니다.
 */
function sendBulkEmail() {
  const ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.getActiveSheet();
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  try {
    // --- 1. 발송 기본 정보 로드 ---
    const subject = sheet.getRange('C8').getValue();
    const htmlBodyTemplate = sheet.getRange('C15').getValue(); // 원본 템플릿
    const senderEmail = sheet.getRange('C19').getValue().trim();
    const senderName = sheet.getRange('C20').getValue().trim();
    
    // --- 2. 주소록(스마트칩) 정보 로드 ---
    const addressBookSheetName = sheet.getRange('C22').getValue().trim();
    
    // C21에서 스마트칩 URL 가져오기
    const sheetName = sheet.getName();
    const a1Notation = '${sheetName}'!C21; // C21

    let obj;
    try {
      obj = Sheets.Spreadsheets.get(ss.getId(), {
        ranges: [a1Notation],
        fields: "sheets(data(rowData(values(userEnteredValue,chipRuns))))"
      });
    } catch (e) {
      Logger.log('Sheets API 호출 에러: ' + e.toString());
      ui.alert('Google Sheets API 고급 서비스가 활성화되어 있는지 확인하세요. 에러: ' + e.toString());
      return;
    }

    let addressBookSheetUrl = null;
    try {
      const rowData = obj.sheets[0].data[0].rowData[0];
      const chipRuns = rowData.values[0].chipRuns;
      if (chipRuns && chipRuns.length > 0) {
        for (const run of chipRuns) {
          if (run.chip && run.chip.richLinkProperties && run.chip.richLinkProperties.uri) {
            addressBookSheetUrl = run.chip.richLinkProperties.uri;
            break;
          }
        }
      }
    } catch (e) {
      Logger.log('C21 chipRuns 파싱 에러: ' + e.toString());
    }

    // --- 3. 주소록 데이터 매핑 ---
    const emailColumnLetter = sheet.getRange('C23').getValue().trim().toUpperCase();
    const nameColumnLetter = sheet.getRange('C24').getValue().trim().toUpperCase();

    // --- 필수 정보 체크 ---
    if (!subject || !htmlBodyTemplate) {
      ui.alert('C8(제목)과 C15(HTML 디자인)에 내용이 있는지 확인해주세요.');
      return;
    }
    if (!addressBookSheetUrl || !addressBookSheetName || !emailColumnLetter || !nameColumnLetter) {
      ui.alert('C21:C24의 주소록 설정값(스마트칩, 시트명, 열이름)을 모두 입력해주세요.');
      return;
    }
    if (!senderName) {
      ui.alert('C20(발신자 이름)을 입력해주세요.');
      return;
    }

    // --- 4. 주소록 데이터 전체 로드 ---
    const addressBookSheetId = getDocIdFromUrl(addressBookSheetUrl); // 헬퍼 함수 재사용
    if (!addressBookSheetId) {
      ui.alert('C21의 주소록 시트 URL이 유효하지 않습니다.');
      return;
    }

    const addressBookSpreadsheet = SpreadsheetApp.openById(addressBookSheetId);
    const addressBookSheet = addressBookSpreadsheet.getSheetByName(addressBookSheetName);
    
    if (!addressBookSheet) {
      ui.alert(연결된 주소록 시트에서 '${addressBookSheetName}' 시트를 찾을 수 없습니다.);
      return;
    }

    const data = addressBookSheet.getDataRange().getValues();
    
    // 열 문자를 0-based 인덱스로 변환
    const emailColIndex = columnLetterToIndex(emailColumnLetter);
    const nameColIndex = columnLetterToIndex(nameColumnLetter);

    if (emailColIndex === -1 || nameColIndex === -1) {
      ui.alert('이메일 또는 이름 열이름이 유효하지 않습니다 (예: A, B, C...).');
      return;
    }

    // --- 5. 개인화 및 순차 발송 (Loop) ---
    let sentCount = 0;
    const placeholder = /{{이름}}/g; // 치환 태그 (정규식, global replace)

    // GmailApp 옵션
    const options = {
      name: senderName, // 발신자 이름
      htmlBody: null, // 루프 안에서 채워짐
    };

    if (senderEmail) {
      options.from = senderEmail; // 발신자 이메일 (별칭 등)
    }

    // 첫 번째 줄(헤더)은 건너뛰고 (i = 1) 시작
    for (let i = 1; i < data.length; i++) {
      const row = data[i];
      const recipientEmail = row[emailColIndex];
      const recipientName = row[nameColIndex];

      // 이메일 주소가 없거나 유효하지 않으면 건너뛰기
      if (!recipientEmail || recipientEmail.indexOf('@') === -1) {
        Logger.log(Skipped row ${i + 1}: Invalid email.);
        continue;
      }
      
      // 이름이 없을 경우를 대비 (예: "고객"님)
      const nameForEmail = recipientName || "고객"; 
      
      // 개인화된 HTML 본문 생성
      const personalizedBody = htmlBodyTemplate.replace(placeholder, nameForEmail);
      options.htmlBody = personalizedBody;

      // 이메일 발송
      try {
        GmailApp.sendEmail(recipientEmail, subject, "", options); // 3번째 인자(일반 텍스트 본문)는 비워둠
        Logger.log(Sent to: ${recipientEmail});
        sentCount++;
        
        // Google Quota 정책을 위한 1초 대기
        Utilities.sleep(1000); 
      } catch (e) {
        Logger.log(발송 실패 (${recipientEmail}): ${e.toString()});
        ui.alert(발송 중단: ${recipientEmail}님에게 발송 실패. \n에러: ${e.toString()});
        return; // 한 건이라도 실패하면 중단
      }
    }

    // --- 6. 결과 알림 ---
    ui.alert(총 ${sentCount}건의 이메일이 성공적으로 발송되었습니다.);

  } catch (error) {
    Logger.log('sendBulkEmail 에러: ' + error.toString());
    ui.alert('단체 발송 중 알 수 없는 에러가 발생했습니다: ' + error.toString());
  }
}

// --- 헬퍼 함수 (Helper Functions) ---

/**
 * 구글 독스 URL에서 문서 ID를 추출합니다.
 * (Docs와 Sheets URL 모두에서 ID 추출 가능)
 * @param {string} url - 구글 독스/시트 URL
 * @return {string|null} - 추출된 문서 ID 또는 null
 */
function getDocIdFromUrl(url) {
  // 정규식으로 URL에서 ID 추출
  const match = url.match(/[-\w]{25,}/); 
  return match ? match[0] : null;
}

/**
 * 구글 독스 URL을 받아 해당 문서의 텍스트 내용을 반환합니다.
 * @param {string} docUrl - 구글 독스 URL (스마트칩에서 추출)
 * @return {string|null} - 문서 텍스트 내용 또는 null (에러 시)
 */
function getDocContent(docUrl) {
  try {
    const docId = getDocIdFromUrl(docUrl);
    if (!docId) {
      Logger.log('유효하지 않은 독스 URL입니다: ' + docUrl);
      return null;
    }
    
    // DocumentApp을 사용하여 문서 열기
    const doc = DocumentApp.openById(docId);
    const body = doc.getBody();
    const text = body.getText();
    
    return text;
    
  } catch (error) {
    Logger.log('독스를 읽는 중 에러 발생: ' + error.toString());
    return null;
  }
}

/**
 * [NEW] 스프레드시트 열 문자(A, B, C...)를 0-based 인덱스로 변환합니다.
 * @param {string} letter - 열 문자 (예: "A", "C", "AA")
 * @return {number} - 0-based 인덱스 (예: "A" -> 0, "C" -> 2)
 */
function columnLetterToIndex(letter) {
  try {
    let column = 0, length = letter.length;
    for (let i = 0; i < length; i++) {
      column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
    }
    return column - 1; // 0-based index
  } catch (e) {
    Logger.log(columnLetterToIndex 에러: ${e.toString()});
    return -1; // 에러 시 -1 반환
  }
}

---

위는 앱스스크립트 코드야.
개발 잘 모르는 초보자한테 이 시스템이 어떻게 동작하는지 설명하고,
코드의 각 부분이 어떤 역할을 하는지 간단히 리뷰하면서 소개해주려고 해.
코드 분석해서 진행해줘. 필요한 정보 있으면 나에게 물어봐.

=== Assistant: 아래 설명은 “개발 잘 모르는 초보자”에게 말해준다는 전제로 썼습니다. ===
아래 설명은 “개발 잘 모르는 초보자”에게 말해준다는 전제로 썼습니다.
먼저 시스템 전체 흐름을 사람 눈높이에서 정리하고, 그다음 코드 블록별로 역할을 짧게 리뷰합니다.

==== 1. 이 스크립트로 실제로 할 수 있는 일 (사용자 관점) ====

구글 스프레드시트에서 메뉴에 “CRM 자동화”가 생기고, 거기에 3가지 기능이 생깁니다.
# 이메일 내용 자동 생성 (메뉴 1번) - C6: “이메일 핵심 내용”을 적어둠 - C7: “내용 프롬프트가 적힌 구글 독스 문서”를 스마트칩으로 연결 → 버튼을 누르면, - Gemini가 C6 + 독스 내용을 보고 - C8: 이메일 제목 - C9: 이메일 본문 을 자동으로 만들어 줍니다.
# 이메일 디자인(HTML) 생성 + 테스트 발송 (메뉴 2번) - C9: 방금 만든 이메일 텍스트 - C14: “디자인 지침이 적힌 구글 독스 문서” 스마트칩 - C13: 테스트로 받을 내 이메일 주소 → 버튼을 누르면, - Gemini가 텍스트 + 디자인 지침을 보고 HTML 이메일 디자인을 생성해서 C15에 저장 - C13에 적힌 주소로 [테스트] 제목으로 실제 이메일 발송
# 단체 이메일 발송 (메뉴 3번) - C8: 최종 이메일 제목 - C15: 최종 HTML 이메일 (디자인 포함) - C19~C24: “어떤 주소록 시트, 어떤 열에 이메일/이름이 있는지” 설정 → 버튼을 누르면, - 주소록 스프레드시트를 열고 - 각 사람의 이름과 이메일을 읽어서 - C15 템플릿에서 {{이름}}을 각 사람 이름으로 바꿔 넣고 - Gmail로 한 명씩 순차 발송

즉, '''“핵심 내용 → 문장화 → 디자인된 HTML 이메일 → 개인화된 단체 발송”'''까지 한 번에 이어지는 파이프라인입니다.

==== 2. 코드 전체 구조 지도 ====

코드는 크게 6 덩어리입니다.
# 상단: API 키 읽기
# 메뉴 생성: onOpen, createCustomMenuManually
# 이메일 내용 생성: generateEmail
# 이메일 디자인 생성 + 테스트 발송: generateEmailDesign
# 단체 메일 발송: sendBulkEmail
# 헬퍼 함수: getDocIdFromUrl, getDocContent, columnLetterToIndex

이제 각 함수가 하는 일을 초보자 기준으로 간단히 풀어보겠습니다.

==== 3. 상단: API 키 설정 ====

<syntaxhighlight lang="js">const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');

</syntaxhighlight>
* 스크립트 속성에 저장해 둔 GEMINI_API_KEY 값을 불러와서, Gemini API를 호출할 때 사용할 키를 전역 변수로 들고 있습니다.
* 장점: 코드에 키를 직접 적지 않아서 보안에 더 안전함.

==== 4. 메뉴 생성: onOpen, createCustomMenuManually ====

<syntaxhighlight lang="js">function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('CRM 자동화')
    .addItem('1. 이메일 내용 생성...', 'generateEmail')
    .addItem('2. 이메일 디자인 생성 및 테스트 발송...', 'generateEmailDesign')
    .addSeparator()
    .addItem('3. 단체 이메일 발송', 'sendBulkEmail')
    .addToUi();
}

</syntaxhighlight>
* 시트를 열 때 자동으로 실행됨.
* 상단 메뉴에 “CRM 자동화”라는 커스텀 메뉴를 만들고, 각 항목을 함수 이름에 연결: - 메뉴 1 → generateEmail - 메뉴 2 → generateEmailDesign - 메뉴 3 → sendBulkEmail

<syntaxhighlight lang="js">function createCustomMenuManually() { ... }

</syntaxhighlight>
* onOpen이 잘 안 동작할 때, 앱스 스크립트 편집기에서 이 함수를 한 번 수동 실행하면 동일한 메뉴를 만들어 주는 “비상용 버튼”입니다.

==== 5. 이메일 내용 생성: generateEmail ====

이 함수는 “C6 + C7의 구글독스 → C8, C9” 를 담당합니다.

===== 5-1. 기본 셀 값 읽기 =====

<syntaxhighlight lang="js">const sheet = SpreadsheetApp.getActiveSheet();
const ss = SpreadsheetApp.getActiveSpreadsheet();
const emailCore = sheet.getRange('C6').getValue();

</syntaxhighlight>
* 현재 활성 시트와 스프레드시트 객체를 가져옴.
* C6에서 “이메일 핵심 내용”을 읽음.

===== 5-2. C7 스마트칩(구글 독스 링크) 가져오기 =====

<syntaxhighlight lang="js">const sheetName = sheet.getName();
const a1Notation = <code>'${sheetName}'!C7</code>;

obj = Sheets.Spreadsheets.get(ss.getId(), {
  ranges: [a1Notation],
  fields: "sheets(data(rowData(values(userEnteredValue,chipRuns))))"
});

</syntaxhighlight>
* '''고급 서비스인 “Sheets API”'''를 사용해서, C7의 상세 정보를 가져옵니다.
* 일반 getValue()로는 스마트칩 안의 URL을 바로 알 수 없기 때문에, chipRuns라는 내부 구조를 읽어오는 방식.

<syntaxhighlight lang="js">const rowData = obj.sheets[0].data[0].rowData[0];
const chipRuns = rowData.values[0].chipRuns;
...
docUrl = run.chip.richLinkProperties.uri;

</syntaxhighlight>
* chipRuns 배열 안에서 richLinkProperties.uri에 실제 구글 독스 URL이 들어있어서 그걸 docUrl로 뽑아냅니다.

===== 5-3. 입력값 체크 =====

<syntaxhighlight lang="js">if (!emailCore) { alert('C6에 이메일 핵심 내용을...'); return; }
if (!docUrl) { alert('C7에 구글 독스 스마트칩이 없습니다!'); return; }

</syntaxhighlight>
* 핵심 내용(C6)이나 프롬프트 독스(C7)가 비어 있으면 바로 사용자에게 알리고 함수 종료.

===== 5-4. 구글 독스에서 프롬프트 내용 읽기 =====

<syntaxhighlight lang="js">const userPrompt = getDocContent(docUrl);

</syntaxhighlight>
* 아래 헬퍼 함수 getDocContent를 호출해서 구글 독스 문서 전체 텍스트를 가져옴.
* 권한 문제나 URL 문제로 실패하면 null 반환 → 경고 후 종료.

===== 5-5. Gemini에 보낼 최종 프롬프트 구성 =====

<syntaxhighlight lang="js">const finalPrompt = <code>${userPrompt}

이메일 핵심 내용:
${emailCore}

다음 형식으로 정확하게 응답해주세요:
제목: [여기에 이메일 제목]
---
[여기에 이메일 본문]</code>;

</syntaxhighlight>
* 독스에 적어둔 “작성 가이드 프롬프트” + C6의 핵심 내용 + “반드시 이런 형식으로 답해라”라는 포맷 지침을 하나로 합친 문자열.

===== 5-6. Gemini API 호출 =====

<syntaxhighlight lang="js">const payload = { contents: [{ parts: [{ text: finalPrompt }] }] };
const url = 'https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent';

const options = {
  method: 'POST',
  contentType: 'application/json',
  headers: { 'x-goog-api-key': apiKey },
  payload: JSON.stringify(payload)
};

const response = UrlFetchApp.fetch(url, options);
const data = JSON.parse(response.getContentText());
const content = data['candidates'][0]['content']['parts'][0]['text'];

</syntaxhighlight>
* Gemini 2.5 Flash 모델에 POST 요청.
* 응답의 첫 번째 후보 텍스트를 꺼냄.

===== 5-7. “제목 / 본문” 파싱 후 C8, C9에 쓰기 =====

<syntaxhighlight lang="js">const lines = content.split('\n');
let subject = '';
let body = '';
let foundSeparator = false;

for (...) {
  if (line.startsWith('제목:')) { subject = ... }
  else if (line === '---') { foundSeparator = true; }
  else if (foundSeparator) { body += ... }
}

sheet.getRange('C8').setValue(subject.trim());
sheet.getRange('C9').setValue(body.trim());

</syntaxhighlight>
* 응답 텍스트를 줄 단위로 쪼개서 - 제목:으로 시작하는 줄 → 제목 - --- 이후의 내용 → 본문
* 최종적으로 C8, C9에 기록.
* 완료 후 알림창: “이메일 내용이 생성되었습니다!”

==== 6. 이메일 디자인 + 테스트 발송: generateEmailDesign ====

이 함수는 “텍스트(C9) + 디자인 프롬프트(C14) → HTML(C15) + 테스트 메일 발송(C13)” 담당.

===== 6-1. 기본 값 및 C14 스마트칩 처리 =====
* C9에서 이메일 텍스트 내용 읽기
* C14에서, 위와 동일한 방식으로 chipRuns를 통해 구글 독스 URL 추출
* getDocContent(designPromptDocUrl)로 디자인 지침 텍스트 가져오기
* 각 값이 비어 있으면 적절한 알림 후 종료

===== 6-2. Gemini 프롬프트 구성 =====

<syntaxhighlight lang="js">const finalPrompt = <code>
당신은 전문 이메일 HTML 디자이너입니다.
다음은 디자이너에게 전하는 디자인 지침(프롬프트)입니다:
---[디자인 지침 시작]---
${designPrompt}
---[디자인 지침 끝]---

다음은 이메일에 들어갈 실제 텍스트 내용입니다:
---[이메일 텍스트 내용 시작]---
${emailText}
---[이메일 텍스트 내용 끝]---

[요청 사항]
... (인라인 스타일, 순수 HTML만, UTF-8 등)
</code>;

</syntaxhighlight>
* 역할 지시(“전문 이메일 HTML 디자이너”)와 요구사항(인라인 스타일, <html>...</html>만, UTF-8 등)을 명확히 적어 줌.

===== 6-3. Gemini 호출 및 HTML 정리 =====

Gemini 호출 부분은 generateEmail과 거의 동일.

<syntaxhighlight lang="js">htmlResult = content.trim();
if (htmlResult.startsWith('</syntaxhighlight>html')) { ... }
if (htmlResult.endsWith('``<code>')) { ... }
sheet.getRange('C15').setValue(htmlResult);

<syntaxhighlight>
* 모델이 종종 html 코드블록 형태로 답하는 경우를 대비해서, 백틱을 제거하고 순수 HTML만 남기고 C15에 저장.

===== 6-4. 테스트 메일 발송 =====

</syntaxhighlight>js
const testEmail = sheet.getRange('C13').getValue().trim();
const subject = sheet.getRange('C8').getValue();

if (testEmail) {
  const emailOptions = {
    htmlBody: htmlResult,
    name: 'AI CRM 시스템 (테스트)',
  };
  GmailApp.sendEmail(testEmail, </code>[테스트] ${subject}<code>, "", emailOptions);
  ui.alert(</code>... 테스트 메일이 ${testEmail}로 발송되었습니다!<code>);
} else {
  ui.alert('이메일 디자인이 생성되어 C15에 입력되었습니다! (C13에 테스트 주소가 없어 발송되지 않았습니다)');
}

<syntaxhighlight>
* C13에 테스트 이메일이 있으면: - GmailApp.sendEmail로 HTML 메일 발송 - 제목 앞에 [테스트] 붙임
* 없으면: “디자인은 저장했지만 발송은 안 했다”는 알림만.

==== 7. 단체 메일 발송: sendBulkEmail ====

이 함수는 주소록 스프레드시트 + 설정 정보 → 개인화된 대량 발송 담당.

===== 7-1. 발송 기본 정보 로드 =====

</syntaxhighlight>js
const subject = sheet.getRange('C8').getValue();
const htmlBodyTemplate = sheet.getRange('C15').getValue();
const senderEmail = sheet.getRange('C19').getValue().trim();
const senderName = sheet.getRange('C20').getValue().trim();

<syntaxhighlight>
* 제목, HTML 템플릿, 발신자 이메일/이름 가져오기.

===== 7-2. 주소록 시트 정보 읽기 =====

</syntaxhighlight>js
const addressBookSheetName = sheet.getRange('C22').getValue().trim();
...
// C21 스마트칩에서 URL 추출 (C7/C14와 동일 방식)
addressBookSheetUrl = run.chip.richLinkProperties.uri;

<syntaxhighlight>
* C21: 주소록 스프레드시트 링크(스마트칩)
* C22: 그 스프레드시트 안에서 사용할 시트 이름

===== 7-3. 이메일/이름 열 정보 읽기 =====

</syntaxhighlight>js
const emailColumnLetter = sheet.getRange('C23').getValue().trim().toUpperCase();
const nameColumnLetter = sheet.getRange('C24').getValue().trim().toUpperCase();

<syntaxhighlight>
* 예: 이메일 열이 B, 이름 열이 C 등.

===== 7-4. 필수값 체크 =====

</syntaxhighlight>js
if (!subject || !htmlBodyTemplate) ...
if (!addressBookSheetUrl || !addressBookSheetName || !emailColumnLetter || !nameColumnLetter) ...
if (!senderName) ...

<syntaxhighlight>
* 기본 설정이 빠진 경우 즉시 알림 후 중단.

===== 7-5. 주소록 스프레드시트/시트 열기 =====

</syntaxhighlight>js
const addressBookSheetId = getDocIdFromUrl(addressBookSheetUrl);
const addressBookSpreadsheet = SpreadsheetApp.openById(addressBookSheetId);
const addressBookSheet = addressBookSpreadsheet.getSheetByName(addressBookSheetName);
const data = addressBookSheet.getDataRange().getValues();

<syntaxhighlight>
* C21의 URL에서 문서 ID를 추출(getDocIdFromUrl)하고
* openById로 시트 파일을 열어,
* C22에 적힌 시트 이름으로 해당 시트를 찾고 전체 데이터를 2차원 배열로 가져옴.

===== 7-6. 열 문자 → 인덱스로 변환 =====

</syntaxhighlight>js
const emailColIndex = columnLetterToIndex(emailColumnLetter);
const nameColIndex = columnLetterToIndex(nameColumnLetter);

<syntaxhighlight>
* "A" → 0, "B" → 1, "AA" → 26 이런 식으로 바꾸는 함수.
* 나중에 data[row][index]로 접근하기 위해 필요.

===== 7-7. 루프 돌면서 개인화 + 발송 =====

</syntaxhighlight>js
let sentCount = 0;
const placeholder = /{{이름}}/g;

const options = {
  name: senderName,
  htmlBody: null,
};

if (senderEmail) {
  options.from = senderEmail;
}

for (let i = 1; i < data.length; i++) {
  const recipientEmail = row[emailColIndex];
  const recipientName = row[nameColIndex];

  if (!recipientEmail || recipientEmail.indexOf('@') === -1) { continue; }

  const nameForEmail = recipientName || "고객";
  const personalizedBody = htmlBodyTemplate.replace(placeholder, nameForEmail);
  options.htmlBody = personalizedBody;

  GmailApp.sendEmail(recipientEmail, subject, "", options);
  sentCount++;
  Utilities.sleep(1000);
}
ui.alert(</code>총 ${sentCount}건의 이메일이 성공적으로 발송되었습니다.<code>);

<syntaxhighlight>
* 1행은 헤더라고 보고 2행(i=1)부터 반복.
* 이메일이 비어 있거나 @가 없으면 건너뜀.
* 이름이 없으면 “고객”으로 대체.
* C15 템플릿에서 {{이름}}이라는 문자열을 해당 사람 이름으로 모두 치환.
* GmailApp.sendEmail로 HTML 발송.
* 구글 발송 제한(쿼터)에 조금 여유를 주기 위해 1초씩 딜레이.
* 한 사람이라도 에러 나면 바로 중단 및 알림.

==== 8. 헬퍼 함수들 ====

===== 8-1. getDocIdFromUrl(url) =====

</syntaxhighlight>js
const match = url.match(/[-\w]{25,}/);
return match ? match[0] : null;

<syntaxhighlight>
* 구글 문서/시트 URL 안에서 25자 이상짜리 ID 패턴을 정규식으로 찾아서 반환.
* Docs, Sheets 모두 이런 형식이라 공용으로 사용 가능.

===== 8-2. getDocContent(docUrl) =====

</syntaxhighlight>js
const docId = getDocIdFromUrl(docUrl);
const doc = DocumentApp.openById(docId);
const body = doc.getBody();
const text = body.getText();
return text;

<syntaxhighlight>
* 구글 독스 문서를 열어서 본문 전체 텍스트를 반환.
* 권한 문제 등으로 실패하면 null 반환.

===== 8-3. columnLetterToIndex(letter) =====

</syntaxhighlight>js
let column = 0;
for (...) {
  column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
}
return column - 1;

</code>``
* 엑셀/시트의 “열 문자”를 숫자 인덱스로 바꾸는 함수. - "A" → 0, "B" → 1, "Z" → 25, "AA" → 26 …

==== 9. 초보자에게 설명할 때 포인트 정리 ====

발표/설명할 때는 이렇게 요약해 주면 이해가 쉽습니다.
# “이 시스템은 3단계 자동화 파이프라인이다” 1. 글을 잘 쓰는 AI에게 내용(제목+본문)을 받아오고 2. 디자이너 역할의 AI에게 HTML 디자인을 받아오고 3. 주소록을 읽어서 사람 이름을 끼워 넣어 단체 발송한다.
# “각 셀의 역할만 기억하면 된다” - C6, C7 → 내용 생성에 쓰이는 재료 - C8, C9 → 최종 제목/본문 - C13, C14, C15 → 테스트용 디자인 생성 및 발송 - C19~C24 → 단체 발송 설정 (발신자, 주소록 위치, 열 정보)
# “코드는 그 과정을 자동으로 수행하는 버튼의 뇌” - 메뉴 함수: 버튼을 만든다. - generateEmail: 글쓰기 AI 호출 담당 - generateEmailDesign: 디자인 AI + 테스트 발송 담당 - sendBulkEmail: 주소록 + 개인화 + 단체 발송 담당 - 헬퍼 함수: URL에서 ID 뽑기, 독스 읽기, 열 문자 변환 같은 반복 작업 담당

이 정도 구조로 설명하면, 개발을 잘 모르는 사람도 “전체 그림”과 “각 함수 역할”을 꽤 명확하게 이해할 수 있습니다.

== References ==
<references />


[[Category:ChatGPT Shares]]
[[Category:openai]]