如何修改App Script从Gmail指定标签下载Excel附件到Google Drive

下面是优化后的Google App Script代码，实现了仅下载带有「excelHere」标签的邮件中的Excel附件，核心修改了两处逻辑：

修改点说明 #

• 限定目标邮件：在Gmail搜索条件中加入label:excelHere，只筛选带该标签、有附件且发送到指定邮箱的邮件
• 过滤Excel附件：通过检查附件的MIME类型，只保留常见的Excel格式文件（.xlsx/.xls/.xlsm）

完整优化代码 #

function saveNewAttachmentsToDrive() {
  var folderId = "ABCDE"; // 替换为Google Drive目标文件夹ID
  var emailAddress = "myemail@address.com.ph"; // 你的邮箱地址
  // 修改搜索条件：仅筛选带excelHere标签、有附件、发给指定邮箱的邮件
  var searchQuery = "to:" + emailAddress + " has:attachment label:excelHere"; 
  var lastExecutionTime = getLastExecutionDate();
  var threads = GmailApp.search(searchQuery + " after:" + lastExecutionTime);
  var driveFolder = DriveApp.getFolderById(folderId);
  // 定义Excel文件的MIME类型集合
  var excelMimeTypes = [
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", // .xlsx
    "application/vnd.ms-excel", // .xls
    "application/vnd.ms-excel.sheet.macroEnabled.12" // .xlsm
  ];

  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      var message = messages[j];
      var attachments = message.getAttachments();
      for (var k = 0; k < attachments.length; k++) {
        var attachment = attachments[k];
        // 仅处理Excel类型的附件
        if (excelMimeTypes.includes(attachment.getContentType())) {
          var attachmentBlob = attachment.copyBlob();
          var fileName = attachment.getName();
          driveFolder.createFile(attachmentBlob).setName(fileName);
        }
      }
    }
  }
  updateLastExecutionDate();
}

function getLastExecutionDate() {
  var properties = PropertiesService.getUserProperties();
  return properties.getProperty("lastExecutionDate") || "2023-09-01";
}

function resetLastExecutionDate() {
  PropertiesService.getUserProperties().deleteProperty("lastExecutionDate");
}

function updateLastExecutionDate() {
  var now = new Date();
  var dateString = now.toISOString().split("T")[0];
  var properties = PropertiesService.getUserProperties();
  properties.setProperty("lastExecutionDate", dateString);
}

额外说明 #

• 如果需要支持更多Excel衍生格式，可以在excelMimeTypes数组中添加对应的MIME类型
• 原脚本的执行时间记录逻辑保留，确保只会处理上次执行时间之后的新邮件，避免重复下载

内容的提问来源于stack exchange，提问作者Arvin Villodres