Search code examples
google-apps-scriptgoogle-sheetsautomationgmail

Google Script to look through an email and copy Spreadsheet attachments into Google Drive

Every Monday, I get a report sent to my Gmail that has two Google Spreadsheet attachments.

I need to open these attachments and make a copy of them into a specific folder in my Google Drive each week.

I would like a Google Script that will

1). Look through my Gmail each Monday and search for a new email with the subject: "Hourly Seniority Report as of [date] and Hourly Termination Report as of [date]" | Note: The same user sends out the report each week

2). Make a copy of the two attachments and move the copy to a Google Folder via the Folder's ID.

I'm fairly new to Google scripting and looking online, I haven't found much that could help me with this automation. Does anyone have any ideas on how to approach this? Thank you!

Weekly Email Contents

Solution

  • If the file is attached to the email

    • Find an email with an specific subject: This can be achieved with the GmailApp.search() function (you can read more about it here). Note that this will return a list of threads that each one in turn will contain a list of messages. Grabbing the first message of the first thread (my approach) will work fine as long as there are no other emails with that subject (which seems to be the case).
    • Saving an attachment into Google Drive. This can be accomplished with the createFile() function of a Folder object (you can read more about it here). You can get the desired Folder object by using the getFolderById() function (you can read more about it here).
    
function myFunction() {
  var emailSubject = ""; // YOUR EMAIL SUBJECT
  var folderId = ""; //YOUR FOLDER ID (FOUND IN URL)

  var folder = DriveApp.getFolderById(folderId);

  var thread = GmailApp.search(emailSubject)[0];
  var message = GmailApp.getMessagesForThread(thread)[0];
  var attachments = message.getAttachments();
  
  for (var i = 0; i<attachments.length; i++){
    folder.createFile(attachments[i].copyBlob());
  }
}

    If the file is a link to a shared Drive

    In this case, the file will not show up as an attachment to the email, so the same method used before cannot be employed here. However, the file will show up as shared with me in your Drive. The approach I used here is to gather all the files that are shared with you (using the searchFiles() function, you can read more about it here) and then scan the email's plain text to find whether or not each file's id was mentioned in that email (meaning it was shared).

    function myFunction() {
  var emailSubject = ""; // YOUR EMAIL SUBJECT
  var folderId = ""; //YOUR FOLDER ID (FOUND IN URL)

  var folder = DriveApp.getFolderById(folderId);

  var thread = GmailApp.search(emailSubject)[0];
  var message = GmailApp.getMessagesForThread(thread)[0];
  
  var files = DriveApp.searchFiles("sharedWithMe = true");

  while (files.hasNext()) {
    var file = files.next();
    if (message.getBody().includes(file.getId())){

      folder.createFile(file.getName(), file.getBlob(), file.getMimeType());
    }
  }

}

    Of course, you can make this script a Time bound trigger script so that it will execute once a day if you wish so. In that case, the date of the execution should be grabbed dynamically by the script to gather the correct email each day. You can read more about triggers here.