구글 스프레드시트 앱스크립트 체크박스 이메일 보내기

구글 스프레드시트 앱스크립트 체크박스 이메일 자동 보내기

 

스프레드시트의 체크박스만 눌러주면 자동으로 보내는 기능을 만들어보겠습니다.

 

그 전에 왜 스프레드 시트를 활용하는가?에 대해서 한번 생각해볼 것이 있습니다.

첫번째는 HTML을 이용할 수 있다는 점입니다.

현재 g-mail을 기본적으로는 HTML로 작성된 이메일을 보낼 수 없고 편법을 이용해야 합니다.

두번째는 보낸 내역을 기록으로 남기기 편하다는 것입니다.

어떤 것을 메일 보냈고, 보내지 않았는지 한눈에 확인 가능합니다.

세번째는 두번째와 연결해서 보낼 리스트를 쌓아둘 수 있다는 것입니다.

이메일을 시간차를 두고 순차적으로 보내야한다면?

스프레드시트는 미리 리스트를 쌓아둘 수 있습니다. 

 

그럼 본격적으로 체크박스를 눌러서 이메일 발송하기를 구현해보겠습니다.

 

구글 스프레드시트의 확장프로그램에서 앱스크립트(Apps Script)를 실행합니다.

 

전반적인 흐름을 요약해보자면 아래와 같습니다.

1. 스프레드시트에 이메일 제목과 본문 그리고 수신인 정보를 기록한다.
2. 앱스크립트에서 스프레드시트의 내용을 가져온다.
3. 조건문으로 체크박스가 체크되었을 때 메일을 발송하게 한다.
4. 스프레드시트가 수정될 때 함수가 실행하게 설정한다.

 

1. 스프레드시트에  이메일 제목과 본문 그리고 수신인 정보를 기록한다.

스프레드시트에 기록되는 정보들은 각자 좌표값을 가진다고 생각하시면 됩니다.

예를 들어 1번의 홍길동 이름은 B12가 위치입니다. 

 

사실상 여기에서 필요한 정보는 이메일 주소와 체크박스 버튼입니다.

스프레드시트 메뉴에서 '삽입'을 들어가보시면 체크박스를 추가할 수 있습니다.

눈으로 보이는 것은 체크박스 표시이지만,

체크가 되는 값은 TRUE 이고, 

체크가 해제되어 있으면 FALSE 값을 가집니다.

 

 

2. 앱스크립트에서 스프레드시트의 내용을 가져온다.

function onEdit(e){
  try{
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = ss.getActiveSheet();
    let range = e.range;

onEdit이라는 함수입니다.

이 때 파라미터로 전달되는 인자값 e는 이벤트 객체이고

이 객체는 트리거를 발생시킨 컨택스트에 대한 정보가 포함되어 있습니다.

이벤트 객체에서 사용할 수 있는 메서드나 정보는 아래 문서에 나와있습니다.

https://developers.google.com/apps-script/guides/triggers/events?hl=en 

Event Objects  |  Apps Script  |  Google Developers

range 는 수정된 셀의 범위를 나타냅니다. 

3. 조건문으로 체크박스가 체크되었을 때 메일을 발송하게 한다.

 if(range.isChecked() === true){
      try{
          let row = range.getRow();
          let column = range.getColumn();
          let emailNum = column - 1;
          let resultNum = column + 1;
          let emailAddress = sheet.getRange(row,emailNum).getValue();

          let subject = sheet.getRange("F2").getValue();

          let firstBody = sheet.getRange("F4").getValue();
          let secondBody = sheet.getRange("F6").getValue();
          let thirdBody = sheet.getRange("F8").getValue();
          let fourthBody = sheet.getRange("F10").getValue();
        
      	  MailApp.sendEmail({
              to: emailAddress,
              subject: subject,
              htmlBody: firstBody+secondBody+thirdBody+fourthBody
          });
        	sheet.getRange(row,resultNum).setValue("발송완료");
      }catch(e){
        	sheet.getRange(row,resultNum).setValue("발송실패");
			console.log(e);
      }

isChecked() 메서드는 범위 안의 모든 셀의 체크박스가 체크되었는지 여부를 반환합니다. 

이 때 스프레드 시트에서 체크박스 위치를 기준으로 앞 뒤에 수신인의 이메일과

발송 결과를 찍어줄 예정이기 때문에 체크박스 column 앞뒤(-1, +1)값을 변수에 담아줍니다.

그리고 본문 html 내용이 너무 길어서

스프레드시트 단일 셀 안에 안들어가기 때문에 

개별 본문으로 나눠서 담아주고, 이후에 합쳐서 발송합니다.

 

 

4. 스프레드시트가 수정될 때 함수가 실행하게 설정한다.

트리거 부분 설정입니다.

언제 이 함수가 동작하도록 할 것인지 설정하는 것인데,

우리는 onEdit 함수가 스프레드시트가 업데이트, 

그러니까 수정될 때마다 동작해서,

체크박스가 체크되었는지 확인할 것이기 때문에 

위와같이 설정합니다. 

 

그리고 마지막 팁으로는 

체크박스가 있는 열에는 조건부 서식으로 체크된 경우 색으로 강조를 해주도록 합니다.

아무래도 이미 발송한 메일을 다시 보내지 않기 위함입니다.

 

이제 체크박스를 체크하면,

이메일 주소로 html 로 작성된 이메일이 발송됩니다!

 

만들면서 생각이 든 것은,

이 앱스크립트(Apps Script)로 못 만들 것이 없을 것 같다는 생각입니다.

 

구글 스프레드시트 앱스크립트 체크박스 이메일 자동 보내기