Gmail Excel Attachment to Google Drive

Overview

Save Excel attachments received in Gmail to Google Drive. You'll just need to ensure that the emails with the attachments you want to parse have some sort of label applied to them (via automated rule or otherwise) and have a Google Drive folder configured for saving the Google Sheets to.

Instructions

1. Create a new folder (for instruction's sake, we'll call this Folder A) within your Google Drive. Within this new folder (again, Folder A), create a new folder (we'll call this Folder B).

2. In Folder A, create a new Apps Script file.

3. Paste the code at the bottom of this page into the Apps Script Editor.

4. Update the folderId variable to the Folder ID of Folder B. This is where the files will be saved.

5. If you want to download Excel attachments from emails with a label other than filesToDownload, update the query variable and change filesToDownload to whatever you want your custom label to be.

   1. • By default, after files are downloaded, they will be labeled AttachmentSavedInDrive, but you can change this in the labelName and query variables if you'd like.

6. Run and approve the script. Make sure that you have a test file in your Gmail before scheduling the script to run routinely.

//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 folderId = 'XXXXXXXXXX';

//Name of the label which will be applied after processing the mail message

var labelName = 'AttachmentSavedInDrive';

function GmailToDrive(){

  //build query to search emails

  query = 'label:filesToDownload -label:AttachmentSavedInDrive filename:xlsx';

//  query += ' after:'+getDateNDaysBack_(1);

  var threads = GmailApp.search(query);

  var label = getGmailLabel_(labelName);

  var parentFolder;

  Logger.log(threads.length);

  if(threads.length > 0){

    parentFolder = DriveApp.getFolderById(folderId);

  }

  var root = DriveApp.getRootFolder();

  for(var i in threads){

    var mesgs = threads[i].getMessages();

    Logger.log('Subject: '+mesgs[0].getSubject());

for(var j in mesgs){

      //get attachments

      var attachments = mesgs[j].getAttachments();

      for(var k in attachments){

        var attachment = attachments[k];

        var attachmentName = attachment.getName()

       var attachmentBlob = attachment.copyBlob();

        if (attachmentName.indexOf('.csv')>-1 || attachmentName.indexOf('.xls')>-1) {

          var m=0;

          while (m<3){

            try {

              var file = {

              title: attachmentName

              };

              file = Drive.Files.insert(file, attachmentBlob, {

                convert: true

              });

              DriveApp.getFileById(file.getId()).moveTo(parentFolder);

              break;

            }

            catch(e){

              Utilities.sleep(5000);

              m++;

              var attemptNo = m+1;

              Logger.log('Attempt '+attemptNo+': '+attachmentName);

            }

          }

        }

        else {

          continue;

        }

      }

}

threads[i].addLabel(label);

  }

}

//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;

}