java

function getNewEmailsByToAddressToSheet() {
  var today = new Date();
  var startDate = new Date('2024-10-01');
  var endDate = new Date('2024-10-31');

  if (today < startDate || today > endDate) {
    return;
  }

  // 現在の月を取得し、シート名を「MM月」形式に設定
  var month = ('0' + (today.getMonth() + 1)).slice(-2) + "月"; // 例: "10月"
  var sheetName = month;

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetName);

  // 指定したシートが存在しない場合、新規作成
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }

  // シートのヘッダーが存在しない場合、ヘッダーを設定
  if (sheet.getRange(1, 1).getValue() !== "No.") {
    sheet.getRange(1, 1).setValue("No.");
    sheet.getRange(1, 2).setValue("Check");
    sheet.getRange(1, 3).setValue("担当者");
    sheet.getRange(1, 4).setValue("記入者");
    sheet.getRange(1, 5).setValue("受信時間");
    sheet.getRange(1, 6).setValue("件名");
  }

  var responsiblePersons = ["対応中", "対応不要", "対応不要"];
  var authors = ["記入者A", "記入者B", "記入者C"];
  var startOfDay = new Date(today.getFullYear(), today.getMonth(), today.getDate(), 0, 0, 0);
  var endOfDay = new Date(today.getFullYear(), today.getMonth(), today.getDate() + 1, 0, 0, 0);

  var startTime = Math.floor(startOfDay.getTime() / 1000);
  var endTime = Math.floor(endOfDay.getTime() / 1000);
  var query = 'to:' + toAddress + ' OR cc:' + ccAddress + ' after:' + startTime + ' before:' + endTime;

  var threads = GmailApp.search(query);

  function getExistingData(sheet) {
    var lastRow = sheet.getLastRow();
    if (lastRow > 1) {
      return sheet.getRange(2, 5, lastRow - 1, 2).getValues();
    }
    return [];
  }

  var existingData = getExistingData(sheet);
  var row = sheet.getLastRow() + 1;

  for (var i = 0; i < threads.length; i++) {
    var threadMessages = threads[i].getMessages();

    threadMessages.sort(function(a, b) {
      return a.getDate().getTime() - b.getDate().getTime();
    });

    for (var j = 0; j < threadMessages.length; j++) {
      var message = threadMessages[j];
      var subject = message.getSubject();
      var date = message.getDate();
      var formattedDate = Utilities.formatDate(date, Session.getScriptTimeZone(), "MM/dd/HH:mm");

      var isDuplicate = existingData.some(function(existingRow) {
        return existingRow[0] === formattedDate && existingRow[1] === subject;
      });

      if (isDuplicate) {
        continue;
      }

      sheet.getRange(row, 1).setValue(row - 1);
      sheet.getRange(row, 5).setValue(formattedDate);
      sheet.getRange(row, 6).setValue(subject);
      sheet.getRange(row, 2).insertCheckboxes();

      var responsibleCell = sheet.getRange(row, 3);
      var responsibleRule = SpreadsheetApp.newDataValidation().requireValueInList(responsiblePersons).build();
      responsibleCell.setDataValidation(responsibleRule);

      var authorCell = sheet.getRange(row, 4);
      var authorRule = SpreadsheetApp.newDataValidation().requireValueInList(authors).build();
      authorCell.setDataValidation(authorRule);

      row++;
    }
  }
}