APPS SCRIPT: Download Gmails to GSheet, using Gmail Filters/Labels

AppSheet Tips & Tricks
, ,
19

@MultiTech_Visions
And of course, the code you are using get every single file attachment, not only PDFs. To get ONLY PDF attachments, you need to tune the code a bit like this:

function emailToSheetWithAttach() {
  /////GMAIL SETUP
  var label = GmailApp.getUserLabelByName('YOUR_CUSTOM_LABEL'); // <-- RENAME TO YOUR CUSTOM FILTER LABEL
  //var moveToLabel = GmailApp.getUserLabelByName('MOVE_TO_LABEL'); // <-- Uncomment to move to new label after download
  
  /////GSHEET SETUP
  var ss = SpreadsheetApp.openById('GSHEET_ID'); //  <-- INSERT GSHEET_ID
  var sh = ss.getSheetByName("Email");  //  <-- Enter Sheet-Name where records should be created
  
  /////GDRIVE SETUP
  var destinationFolderID = 'Google_Drive_Folder_Id'; // <-- Enter the ID of the folder to copy files to
  var filepath = "ENTER_THE_FILEPATH_TO_APPEND"; // <-- Enter the relative filepath to use when adding the file into a File column
  
  //////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  var destinationFolder = DriveApp.getFolderbyID(destinationFolderID);
  var threads = label.getThreads();
  
  threads.forEach(function(thread) {
    var messages = thread.getMessages();
    var  mailData = [];
    
    messages.forEach(function(message) {
      var sent = message.getDate();
      var sender = message.getFrom();
      var subject = message.getSubject();
      var body = message.getPlainBody();
      var attachments = message.getAttachments({
        includeInlineImages: false,
        includeInlineAttachments: true
      });
      var attachmentNames = [];
      var newFilePath
      
      attachments.forEach(function(attachment) {
        newFilePath = "";
        if (attachment.getContentType() === "application/pdf") {
          var file = DriveApp.createFile(attachment.getName(), attachment.copyBlob(), attachment.getContentType());
          file.moveTo(destinationFolder);
          newFilePath = filepath + '/' + attachment.getName();
          
          attachmentNames.push(newFilePath);
        }
      });
      
      mailData.push([sent, sender, subject, body, attachmentNames.toString()]);
    });
    
    thread.removeLabel(label);
    thread.moveToArchive();
    thread.markRead();
    if (typeof moveToLabel !== 'undefined') {thread.addLabel(moveToLabel)}
    
  });
  var rowIndex = parseInt(sh.getLastRow() + 1); // Next available row after the last row
  var colIndex = 1; // Column A
  var numOfRows = mailData.length;
  var numOfCols = mailData[0].length;
  sh.getRange(rowIndex, colIndex, numOfRows, numOfCols).setValues(mailData);
}
2 Likes