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

=== 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 뽑기, 독스 읽기, 열 문자 변환 같은 반복 작업 담당

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