[챗GPT+앱스스크립트 자동화] 한꺼번에 이메일 100개 보내기

메일링 자동화를 하기 전에 자신이 처한 문제 상황을 명확하게 분석해야 제대로 작업을 진행할 수 있습니다. 하나씩 정리해봅시다.

[분석 01] 메일을 100개씩 보내야 한다

지금 여러분은 메일을 100개씩 보내야 합니다. 정기적으로 한다면 횟수는 더 많아질 것입니다. 그런데 사람이 같은 일을 반복하기는 쉽지 않죠. 이런 반복 업무는 컴퓨터와 코드를 이용하여 해결하는 것이 좋습니다.

[분석 02] 구글 스프레드시트에서 값을 ‘복붙’하고 있다

어느 정도 업무를 편안하게 하기 위한 방법으로 구글 스프레드시트를 사용한다고 해봅시다. 구글 스프레드시트에 자신이 이메일에 보내야 하는 값을 정리하는 것입니다. 하지만 이렇게 해도 문제는 여전히 남아 있습니다. 이 값들을 마우스로 하나씩 클릭하고, 복붙하고 있는 것이죠. 이렇게 작업을 하면 반드시 실수가 생깁니다. 3열의 A값을 복사해야 하는데 잘못 클릭하여 B값을 복사할 수도 있고, 아니면 4열을 복사할 수도 있죠. 지금은 정보가 100개지만 고객이 늘어나 정보가 1,000개가 되면 실수는 더 많아질 것입니다. 만약 컴퓨터가 구글 스프레드시트의 값을 순서대로 하나씩 읽어 메일로 복붙하게 만들 수 있다면 이런 실수는 일어나지 않을 것입니다. 그래서 우리는 구글 스프레드시트와 구글 앱스 스크립트를 연동하여 문제를 해결할 것입니다.

[분석 03] 템플릿을 ‘복붙’하고 있다

이것도 아주 사소하지만 피곤한 문제입니다. 템플릿은 다음과 같습니다.

전체 템플릿을 복사하는 것도 일이지만 중간에 있는 값을 마우스로 드래그하여 선택하는 것도 일입니다. 이 문제를 해결하는 방법도 구글 앱스 스크립트입니다.

자 이제 문제가 다 정리되었습니다. 지금부터 구글 앱스 스크립트로 구글 스프레드시트에 있는 값들을 읽어 자동으로 메일을 발송해볼까요?

이메일에 쓸 데이터 가져오기

챗GPT로 만든 스프레드시트 파일에 구글 앱스 스크립트를 붙여 문제를 해결하는 과정을 알아봅시다.

01 스프레드시트의 상단 메뉴에서 [확장 프로그램 → Apps Script]를 선택합니다.

02 그러면 새 탭에 앱스 스크립트 프로젝트가 만들어집니다.

03 다음 코드를 작성해봅시다. 이렇게 코드를 작성하면 myFunction( ) 함수를 실행했을 때 ‘현재 실행 중인 스프레드시트 앱의 활성화된 시트’를 코드로 가져옵니다.

 바로 자동화 코드: http://bit.ly/3Q431Pd

이 Chapter의 최종 코드입니다! 실습을 진행하면서 헷갈릴 때 참고하세요!

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
}

이 코드의 뜻을 하나씩 뜯어보면 다음과 같습니다.

• SpreadsheetApp.getActiveSpreadsheet( );

→ 현재 만든 앱스 스크립트와 연결된 스프레드시트를 코드로 가져와라!

• const spreadSheet =

→ 가져온 무언가를 spreadSheet라는 변수에 저장하라!

이 상태를 그림으로 보면 다음과 같습니다.

방금 여러분이 스프레드시트에서 생성한 앱스 스크립트를 spreadSheet라는 변수에 저장한 것입니다. 그러면 이때부터 spreadSheet라는 변수를 이용하여 스프레드시트를 이용한 작업들을 코드로 할 수 있게 됩니다.

