0

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
Yunnosch
  • 26,130
  • 9
  • 42
  • 54
Sergio Isac
  • 15
  • 3
  • You seem to have accidentally deleted most of the information in your post, while still referring to it with "this script" in what remains. I undid that for you. Generally please do not edit questions after you got an answer to it. The question should stay as complete and answerable as it was before the answer. Including the helpful info you initially provided. – Yunnosch May 18 '21 at 09:06

1 Answers1

0

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

iansedano
  • 6,169
  • 2
  • 12
  • 24