ERROR - Invalid mime type provided - Mime type used: MimeType.GOOGLE_SHEETS

[Finn Harnett O'Meara]

Hi there,

I seem to be having an issue where the Mime Type GOOGLE_SHEETS is not recognised. I've had the exact same script work on one account but not another so I think that maybe there is a permissions error or something? I have the drive api v2 enabled in the advanced google services setting so it shouldn't be that.

The exact error is ths: API call to drive.files.insert failed with error: Invalid mime type provided (line 86, file "Code")

The code I'm using is included below. Any help is very appreciated, thank you! 

Finn

var SPREADSHEET_ID = 'SPREADSHEET_ID';


/* ------------- no changes below needed ------------- */


var spreadSheet = SpreadsheetApp.openById(SPREADSHEET_ID);


var GMAIL_LABEL = 'LABEL';
var SPREADSHEET_NAME = 'SPREADSHEET_NAME';


var sheet = spreadSheet.getSheetByName(SPREADSHEET_NAME);


/**
* Get all the starred threads within our label and process their attachments
*/
function main() {
  var labels = getSubLabels(GMAIL_LABEL);
  for (var i = 0; i < labels.length; i++) {
    var threads = getUnprocessedThreads(labels[i]);
    for(var j = 0; j < threads.length; j++) {
      processThread(threads[j], labels[i]);
    }
  }
}


/**
* Get all the given label and all its sub labels
*
* @param {string} name
* @return {GmailLabel[]}
*/
function getSubLabels(name) {
  var labels = GmailApp.getUserLabels();
  var matches = [];
 
  for(var i = 0; i < labels.length; i++){
    var label = labels[i];
    if (label.getName() === name || label.getName().substr(0, name.length+1) === name + '/') {
      matches.push(label);
    }
  }
 
  return matches;
}


/**
* Get all starred threads in the given label
*
* @param {GmailLabel} label
* @return {GmailThread[]}
*/
function getUnprocessedThreads(label) {
  var from = 0;
  var perrun = 50; //maximum is 500
  var threads;
  var result = [];
 
  do {
    threads = label.getThreads(from, perrun);
    from += perrun;
   
    for(var i = 0; i < threads.length; i++) {
      if(!threads[i].hasStarredMessages()) continue;
      result.push(threads[i]);
    }
  } while (threads.length === perrun);
 
  Logger.log(result.length + ' threads to process in ' + label.getName());
  return result;
}


/**
* @param {GmailThread} thread
* @param {GmailLabel} label where this thread was found
*/
function processThread(thread, label) {
  var messages = thread.getMessages();
  for(var j = 0; j < messages.length; j++) {
    var message = messages[j];
    if(!message.isStarred()) continue;
    Logger.log('processing message from '+message.getDate());
   
    var attachments = message.getAttachments();
    for(var i = 0; i < attachments.length; i++) {
     
      var xlsxBlob = attachments[i]; // Is supposes that attachments[i] is the blob of xlsx file.
      var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, xlsxBlob).id;
      var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0]; // There is the data in 1st tab.
      var data = sheet.getDataRange().getValues();
      Drive.Files.remove(convertedSpreadsheetId); // Remove the converted file.
     
      writeToGoogleSheet(data);
    }
    message.unstar();
  }
}


/**
* Get the extension of a file
*
* @param  {string} name
* @return {string}
*/
function getExtension(name) {
  var re = /(?:\.([^.]+))?$/;
  var result = re.exec(name);
  if(result && result[1]) {
    return result[1].toLowerCase();
  } else {
    return 'unknown';
  }
}


/*
 * Write results to Google Sheet
 *
 * @param {Array<Array>} csvData 2D array to be printed to the Google Sheet
 */
