[앱스 스크립트 활용] 가계부 템플릿 스프레드시트에서 메일로 월간 보고서 받기

이 포스팅은 [스프레드시트 활용] 가계부 템플릿 무료 공유 및 사용방법 – 연간 저축 목표를 달성해보세요! 의 후속 컨텐츠입니다. 이어지는 내용이오니 가계부 템플릿은 이전 포스팅에서 확인해주세요.

이미 다양한 템플릿이 존재하기에 그냥 가계부 템플릿만 공유하면 경쟁력이 부족할 것이라 판단하여 가계부 템플릿으로 스프레드시트 활용법과 Apps Script로 스프레드시트의 데이터를 메일로 보내는 기능 만드는 법 두가지를 함께 배울 수 있는 컨텐츠로 준비해보았습니다.
이번 포스팅에서는 가계부 템플릿의 기본 사용방법을 배우고, 템플릿에 본인의 가계부 데이터를 채워보시고 다음 단계로 넘어가시길 바랍니다. 다음 포스팅에서는 Apps Script로 월간 보고서를 메일로 받아보는 기능을 알려드리겠습니다.

---

가계부 템플릿에서 월간 보고서 시트 열어보기

미리 예고했던대로 이번 포스팅에서는 앱스 스크립트로 스프레드시트의 데이터를 메일로 보내는 기능을 만들어보겠습니다. 가계부 템플릿에 데이터를 입력하고 월간 보고서 시트를 열어보겠습니다.

월간 보고서 시트에서 지정한 달의 한달치 지출과 수입 상세 내역과 항목별 합계 금액을 볼 수 있는 그래프, 카테고리별 지출 금액 합계 그래프 등을 확인할 수 있습니다.

• 수입 합계 : 지정한 달의 들어온 돈의 합계
• 지출 합계 : 지정한 달의 저축을 포함하여 나간 돈의 합계
• 수입 – 지출 : 수입 합계에서 지출 합계를 뺀 금액으로, 이 달의 남은 돈
• 저축 합계 : 지정한 달의 지출 중에 저축에 해당하는 돈의 합계

월간 보고서 시트의 지출 상세 내역과 상기 요약 합계 금액, 월간 대시보드 그래프, 카테고리별 지출 금액 합계 그래프를 메일로 보내기 위해 Apps Script를 열어보겠습니다.

Apps Script 열기

상단 메뉴에서 확장 프로그램 > Apps Script 를 클릭합니다.

Apps Script 메뉴를 클릭하면 새로운 창에서 가계부 템플릿 시트에 연결되어 있는 Apps Script 파일을 확인할 수 있습니다.

Apps Script 실행하기

상단에 드롭다운 메뉴에서 sendmail_monthly_report 를 선택합니다.

실행 버튼을 클릭합니다.

권한 승인하기

Apps Script를 최초로 실행할 때에는 권한을 승인해주어야 합니다. 권한 승인은 처음에 한번만 해주면 됩니다.

승인 필요 팝업창에서 권한 검토 버튼을 클릭합니다.

계정 선택 화면에서 Google 계정을 선택합니다.
확인되지 않은 앱 화면에서 왼쪽 아래의 고급을 클릭합니다.

확인되지 않은 앱 화면에서 아래쪽의 월간 보고서 메일 전송 (으)로 이동(안전하지 않음) 을 클릭합니다.
월간 보고서 메일 전송에서 내 Google 계정에 액세스하려고 합니다 화면에서 허용 버튼을 클릭합니다.

Apps Script가 실행되고, 화면 아래쪽에 실행 로그가 표시되는 것을 확인합니다.

Gmail에 도착한 월간 보고서 메일 확인하기

새 창을 열어 Gmail에 접속하면 [가계부] 0000년 0월 월간 보고서 라는 제목의 메일이 도착해있는 것을 확인할 수 있습니다. 제목에 사용된 연도와 월은 월간 보고서에서 지정한 달의 날짜에서 추출된 데이터입니다. 예시에서는 2024년도 1월달의 데이터였기 때문에 [가계부] 2024년 1월 월간 보고서 라는 제목으로 도착해있습니다.

