How to get Google Drive PDF files with "name contains info in cell D1:D from Spreadsheet" and send them as emails to each contact in column A

Question

Disclaimer: I am new to programming so please bear with me :)

I am trying to send emails with a PDF file attachment to different email contacts on a Google Spreadsheet. Each attachment has to be unique to each Name by looking up that file name on google drive. Can someone help with that extra bit of code?

The code I have is currently sending emails with a custom message and a file attachment, and marking each email as sent in column C once the script ends running. However, I would like to send a different PDF attachment to each and every email address, ideally by writing some code that gets the "Investor Name" in column D, looks up the file in Google Drive that contains that name, and attaches that file to the email to be sent out.

So, I need to get the PDF file by each name in each corresponding cell, and not as written below (I believe):

var file = DriveApp.getFilesByName('SHARE CERTIFICATE #60 - JOHN APPLE.pdf');

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 250;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 250)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var emailSent = row[2];     // Third column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = "Sending emails from a Spreadsheet";
      var file = DriveApp.getFilesByName('SHARE CERTIFICATE #60 - JOHN APPLE.pdf');
  if (file.hasNext()) {
    MailApp.sendEmail(emailAddress, subject, message, {
    attachments: [file.next().getAs(MimeType.PDF)],
    name: 'Custom email Team'
 });
      sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}
}

The final result I expect is to: - Send Emails to different email addresses with a message and subject - Attach a PDF file with file name that correspond to the Name of each email recipient

Thanks a lot in advance, this will massively help! :)

Answer 1

Your code is missing a small detail. Basically, you should get the data from column D when using getRange:

var numRows = sheet.getLastRow() - startRow + 1; // Number of rows to process
var startCol = 1;
var numCols = 4; // Number of columns to process (should include column D)
// Fetch the range of cells A2:D
var dataRange = sheet.getRange(startRow, startCol, numRows, numCols);

Notice that the number of rows to be retrieved doesn't have to be hardcoded. If you use getLastRow you can get the index of the last row with content, and use that to get the desired range. This way you can get exactly the number of rows you want; not more, not less.

Then, inside the loop, retrieve the value in column D, like this:

var fileName = row[3];      // Fourth column

Finally, you retrieve the file from Drive like this:

var files = DriveApp.getFilesByName(fileName);

So the full modified code could be like:

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = sheet.getLastRow() - startRow + 1; // Number of rows to process
  var startCol = 1;
  var numCols = 4; // Number of columns to process (should include column D)
  // Fetch the range of cells A2:D
  var dataRange = sheet.getRange(startRow, startCol, numRows, numCols);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var emailSent = row[2];     // Third column
    var fileName = row[3];      // Fourth column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = "Sending emails from a Spreadsheet";
      var files = DriveApp.getFilesByName(fileName);
      if (files.hasNext()) {
        var file = files.next();
        MailApp.sendEmail(emailAddress, subject, message, {
          attachments: [file], // No need to use getAs if your file is already a PDF
          name: 'Custom email Team'
        });
        sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
        // Make sure the cell is updated right away in case the script is interrupted
        SpreadsheetApp.flush();
      }
    }
  }
}

I have made some additional small changes to that code (for example, changing the variable file to files and then defining var file = files.next()).

I hope this is of any help to you.

Answer 2

Something like this will work I think:

function sendEmails() {
  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getActiveSheet();
  var logsheet=ss.getSheetByName('logSheet');//added logsheet
  if(!logsheet){ss.insertSheet('logSheet');}
  var startRow=3;//added one row here to accomodate subject and pdfFldrId  
  var dataRange = sheet.getRange(startRow, 1, sheet.getLastRow()-startRow-1,sheet.getLastColumn())
  var data = dataRange.getValues();
  var subject= sheet.getRange("B2").getValue();
  var pdfFldrId=sheet.getRange("C2").getValue();
  for (var i=0;i<data.length;++i) {
    var row=data[i];
    var emailAddress=row[0];  
    var message=row[1];       
    var emailSent=row[2];     
    var pdfName=row[3];        
    var files=DriveApp.getFolderById(pdfFldrId).getFilesByName(pdfName);
    var n=0;
    while(files.hasNext()) {
      var file=files.next();
      n++;
    }
    if(n>1){
      var ts=Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss");
      logsheet.append([ts,pdfName,'more than file with this name']);
      continue;//breaks out of loop increment index and keep going
    }
    if(emailSent!="EMAIL_SENT") {

    MailApp.sendEmail(emailAddress, subject, message, {attachments:[file]});
      sheet.getRange(startRow + i, 3).setValue("EMAIL_SENT");
    }
  }
}

If you want to send a different image with the same email list just duplicate the sheet and change the pdfFldrId and the subject which are found in C2 and B2 and learn to keep the different images is different folders with the same filenames.