Search code examples
emailgoogle-apps-scriptattachmentemail-attachments

Apps Script - add attachment to emailconfirmation

With this script I am releasing two different emails (index1 or index 2) depending on some fields in sheets but I needed to add a .docx file from google drive as an attachment to the released email

This is the script I have so far and it is working I just need to add the docx as attachment file from google drive.. Please helpp

//Pulling all data from spreadsheet variables
var SpreadsheetID = "sheet id";
var SheetName = "namesheet";
var now = new Date();
var ss = SpreadsheetApp.openById(SpreadsheetID)
var sheet = ss.getSheetByName(SheetName);
var range = sheet.getDataRange();
var data = range.getValues();
 
function project_Notification() {
 
 Logger.log(sheet.getLastRow() + " Is the last Row.");
 for (var row = 1; row < sheet.getLastRow(); row++) {
   var Notification = (data[row][12])
   var Email_sent = (data[row][13])
   var Admin_email = (data[row][5])
   if (Notification == "Yes" && Email_sent == "" && Admin_email != "") {
     Logger.log(Notification)
     SendEmailConfirmation(data[row][5], data[row][1], data[row][14], data[row][3], data[row][6], data[row][9], data[row][10], data[row][11])
     var Notification_cell = sheet.getRange("N" + (row + 1));
     Logger.log("N" + (row + 1))
     var Today = Utilities.formatDate(new Date(), "GMT", "dd/MM/yyyy");
     Notification_cell.setValue(now);
   }
 }
}
 
function SendEmailConfirmation(email, Company, Admin_Name, Manager_Email, Landing_Page, QL_code, QL_url, PS_code) {
 if (email != "" && Landing_Page != "") {
     Logger.log(email)
 
     var admin = {
       "name": Admin_Name,
       "company": Company,
       "landingPage": Landing_Page,
       "QLcode": QL_code,
       "QLurl": QL_url,
       "PScode": PS_code
     };
 
     var html1 = HtmlService.createTemplateFromFile('Index1.html');
       html1.name = admin.name;
       html1.comp = admin.company;
       html1.landingPage = admin.landingPage;
       html1.qlcode = admin.QLcode;
       html1.qlurl = admin.QLurl;
     var html2 = HtmlService.createTemplateFromFile('Index2.html');
       html2.name = admin.name;
       html2.comp = admin.company;
       html2.landingPage = admin.landingPage;
       html2.qlcode = admin.QLcode;
       html2.qlurl = admin.QLurl;
       html2.pscode = admin.PScode;
 
     var aliases = GmailApp.getAliases();
     Logger.log(aliases);
     if (aliases.length > 0 && Landing_Page == "Product1") {
       GmailApp.sendEmail(email, "Get started with your trial on Product1", '', {
         'from': "product1@gmail.com",
         replyTo: "product1@product.com",
         cc: Manager_Email,
         htmlBody: html1.evaluate().getContent()
       });
     } else if (Landing_Page == "Product2") {
       GmailApp.sendEmail(email, "Get started with your trial on Product2", '', {
         'from': "product2@gmail.com",
         replyTo: "product2@product.com",
         cc: Manager_Email,
         htmlBody: html2.evaluate().getContent()
       });
     }
     else {
       GmailApp.sendEmail("products-admins@gmail.com", 'Script error', 'Please check the Products overview sheet for errors.');
     }
 }
}
//Send email for a confirmation that the script run correctly
Solution

  • How to send attachments from Drive via Email with Apps Script.

    There is an attachments advanced parameter for GmailApp.sendEmail(). The parameters are:

    GmailApp.sendEmail(recipient, subject, plainTextBody, options);

    Using your example, the current state of the script is:

    GmailApp.sendEmail(email, "Get started with your trial on Product1", '', {
         'from': "product1@gmail.com",
         replyTo: "product1@product.com",
         cc: Manager_Email,
         htmlBody: html1.evaluate().getContent()
       });

    Within options you can use many advanced parameters. You are using:

    {
         'from': "product1@gmail.com",
         replyTo: "product1@product.com",
         cc: Manager_Email,
         htmlBody: html1.evaluate().getContent()
}

    To this you can add the attachments parameter as list of BlobSource.

    To get the blob source from a Drive file, you will need to obtain the ID of this file, once you have it, you can get the blob like this:

    const file = DriveApp.getFileById("[FILE_ID]")
const blob = file.getBlob()

    Just remember to include it in an array of blobs when you pass it to sendEmail, like this:

    const file = DriveApp.getFileById("[FILE_ID]")
const blob = file.getBlob()
GmailApp.sendEmail(email, "Get started with your trial on Product1", '', {
         'from': "product1@gmail.com",
         replyTo: "product1@product.com",
         cc: Manager_Email,
         htmlBody: html1.evaluate().getContent(),
         attachments: [blob]
       });

    EDIT

    To do various attachments you can just include more in the list:

    const file1 = DriveApp.getFileById("[FILE_ID1]")
const file2 = DriveApp.getFileById("[FILE_ID2]")
const blob1 = file1.getBlob()
const blob2 = file2.getBlob()
GmailApp.sendEmail(email, "Get started with your trial on Product1", '', {
         'from': "product1@gmail.com",
         replyTo: "product1@product.com",
         cc: Manager_Email,
         htmlBody: html1.evaluate().getContent(),
         attachments: [blob1, blob2]
       });

    References