04 코드로 스프레드시트의 시트를 제대로 가져왔는지 확인해볼까요? 다음 코드를 실행하여 스프레드시트의 이름을 출력해보겠습니다.

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  Logger.log(spreadSheet.getName());
}

• spreadSheet.getName()

→ 스프레드시트의 이름을 출력합니다

결과를 보면 스프레드시트의 파일 이름을 그대로 출력했습니다. 코드에서 스프레드시트를 알고 있는 것입니다! 코드와 스프레드시트, 출력 로그를 연관지어 설명하면 다음과 같습니다.

이렇게 코드로 작업을 지시하려면 아주 잘게 쪼갠 단위로 생각해야 합니다. 이 과정에 익숙해지려면 시간이 좀 걸릴 겁니다.

스프레드시트에 있는 값 읽어오기

이번엔 다른 작업을 해봅시다. 이번에 할 작업은 스프레드시트의 제목만 가져오는 것이 아니라 시트의 셀 값을 가져오는 것입니다.

01 다음 코드를 입력하면 list 시트의 특정 범위의 셀 값을 읽어 가져올 수 있습니다.

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const listSheet = spreadSheet.getSheetByName("list"); // ➊ list 시트를 가져옴
  Logger.log(listSheet.getRange("A2:D5").getValues()); // ➋ 가져온 시트에서 A2:D5의 값을 읽어옴
}

[[최민준, 70184.0, 골든래빗, canine8989@gmail.com], [강하윤, 72755.0, 골든래빗, canine8989@gmail.com], [최서연, 43439.0, 골든래빗, canine8989@gmail.com], [강민준, 49791.0, 골든래빗, canine8989@gmail.com]]

결과를 보면 해당 범위의 값을 가져와 출력한 것을 알 수 있습니다. 코드의 구체적인 설명은 다음과 같습니다. 그림과 함께 맞춰 보면서 이해해보세요. ➊과 ➋를 그림으로 표현하자면 다음과 같습니다.

출력 결과의 형태에서 대괄호가 눈에 띕니다. 출력 결과를 보기 좋게 나열하면 다음과 같습니다.

[
[최민준, 70184.0, 골든래빗, canine8989@gmail.com],
[강하윤, 72755.0, 골든래빗, canine8989@gmail.com],
[최서연, 43439.0, 골든래빗, canine8989@gmail.com],
[강민준, 49791.0, 골든래빗, canine8989@gmail.com]
]

이 대괄호로 출력되는 결과값의 형태는 프로그래밍 개념 중 배열이라는 것을 표현하기 위한 것입니다. 대괄호가 1겹이면 1차원 배열, 2겹이면 2차원 배열이라고 부릅니다. 지금은 대괄호가 2겹이므로 2차원 배열입니다. 보기 좋게 나열한 배열의 형태를 보면 스프레드시트와 크게 다르지 않습니다. 앞으로 여러분이 스프레드시트에서 데이터를 가져오면 앱스 스크립트는 그 결과를 2차원 배열로 보여줄 것입니다. 앞으로 스프레드시트 데이터를 자주 다루게 될 것이므로 2차원 배열에 대해서 간단히 설명하고 넘어가겠습니다. 1겹의 대괄호는 스프레드시트의 ‘행’과 비슷한 개념입니다.

[최민준, 70184.0, 골든래빗, canine8989@gmail.com]이 스프레드시트의 행입니다.
이 행들을 묶어 다시 대괄호로 감싸면 2차원 배열이 됩니다.

02 이제 template 시트에 있는 값도 출력해봅시다. 다음 코드를 입력하고 저장한 다음 실행하세요. 코드를 저장하지 않으면 작성한 코드로 실행되지 않으니 반드시 저장하고 실행하세요.

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();

  // ➊ list 시트와 template 시트를 가져옴
  const list = spreadSheet.getSheetByName("list");
  const template = spreadSheet.getSheetByName("template");

  // ➋ 각 시트의 필요한 데이터를 가져옴
  const data = list.getRange('A2:D5').getValues();
  const templateData = template.getRange('A2:B2').getValues();

  // ➌ 가져온 데이터 출력
  Logger.log(data);
  Logger.log(templateData);
}