메일을 열어보면 메일 본문에 월간 보고서 시트의 요약 합계 금액, 월간 대시보드 그래프, 카테고리별 지출 금액 합계 그래프, 지출 상세 내역이 표시되는 것을 확인할 수 있습니다.

가계부 템플릿에서 바로 메일 보내는 방법

해당 기능을 Apps Script화면을 열지 않고 스프레드시트 화면에서 바로 실행할 수 있는 방법이 있습니다.

상단 메뉴에서 확장 프로그램 > 매크로 > 매크로 가져오기 를 클릭합니다.

가져오기 화면에서 sendmail_monthly_report 항목의 함수 추가 버튼을 클릭합니다.

함수 추가 후 확장 프로그램 > 매크로 하위 메뉴에 sendmail_monthly_report 메뉴가 추가된 것을 확인할 수 있습니다.

매크로 테스트를 위해 월간 보고서 시트에서 이전과 다른 달을 선택해보겠습니다. 예시에서는 2024년도 12월달을 선택했습니다.

확장 프로그램 > 매크로 > sendmail_monthly_report 메뉴를 클릭합니다.

Gmail을 열어 월간 보고서 메일이 도착한것을 확인합니다.

메일을 열어보면 메일 본문에 실행 전에 선택한 2024년 12월 월간 보고서가 표시되는 것을 확인할 수 있습니다.

이렇게 매크로 가져오기로 앱스 스크립트 함수를 추가해두면 Apps Script 화면을 열지 않고, 가계부 템플릿 스프레드시트에서 확장 프로그램 > 매크로 하위 메뉴 sendmail_monthly_report 를 이용하여 지정한 달의 월간 보고서 메일을 보낼 수 있습니다.

Apps Script 스프레드시트 데이터를 메일로 보내는 코드

여기서 실행한 Apps Script 코드는 아래와 같습니다. 이 코드는 공유한 가계부 템플릿에 포함되어 있습니다.

