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

问题描述

我正在尝试使用Google Apps脚本从Gmail附件将xlsx文件导入Google云端硬盘(作为Google表格).我尝试在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/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 provided.
• 您要将上述blob的XLSX文件转换为Google Spreadsheet并将其创建为文件.
• 您想使用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云端硬盘中打开.在这种情况下，需要将创建的文件的mimeType更改为XLSX.我认为这是由于This field can be left blank, and the mimetype will be determined from the uploaded content's MIME type.引起的. 参考
  • 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时，结果都是相同的.
    • 在您的问题中，您说an xlsx file from a Gmail Attachment.然后您说the Gmail attachment is imported to Google Apps Script as application/octet instead of application/vnd.ms-excel.
      • 如果文件是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
        • setContentType(contentType)
        • setContentTypeFromExtension()
        • Enum MimeType

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

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

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