[[최민준, 70184.0, 골든래빗, canine8989@gmail.com],
[강하윤, 72755.0, 골든래빗, canine8989@gmail.com],
[최서연, 43439.0, 골든래빗, canine8989@gmail.com],
[강민준, 49791.0, 골든래빗, canine8989@gmail.com]]
실행 결과
[[여기에 제목이 들어갑니다., 안녕하세요 <name>님.
이번 달 청구 금액 안내드립니다.
청구 금액 : <billing>
감사합니다.]]

스프레드시트에서 대량으로 이메일 데이터 읽어오기

이제 마지막 단계입니다! 데이터를 가져왔으니 이제 이 데이터를 이용해서 메일을 보내봅시다. 그 전에 아직 2차원 배열에서 풀어야 할 숙제가 남아 있습니다. 바로 대괄호에서 어떻게 값을 빼낼 것이냐에 대한 숙제죠. 실습을 통해 2차원 배열에서 값을 꺼내는 방법을 알아보고 메일을 보내보겠습니다.

2차원 배열에서 값을 꺼내기

배열에서 값을 꺼내려면 배열명[숫자][숫자]와 같이 배열명과 대괄호, 숫자를 사용해야 합니다. 프로그래밍 세계에서는 순서를 0부터 부여하므로 이 점에 유의합시다. 앞에서 출력한 A2:D5의 데이터를 다시 보면서 코드를 작성해봅시다.

[
[최민준, 70184.0, 골든래빗, canine8989@gmail.com],
[강하윤, 72755.0, 골든래빗, canine8989@gmail.com],
[최서연, 43439.0, 골든래빗, canine8989@gmail.com],
[강민준, 49791.0, 골든래빗, canine8989@gmail.com]
]

01 만약 최서연을 꺼내고 싶다면 배열명[2][0]이라고 입력해야 합니다. 정말로 그런지 코드를 통해 확인해보겠습니다.

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const list = spreadSheet.getSheetByName("list");
  const data = list.getRange('A2:D5').getValues();
  Logger.log(data[2][0]);
}

최서연

결과를 보면 최서연을 잘 꺼내왔습니다. 2번째 긴 배열에서 0번째 값을 꺼내온 것입니다. 별로 어렵지 않죠?

02 이 원리를 이용해서 template 시트에 있는 메일 내용도 꺼내오겠습니다.

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const template = spreadSheet.getSheetByName("template");
  const templateData = template.getRange('A2:B2').getValues();
  
  // ➊ templateData가 가진 0번째 행 데이터 줄을 templateRow에 저장
  const templateRow = templateData[0];
  Logger.log(templateRow);

  // ➋ templateRow에 있는 0번째 데이터를 subject에 저장
  const subject = templateRow[0];

  // ➌ templateRow에 있는 1번째 데이터를 body에 저장
  const body = templateRow[1];
  Logger.log("제목 : " + subject);
  Logger.log("내용 : " + body);
}

[여기에 제목이 들어갑니다., 안녕하세요 <name>님.
이번 달 청구 금액 안내드립니다.
청구 금액 : <billing>
감사합니다.]
제목 : 여기에 제목이 들어갑니다.
내용 : 안녕하세요 <name>님.
이번 달 청구 금액 안내드립니다.
Chapter 06 한꺼번에 이메일 100개 보내기 49
청구 금액 : <billing>
감사합니다.

➊ templateRow에 templateData[0]로 2차원 배열에서 첫 번째 배열 요소를 가져와 저장합니다.

➋ templateRow에서 첫 번째 배열 요소를 가져와 저장합니다.

➌ templateRow에서 두 번째 배열 요소를 가져와 저장합니다.

이제 list, template 시트에서 원하는 데이터를 가져와 변수에 담을 수 있는 방법을 알았습니다. 이 변수를 이용해서 메일을 보내면 될 것 같습니다.

대량으로 이메일 보내기

