[구글 앱스크립트] 구글 스프레드시트 보고서 이메일 자동 발송

수행 내용 및 목표 + 넋두리?

 

회사의 해외 영업 부에서 작성하는 해외 업체 거래 내역의 업체별 미수금 내역을 경영지원팀에 자동으로 보내는 기능을 만들어서 실행해본다.

---

아래는 해당 프로젝트를 수행하게 된 전반적인 이유(긴글)

※ tmi 긴 하지만, 원래 해외 영업 부서에서 (라고 하기 보다는 나 혼자서) 엑셀로 한 해 동안의 매출 내역을 작성하고,

경영 지원팀에서 자금 계획을 원활히 세울 수 있도록 미수금 내역을 매일 매일 보내주고 있었다.하지만, 팀원이 증가하며 어느샌가 모든 거래 내역을 내가 파악을 하기가 어려워졌고(이를테면, 내가 담당자가 아닌 업체 중 특정 주문이 몇월 며칠에 출고 되었고, 잔량 주문이 얼마인지 등)이를 팀원 모두가 본인 담당 업체 내역은 스스로가 작성할 수 있게 구글 스프레드 시트로 만들어 사용해야 겠다고 생각했다.

 

회사에서 해당 시스템이나 프로그램 도입을 해주지 않기 때문에, 궁여지책으로 구글 스프레드 시트를 활용해 공유 시트를 만들어 전 직원들이 회사의 주문 내역, 현재 생산 진행 상황 등을 파악할 수 있도록 만들어서 사용한 지 꽤 됐었다.(모든 제조 중소기업들이 그런가.... 경영진들은 회사 시스템 도입에 대해 회의적이고, 생산직 직원들은 제품 제조에만 신경쓰려고 하고, 일련의 제조 활동을 기록으로 남기는 것에 대해 참여율이 높지 않다.)

 

아무튼, 나는 기업을 운영하는 데 데이터를 기록하는 것이 정말 중요하다고 생각하는 사람이다.데이터를 통해 과거를 돌아보고, 현재를 파악하고, 미래를 예측할 수 있다고 믿고, 또 실제로 내가 기록해둔 데이터를 통해 신제품을 개발하거나, 적정 재고 생산 등에 활용하고 있다.

며칠 전 몇 시간에 걸쳐 나 혼자 작성하고 보유하고 있던 해외 거래 내역 파일을 구글 스프레드 시트에 옮겨 넣으며 여러 가지 기능을 보완하여 팀원들에게 공유하였고, 생산 책임자분께 어떤 주문이 오래 되었는지, 어떤 주문이 급한지 등을 파악해 생산 계획을 세우는 데 활용하라고 공유하였다.

 

또한, 경영지원팀과 경영진에도 해당 시트에 업체 별 주문 내역, 출고 내역, 입금 내역, 미수금, 미출고 잔량 등의 내용이 들어가 있고, 만약 생산이 완료된 업체에는 몇월 며칠에 송금 요청을 했는지 알 수 있도록 하여 자금 계획을 세우는 데 참고할 수 있게끔 하였다.

그러나.. 문제는, 경영진이 시트를 안 보겠다고, 예전처럼 엑셀 파일을 매일 매일 보내달라는 요청을 하셨다. 나이가 많아서 구글 스프레드시트를 들어가기 싫어서 그럴 수도 있겠다라고 이해가 되기도 하나, 회사의 운영 내역이 상세히 담겨있는 자료를 귀찮다는 이유로 보기 싫다는 게 말이 안 된다고 생각했다.물론, 여느 회사나 경영진께 보고할 때는 최대한 핵심만 정리하여 깔끔하고 간단한 내용만 담아 전달하는 게 맞긴 하다는 생각도 든다. 그러나, 이제는 해외 영업 운영 내역을 더 이상 엑셀로 작성하지를 않는데, 그렇다고 매일 매일 해당 내용만 엑셀로 작성하여 따로 드리거나, 해당 서식 내용을 캡처하여 드리는 것도 영 아니라고 생각했다.

---

구글 스프레드 시트 내용 이메일로 보내기

 

우선, 이메일로 보내고자 하는 해외 업체 거래 내역은 대충 이렇게 생겼다.

시트에 각 업체 별로 거래 상세 내역이 담긴 탭이 있고, 이 탭에는 함수를 걸어놔서 각 업체 별로 주문, 입금, 미수금, 미출고 금액을 파악할 수 있게 해뒀다.

 

