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.
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!
GreenFlux:
once I figure out how to auto-download attachments to GDrive
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?
MultiTech_Visions:
@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!!!
LeventK:
.setValues(URLs);
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
???
MultiTech_Visions:
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?
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.
LeventK:
To record values to a range ( i.e. A1:A100 or A1:B100 ), the values parameter shall be an array.
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.
MultiTech_Visions:
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?
@MultiTech_Visions
sht.getRange(rowIndex, colIndex, 1, 1).setValues(URLs.toString());
Literally; ArrayName.toString() will create a comma seperated list of items
LeventK:
URLs.toString()
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.
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:
auto> 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()])> >
@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);
}
@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);
}
LeventK:
sh.getRange(1, 1, mailData.length, mailData[0].length).setValues(mailData);
This is brilliant.