메일을 보내기 전에 한 번 생각해봅시다. 우리에게는 100명의 고객 정보가 있으므로 ‘반복해서 메일을 보낸다’라는 작업을 수행할 수 있어야 합니다. 우리가 100번의 복사, 붙여넣기, 메일 보내기를 하는 행위를 대신할 코드가 필요한 것이죠. 바로 그것이 반복문 문법입니다. 앱스 스크립트에 반복문 문법은 여러 가지가 있습니다만 지금은 for문만 공부하겠습니다. for문의 기본 형태는 다음과 같습니다.

for ([초기값]; [반복문을 지속할 조건]; [반복 작업 코드를 실행하고 나서 초기값 변화]) {
  // 반복 작업 코드를 나열
  // 반복 작업 코드를 나열
  // 반복 작업 코드를 나열
}

기본 형태만 보고서 ‘아, 반복문은 이런 것이구나!’라는 감이 잘 오지 않을 것입니다. 실제 코드를 실행하며 반복문이 무엇인지 알아봅시다.

01 다음 코드는 list 시트에서 고객의 이름과 청구 대금을 하나씩 반복하여 출력하는 코드입니다.

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();

  // ➊ list 시트의 모든 데이터(A2:B101) 가져오기
  const customerListSheet = spreadSheet.getSheetByName("list");
  const customerDatas = customerListSheet.getRange('A2:B101').getValues();

  // ➋ i를 0부터 시작하여 1씩 늘리면서 customerDatas의 길이(100)만큼 반복하기
  for (let i = 0; i < customerDatas.length; i++) {
  console.log(customerDatas[i]); // ➌ i는 0부터 순서대로 0, 1, 2, ... 99
  }
}

[ '최민준', 70184 ]
실행 결과
...생략...
[ '조하윤', 49982 ]
[ '윤민준', 84960 ]

➊ list 시트에서 A2:B101 범위의 데이터를 모두 가져와 2차원 배열을 customerDatas에 담습니다.

➋ 반복문입니다. i를 0부터 시작하여 customerDatas.length가 되기 전까지(미만) i를 1씩 늘려가며 중괄호 내용을 반복합니다. 이때 customerDatas 길이는 2행부터 101행까지이므로 길이는 100입니다. ➌ 그러면 i는 0, 1, 2, 3, …, 99까지 순서대로 늘어나고 이 때마다 1번씩 중괄호의 내용을 반복할 것입니다. 그림으로 설명하면 다음과 같습니다.

① 우선 반복문을 수행하기 전에 i의 초기값을 0으로 지정합니다.

② 그런 다음 반복문의 조건에 맞는지 확인합니다. 초기값은 0이고 customersDatas.length는 100이므로 조건에 맞습니다.

③ 반복문을 수행합니다. customerDatas[0]에 해당하는 [ ‘최민준’, 70184 ]을 출력합니다.

④ 반복문 ③의 수행이 끝났고, i를 1만큼 늘립니다. i는 1이 되었습니다. 다시 조건을 확인하는 ②단계로 돌아갑니다.

이 과정을 반복하여 ②의 조건이 어긋나면 반복문이 끝나는 것입니다. i가 100이 되면 ③을 수행하지 못하고 반복문은 종료되겠네요. 그러니 i는 0부터 99까지 증가하고 customerDatas[0]부터 customerDatas[99]까지 출력되어 모든 행의 데이터를 출력할 수 있는 것입니다.

02 이제 100회의 반복을 할 수 있게 되었습니다. 그러면 template 시트에 있는 메일 내용을 조합하여 메일 문구도 만들어볼 수 있겠네요. 다음 코드를 이용하여 로그에 100번의 메일 제목, 메일 내용을 출력해봅시다.

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  // ➊ list 시트의 모든 데이터(A2:B101) 가져오기
  const customerListSheet = spreadSheet.getSheetByName("list");
  const customerDatas = customerListSheet.getRange("A2:B101").getValues();

  // ➋ template 시트의 메일 내용 가져오기
  const mailTemplateSheet = spreadSheet.getSheetByName("template");
  const templateDatas = mailTemplateSheet.getRange("A2:B2").getValues();
  const mailTitle = templateDatas[0][0]; // 메일 제목
  const mailContent = templateDatas[0][1]; // 메일 내용

  // ➌ customerDatas의 길이(100)만큼 반복하기
  for (let i = 0; i < customerDatas.length; i++) {
    const customerName = customerDatas[i][0];
    const customerBilling = customerDatas[i][1];
    const modifiedContent = mailContent.replace("<name>", customerName).replace("<billing>", customerBilling);
    Logger.log(mailTitle);
    Logger.log(modifiedContent);
  }
}

