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

This Apps Script will monitor your Gmail for new emails that match a certain label, and:

  • Add a New Row to GSheet (Time, Sender, Subject, Body)
  • Remove the email from the Inbox
  • Remove the label
  • Optionally add a new label to processed emails

This lets you define the email-matching logic using Gmail’s search operators, instead of having to edit the script code. The only thing you have to edit is the label-name to monitor, and the GSheet_ID.

SCRIPT
// #########################################################
//     DOWNLOADS NEW GMAILS WITH MATCHING LABEL TO SHEET

// 1. Create filter rule in Gmail to apply custom named 'label' 
// 2. Replace 'YOUR_CUSTOM_LABEL' below with the new label name
// 3. Replace 'GSHEET_ID' with the ID from the sheet URL
// 3. Save script and click run, then authorize 
// 4. Install timed trigger for script

// Matching emails are downloaded, and then the label is removed

// Script by GreenFlux, LLC | www.greenflux.us
// #########################################################

function emailToSheet() {
var label = GmailApp.getUserLabelByName('YOUR_CUSTOM_LABEL'); // <-- RENAME TO YOUR CUSTOM FILTER LABEL
var ss = SpreadsheetApp.openById('GSHEET_ID'); //  <-- INSERT GSHEET_ID
var sh = ss.getSheetByName("Email");  //  <-- RENAME TO SAVE TO DIFFERENT SHEET
//var moveToLabel = GmailApp.getUserLabelByName('MOVE_TO_LABEL'); // <-- Uncomment to move to new label after download
var threads = label.getThreads();

for (var i=0; i<threads.length; i++){
var messages = threads[i].getMessages();

for (var j=0; j<messages.length; j++)  {
  var sent = messages[j].getDate();
  var sender = messages[j].getFrom();
  var subject = messages[j].getSubject();
  var body = messages[j].getPlainBody();

  ss.appendRow([sent, sender, subject, body])
}
  threads[i].removeLabel(label);
  threads[i].moveToArchive();
  threads[i].markRead();
  if (typeof moveToLabel !== 'undefined') {threads[i].addLabel(moveToLabel)}

}
}
14 Likes

Seems very Cool. Dont need it but will of course watch and learn :slight_smile:

2 Likes

It will be a lot more useful once I figure out how to auto-download attachments to GDrive, and save the links to the sheet, too. Maybe @LeventK can help me figure out that part :slight_smile:

2 Likes

@GreenFlux
For sure Joseph, would like to help.

3 Likes

@GreenFlux I actually have need for this exact functionality in a build I’m working on right now! partyparrot (Appsheet)

This is actually what I need, I found the following script online for PDFs:

Download Gmail Attachments to Google Drive with Apps Script
From: https://www.labnol.org/code/20617-download-gmail-attachments-to-google-drive

export const saveGmailtoGoogleDrive = () => {
  const folderId = 'Google_Drive_Folder_Id';
  const searchQuery = 'has:attachments';
  const threads = GmailApp.search(searchQuery, 0, 10);
  threads.forEach(thread => {
    const messages = thread.getMessages();
    messages.forEach(message => {
      const attachments = message.getAttachments({
          includeInlineImages: false,
          includeAttachments: true
      });
      attachments.forEach(attachment => {
        Drive.Files.insert(
          {
            title: attachment.getName(),
            mimeType: attachment.getContentType(),
            parents: [{ id: folderId }]
          },
          attachment.copyBlob()
        );
      });
    });
  });
};
2 Likes

@MultiTech_Visions
This code uses Drive API, not built-in DriveApp. FYI.

1 Like

No idea how that works; literally a copy/paste. :wink:

The semicolons at the end of the thing has got me mixed up; how’s that work! haha


@LeventK how would you include something to copy PDFs and files into a Gdrive folder then? Is there something inherently more difficult about using the Drive API?

If you explicitly know what to do, then no. The processes and the parameters are a bit different then built-in DriveApp actually. No matter what, Drive API is a bit faster than DriveApp class sometimes. However your code block works if and only if you are using an add-on to save your gmails to gDrive.

const folderID = "Some_Folder_ID";
const sheetID = "Some_GSheet_ID";
var URLs = [];