function writeToGoogleSheet(csvData) {
  sheet.clearContents().clearFormats();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

[Bauke de Kroon]

Hi Finn,

If you replace "MimeType.GOOGLE_SHEETS" with "application/vnd.google-apps.spreadsheet" do you still get the same error message?

See the page "Supported MIME Types" for the Drive API v2.

Regards,

Bauke

[Finn Harnett O'Meara]

Hi Bauke,

Thanks for the reply. 

Yeah exactly the same error. As I said, weirdly the script works on another account so I don't think the issue is with the code itself. 

Does similar code work in your account (changing an xlsx document to a Google Sheet)?

Thanks, 

Finn

[Bauke de Kroon]

Hi Finn,

I did a quick test with an uploaded Excel file to Google Drive and the code below. That worked just fine, I did not get an error message. See also the screenshot for the log results.

Maybe you can check what the content type of the variable "xlsxBlob" is before trying to convert it? It could be that the attachment is not an Excel sheet but an image for example that is also present in the email. You can use message.getAttachments({includeInlineImages: false}) to prevent images from being included in the array that is returned.

function convertXlsxToGSheet() {
//https://drive.google.com/file/d/1YZNOpYPZrLyyi2s1NT4n4E1_TLv4fsfi/view?usp=sharing
  var xlsxBlob = DriveApp.getFileById("1YZNOpYPZrLyyi2s1NT4n4E1_TLv4fsfi").getBlob();

  var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, xlsxBlob).id;
  var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0]; // There is the data in 1st tab.

  Logger.log("Sheet name: " + sheet.getName());
  var data = sheet.getDataRange().getValues();  
  Logger.log(data);
}

Regards,

Bauke

[Finn Harnett O'Meara]

Hi Bauke,

Thanks for testing. It's 100% an Excel file, it's the only attachment in the email, the name matches and has an xlsx extension name. Do you know of any settings that I might have different to you that would prevent me from using that Mime Type?

Thanks,

Finn

[Bauke de Kroon]

Hi Finn,

I have a G Suite Business account that I used for testing this. I don't see what settings might be different as I created a new Google Apps Script file and a blank sheet in Google Drive. I then pasted your code in the editor, changed the sheet id etc and tested the code with an email I send to myself with an Excel file as an attachment and that also worked without an error. You might try with a new apps script file and pasting the code in there and see of that works? I know it's a long shot but it might just work. I hope that helps.

Regards,

Bauke

[Finn Harnett O'Meara]

Hi Bauke,

That's so strange, that's exactly the same as what I've done. And a new file doesn't seem to make any difference. Maybe there's an issue with my Drive API credentials or something? 

Still not solved :(

Thanks so much for all your help!

Finn

[Bauke de Kroon]

Hi Finn,

You're welcome.

Do you have another G Suite or Gmail account that you can test the code with? Another thing you could try is to remove the authorisation for the code from your account settings "Third-party apps with account access". When you run the code you have to authorise it again.

Regards,

Bauke

[Finn Harnett O'Meara]

Hi Bauke,

So I have tested it in a different G Suite and it's worked on two emails and failed on one email. Even with the same script when running it from one account it works and another it fails. That's what made me think that the permissions might have been different account to account. Removing the permissions didn't work I'm afraid. Not really sure what else to try.

Thanks again,

Finn

[Finn Harnett O'Meara]

Hey,

Sorry to keep posting but I really am not sure how to fix this issue. 

When I do mime type CSV the script runs but encodes the data strangely. Is there any way I can link someone my exact script and they can look at the issue for me?

Thanks,

Finn

[Bauke de Kroon]

Hi Finn,

What you can do is set the character encoding for your csv file to the character set that you need.

var csvBlob = Utilities.newBlob("");
csvBlob.setDataFromString(<CSV_DATA>, 'ISO-8859-1');
var csvFile = DriveApp.createFile(csvBlob);

See the link below for more information

https://developers.google.com/apps-script/reference/base/blob#setDataFromString(String,String)

Regards,

Bauke

[Finn Harnett O'Meara]

Hi Bauke,

Thanks so much for all of your help with this I really appreciate it!

I've managed to get around the issue by first saving the file in the drive using the mime type MICROSOFT_EXCEL and then converting it to a Google Sheet afterwards. I'll include the code below. I know it's a bit of a hack but I've finally got it working so I'm happy enough.

        var convertedSpreadsheet = Drive.Files.insert({mimeType: MimeType.MICROSOFT_EXCEL}, xlsxBlob);
        var newFileBlob = DriveApp.getFileById(convertedSpreadsheet.id).getBlob();
        var newGoogleSheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, newFileBlob).id;
        var newSpreadsheet = SpreadsheetApp.openById(newGoogleSheetId);
        var newData = newSpreadsheet.getSheets()[0].getDataRange().getValues();

Thanks again,

Finn

[Bauke de Kroon]

Hi Finn,

You're welcome, I am glad you got it working hack or not ;-)

Regards,

Bauke