Adding Replace Existing File to GmailToDrive App Script - Follow up Question

Question

This is an additional question off of the related post: Adding Replace Existing File to GmailToDrive App Script answered by @Tanaike.

I'm trying to use the code from the previous post with the Edit function with an xlsx file. Everything seems to be working fine where it grabs the file and saves it and it even updated the modified date in the google drive folder. However, the content of the excel file does not change from the original. I've enabled the Drive API. Is this compatible with xlsx file types? Is there something additional needed to actually update the content in the file and not just the metadata?

// GLOBALS
//Array of file extension which you would like to extract to Drive
var fileTypesToExtract = ['xlsx'];
//Name of the folder in google drive i which files will be put
var folderName = 'YourfolderName;
//Name of the label which will be applied after processing the mail message
var labelName = 'Saved2';


function GmailToDrive(){
  //build query to search emails
  var query = '';
  //filename:jpg OR filename:tif OR filename:gif OR fileName:png OR filename:bmp OR filename:svg'; //'after:'+formattedDate+
  for(var i in fileTypesToExtract){
 query += (query === '' ?('filename:'+fileTypesToExtract[i]) : (' OR filename:'+fileTypesToExtract[i]));
  }
  query = 'in:inbox label:MyCustomLabel has:attachment ' + query;
  var threads = GmailApp.search(query);
  var label = getGmailLabel_(labelName);
  var parentFolder;
  if(threads.length > 0){
    parentFolder = getFolder_(folderName);
  }
  var root = DriveApp.getRootFolder();
  for(var i in threads){
    var mesgs = threads[i].getMessages();
 for(var j in mesgs){
      //get attachments
      var attachments = mesgs[j].getAttachments();
      for(var k in attachments){
        var attachment = attachments[k];
        var isDefinedType = checkIfDefinedType_(attachment);
     if(!isDefinedType) continue;
        var attachmentBlob = attachment.copyBlob();
        var existingFile = DriveApp.getFilesByName(attachment.getName());
        if (existingFile.hasNext()) {
          var file = existingFile.next();
          Drive.Files.update({}, file.getId(), attachmentBlob);
        } else { // Added
          var file = DriveApp.createFile(attachmentBlob); // Added
          parentFolder.addFile(file); // Added
          root.removeFile(file); // Added
        }
      }
 }
 threads[i].addLabel(label);
  }
}

//This function will get the parent folder in Google drive
function getFolder_(folderName){
  var folder;
  var fi = DriveApp.getFoldersByName(folderName);
  if(fi.hasNext()){
    folder = fi.next();
  }
  else{
    folder = DriveApp.createFolder(folderName);
  }
  return folder;
}

//getDate n days back
// n must be integer
function getDateNDaysBack_(n){
  n = parseInt(8);
  var date = new Date();
  date.setDate(date.getDate() - n);
  return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy/MM/dd');
}

function getGmailLabel_(name){
  var label = GmailApp.getUserLabelByName(name);
  if(!label){
 label = GmailApp.createLabel(name);
  }
  return label;
}

//this function will check for filextension type.
// and return boolean
function checkIfDefinedType_(attachment){
  var fileName = attachment.getName();
  var temp = fileName.split('.');
  var fileExtension = temp[temp.length-1].toLowerCase();
  if(fileTypesToExtract.indexOf(fileExtension) !== -1) return true;
  else return false;
}

Answer 1

Because .xlsx is the Excel format, Apps Script can't modify its content because it's not a Google product, it's like trying to modify a pdf using Apps Script, but don't worry, you could convert the .xlsx file into the native Google Spreadsheet format and then handle it like a normal Google Spreadsheet. This function will do the conversion for you:

// this Function will convert your .xlsx files into native Google Spreadsheet format
function convertExceltoGoogleSpreadsheet(parentFolder) {
  var files = parentFolder.getFiles();
  while(files.hasNext()){
    var file = files.next();
    // If the file contains the .xlsx extention in its name
    // then it will return an array with length 2
    var filename = file.getName().split(/.xlsx?/);
    if(filename.length > 1){
      // build info to create the new file with Google Spreadsheet format
      var resource = {
        title: filename[0],
        mimeType: MimeType.GOOGLE_SHEETS,
        parents: [{id: parentFolder.getId()}],
      };
      // Return the response after creating the file 
      return Drive.Files.insert(resource, file.getBlob());
    }   
  }
}

Then, use that function in this part of your code:

...
        var attachmentBlob = attachment.copyBlob();
        var existingFile = DriveApp.getFilesByName(attachment.getName());
        if (existingFile.hasNext()) {
          var file = existingFile.next();
          Drive.Files.update({}, file.getId(), attachmentBlob);
        } else { 
           var file = DriveApp.createFile(attachmentBlob)
           parentFolder.addFile(file); 
           root.removeFile(file); 
        }
        // Let's change the format and insert a "Hello world" message
        // ----START----
        var spreadSheetResponse = convertExceltoGoogleSpreadsheet(parentFolder);
        var spreadSheetId = spreadSheetResponse.getId()
        var sheet = SpreadsheetApp.openById(spreadSheetId).getSheets()[0];
        sheet.getRange(1, 1).setValue("Hello world");
        // ----END----
...

Notice

In my code I left the .xlsx file in the Drive, if you want you could delete it after converting it iinto the native Google Spreadsheet format

Docs

This is what I used to help you:

• SpreadsheetApp

• Drive - Class File