🗂
GAS メールのスプレッドシート 12月
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;
}
var toAddress = "@gmail.com";
var ccAddress = "@gmail.com";
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = "シート1";
var sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
throw new Error("指定されたシートが見つかりません: " + 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++;
}
}
}
Discussion