App Script를 이용한 메일 보내기

학생들에게 숙제 확인을 하거나 많은 사람을 상대할 때 반복적인 이메일 작업을 할 때가 있습니다 이럴 때 간단하게 사용할 수 있는 App Script를 소개합니다. 이내용은 Google의 아래 샘플 코드를 수정한 내용입니다.

https://developers.google.com/apps-script/articles/sending_emails

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = 'EMAIL_SENT';
/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = 2; // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 3);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var message = row[1]; // Second column
    var emailSent = row[2]; // Third column
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
      var subject = 'Sending emails from a Spreadsheet';
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

제목을 추가하기 위해서 다음과 같이 수정했습니다. MailApp class를 이용하여 간단하게 수신자 이메일, 제목, 내용만으로 구성했습니다.

———–

// This constant is written in column C for rows for which an email

// has been sent successfully.

var EMAIL_SENT = ‘EMAIL_SENT’;

/**

* Sends non-duplicate emails with data from the current spreadsheet.

*/

function sendEmails2() {

var sheet = SpreadsheetApp.getActiveSheet();

var startRow = 2; // First row of data to process

var numRows = 2; // Number of rows to process

// Fetch the range of cells A2:B3

var dataRange = sheet.getRange(startRow, 1, numRows, 4);

// Fetch values for each row in the Range.

var data = dataRange.getValues();

for (var i = 0; i < data.length; ++i) {

var row = data[i];

var emailAddress = row[0]; // First column

var subject = row[1];

var message = row[2];

var emailSent = row[3];

if (emailSent != EMAIL_SENT) { // Prevents sending duplicates

MailApp.sendEmail(emailAddress, subject, message);

sheet.getRange(startRow + i, 4).setValue(EMAIL_SENT);

// Make sure the cell is updated right away in case the script is interrupted

SpreadsheetApp.flush();

}

}

}

———————-

끝