Hey everyone! 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 make copies of these and save them in a specific Drive folder without doing it manually.
I’m hoping to create a script that can:
Check my Gmail on Mondays for an email with a subject like “Hourly Seniority Report as of [date] and Hourly Termination Report as of [date]”
Find the two Sheet attachments
Make copies of them
Move the copies to a certain folder in my Drive
I’m pretty new to Google Apps Script and haven’t found much help online. Has anyone done something similar or have any tips on where to start? Thanks a bunch for any help you can give!
hey miar, ive done smthing similar before. u can use GmailApp to search for emails, then get attachments with getAttachments(). for copying sheets, DriveApp.getFileById().makeCopy() works. move files with DriveApp.getFolderById().addFile(). pm me if u need more help!
I’ve implemented a similar automation for my team’s reporting process and found that the key is to combine Gmail and Drive services carefully. In this case, you can search for the specific emails by using GmailApp.search with appropriate filters for the subject and date. Once the email is identified, you can process its attachments using getAttachments and then create a copy by utilizing DriveApp.createFile, rather than directly handling the Google Sheets. Finally, transferring the files into your desired folder via DriveApp.getFolderById ensures they are properly organized. Setting up a time-based trigger to run the script every Monday, while fine-tuning error handling and search criteria, has proven essential. Testing with a small subset before full implementation is advisable.
I’ve tackled a similar challenge in my work. Here’s what worked for me:
First, use GmailApp.search() with a date range and subject keywords to find the right email. Then, loop through the attachments with message.getAttachments(). For each attachment, use DriveApp.createFile() to make a copy in your desired folder.
A tip: Use Utilities.formatDate() to generate the correct date string for your search query. This helps ensure you’re always grabbing the most recent report.
Also, consider adding some error handling. Sometimes attachments might be missing or in an unexpected format. A try-catch block can help your script continue running even if there’s an issue with one file.
Lastly, set up a time-based trigger to run your script every Monday morning. This way, it’ll process the reports as soon as they arrive in your inbox.