解决application/octet-stream类型Excel附件上传Google Drive失败问题

我每晚都会收到带Excel附件的邮件，但发送方未定义正确的内容类型，导致附件的content type为application/octet-stream。我的脚本尝试将这些文件上传至Google Drive并转为Google Sheets，但上传失败，报错GoogleJsonResponseException: Invalid mime type provided。我曾尝试转发附件并指定正确内容类型，但转发后的文件仍为octet-stream类型。相关代码如下：

var ureadMsgsCount = GmailApp.getInboxUnreadCount(); 
if (ureadMsgsCount > 0) { 
  var threads = GmailApp.search("Excel Files") 
  for (var i = 0; i < threads.length; i++) { 
    if (threads[i].isInInbox()) { 
      messages = threads[i].getMessages(); 
      for (var j = 0; j < messages.length; j++) { 
        if (messages[j].isUnread()) { 
          var attachments = messages[j].getAttachments(); 
          for (var k = 0; k < attachments.length; k++) { 
            // Is the attachment an excel file 
            var excelFile = attachments[k]; 
            var fileName = attachments[k].getName(); 
            var contentType = attachments[k].getContentType(); 
            if (fileName.indexOf("xls") > -1) { 
              var folderId = getFolderID('saved files'); 
              var resource = { 
                title: fileName.replace(/.xls?/, ""), 
                mimeType: 'application/vnd.google-apps.spreadsheet', 
                parents: [{ id: folderId }] 
              }; 
              Drive.Files.insert(resource, excelFile); 
            } 
          } 
        } 
      } 
    } 
  } 
}

解决办法 #

这个问题的核心是Google Drive无法通过application/octet-stream这个通用类型识别出文件实际是Excel，所以我们需要手动帮它明确文件类型，或者换一种转换方式。这里有两个可行的方案：

方案1：修正附件的MIME类型后直接上传 #

我们可以根据文件名后缀判断正确的Excel MIME类型，然后修改附件blob的类型再上传，这样Drive就能正确识别并转换了：

var ureadMsgsCount = GmailApp.getInboxUnreadCount(); 
if (ureadMsgsCount > 0) { 
  var threads = GmailApp.search("Excel Files") 
  for (var i = 0; i < threads.length; i++) { 
    if (threads[i].isInInbox()) { 
      messages = threads[i].getMessages(); 
      for (var j = 0; j < messages.length; j++) { 
        if (messages[j].isUnread()) { 
          var attachments = messages[j].getAttachments(); 
          for (var k = 0; k < attachments.length; k++) { 
            var excelFile = attachments[k]; 
            var fileName = attachments[k].getName(); 
            // 用正则更精准匹配Excel文件后缀
            if (/\.xls(x)?$/i.test(fileName)) { 
              try {
                var folderId = getFolderID('saved files'); 
                // 根据后缀选择正确的MIME类型
                var correctMimeType = fileName.endsWith('.xlsx') 
                  ? 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' 
                  : 'application/vnd.ms-excel';
                // 修正附件的MIME类型
                var correctedBlob = excelFile.setContentType(correctMimeType);
                
                var resource = { 
                  title: fileName.replace(/.xls?/, ""), 
                  mimeType: 'application/vnd.google-apps.spreadsheet', 
                  parents: [{ id: folderId }] 
                }; 
                Drive.Files.insert(resource, correctedBlob);
                // 处理完成后标记邮件为已读
                messages[j].markRead();
              } catch (e) {
                Logger.log(`处理附件 ${fileName} 失败：${e.message}`);
              }
            } 
          } 
        } 
      } 
    } 
  } 
}

方案2：先上传原始文件，再转换为Google Sheets #

如果第一种方法还是有兼容性问题，我们可以分两步走：先把文件原封不动上传到Drive，再调用API把它转换为Sheets格式：

var ureadMsgsCount = GmailApp.getInboxUnreadCount(); 
if (ureadMsgsCount > 0) { 
  var threads = GmailApp.search("Excel Files") 
  for (var i = 0; i < threads.length; i++) { 
    if (threads[i].isInInbox()) { 
      messages = threads[i].getMessages(); 
      for (var j = 0; j < messages.length; j++) { 
        if (messages[j].isUnread()) { 
          var attachments = messages[j].getAttachments(); 
          for (var k = 0; k < attachments.length; k++) { 
            var excelFile = attachments[k]; 
            var fileName = attachments[k].getName(); 
            if (/\.xls(x)?$/i.test(fileName)) { 
              try {
                var folderId = getFolderID('saved files'); 
                // 1. 先上传原始文件到Drive
                var uploadResource = { 
                  title: fileName, 
                  parents: [{ id: folderId }] 
                }; 
                var uploadedFile = Drive.Files.insert(uploadResource, excelFile);
                
                // 2. 将上传的文件转换为Google Sheets
                var convertResource = { 
                  mimeType: 'application/vnd.google-apps.spreadsheet' 
                }; 
                Drive.Files.patch(convertResource, uploadedFile.id);
                
                // 可选：如果不需要保留原始Excel文件，可以删除它
                // Drive.Files.remove(uploadedFile.id);
                
                messages[j].markRead();
              } catch (e) {
                Logger.log(`处理附件 ${fileName} 失败：${e.message}`);
              }
            } 
          } 
        } 
      } 
    } 
  } 
}

额外小提示 #

• 正则匹配文件名后缀比indexOf更可靠，能避免误判文件名中包含"xls"但不是Excel的文件；
• 加上try-catch可以捕获异常并记录日志，方便后续排查问题；
• 记得在处理完成后标记邮件为已读，避免重复处理。

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