이 내용 중 주요 업체부터 미출고 액 컬럼의 내용들을 이메일로 보내려고 한다.

상단의 '확장 프로그램 - Apps Script' 을 이용하면 된다.

 

※ 구글 Apps Script란?

 

자세한 내용은 하단의 링크 참고.

https://workspace.google.com/intl/ko/products/apps-script/

Google Apps Script: Google Workspace 자동화, 통합, 확장

---

구글 Apps Script 코드 짜기 (이메일 발송) with Chat GPT

 

구글 Apps Scirpt는 자바스크립트 기반으로, 자바스크립트 언어에 능숙한 사람이라면 쉽게 코드를 작성할 수 있다고 한다.

그러나, 나는 프로그래밍 언어 초보 이기 때문에 Chat GPT를 활용하여 코드를 짜보려고 한다.

 

우선, 전체코드는 이러하다.

function sendEmailWithFormattedRangeAndMultipleRecipients() {
  // 활성 스프레드시트와 시트를 가져옵니다.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("전체");
  const range = sheet.getRange("B5:G49"); // 범위 설정
  const values = range.getValues();

  // 각 셀 값을 소수점 2자리까지 float 형으로 변환
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      let cellValue = parseFloat(values[i][j]);
      if (!isNaN(cellValue)) {
        values[i][j] = cellValue.toFixed(2);
      }
    }
  }

  // 변환된 값을 범위에 다시 설정
  range.setValues(values);

  // 소수점 2자리 형식 적용
  range.setNumberFormat("#,##0.00");

  // HTML 테이블 생성
  let tableHtml = '<table border="1" cellspacing="0" cellpadding="5">';
  for (let i = 0; i < values.length; i++) {
    tableHtml += '<tr>';
    for (let j = 0; j < values[i].length; j++) {
      tableHtml += `<td>${values[i][j]}</td>`;
    }
    tableHtml += '</tr>';
  }
  tableHtml += '</table>';

  // 날짜 넣기
  const today = new Date()
  const formattedDate = Utilities.formatDate(today, Session.getScriptTimeZone(), "yyyy-MM-dd");

  // 이메일 주소 배열
  const emailAddresses = ["메일주소1", "메일주소2", "메일주소3", "메일주소4"];
  
  // 배열을 콤마로 구분된 문자열로 변환
  const recipients = emailAddresses.join(',');

  // 이메일 보내기
  MailApp.sendEmail({
    to: recipients,
    subject: "회사명_해외 업체 거래 내역 [2024년]",
    htmlBody: `안녕하세요,<br><br>아래는 해외 업체별 거래 내역 데이터입니다:<br><br>작성일자:${today}<br><br>${tableHtml}<br><br>감사합니다.`
  });

  Logger.log('Email sent with formatted data.');
}

---

코드 상세 설명

 

주요 코드 별로 내용을 설명하자면,

 

1. 스프레드시트와 시트 가져오기

- SpreadsheetApp.getActiveSpreadsheet().getSheetByName("전체"):  현재 활성화된 스프레드시트에서 이름이 "전체"인 시트를 가져옴.
- sheet.getRange("B5:G49"): 시트에서 B5부터 G49까지의 범위를 설정.
- range.getValues(): 설정한 범위 내의 모든 셀 값을 2차원 배열 형태로 가져옴.

 

2. 셀 값을 소수점 2자리까지 float 형으로 변환

- 중첩된 for 루프를 사용하여 모든 셀 값을 순회.
- parseFloat을 사용해 셀 값을 float 형으로 변환.
- isNaN을 통해 변환된 값이 숫자인지 확인하고, 숫자일 경우 toFixed(2)를 사용하여 소수점 2자리까지 반올림.

 

3. 변환된 값을 범위에 다시 설정하고 형식 지정

- range.setValues(values): 변환된 값을 다시 범위에 설정.
- range.setNumberFormat("#,##0.00"): 해당 범위의 셀에 천 단위 콤마 표시 + 소수점 2자리 형식을 적용.

4. HTML 테이블 생성

- HTML 테이블을 생성하여 각 셀 값을 테이블 형태로 변환.
- <table>, <tr>, <td> 태그를 사용하여 HTML 구조를 만듦.

 

5. 이메일 주소 배열 설정 및 문자열 변환

- 이메일 주소를 배열 형태로 설정.
- join(',')을 사용해 배열을 콤마로 구분된 문자열로 변환.

 

6. 이메일 보내기