여기에 제목이 들어갑니다.
안녕하세요 최민준님.
이번 달 청구 금액 안내드립니다.
청구 금액 : 70184
감사합니다.
...생략...

➊ list 시트의 모든 데이터를 가져와 customerDatas에 저장합니다.

➋ templates 시트의 메일 제목과 메일 내용을 가져와 mailTitle, mailContent에 저장합니다.

➌ 100회 반복하면서 ➊~➋에 담은 값을 적절히 사용하여 replace( ) 함수를 이용해 template의 B2에 있던 값에서 <name>과 <billing>의 값을 list 시트의 고객 이름 customerName과 청구 대금 customerBilling으로 교체합니다. 그림으로 보면 다음과 같습니다.

replace( ) 함수는 2개의 인수를 받아 왼쪽 인수의 값을 오른쪽 인수의 값으로 바꾸는 역할을 합니다. replace(“<name>”, customerName)은 메일 템플릿에서 <name>을 찾아 customerName으로 바꾸는 것이지요. 이렇게 100개의 메일 제목과 메일 내용을 준비하여 출력해보았습니다. 이제 이 출력 부분을 메일 보내기로 바꾸기만 하면 끝입니다.

03 앱스 스크립트에서 이메일을 보낼 때는 MailApp 클래스의 sendEmail( ) 메서드를 활용하면 됩니다.

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  // list 시트의 모든 데이터(A2:D101) 가져오기
  const customerListSheet = spreadSheet.getSheetByName("list");
  const customerDatas = customerListSheet.getRange("A2:D101").getValues();

  // template 시트의 메일 내용 가져오기
  const mailTemplateSheet = spreadSheet.getSheetByName("template");
  const templateDatas = mailTemplateSheet.getRange("A2:B2").getValues();
  const mailTitle = templateDatas[0][0]; // 메일 제목
  const mailContent = templateDatas[0][1]; // 메일 내용

  // customerDatas의 길이(100)만큼 반복하기
  for (let i = 0; i < customerDatas.length; i++) {
    const customerName = customerDatas[i][0]; // list 시트의 고객 이름
    const customerBilling = customerDatas[i][1]; // list 시트의 청구 대금
    const customerEmail = customerDatas[i][3]; // list 시트의 메일 주소
    const modifiedContent = mailContent.replace("<name>", customerName).replace("<billing>", customerBilling);

    // ➊ to는 받을 메일 주소, subject는 메일 제목, htmlBody는 메일 내용
    MailApp.sendEmail({to : customerEmail, subject : mailTitle, htmlBody:modifiedContent});
  }
}

➊ 다음과 같은 구조로 메일 쓰기에 필요한 정보를 담아 sendEmail( ) 함수에 전달하면 메일을 보냅니다.

{
to : 받을 메일 주소,
subject : 메일 제목,
htmlBody : 메일 내용,
}

이 코드를 실행하면 총 100개의 메일을 D열의 메일 주소로 보냅니다. 지금 D열의 메일 주소가 자기 자신의 메일 주소로 지정했는지 확인한 후 메일을 보내보세요.\

대량으로 이메일 보내기에 성공했습니다!

04 하지만 아마도 오류 메시지가 뜨면서 프로그램이 종료되고, 결과도 100건의 메일을 보내진 못했을 겁니다. 이런 오류가 발생한 이유는 무엇일까요? 이럴 때 챗GPT를 사용해보면 좋습니다. 오류 메시지를 그대로 복사하여 챗GPT에게 물어봅시다.

