Hey folks, I’m trying to set up an automatic process for my weekly reports. Every Monday, I get an email with two Google Sheets attached. I want to save copies of these sheets to a specific folder in my Drive without doing it manually each time.
Basically, I need a script that can:
- Check my inbox on Mondays
- Find the email with a title like “Hourly Seniority Report…and Hourly Termination Report…”
- Grab the two spreadsheets from that email
- Make copies and put them in a certain Drive folder
I’m pretty new to Google Scripts and haven’t had much luck finding help online. Any tips on how to tackle this? Thanks a bunch!
Here’s a simple example of what I’m thinking:
function backupWeeklyReports() {
var inbox = GmailApp.getInboxThreads();
var targetFolder = DriveApp.getFolderById('your_folder_id_here');
for (var i = 0; i < inbox.length; i++) {
var thread = inbox[i];
var subject = thread.getFirstMessageSubject();
if (subject.includes('Hourly Seniority Report') && subject.includes('Hourly Termination Report')) {
// Code to handle attachments and copying to Drive
}
}
}
This is just a starting point, of course. I’d need help filling in the details. Any ideas?
I’ve tackled a similar challenge in my work. One thing that really helped was using GmailApp.search() with specific query parameters. You can combine date and subject filters like this: ‘subject:(Hourly Seniority Report Hourly Termination Report) after:2023/11/06 before:2023/11/07’. This narrows down your search significantly.
For handling attachments, I found the Attachment class in GmailApp quite useful. You can use getAs() method to check if it’s a Google Sheet before proceeding. Then, use DriveApp.createFile() to create a copy in your target folder.
A word of caution: watch out for API quotas. If you’re dealing with large files or many emails, you might hit limits. I learned this the hard way! Consider adding some Utilities.sleep() calls if you’re processing multiple files.
Lastly, don’t forget to set up a time-based trigger to run your script every Monday. It’s a game-changer for consistency.
heyy Tom, I’ve done smthing similar before! u can use GmailApp.search() to find emails by subject and date. then loop thru attachments, check if theyre sheets, and use Drive.Files.copy() to duplicate em. might need to enable advanced Drive API tho. good luck man!
I’ve implemented a similar automation for my team. In my experience, it helps to first narrow down the search for emails by specifying a date range that targets Monday’s mail. Using regular expressions to match the email subject can then filter the right messages. When handling attachments, check their type to ensure you’re working with Google Sheets before copying them with DriveApp methods. Finally, setting up a trigger to run this script automatically each week and adding proper error handling can streamline the process considerably.