- MailApp.sendEmail을 사용하여 이메일 발송.
- to 필드에 수신자 목록을 넣고, subject와 htmlBody 필드를 통해 이메일 제목과 내용을 설정.

 

7. 로그 남기기

Logger.log를 사용해 이메일 발송을 기록.

 

※ 추가 (작성일자 넣기)

- const today = new Date()
- const formattedDate = Utilities.formatDate(today, Session.getScriptTimeZone(), "yyyy-MM-dd");

 

메일이 보내지는 시간을 넣는 코드를 삽입하고,

  // 이메일 보내기
  MailApp.sendEmail({
    to: recipients,
    subject: "회사명_해외 업체 거래 내역 [2024년]",
    htmlBody: `안녕하세요,<br><br>아래는 해외 업체별 거래 내역 데이터입니다:<br><br>작성일자:${today}<br><br>${tableHtml}<br><br>감사합니다.`
  });

 

htmlBody 내역에 작성일자도 표시될 수 있도록 코드를 변경해주었다.

시간대 포맷이 원하는 대로 안 나오긴 했지만, 어쨌든 날짜가 나오니 추후에 수정해야겠다. (오전 내내 지쳤음...)

 

★ 날짜 형식 바꾸기 (스크립트 수정)

  // 이메일 보내기
  MailApp.sendEmail({
    to: recipients,
    subject: "MK Archery_해외 업체 거래 내역 [2024년]",
    htmlBody: `안녕하세요,<br><br>아래는 해외 업체별 거래 내역 데이터입니다:<br><br>작성일자:${formattedDate}<br><br>${tableHtml}<br><br>감사합니다.`
  });

기존 스크립트에서는  '작성일자:${today}' 로 걸어서 날짜 형식이 바뀌지 않고, 그대로 발송되었는데,

참조를 "yyyy-MM-dd" 형으로 바꾼 'formattedData'로 변경하니, 날짜 형식이 지정한 대로 잘 발송되었다.

 

---

작동 잘 되는지 확인

 

코드를 입력 후 Apps Script 에서 저장 버튼(실행 버튼 왼쪽)을 클릭 후, 실행 버튼을 누르면 하단에 실행 로그 내역이 나온다.

그리고 메일 수신자로 설정해둔 메일로 접속해보면,

표가 좀 투박하긴 하지만, 메일로 표 형태의 내용이 잘 발송되었다.

다른 수신인으로 지정한 팀원에게도 메일 수신 여부를 물어보니 잘 받았다고 하였다.

---

구글 앱스 스크립트 트리거

 

그렇다면, 경영지원팀과 경영진에게 해당 내용이 특정 시간에 발송될 수 있도록 하는 방법은 없을까?

찾아보니, 구글 Apps Script 에는 '트리거(Trigger)' 라는 기능이 있어 해당 기능을 이용해 특정 시간에 스크립트를 작동시키게 할 수 있다.

왼쪽의 네 번째 버튼(파란색 음영)을 클릭하면 트리거 기능을 설정 및 이용할 수 있다.

현재 해당 트리거에 아무 것도 없기 때문에, '새 트리거를 만듭니다.' 를 클릭하여 새로운 트리거를 생성해야 한다.

새 트리거를 추가하면 된다.

나같은 경우에는 매일 퇴근 전 (5시 50분에서 6시 사이)에 경영팀에 해당 내용을 보냈었으니,

일 단위 타이머 / 오후 5시~오후6시 사이를 선택 후 저장을 하면 된다.

이렇듯 각자의 상황에 맞게 설정하면 된다.

---

문제점 확인

 

오후 5시 23분에 메일은 잘 왔다.

그러나 문제가 발생했다.

앞서 전체 코드 중 데이터들의 데이터 형식을 맞춰주기 위해 넣은 스크립트로 인해

구글 시트에 걸어둔 함수가 다 풀려버렸다.

따라서, 소수점 2자리로 만들어주는 스크립트는 삭제하였다.

  // 각 셀 값을 소수점 2자리까지 float 형으로 변환
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      let cellValue = parseFloat(values[i][j]);
      if (!isNaN(cellValue)) {
        values[i][j] = cellValue.toFixed(2);
      }
    }
  }

  // 변환된 값을 범위에 다시 설정
  range.setValues(values);

  // 소수점 2자리 형식 적용
  range.setNumberFormat("#,##0.00");

 

내일 다시 테스트 해보고, 데이터 형이 통일되지 않는 등의 문제를 찾아서 해결해봐야 할 거 같다. (우선은 퇴근해야 하니까..^^)

---