0

I have an Excel-File (.xlsm) as a Gmail-attachment in my inbox and want to transmit it to GDrive. There it should be saved as a GSheet. I am trying to automatize this process using Apps Script.

Unfortunately, I get an error starting the Script. (API call to drive.files.insert failed with error: Bad Request) It's very strange because a few times the script works and the file could be converted without any problems. Last week, it worked as soon as I forwarded the Mail with the attachment to someone (Don't ask me where's the context). But now, it's all history and I don't know how to fix the error.

I am new on StackOverflow and I am really looking forward to every answer from you. Thank you very much.

Here's the code:

function importFunction() {

  var threads =  GmailApp.search('CC520_Report_Lukas_GAS_ABC');                             
  var messages = threads[0].getMessages();    
  var message = messages[0];                                                                
  var attachment = message.getAttachments()[0];                                            

  var resource = {
    title: 'NewFileLukas',                                
    mimeType: MimeType.GOOGLE_SHEETS,
    parents: [{id: 'xxxxx6BD1SIfI0Cz5bmGahzSlHUxxxxxx'}], 
};

var insert = Drive.Files.insert(resource, attachment);      // Here comes the error.
Lukas127127
  • 3
  • 1
  • Can you add the line `Logger.log(message.getSubject());` and then identify the email with the subject from the Gmail UI. Have a look at the attachment to see what is the difference between the attachments that can be converted and then ones that can't. – ziganotschka Apr 29 '20 at 09:59

1 Answers1

2

I believe your goal as follows.

  • You want to convert a .xlsm file to Google Spreadsheet.
  • attachment you are using is the .xlsm file.
  • In your situation, an error of API call to drive.files.insert failed with error: Bad Request occurs at Drive.Files.insert(resource, attachment).
    • You want to remove this error.

For this, how about this answer? I had also experienced the same issue.

Modification points:

  • The mimeType of the .xlsm file is application/vnd.ms-excel.sheet.macroenabled.12. In your script, when console.log(attachment.getContentType()) is used, if attachment is the .xlsm file, such mimeType is returned.
  • When importFormats is confirmed with the method of "About: get" in Drive API Ref, it seems that application/vnd.ms-excel.sheet.macroenabled.12 can be converted to application/vnd.google-apps.spreadsheet. This can be seen at Drive API v2 and v3.
    • But, when application/vnd.ms-excel.sheet.macroenabled.12 data is used to Drive.Files.insert(resource, attachment), an error occurs. In this case, I confirmed that even when I tested this using Drive API v3, the same issue occurred.
    • I thought that this conversion might have not been reflected yet. Also I believe that this will be modified at the future update.

So as the current workaround, I would like to propose to convert the .xlsm file to Google Spreadsheet by changing the mimeType of the blob.

Modified script:

When your script is modified, please modify as follows.

From: 
var attachment = message.getAttachments()[0];
To: 
var attachment = message.getAttachments()[0].setContentType(MimeType.MICROSOFT_EXCEL);

or

var attachment = message.getAttachments()[0];
if (attachment.getContentType() == "application/vnd.ms-excel.sheet.macroenabled.12") {
  attachment.setContentType(MimeType.MICROSOFT_EXCEL);
}

Note:

  • In my environment, I could confirm that by changing the mimeType to MimeType.MICROSOFT_EXCEL, the .xlsm file could be converted to Google Spreadsheet.
  • In this case, the macro of the .xlsm file is not converted to Google Apps Script. And also, after the .xlsm file was converted to Google Spreadsheet, when the Google Spreadsheet is converted to the excel file, the macro is not included. I think that this is the specification. So please be careful this.

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165