从 Gmail 到 Google 表格的 XLSX:无效的 mime 类型.内容类型似乎是应用程序/八位字节? [英] XLSX from Gmail to Google Sheets: invalid mime type. Content type seems to be application/octet?

问题描述

我正在尝试使用 Google Apps 脚本将一个 xlsx 文件从 Gmail 附件导入到 Google Drive(作为 Google Sheet).我曾尝试在 GAS 中使用 Advanced Drive API，但这样做会导致此错误:

I'm trying to import an xlsx file from a Gmail Attachment to Google Drive (as Google Sheet) using Google Apps Script. I've tried using the Advanced Drive API in GAS, but doing this results in this error:

对 drive.files.insert 的 API 调用失败并出现错误:无效的 mime 类型提供

API call to drive.files.insert failed with error: Invalid mime type provided

我发现 Gmail 附件作为 application/octet 而不是 application/vnd.ms-excel 导入到 Google Apps 脚本，我认为这是问题所在.但是，附件是一个 xlsx 文件，我不明白为什么它会被识别为应用程序/八位字节.

I've figured out that the Gmail attachment is imported to Google Apps Script as application/octet instead of application/vnd.ms-excel, which I think is the problem. However, the attachment is an xlsx file and I don't see why that would be recognised as application/octet.

请注意，我想将 XLSX 转换为 Google 表格.因此我需要 MimeType.代码如下:

Keep in mind, I want to convert the XLSX to Google Sheets. Therefore I need the MimeType. Here's the code:

 var mail  = GmailApp.search("XXXXXXX")[0];
  var msg = mail.getMessages()[0]
  var attachment = msg.getAttachments()[0];
  var blob =attachment
  var name = attachment.getName();
  var folderId = 'XXXXXX'; 

  var file = {
    title: 'Converted Spreadsheet',
   parents: [{id: folderId}],
    mimeType: MimeType.GOOGLE_SHEETS
  };
  file = Drive.Files.insert(file, blob, {convert: true})

有没有人知道如何修复错误或找到另一种方法将此 XLSX 转换为工作表?谢谢！

Does anyone have an idea of how to fix the error or find another way to convert this XLSX to a sheet? Thanks!

推荐答案

• 在您的情况下，附件的 blob 的 mimeType 是 application/octet.
• blob 实际上是一个 XLSX 文件.
• application/octet的blob与Drive.Files.insert()一起使用时，Invalid mime type的错误提供发生.
• 您希望将上述 blob 的 XLSX 文件转换为 Google 电子表格并将其创建为文件.
• 您希望使用 Google Apps 脚本实现此目的.
• In your situation, the mimeType of blob of the attachment file is application/octet.
• blob is actually a XLSX file.
• When blob of application/octet is used with Drive.Files.insert(), an error of Invalid mime type provided occurs.
• You want to convert the XLSX file of above blob to Google Spreadsheet and create it as a file.
• You want to achieve this using Google Apps Script.

如果我的理解是正确的，那么这个答案呢?请认为这只是几个可能的答案之一.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

• 当blob的mimeType为application/octet时，
  • 当 Drive.Files.insert({title: 'Converted Spreadsheet', parents: [{id: "root"}], mimeType: MimeType.GOOGLE_SHEETS}, blob, {convert: true})，出现Invalid mime type provided的错误.
  • 当 Drive.Files.insert({title: 'Converted Spreadsheet', parents: [{id: "root"}]}, blob, {convert: true}) 运行时，没有发生错误.但是创建文件的 mimeType 是 application/octet.这样，该文件就无法直接在 Google Drive 上打开.在这种情况下，需要将创建文件的 mimeType 更改为 XLSX.我认为这是由于此字段可以留空，mimetype将根据上传内容的MIME类型来确定..参考
  • When the mimeType of blob is application/octet,
    • When Drive.Files.insert({title: 'Converted Spreadsheet', parents: [{id: "root"}], mimeType: MimeType.GOOGLE_SHEETS}, blob, {convert: true}) is run, the error of Invalid mime type provided occurs.
    • When Drive.Files.insert({title: 'Converted Spreadsheet', parents: [{id: "root"}]}, blob, {convert: true}) is run, no error occurs. But the mimeType of created file is application/octet. By this, the file cannot be directly opened at Google Drive. In this case, it is required to change the mimeType of created file to XLSX. I think that the reason of this is due to This field can be left blank, and the mimetype will be determined from the uploaded content's MIME type.. Ref

    根据上述情况，在这个答案中，我建议在运行 Drive.Files.insert 之前将 XLSX 的 mimeType 设置为 blob.

    From above situation, in this answer, I would like to propose to set the mimeType of XLSX to blob before Drive.Files.insert is run.

    当你的脚本被修改时，请进行如下修改.

    When your script is modified, please modify as follows.

    var blob =attachment
    

    收件人:

    var blob = attachment.setContentType(MimeType.MICROSOFT_EXCEL);  // MimeType.MICROSOFT_EXCEL or MimeType.MICROSOFT_EXCEL_LEGACY
    

    或

    如果 blob 的文件名带有扩展名，您也可以使用以下脚本.在这种情况下，mimeType 由文件名的扩展名自动给出.

    If blob has the filename with the extension, you can also use the following script. In this case, the mimeType is automatically given by the extension of the filename.

    var blob = attachment.setContentTypeFromExtension();
    

    注意:

    • 在上述修改后的脚本中，file 中有无mimeType: MimeType.GOOGLE_SHEETS 的结果是一样的.
    • 在您的问题中，您说 来自 Gmail 附件的 xlsx 文件.你说 Gmail 附件作为 application/octet 而不是 application/vnd.ms-excel 导入到 Google Apps 脚本.
      • 如果文件为 XLSX 文件，则 mimeType 为 application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.
      • 如果文件是 XLS 文件，则 mimeType 是 application/vnd.ms-excel.

      Note:

      • In above modified script, the result is the same with and without mimeType: MimeType.GOOGLE_SHEETS in file.
      • In your question, you say an xlsx file from a Gmail Attachment. And you say the Gmail attachment is imported to Google Apps Script as application/octet instead of application/vnd.ms-excel.
        • If the file is XLSX file, the mimeType is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.
        • If the fie is XLS file, the mimeType is application/vnd.ms-excel.
        • setContentType(contentType)
        • setContentTypeFromExtension()
        • 枚举 MimeType

        如果这没有解决您的问题，我深表歉意.

        If this didn't resolve your issue, I apologize.

        这篇关于从 Gmail 到 Google 表格的 XLSX:无效的 mime 类型.内容类型似乎是应用程序/八位字节?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！