.png&blockId=ce200852-91bf-4427-9b0c-66a4be150edf)
목적
구글 스프레드시트의 스크립트 편집기를 사용하여, 사용자가 전송하기 원하는 이메일 주소로 HTML 요소를 추가하여 편집한 메일을 전송하는 기능을 익힙니다.
예제 1 - 시트 데이터를 폼으로 사용하여 메일 전송 스크립트 실행
지금까지 배운 내용들을 응용하여 상대방에게 효과적으로 메일을 전송하는 폼을 생성할 수 있습니다.
예를 들어 다음처럼 당월 수수료와 비용 내역을 계산하여 메일 전송 대상자에게 청구하는 메일을 전송해 보겠습니다.
우선 아래와 같이 G열까지 열 순서대로 정보를 입력해 봅니다.
A 열 : 이메일 주소
B 열 : 이름
C 열 : 지점
D 열 : 날짜
E 열 : 매출
F 열 : 수수료
G 열 : 선 비용 청산 & 오브젝트 사용료
'연락처' 시트
경우에 따라서 특정 열은 자동으로 데이터가 입력되게 할 수도 있습니다.
예를 들어, 날짜열인 D열의 경우 다음과 같은 함수로 한 행에 A열, B열, C열이 모두 빈 칸이 아닐 때 당월 날짜를 반환하도록 할 수 있습니다.
=if(and(A2<>"",B2<>"",C2<>""),TODAY(),"")
C++
복사
또한 수수료 데이터의 F 열은 매출 데이터의 E 열로부터 입력된 수의 10% 를 반환하여 자동으로 입력되도록 다음과 같은 수식을 적용할 수 있습니다. 예시 시트에서는 실제로 이 수식이 적용되었습니다.
=E2*0.1
C++
복사
다음은 예시로, "메일 전송 내용" 이라는 이름으로 지정한 시트 내용입니다.
'메일 전송 내용' 시트
중괄호로 입력된 부분은 다음과 같은 목적을 가지고 있습니다.
{name} : "연락처" 시트의 '이름' 데이터인 "B열"의 데이터가 대치될 위치입니다.
{area} : "연락처" 시트의 '지점' 데이터인 "C열"의 데이터가 대치될 위치입니다.
{date} : "연락처" 시트의 '날짜' 데이터인 "D열"의 데이터가 대치될 위치입니다.
{cost_vat} : "연락처" 시트의 '수수료' 데이터인 "F열"의 데이터가 대치될 위치입니다.
{cost_used} : "연락처" 시트의 '선 비용 청산 & 오브젝트 사용료' 데이터인 "G열"의 데이터가 대치될 위치입니다.
{bankname} : "메일 전송 내용" 시트의 B2 셀에 입력된 데이터가 대치될 위치입니다.
{banknumber} : "메일 전송 내용" 시트의 B3 셀에 입력된 데이터가 대치될 위치입니다.
{companyname} : "메일 전송 내용" 시트의 B4 셀에 입력된 데이터가 대치될 위치입니다.
이제 이를 기반으로 본격적으로 스크립트를 작성해 보겠습니다.
function sendEmails(){
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("연락처").activate();
let ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
let lr = ss.getLastRow();
let templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("메일 전송 내용");
let textBody = templateText.getRange(1, 2).getValue();
let bankName = templateText.getRange(2, 2).getValue();
let bankAddress = templateText.getRange(3, 2).getValue();
let companyName = templateText.getRange(4, 2).getValue();
let quotaLeft = MailApp.getRemainingDailyQuota();
Logger.log(quotaLeft);
if((lr-1) > quotaLeft){
Browser.msgBox("현재 고객님의 오늘 메일 전송 잔여량은 " + quotaLeft + " 회이며, " + (lr-1) + " 개의 메일을 보내려고 시도하셨습니다. 이메일이 전송되지 않았습니다.");
} else {
for (let i=2;i<=lr;i++){
let currentEmail = ss.getRange(i, 1).getValue();
let currentName = ss.getRange(i, 2).getValue();
let currentArea = ss.getRange(i, 3).getValue();
let currentDate = ss.getRange(i, 4).getDisplayValue();
let costVat = ss.getRange(i, 6).getDisplayValue();
let costUsed = ss.getRange(i, 7).getDisplayValue();
messageBody = textBody.replace("{name}",currentName)
.replace("{area}",currentArea)
.replace("{date}",currentDate)
.replace("{cost_vat}",costVat)
.replace("{cost_used}",costUsed)
.replace("{bankname}",bankName)
.replace("{banknumber}",bankAddress)
.replace("{companyname}",companyName);
let subjectLine = currentArea + "점의 " + currentName + " 님께 다음 메일이 전송되었습니다.";
MailApp.sendEmail(currentEmail, subjectLine, messageBody);
} // 반복문 종료
} // else 문 종료.
}
JavaScript
복사
코드를 상단의 변수 선언 부분, 하단의 조건문 부분으로 나눠서 분석해 보겠습니다.
연락처 시트에서 데이터가 입력된 가장 마지막 셀의 위치를 변수 lr 로 반환합니다.
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("연락처").activate();
let ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
let lr = ss.getLastRow();
let templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("메일 전송 내용");
let textBody = templateText.getRange(1, 2).getValue();
let bankName = templateText.getRange(2, 2).getValue();
let bankAddress = templateText.getRange(3, 2).getValue();
let companyName = templateText.getRange(4, 2).getValue();
let quotaLeft = MailApp.getRemainingDailyQuota();
Logger.log(quotaLeft);
JavaScript
복사
상단의 변수 선언 부분
"연락처" 시트에서 데이터가 입력된 가장 마지막 행의 위치를 변수 lr 로 반환합니다.
"메일 전송 내용" 시트에서 B열에 각 행마다 입력된 데이터들을 순서대로 변수 textbody, bankName, bankAddress, companyName 으로 반환합니다.
일일 메일 전송 가능 잔여 수량을 변수 quotaLeft 로 반환하여 실행 로그에 출력합니다.
if((lr-1) > quotaLeft){
Browser.msgBox("현재 고객님의 오늘 메일 전송 잔여량은 " + quotaLeft + " 회이며, " + (lr-1) + " 개의 메일을 보내려고 시도하셨습니다. 이메일이 전송되지 않았습니다.");
} else {
for (let i=2;i<=lr;i++){
let currentEmail = ss.getRange(i, 1).getValue();
let currentName = ss.getRange(i, 2).getValue();
let currentArea = ss.getRange(i, 3).getValue();
let currentDate = ss.getRange(i, 4).getDisplayValue();
let costVat = ss.getRange(i, 6).getDisplayValue();
let costUsed = ss.getRange(i, 7).getDisplayValue();
messageBody = textBody.replace("{name}",currentName)
.replace("{area}",currentArea)
.replace("{date}",currentDate)
.replace("{cost_vat}",costVat)
.replace("{cost_used}",costUsed)
.replace("{bankname}",bankName)
.replace("{banknumber}",bankAddress)
.replace("{companyname}",companyName);
let subjectLine = currentArea + "점의 " + currentName + " 님께 다음 메일이 전송되었습니다.";
MailApp.sendEmail(currentEmail, subjectLine, messageBody);
} // 반복문 종료
} // else 문 종료.
JavaScript
복사
하단의 조건문 부분.
일일 메일 전송 가능 잔여 수량이 전송을 시도할 이메일 수보다 적어지면, 스프레드시트에 메일 전송이 실패했다는 알림창을, 실행 로그에 출력된 데이터를 메시지로 담아 표시합니다.
일일 메일 전송 가능 잔여 수량이 전송을 시도할 이메일 수보다 많을 때는 다음 작업을 반복 수행합니다.
(1) "연락처" 시트의 E열을 제외한 데이터들을 각 열을 기준으로 삼아 변수로 담습니다. (이 때 "연락처" 시트의 D, F, G 열의 데이터는 시트에 표시된 형식 그대로 담습니다.)
(2) "메일 전송 내용" 시트의 B1 셀에 입력되어 있던 내용 중, 모든 중괄호의 내용을 (1) 과정에서 변수로 담았던 내용으로 대체합니다.
(3) currentEmail 로 담은 이메일 주소에 subjectLine 으로 담은 내용을 메일 제목으로, messageBody 으로 담은 내용을 메일 내용으로 지정하여 메일을 전송합니다.
예시를 기준으로 "연락처" 시트에서 총 3행의 데이터가 입력되었기 때문에 메일 전송은 총 3번 실행됩니다.
jinsoo@characterbox.co.kr 로 전송된 메일.
jinsoo@appdesign.co.kr 로 전송된 메일.
jinsoo@autooffice.co.kr 로 전송된 메일.
예제 2-1 - 시트 데이터 폼의 한계
시트에 입력된 데이터를 폼으로 사용하는 것에는 한계가 있습니다. 예제 1에서 사용한 폼을 예로 들겠습니다.
만약 영업 관계자 또는 고객이 '중요한 내용이 구별되어 바로 볼 수 있게 굵은 폰트로 전송되도록 요청합니다' 와 같이 피드백을 줄 상황이 생길 수 있습니다.
사진을 예로 들자면 수수료와 선 비용 정산 및 오브젝트 사용료 폰트가 볼드체로 메일이 전송되도록 요청했다고 가정하겠습니다.
"예제 1" 에서 사용한 "메일 전송 내용" 시트에 기록된 내용. '수수료' 폰트와 '오토오피스 선 비용 정산 및 오브젝트 사용료' 폰트에 볼드체가 적용된 상태.
문제는 스프레드시트에서 볼드체로 적용해도 실제로 메일이 전송될 때, 전송받은 메일에서는 볼드체가 적용되지 않는다는 것입니다.
시트에서 볼드체를 적용하기 전 스크립트 실행 결과.
시트에서 볼드체를 적용한 후 스크립트 실행 결과. 스크립트를 실행하기 전에 "연락처" 시트에서 이름 데이터를 수정한 상태입니다.
이런 이슈를 해결하기 위해 사용할 수 있는 방법 중에는 스크립트 편집기에서 HTML 폼을 만들고, HTML 폼을 JS 와 연동하여 메일을 전송하는 것이 있습니다.
예제 2-2 - HTML 폼을 사용하여 메일 전송 스크립트 실행
이제 HTML 폼으로 전송하기 위해서는 HTML 로 구성된 파일이 있어야 할 것입니다.
이 부분을 스크립트 편집기에서 제작이 가능하며, 지금부터 HTML 폼을 스크립트 편집기에서 제작해 보겠습니다.
파일 추가 드롭다운이 활성화된 상태.
편집기에서 좌측 상단의 "파일" 라인 오른쪽에 "+" 버튼을 클릭 시, 스크립트 파일과 HTML 파일 중 추가할 파일 형식을 묻는 드롭다운이 생깁니다.
HTML 파일을 클릭하여 파일 이름을 Htmlbody 라고 지정하겠습니다.
".html" 을 입력할 필요 없이 파일 형식을 이미 HTML 로 지정했기 때문에 확장자가 자동으로 ".html" 로 지정됩니다.
Mailbody.html 파일이 추가된 상태
Mailbody.html 파일에 들어갈 소스 코드는 다음과 같습니다.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<?= currentName ?> 점주님 안녕하세요,<br><br>
#<?= currentArea ?>점의 <?= currentDate ?> 수수료와 비용내역을 각각 청구드립니다.<br>
관련하여 궁금하신 점은 편하게 말씀해주십시오!<br>
<ul>
<li><b>수수료(매출의 10%) : <?= costVat ?> 원</b></li>
<li><b>오토오피스 선 비용 정산 및 오브젝트 사용료 : <?= costUsed ?> 원</b></li>
</ul><br>
입금계좌 : <?= bankName ?> <?= bankAddress ?> <?= companyName ?><br>
감사합니다.<br>
오토오피스 직원 드림
</body>
</html>
HTML
복사
Mailbody.html 소스 코드.
시트의 데이터 폼과 형태는 거의 비슷하나, HTML 폼에서 사용하는 방식에 다소 차이점이 존재합니다.
변수가 들어가야 할 부분에 시트에서는 {currentName} 방식과 같이 중괄호를 써서 그 안에 변수 이름을 넣었지만, html 폼에서는 <?= currentName ?> 방식으로 사용하죠.
code.gs 파일에도 약간의 변화를 추가하겠습니다.
함수를 별도로 분리하여 분리한 함수 명은 sendEmails2() 로 지정하겠습니다.
function sendEmails2(){
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("연락처").activate();
let ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
let lr = ss.getLastRow();
let templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("메일 전송 내용");
let htmlTemplate = HtmlService.createTemplateFromFile("Mailbody");
let bankName = templateText.getRange(2, 2).getValue();
let bankAddress = templateText.getRange(3, 2).getValue();
let companyName = templateText.getRange(4, 2).getValue();
htmlTemplate.bankName = bankName;
htmlTemplate.bankAddress = bankAddress;
htmlTemplate.companyName = companyName;
let quotaLeft = MailApp.getRemainingDailyQuota();
Logger.log(quotaLeft);
if((lr-1) > quotaLeft){
Browser.msgBox("현재 고객님의 오늘 메일 전송 잔여량은 " + quotaLeft + " 회이며, " + (lr-1) + " 개의 메일을 보내려고 시도하셨습니다. 이메일이 전송되지 않았습니다.");
} else {
for (let i=2;i<=lr;i++){
let currentEmail = ss.getRange(i, 1).getValue();
let currentName = ss.getRange(i, 2).getValue();
let currentArea = ss.getRange(i, 3).getValue();
let currentDate = ss.getRange(i, 4).getDisplayValue();
let costVat = ss.getRange(i, 6).getDisplayValue();
let costUsed = ss.getRange(i, 7).getDisplayValue();
htmlTemplate.currentName = currentName;
htmlTemplate.currentArea = currentArea;
htmlTemplate.currentDate = currentDate;
htmlTemplate.costVat = costVat;
htmlTemplate.costUsed = costUsed;
let htmlForEmail = htmlTemplate.evaluate().getContent();
console.log(htmlForEmail);
let subjectLine = currentArea + "점의 " + currentName + " 님께 다음 메일이 전송되었습니다.";
MailApp.sendEmail(
currentEmail,
subjectLine,
"HTML 이 지원되는 클라이언트에서 이메일을 확인하세요.",
{htmlBody: htmlForEmail}
);
} // 반복문 종료
} // else 문 종료.
}
JavaScript
복사
sendEmails2() 함수의 소스 코드.
예제 1에서 작성한 sendEmails() 함수에서 추가된 코드는 다음과 같습니다.
먼저 상단의 변수 지정 부분입니다.
let htmlTemplate = HtmlService.createTemplateFromFile("Mailbody");
JavaScript
복사
10번째 줄 추가 부분.
스크립트 편집기에서 createTemplateFromFile() 메소드의 소괄호 내에 입력된 파일명의 html 파일을 탬플릿으로 제작하여 변수 htmlTemplate 로 저장했습니다.
htmlTemplate.bankName = bankName;
htmlTemplate.bankAddress = bankAddress;
htmlTemplate.companyName = companyName;
JavaScript
복사
16 ~ 18번째 줄 추가 부분
htmlTemplate 변수에 저장한 HTML 탬플릿에서 bankName, bankAddress, companyName 을 각각 "메일 전송 내용" 시트의 B2, B3, B4 셀에 입력된 데이터와 연동합니다.
다음은 하단의 조건문 부분인데, 변경된 부분은 조건문이 거짓일 때 실행되는 반복문 부분 외에는 없습니다.
htmlTemplate.currentName = currentName;
htmlTemplate.currentArea = currentArea;
htmlTemplate.currentDate = currentDate;
htmlTemplate.costVat = costVat;
htmlTemplate.costUsed = costUsed;
let htmlForEmail = htmlTemplate.evaluate().getContent();
JavaScript
복사
37 ~ 43번째 줄 추가 부분
htmlTemplate 변수에 저장한 HTML 템플릿에서 currentName, currentArea, currentDate, costVat, costUsed 을 각각 "연락처" 시트의 B, C, D, F, G 열에 입력된 데이터와 연동합니다.
그리고 htmlTemplate 에 저장한 HTML 템플릿의 컨텐츠를 변수 htmlForEmail 로 저장합니다.
jinsoo@autooffice.co.kr 로 전송된 메일.
수수료와 비용 정산 폰트에 볼드체가 적용되었습니다.
HTML 특성상 특정 폰트에 볼드체를 적용하는 것 외에 표를 추가하거나 특정 폰트의 색을 변경하거나, 테이블 형식을 채용하는 등 다양한 폼으로 활용할 수 있습니다.
