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

This conversation was exported from ChatGPT

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

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

2. 코드 전체 구조 지도

코드는 크게 6 덩어리입니다.

1. 상단: API 키 읽기
2. 메뉴 생성: onOpen, createCustomMenuManually
3. 이메일 내용 생성: generateEmail
4. 이메일 디자인 생성 + 테스트 발송: generateEmailDesign
5. 단체 메일 발송: sendBulkEmail
6. 헬퍼 함수: getDocIdFromUrl, getDocContent, columnLetterToIndex

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

3. 상단: API 키 설정

const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');

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

4. 메뉴 생성: onOpen, createCustomMenuManually

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

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

function createCustomMenuManually() { ... }

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

5. 이메일 내용 생성: generateEmail

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

5-1. 기본 셀 값 읽기

const sheet = SpreadsheetApp.getActiveSheet();
const ss = SpreadsheetApp.getActiveSpreadsheet();
const emailCore = sheet.getRange('C6').getValue();

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

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

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))))"
});

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

const rowData = obj.sheets[0].data[0].rowData[0];
const chipRuns = rowData.values[0].chipRuns;
...
docUrl = run.chip.richLinkProperties.uri;

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

5-3. 입력값 체크

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

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

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

const userPrompt = getDocContent(docUrl);

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

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

const finalPrompt = <code>${userPrompt}

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

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

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

5-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)
};

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

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

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

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());

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

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

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

6-1. 기본 값 및 C14 스마트칩 처리

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

6-2. Gemini 프롬프트 구성

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

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

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

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

6-3. Gemini 호출 및 HTML 정리

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

htmlResult = content.trim();
if (htmlResult.startsWith('

html')) { ... }

if (htmlResult.endsWith('``')) { ... } sheet.getRange('C15').setValue(htmlResult);

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

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

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, [테스트] ${subject}, "", emailOptions);
 ui.alert(... 테스트 메일이 ${testEmail}로 발송되었습니다!);

} else {

 ui.alert('이메일 디자인이 생성되어 C15에 입력되었습니다! (C13에 테스트 주소가 없어 발송되지 않았습니다)');

}

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

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

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

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

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();

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

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

js

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

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

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

js

const emailColumnLetter = sheet.getRange('C23').getValue().trim().toUpperCase(); const nameColumnLetter = sheet.getRange('C24').getValue().trim().toUpperCase();

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

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

js

if (!subject || !htmlBodyTemplate) ... if (!addressBookSheetUrl || !addressBookSheetName || !emailColumnLetter || !nameColumnLetter) ... if (!senderName) ...

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

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

js

const addressBookSheetId = getDocIdFromUrl(addressBookSheetUrl); const addressBookSpreadsheet = SpreadsheetApp.openById(addressBookSheetId); const addressBookSheet = addressBookSpreadsheet.getSheetByName(addressBookSheetName); const data = addressBookSheet.getDataRange().getValues();

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

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

js

const emailColIndex = columnLetterToIndex(emailColumnLetter); const nameColIndex = columnLetterToIndex(nameColumnLetter);

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

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

js

let sentCount = 0; const placeholder = /Template:이름/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(총 ${sentCount}건의 이메일이 성공적으로 발송되었습니다.);

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

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

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

js

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

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

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

js

const docId = getDocIdFromUrl(docUrl); const doc = DocumentApp.openById(docId); const body = doc.getBody(); const text = body.getText(); return text;

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

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

js

let column = 0; for (...) {

 column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);

} return column - 1;

``

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

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

발표/설명할 때는 이렇게 요약해 주면 이해가 쉽습니다.

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

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