スプレッドシートの一覧にメールを送信する-その2-

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SETTING');
  var dataRange = sheet.getRange('E3:G' + sheet.getLastRow()).getValues();
  
  var emailSettings = {
    subject: sheet.getRange('B2').getValue(),
    fromName: sheet.getRange('B3').getValue(),
    staffName: sheet.getRange('B4').getValue(),
    fromEmail: sheet.getRange('B5').getValue(),
    replyTo: sheet.getRange('B6').getValue(),
    cc: sheet.getRange('B7').getValue(),
    bcc: sheet.getRange('B8').getValue(),
    bodyTemplate: sheet.getRange('A16').getValue().replace(/\n/g, '<br>'),
    A14: {
      category: sheet.getRange('A12').getValue(),
      deadline: sheet.getRange('B12').getValue(),
      url: sheet.getRange('C12').getValue()
    },
    A15: {
      category: sheet.getRange('A13').getValue(),
      deadline: sheet.getRange('B13').getValue(),
      url: sheet.getRange('C13').getValue()
    }
  };

  var emailCount = 0; // メール送信数のカウンタ

  dataRange.forEach(function(row) {
    var category = row[0];
    var recipientName = row[1];
    var recipientEmail = row[2];

    if (category && recipientName && recipientEmail) {
      var personalizedBody = emailSettings.bodyTemplate
        .replace('{Name}', recipientName)
        .replace('{staffname}', emailSettings.staffName);

      if (category === emailSettings.A14.category) {
        personalizedBody = personalizedBody
          .replace('{Deadline}', formatDate(emailSettings.A14.deadline))
          .replace('{Url}', emailSettings.A14.url);
      } else if (category === emailSettings.A15.category) {
        personalizedBody = personalizedBody
          .replace('{Deadline}', formatDate(emailSettings.A15.deadline))
          .replace('{Url}', emailSettings.A15.url);
      }

      MailApp.sendEmail({
        to: recipientEmail,
        subject: emailSettings.subject,
        htmlBody: personalizedBody,
        name: emailSettings.fromName,
        from: emailSettings.fromEmail,
        replyTo: emailSettings.replyTo,
        cc: emailSettings.cc,
        bcc: emailSettings.bcc
      });
      
      emailCount++; // メール送信数をカウント
    }
  });

  Logger.log(emailCount + '件のメールを送信しました');
  SpreadsheetApp.getUi().alert(emailCount + '件のメールを送信しました');
}

function formatDate(date) {
  var daysOfWeek = ['日', '月', '火', '水', '木', '金', '土'];
  var dayOfWeek = daysOfWeek[date.getDay()];
  return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy/M/d') + '(' + dayOfWeek + ')';
}