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.
// #########################################################
// 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)}
}
}
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!
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:
A | B | |
---|---|---|
1 | ABLE3_Logo.png | https://drive.google.com/file/d/0Bx2h-c-vm5LeZVZqMWVGZm9Ebmc |
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:
My hope is to create an enumlist of just that: all the file names - that could then be used later.
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.
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);
}
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!
@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
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
Apologies I am new to this but this script is exactly what I am looking for as I try to learn Apps Script. I can access the video but where is the full script ???
Really sorry to ask a very silly question.
dgoug
It also took me a second to figure out, but all you have to do is click the SCRIPT Text, and the script will appear.