목적
구글 스프레드시트의 스크립트 편집기를 사용하여, 사용자가 전송하기 원하는 이메일 주소로 메일을 전송하는 기능을 익힙니다.
예제 1 - 메일 전송 스크립트 기본 문법
메일 전송 스크립트를 실행하기 전에 아래 이미지와 같이 스프레드시트에 몇 가지 데이터를 작성해 두겠습니다.
"연락처" 시트에 입력된 입력값 정보.
A2 셀에 jinsoo@appdesign.co.kr, A3 셀에 jinsoo@characterbox.co.kr, A4 셀에 jinsoo@autooffice.co.kr 입력값을 받아 스크립트를 실행했을 때 이 3개의 메일 주소로 메일이 전송되게 할 것입니다.
이 입력값이 기록된 시트의 이름은 "연락처"로 지정합니다.
"메일 전송 내용" 시트에 입력된 입력값 정보.
A1 셀에 입력된 시트의 내용을 담아 "연락처" 시트에 기록된 메일로 전송되게 할 것인데, 이 과정에서 셀에 입력된 {name}, {title} 부분을 각각 "연락처" 시트의 2열, 3열에 입력된 데이터로 대치하도록 적용하여 전송되게 할 것입니다.
이 입력값이 기록된 시트의 이름은 "메일 전송 내용"으로 지정합니다.
"메일 전송 내용" 시트의 내용을 본문으로 담은 메일을 전송하기 전에, 스크립트와 메일 전송 결과를 통해 이번 페이지에서 다룰 sendEmail(recipient, subject, body) 메소드의 기본 작성 문법을 알아 보겠습니다.
function sendEmails(){
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
for (var i=2;i<=lr;i++){
var currentEmail = ss.getRange(i, 1).getValue();
var currentClassTitle = ss.getRange(i, 3).getValue();
MailApp.sendEmail(currentEmail, "다음 담당부서로 전송됨: " + currentClassTitle + " 부서", " 스크립트 메일 테스트입니다.");
}
}
JavaScript
복사
스크립트
스크립트 코드를 설명하면 다음과 같습니다.
1.
"연락처" 시트의 메일을 전송할 이메일 주소가 입력된 2~4행 1열의 입력값을 받아 변수 currentEmail 에 지정합니다.
2.
"연락처" 시트의 각 메일 주소에 해당되는 클래스 명이 입력된 2~4행 3열의 입력값을 받아 변수 currentClassTitle 에 지정합니다.
3.
MailApp.sendEmail(recipient, subject, body) 코드를 작성하여 메일을 전송합니다.
이 때, recipient 에는 메일을 전송할 대상 이메일 주소를 입력하는 부분인데, 스크립트를 기준으로 "연락처" 시트에 입력된 메일 주소들이 대상이 됩니다.
subject 에는 메일의 제목을 입력하는 부분인데, "다음 담당부서로 전송됨: (currentClassTitle) 부서" 라는 제목으로 메일이 전송되며, (currentClassTitle) 부분은 "연락처" 시트에 입력된 클래스 이름으로 대치됩니다.
body 에는 메일의 본문을 입력하는 부분인데, " 스크립트 메일 테스트입니다." 라는 내용이 담긴 상태로 메일이 전송됩니다.
4.
이 과정을 "연락처" 시트에서 1행을 제외하고 입력값이 존재하는 마지막 행의 위치까지의 행 수에 따라 반복 수행합니다. 즉, "연락처" 시트에 입력된 모든 메일 주소로 이메일을 전송하는 방식을 수행합니다.
스크립트 결과. 본 메일은 MacOS 를 사용하는 맥 컴퓨터에 등록된 jinsoo@appdesign.co.kr 메일 계정에서 확인한 결과입니다.
스크립트 결과. 본 메일은 MacOS 를 사용하는 맥 컴퓨터에 등록된 jinsoo@characterbox.co.kr 메일 계정에서 확인한 결과입니다.
스크립트 결과. 본 메일은 MacOS 를 사용하는 맥 컴퓨터에 등록된 jinsoo@autooffice.co.kr 메일 계정에서 확인한 결과입니다.
메일이 정상적으로 전송된 것을 확인하실 수 있습니다.
예제 2 - 메일 전송 스크립트 심화 문법
이제 다음 이미지와 같이 "연락처" 시트와 "메일 전송 내용" 시트에 입력된 모든 입력값들을 활용하여 메일을 전송해 보겠습니다.
이 과정에서 "메일 전송 내용" 시트에 입력된 {name} 은 "연락처" 시트의 이름 입력값으로, {title} 은 "연락처" 시트의 클래스 입력값으로 대치하여 전송해 보겠습니다.
"연락처" 시트에 입력된 입력값 정보.
"메일 전송 내용" 시트에 입력된 입력값 정보.
function sendEmails(){
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("연락처").activate();
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("메일 전송 내용").getRange(1, 1).getValue();
for (var i=2;i<=lr;i++){
var currentEmail = ss.getRange(i, 1).getValue();
var currentName = ss.getRange(i, 2).getValue();
var currentClassTitle = ss.getRange(i, 3).getValue();
var messageBody = templateText.replace("{name}",currentName).replace("{title}",currentClassTitle);
var subjectLine = "다음 클래스로 등록된 이메일로 전송되었습니다 : " + currentClassTitle + " 클래스";
MailApp.sendEmail(currentEmail, subjectLine, messageBody);
}
}
JavaScript
복사
스크립트
반복문 내부의 스크립트 코드를 분석해 보겠습니다.
"연락처" 시트의 이메일 주소 입력값을 변수 currentEmail 에, 이름 입력값을 변수 currentName 에, 클래스 입력값을 변수 currentClassTitle 에 저장합니다.
"메일 전송 내용" 시트의 1행 1열 입력값에서 {name} 과 {title} 을 각각 변수 currentName 과 currentClassTitle 의 입력값으로 대치한 결과를 변수 messageBody 에 저장합니다.
"다음 클래스로 등록된 이메일로 전송되었습니다 : " + currentClassTitle + " 클래스" 입력값을 변수 subjectLine 에 저장합니다.
변수 currentEmail 에 해당하는 "연락처" 시트 내의 이메일 주소들을 대상으로, 메일 제목은 변수 subjectLine 결과값으로, 메일의 내용은 변수 messageBody 결과값으로 반환하여 메일을 전송합니다.
jinsoo@appdesign.co.kr 에서 확인한 스크립트 결과
jinsoo@characterbox.co.kr 에서 확인한 스크립트 결과
jinsoo@autooffice.co.kr 에서 확인한 스크립트 결과
예제 3 - 메일 전송 제한에 대해
스크립트를 사용하여 메일 전송이 가능한 횟수는 하루 100회로 제한되어 있습니다. 기본적으로 100회의 제한 횟수를 초과하면 메일을 전송할 수 없게 되며, 당일이 지나면 제한 횟수는 초기화됩니다.
만약 전송 가능 횟수가 초과됨으로 인해 메일이 전송되지 않았다는 것을 일반 사용자가 알 수 있으면 좋지만, 스프레드시트 스크립트는 기본적으로 이러한 알림 기능을 자동으로 처리하여 제공하지 않습니다.
그렇기 때문에 메일 전송 기능을 제공하는 시트를 다른 사용자가 이용할 수 있게 공유한 경우, 오류 알림 기능을 제공하여 다른 사용자로부터 불편 사항 접수를 최소화하기 위해 추가적으로 개발 코드를 작성하는 것은 필수적인 사항이 될 것입니다.
이러한 문제에 해결책으로 사용할 수 있는 메소드인 getRemainingDailyQuota() 에 대해 알아 보겠습니다.
function sendEmails(){
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("연락처").activate();
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("메일 전송 내용").getRange(1, 1).getValue();
var quotaLeft = MailApp.getRemainingDailyQuota();
Logger.log(quotaLeft);
if((lr-1) > quotaLeft){
Browser.msgBox("현재 고객님의 오늘 메일 전송 잔여량은 " + quotaLeft + " 회이며, " + (lr-1) + " 개의 메일을 보내려고 시도하셨습니다. 이메일이 전송되지 않았습니다.");
} else {
for (var i=2;i<=lr;i++){
var currentEmail = ss.getRange(i, 1).getValue();
var currentName = ss.getRange(i, 2).getValue();
var currentClassTitle = ss.getRange(i, 3).getValue();
var messageBody = templateText.replace("{name}",currentName).replace("{title}",currentClassTitle);
var subjectLine = "다음 클래스로 등록된 이메일로 전송되었습니다 : " + currentClassTitle + " 클래스";
MailApp.sendEmail(currentEmail, subjectLine, messageBody);
} // 반복문 종료
} // else 문 종료.
}
JavaScript
복사
스크립트
하루 메일 전송 가능 잔여 수량 결과값을 변수 quotaLeft 에 저장하고, 이를 실행 로그에 출력하는 코드를 추가했습니다.
그리고 조건문을 사용하여 하루 메일 전송 가능 잔여 수량이 lr-1 값, 즉 메일 전송 요청 수량인 3보다 적을 때 스프레드시트 화면에 메일이 전송되지 않았다는 알림창을 출력하고, 그렇지 않은 경우 메일 전송 스크립트를 수행하도록 수정되었습니다.
실제로 오류 알림 기능이 작동되는지 확인하기 위해 quotaLeft 변수를 다음과 같이 수정한 다음 스크립트를 실행하였습니다. 다음과 같이 수정할 경우 일일 메일 전송 가능 잔여 수량은 10회로 변경됩니다.
메일 전송 가능 잔여 수량은 1일마다 100회로 다시 리셋되므로, 아래 코드를 유지한 상태로 날짜가 지날 때마다 최대 10회로 리셋됩니다.
var quotaLeft = MailApp.getRemainingDailyQuota() - 90;
JavaScript
복사
스크립트 10번째 줄을 수정.
스크립트 결과 1. 잔여 수량이 전송량(3)보다 부족해지면 스크립트 실행에 무한로딩 이슈가 시작됨.
스크립트 결과 2. 시트 화면으로 이동하면 이미지와 같이 스크립트 실행 실패 알림창이 출력됨.
스크립트 결과 3. 알림창에서 확인을 누르면 스크립트 실행은 끝나지만, 메일은 전송되지 않음.
예제 4 - 스크립트를 실행하는 버튼을 시트에 추가하기
일반 사용자가 링크화된 버튼을 클릭하는 작업만으로 지금까지 작성했던 스크립트가 실행될 수 있도록 시트에 버튼을 추가하는 작업을 해 보겠습니다.
절차
1.
스프레드시트에서 [삽입] 메뉴 - [그림] 을 선택합니다.
2.
시트에 추가하길 원하는 모양의 버튼을 디자인하고, 우측 상단의 "저장 후 닫기" 버튼을 클릭하면 시트에 그림이 삽입됩니다.
사진의 예시로 사용된 버튼의 백그라운드 색상은 #ff3366 입니다.
3.
원하는 위치에 그림을 배치합니다.
4.
그림 내에서 우측 상단의 점 세 개 아이콘을 클릭한 다음 [스크립트 할당] 메뉴를 클릭합니다.
5.
빈 칸에 스크립트편집기에서 작성하던 스크립트의 함수 이름을 입력합니다.
지금까지 스크립트를 실행할 때 함수 이름을 "sendEmails" 로 정의하고 작성했기 때문에 빈 칸에 "sendEmails" 를 입력했습니다.
6.
이제 버튼을 클릭할 때마다 스크립트가 실행됩니다.
참고로, 스크립트를 실행하기 전에 미리 스크립트편집기에서 quotaLeft 코드를 다음과 같이 수정해 줍니다.
수정 전
var quotaLeft = MailApp.getRemainingDailyQuota() - 94;
JavaScript
복사
수정 후
var quotaLeft = MailApp.getRemainingDailyQuota();
JavaScript
복사
추가 팁
셀 내의 이메일 주소의 경우, 쉼표를 구분용 입력값으로 사용할 수 있으며, 예시로 아래의 이미지처럼 작성한 상태에서 시트를 수행했을 때, 다음과 같이 3개의 메일이 발송됩니다.
다음 클래스로 등록된 이메일로 전송되었습니다 : 앱디자인 및 캐릭터박스 클래스
안녕하세요, 이진수 님.
이 메일은 앱디자인 및 캐릭터박스 클래스로 등록된 귀하께 전송된 이메일입니다.
구글 앱 스크립트는 유용한 기능들이 많이 있습니다.
여러분들도 한 번 시도해 보시기 바랍니다.
JavaScript
복사
위와 같은 메일이 각각 jinsoo@appdesign.co.kr 주소와 jinsoo@characterbox.co.kr 주소로 1개씩 총 2개의 메일이 전송됩니다.
다음 클래스로 등록된 이메일로 전송되었습니다 : 오토오피스 클래스
안녕하세요, 이진수3 님.
이 메일은 오토오피스 클래스로 등록된 귀하께 전송된 이메일입니다.
구글 앱 스크립트는 유용한 기능들이 많이 있습니다.
여러분들도 한 번 시도해 보시기 바랍니다.
JavaScript
복사
위와 같은 메일이 jinsoo@autooffice.co.kr 주소로 1개의 메일이 전송됩니다.