function saveFileURLs2Sheet() {
  var files = DriveApp.getFolderById(folderID).getFiles();
  
  while (files.hasNext()) {
    var file = files.next();
    URLs.push([file.getName(), file.getUrl()]); //Get Filename and File URL into an array
  };
  
  var sSht = SpreadsheetApp.openById(sheetID);
  var sht = sSht.getSheetByName("A_Sheet_Name_To_Record_File_URLs");
  var rowIndex = 1; //Row#1
  var colIndex = 1; //Column A
  sht.getRange(rowIndex, colIndex, URLs.length, URLs[0].length).setValues(URLs);
}
2 Likes

The wizard is in!!! :mage:

So the URLs variable is an array, right; when that’s written to a column is that written like a list would be in appsheet?

Like:
entry 1 , entry 2 , entry 3

or more appropriately:

www.url1.com , www.url2.com , www.url3.com

???

Yes, that’s correct. To records values to a range (i.e. A1:A100 or A1:B100), the values parameer shall be an array. So in the sheet the code would create something like this:

It’s also possible to pre-filter the files iterator as per MIME_Type i.e. PDF, png etc.

1 Like

Let’s say that an email comes in that has 3 PDFs, and I’ve created an array of all 3 filenames:

  • how can I get that array of file names to print into a column
    • like it would if the column was an EnumList from appsheet?

My hope is to create an enumlist of just that: all the file names - that could then be used later.

@MultiTech_Visions

sht.getRange(rowIndex, colIndex, 1, 1).setValues(URLs.toString());

Literally; ArrayName.toString() will create a comma seperated list of items

2 Likes

Things like this… open up a whole new ballgame. partyparrot (Appsheet)

Thank you my friend!

2 Likes

You’re very welcome Matt, my pleasure.

1 Like

Okay @LeventK, if I could impose upon you for some debugging… :slight_smile:

I’ve created a variation of what @GreenFlux originally posted, with the hopes of migrating all PDF attachments into a google drive folder, as well as adding a list of all the file names into the row created by the script.

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(thread => {
	var messages = thread.getMessages();
	
	messages.forEach(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 = [];
		
		attachments.forEach(attachment => {
			var file = DriveApp.createFile(attachment.getName(), attachment.copyBlob(), attachment.getContentType());
			file.moveTo(destinationFolder);
			var newFilePath = filepath + '/' + attachment.getName();
			
			attachmentNames.push([attachment.getName()]);
			
		});
		
		ss.appendRow([sent, sender, subject, body, attachmentNames.toString()])
	});
	
  thread.removeLabel(label);
  thread.moveToArchive();
  thread.markRead();
  if (typeof moveToLabel !== 'undefined') {thread.addLabel(moveToLabel)}

});
}

.forEach() seems super clean, I hope I’m using it right. I tried to find some documentation in the Apps Script documentation, but found very little actual documentation - only uses that I could infer from.

1 Like

@LeventK and @MultiTech_Visions , you guys have been busy!

I posted this morning with the hopes of eventually figuring out the attachment thing, and I come back to find all the hard work done for me! :grinning_face_with_smiling_eyes:

This is seriously an awesome community. Thank you for your help, my friends! :raised_hands:

1 Like

I was procrastinating because I knew there was a TON of stuff I needed to figure out - for extracting emails, getting them into the app, dealing with PDFs.

And here I drop into the community to see what’s up, and @GreenFlux has solved half my problems already! Then @LeventK fills in the rest (and MORE)!

This community is… da bomb! partyparrot (Appsheet)

1 Like

@MultiTech_Visions
Matt,

Change this line > attachmentNames.push([attachment.getName()]);

To this > attachmentNames.push(attachment.getName());

attachment is already declared as an array, and .push adds an element the end of an array. So; literally no need to push an array inside an array if you are pushing only a single item.

Basically I prefer the code like this, as it’s more robust and faster:

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();
  var  mailData = [];

  threads.forEach(thread => {
                  var messages = thread.getMessages();
  
  messages.forEach(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 = [];
  
  attachments.forEach(attachment => {
                      var file = DriveApp.createFile(attachment.getName(), attachment.copyBlob(), attachment.getContentType());
  file.moveTo(destinationFolder);
  var newFilePath = filepath + '/' + attachment.getName();
  
  attachmentNames.push(attachment.getName());
  
});

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 row after the last row in the table
var colIndex = 1 // Column A;
var numOfRows = mailData.length;
var numOfCols = mailData[0].length;
sh.getRange(rowIndex, colIndex, numOfRows, numOfCols).setValues(mailData);
}
1 Like

@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);
}
1 Like

This is brilliant.