학생들에게 숙제 확인을 하거나 많은 사람을 상대할 때 반복적인 이메일 작업을 할 때가 있습니다 이럴 때 간단하게 사용할 수 있는 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();
}
}
}
----------------------
끝
