XLSX from Gmail to Google Sheets: invalid mime type. Content type seems to be application/octet?

Question

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:

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

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.

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})

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

Try to remove the mimetype value. – St3ph Mar 15 '20 at 15:45 — St3ph, Mar 15 '20 at 15:45

score 2 · Answer 1 · answered Mar 16 '20 at 00:37

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.

Issue and workaround:

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

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.

Modified script:

When your script is modified, please modify as follows.

From:

var blob =attachment

To:

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

or

To:

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();

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.

References:

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

score 0 · Answer 2 · answered Mar 15 '20 at 16:21

These

parents: [{id: folderId}],
mimeType: MimeType.GOOGLE_SHEETS

refer to the input file. See docs here. You should skip these.

This snippet worked for me with Google Drive API v2:

function abc() {
  var mail = GmailApp.search("SEARCH_TERM")[0];
  var msg = mail.getMessages()[0];
  var blob = msg.getAttachments()[0];
  var file = {
    title: 'Converted Spreadsheet'
  };
  Drive.Files.insert(file, blob, {convert: true});
}