function sendmail_monthly_report() {
  
  // 스프레드시트에 접근
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet_monthly_report = sheet.getSheetByName("월간 보고서");

  // 이번 연도, 월
  const thisYear = sheet_monthly_report.getRange('H1').getValue();
  const thisMonth = sheet_monthly_report.getRange('I1').getValue();

  // 메일 본문
  let mailBody = '<h1>가계부 월간 보고서</h1>';
  mailBody += '<p>' + thisYear + "년 " + thisMonth +'월 가계부 월간 보고서를 보내드립니다.</p>';

  // 요약 데이터 범위를 가져옵니다.
  var range = sheet_monthly_report.getRange('I2:J5');
  var data = range.getDisplayValues();
 
  // HTML 테이블을 만듭니다.
  mailBody += '<table border="1px solid gray" style="border-collapse:collapse;" width="300px">';
  
  // 데이터 추가
  for (let i = 0; i < data.length; i++) {
    // 첫째칸에 데이터 없으면 끝내기
    if(data[i][0] == ''){
      break;
    }else{
      mailBody += '<tr>';
      for (let j = 0; j < data[i].length; j++) {

        adata = data[i][j];
        if(j == 1){ // 금액 데이터
          mailBody += '<td style="padding: 4px;text-align: right;"> ' + adata + '</td>';
        }else{
          mailBody += '<td style="padding: 4px;">' + adata + '</td>';
        }

      }
      mailBody += '</tr>';
    }
  }
  mailBody += '</table>';

  // 그래프 가져오기
  const charts = sheet_monthly_report.getCharts(); 
  
  // setup some variables for our email
  const chartBlobs = new Array(); 
  const emailImages = {};
  let chartImage = ""; 
  
  charts.forEach(function(chart, i){
    chartBlobs[i] = chart.getAs("image/png");
    chartImage += "<p><img src='cid:chart"+i+"'></p>"; 
    emailImages["chart"+i] = chartBlobs[i];
  });
  mailBody += chartImage;

  mailBody += '<h2>지출 내역 상세</h2>';

  // 지출 데이터 범위를 가져옵니다.
  range = sheet_monthly_report.getRange('A:E');
  data = range.getDisplayValues();

  // HTML 테이블을 만듭니다.
  mailBody += '<table border="1px solid gray" style="border-collapse:collapse;" width="500px">';
  
  // 헤더 추가
  mailBody += '<tr bgcolor="#F2F2F2">';
  for (let i = 0; i < data[0].length; i++) {
    mailBody += '<th style="padding: 5px;">' + data[0][i] + '</th>';
  }
  mailBody += '</tr>';

  // 데이터 추가
  for (let i = 1; i < data.length; i++) {
    // 첫째칸에 데이터 없으면 끝내기
    if(data[i][0] == ''){
      break;
    }else{
      mailBody += '<tr>';
      for (let j = 0; j < data[i].length; j++) {

        adata = data[i][j];
        if(j == 1){ // 금액 데이터
          mailBody += '<td style="padding: 4px;text-align: right;"> ' + adata + '</td>';
        }else{
          mailBody += '<td style="padding: 4px;">' + adata + '</td>';
        }

      }
      mailBody += '</tr>';
    }
  }
  mailBody += '</table>';

  // 가게부 sheet link 덧붙이기
  const sheetUrl = sheet.getUrl() + "#gid=" + sheet_monthly_report.getSheetId();
  mailBody += '<br><p><a href="' + sheetUrl + '">🔗가계부 Sheet 바로가기</a></p> '
  // 메일 본문 종료

  // 이메일 수신자
  const owner = sheet.getOwner();
  const recipient = owner.getEmail();; // 시트의 소유자
  // const recipient = '<이메일주소>'; // 수신 받고싶은 이메일 주소를 입력하세요

  // 메일 제목, 본문
  const subject = '[가계부] ' + thisYear + "년 " + thisMonth +'월 월간 보고서'; // 메일 제목
  const body = mailBody ;
  
  // 이메일 보내기
  MailApp.sendEmail({
    to: recipient,
    subject: subject,
    htmlBody: body,
    inlineImages:emailImages
  });

  Logger.log("Email sent successfully.");

}

사용시 주의사항

이 앱스 스크립트 코드는 제가 공유한 가계부 템플릿의 월간 보고서 시트에 맞춰져있습니다. 월간 보고서 시트의 이름을 변경하거나, 연도, 월, 요약 데이터, 지출 내역 테이블의 위치나 내용을 변경하시면 실행시 오류가 발생하거나, 메일에 잘못된 데이터가 표시될 수 있습니다.

  var sheet_monthly_report = sheet.getSheetByName("월간 보고서");

  // 이번 연도, 월
  const thisYear = sheet_monthly_report.getRange('H1').getValue();
  const thisMonth = sheet_monthly_report.getRange('I1').getValue();

  // 요약 데이터 범위를 가져옵니다.
  var range = sheet_monthly_report.getRange('I2:J5');

그리고 메일은 가계부 템플릿 스프레드시트의 소유자에게 보내지도록 되어 있습니다.

  const recipient = owner.getEmail();; // 시트의 소유자
  // const recipient = '<이메일주소>'; // 수신 받고싶은 이메일 주소를 입력하세요

만약 가계부 템플릿 스프레드시트의 소유자가 아니라 다른 메일 주소로 보내고 싶다면 아래의 코드로 변경하고 사용하시면 됩니다. <이메일주소> 부분에 수신 받고싶은 이메일 주소를 입력하세요.

// const recipient = owner.getEmail();; // 시트의 소유자
const recipient = '<이메일주소>'; // 수신 받고싶은 이메일 주소를 입력하세요

구글 앱스 스크립트의 다른 활용법을 알고싶으시다면!
» 앱스 스크립트 활용 예시 모음