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)}

}
}
20 33 5,175
33 REPLIES 33

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

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

@GreenFlux
For sure Joseph, would like to help.

@GreenFlux I actually have need for this exact functionality in a build Iโ€™m working on right now! 3X_d_5_d51363a862e7ab883241c312ac5d7f271579cdd3.gif

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()
        );
      });
    });
  });
};

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

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

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);
}

The wizard is in!!!

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.

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

Things like thisโ€ฆ open up a whole new ballgame. 3X_d_5_d51363a862e7ab883241c312ac5d7f271579cdd3.gif

Thank you my friend!

Youโ€™re very welcome Matt, my pleasure.

Okay @LeventK, if I could impose upon you for some debuggingโ€ฆ

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.

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

@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!

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

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! 3X_d_5_d51363a862e7ab883241c312ac5d7f271579cdd3.gif

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

One last question and Iโ€™ll leave you be; what is the purpose of the โ€œparseInt()โ€ - couldnโ€™t you just do the math part?
Iโ€™m betting thereโ€™s a good reason that Iโ€™m completely unaware ofโ€ฆ

Eventhough .getLastRow() class returns an integer, as + (plus) sign is considered to be a string concatenator in JS, the return value therefore will be (assuming the last row is 500 for ex.) 5001 instead of 501. To ensure that you need to wrap it with parseInt function.

This is brilliant.

@MultiTech_Visions
I have a little bit enhanced that line and updated my post. FYI.

Indeed; thanks for the tap on the shoulder!

Youโ€™re welcome

Awesome post! Thanks for sharing. The AppScript integration has relevance to most AppSheet apps and is definitely worth learning.

Opens potential for texting through emails for Google voice in Appsheet! Thanks

I know this is a long shot, but would any of the contributors to this thread know what could be causing this error?

ReferenceError: mailData is not defined
emailToSheetWithAttach

@ Code.gs:57

The only thing that appears to work when I run the code is it deletes the thread.

The at part is telling you the line in your code in which the error sits. So if you go back to your apps script editor and fine line number 57, thereโ€™s something starting from there that has a problem.

For further debugging and things, you should really make your own post about your issueโ€ฆ and then reference to this one. (That way informative posts arenโ€™t taken over with personal minutia.)

@GreenFlux Thanks muchly for the script, I've got it to work however I'm curious how you're handling the fact your script is appending a new row for every message in the thread, and every thread is downloaded in its entirety every time the label is applied. Hence you get these cascading duplicate rows every time the email goes back  and fourth

  1. sent 1st message
  2. Replied 1st message
  3. sent 1st message
  4. Replied 2nd message
  5. Replied 1st message
  6. sent 1st message

 

This was very useful, thank you! ๐Ÿ’œ Will try to add a line that moves my email from its original label to a new one so the mail does not simply get "unlabelled" ^^

I know this post is old but it seems to fit something we need. However I don't seem to be able to find the script the post is referring to. Has it been removed from the post?

So I have a script that runs that automatically saves all attachments to my GDrive in a folder called attachments, however each time it runs I get a different folder every time it runs called attachments, its nice if I need to distinguish what day the attachments came in, but what if I dont need but one folder, how do I stop it from creating the same folder each time?

 

The recent adding of app script possibilities to appsheet open new doors? Maybe a bot can now monitor the inbox ?

 

Thanks

 

Top Labels in this Space