Exception: Service invoked too many times for one day: email.

위와 같은 오류가 발생한 이유가 뭐야?

오류 메시지 Exception: Service invoked too many times for one day: email는 Google AppsScript나 Google Workspace API를 사용할 때 특정 서비스(이 경우 이메일 서비스)가 하루 사용 한도를 초과했음을 의미합니다. Google에서는 각 API나 서비스에 대해 일일 할당량을 설정하여 과도한 사용을 방지하는데, 이 오류는 해당 한도를 초과했을 때 발생합니다.

이 오류가 발생하는 일반적인 원인은 다음과 같습니다:

- 이메일 발송 한도 초과: Google은 계정당 하루에 보낼 수 있는 이메일의 수에 제한을 두고 있습니다. 일반 계정은 대략 하루 100개, Google Workspace 계정(기업용)은 약 1,500개 정도로 제한될 수 있습니다.
- 자동화된 스크립트 반복 실행: 스크립트가 주기적으로 실행되어 이메일을 반복적으로 보내는 경우, 하루 할당량을 빠르게 소진할 수 있습니다.
- 테스트 중 과도한 호출: 테스트 중에 이메일 서비스를 반복 호출하는 경우에도 할당량이 초과될 수 있습니다.

이유를 자세히 보니 하이라이트 내용이 핵심인 것 같네요. 메일을 너무 자주 보내니 오류 메시지가 나온 것입니다. 여러분의 계정은 구글 일반 계정일 것이므로 하루 100개 보내는 것도 조절해야 하겠네요.

05 이것을 해결할 방법도 챗GPT에게 물어봅시다.

내 코드는 이렇게 생겼는데, 이 오류 메시지를 없애려면 코드를 어떻게 개선해야 해?

function myFunction() {
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
// list 시트의 모든 데이터(A2:B101) 가져오기
const customerListSheet = spreadSheet.getSheetByName("list");
const customerDatas = customerListSheet.getRange("A2:D101").getValues();
...{여기에 여러분의 코드를 복사하여 붙여넣으세요}...

이 코드는 MailApp.sendEmail을 100번 호출하고 있어, 일일 할당량을 초과할 가능성이 큽니다. 개선을 위해서는 이메일 전송을 효율적으로 제한하거나, 시간을 두고 전송하는 방법을 고려해야 합니다. 다음은 이 문제를 해결할 수 있는 몇 가지 코드 수정 방안입니다.

챗GPT에게 물어보니 코드 대안을 주었습니다. 저는 이렇게 코드를 고치라고 하네요.

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // list 시트의 모든 데이터(A2:B101) 가져오기
  const customerListSheet = spreadSheet.getSheetByName("list");
  const customerDatas = customerListSheet.getRange("A2:D101").getValues();

  // template 시트의 메일 내용 가져오기
  const mailTemplateSheet = spreadSheet.getSheetByName("template");
  const templateDatas = mailTemplateSheet.getRange("A2:B2").getValues();
  const mailTitle = templateDatas[0][0]; // 메일 제목
  const mailContent = templateDatas[0][1]; // 메일 내용

  // customerDatas의 길이(100)에서 50을 빼고 적게 메일 발송
  for (let i = 0; i < customerDatas.length - 50; i++) {
    const customerName = customerDatas[i][0];
    const customerBilling = customerDatas[i][1];
    const customerEmail = customerDatas[i][3];
    const modifiedContent = mailContent.replace("<name>", customerName).replace("<billing>", customerBilling);
    MailApp.sendEmail({to: customerEmail, subject: mailTitle, htmlBody:modifiedContent});
    Utilities.sleep(1000); // 1초 대기
  }
}

하이라이트한 부분만 코드를 추가하여 100개의 메일을 보내는 것에서 50개를 뺐고, 1초정도 대기하고 다시 메일을 보내도록 코드를 수정해주었습니다. 아마 코드를 실행하면 오류 없이 메일이 잘 발송될 것입니다.