Copying attachments from Gmail to Google Drive folder and overwriting old files with Apps Script

Question

I am trying to user Google Apps Script to automate downloading attachments from a Gmail inbox to a specific folder on the account's Google Drive. I found the code below that does that part well, but I also need it to overwrite files of the same name with the new file or delete the old file and upload the new in its place. Also, it still seems to be downloading all filetypes from emails that have more than one attachment when I just need .xlsx files to be copied. Any help would be much appreciated. (credit goes to googleappsscript.org for the code)

// GLOBALS
//Array of file extension which you would like to extract to Drive
var fileTypesToExtract = ['jpg', 'tif', 'png', 'gif', 'bmp', 'svg'];
//Name of the folder in google drive i which files will be put
var folderName = 'GmailToDrive';
//Name of the label which will be applied after processing the mail message
var labelName = '@indrive';



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:'+getDateNDaysBack_(1)+
  for(var i in fileTypesToExtract){
    query += (query === '' ?('filename:'+fileTypesToExtract[i]) : (' OR filename:'+fileTypesToExtract[i]));
  }
  query = 'in:inbox has:nouserlabels ' + query;
//  query += ' after:'+getDateNDaysBack_(1);
  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 file = DriveApp.createFile(attachmentBlob);
        parentFolder.addFile(file);
        root.removeFile(file);
      }
    }
    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(n);
  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

Here is a sample code:

// 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 = 'GmailToDrive';
//Name of the label which will be applied after processing the mail message
var labelName = '@indrive';

function GmailToDrive() {
  var query = '';

  for(var i in fileTypesToExtract){
    query += (query === '' ?('filename:'+fileTypesToExtract[i]) : (' OR filename:'+fileTypesToExtract[i]));
  }
  query = 'in:inbox has:nouserlabels ' + query;
  Logger.log(query);

  var threads = GmailApp.search(query);
  var label = getGmailLabel_(labelName);
  var parentFolder = getFolder_(folderName);

  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];
        Logger.log(attachment.getName());
        Logger.log(attachment.getContentType())

        //Check if attached file is xlsx file
        if(attachment.getContentType() == MimeType.MICROSOFT_EXCEL){
          var attachmentBlob = attachment.copyBlob();

          //Check if filename exist in the drive folder then remove the file
          var files = parentFolder.getFilesByName(attachment.getName());
          while (files.hasNext()) {
            var file = files.next();
            Logger.log("FILE EXIST. Removing.....");
            //Remove existing file
            file.setTrashed(true);
          }
          
          //Create a new file in the drive folder
          var newFile = parentFolder.createFile(attachmentBlob);
        }
      }
    }
    threads[i].addLabel(label);
  }
}

function getGmailLabel_(name){
  var label = GmailApp.getUserLabelByName(name);
  if(!label){
    label = GmailApp.createLabel(name);
  }
  return 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;
}

Modifications done:

• Replace fileTypesToExtract to just extract .xlsx files
• Check if current attachment is a xlsx file using GmailAttachment.getContentType()
• Include checking if the current attachment exist in the drive folder using Folder.getFilesByName(name). Then remove the file using File.setTrashed(trashed)
• Create a new file using Folder.createFile(blob)