I am trying to send Google Drive Attachments to different email addresses using a script but struggling to make it work

Question

I am new to coding and wanted to automate sending emails with attachments. Basically what I would like to do is to send a message and Google Drive attachment to a specific email address in my spreadsheet. Each attachment (google drive link) is different for each email address and I have everything laid out on a spreadsheet.

The spreadsheet is composed of three sheets, and I want to get the info from the first sheet only (called Full Data). The structure of the columns is the following:

A1 Full Name
B1 First Name
C1 Last Name
D1 Email Address
E1 File attachment (google drive link)
F1 Date
G1 Status
H1 Message
I1 Email Sent?

I would like to send an email with a template starting with "Hello {Full Name}," and in the row below starts the message.

Here's the code I have for now:

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

/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  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);
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[3]; // Third column
    var message = row[7]; // Eigth column
    var emailSent = row[8]; // Ninth column
    if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
      var subject = 'Sending emails from a Spreadsheet';
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 8).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

I realize that I have not set a variable for the attachment. The attachment should be taken from column E (corresponding to the row). For example, John Apple in A2 has attachment X in E2, while Mick Orange in A3 has attachment Y in E3, and so on... The range of data goes down till 130 rows but it would be great to have something that always gets the range until there is data (no matter the number of rows).

Does anyone have an idea on how to make this work?

Thanks a lot in advance!!

Is there a fundamental difference between this question and your previous one? I assume, like in previous question, that for each row, you want to look for a file of that name in Drive and send that file as an attachment? — Iamblichus
The difference is that now I managed to get all the attachment links, names, message etc all aligned. In my previous question perhaps I have not been very clear and what I wanted to do there was a little bit different.. — FiloPietra

Iamblichus Iamblichus · Accepted Answer · 2019-11-13T15:20:23

If I understand you correctly, your current question differs from your previous one in that:

The message body starts with "Hello {Full Name}".
You are not looking for files with the same name as in the sheet, but files that have a certain URL.
The columns to fetch the data from are not the same.

Taking all this into account, I reworked the solution I proposed in your previous question so that it does what you pretend now (read inline comments to see what the code is doing, step by step):

var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Full Data");
  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 = 9; // 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[3];
    var fullname = row[0];
    var messageBody = row[7];
    var message = "Hello ".concat(fullname, "\n", messageBody); // Creating message body
    var emailSent = row[8];
    var fileURL = row[4];
    var fileId = fileURL.match(/[-\w]{25,}/); // Extracts file id from file url
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = "Sending emails from a Spreadsheet";
      var file = DriveApp.getFileById(fileId);
      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, 9).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

DriveApp does not have an getFileByUrl method, so to get the file, the code first extracts the file id from the url using regex and then calls getFileById.

Let me know if that works for you.

I am trying to send Google Drive Attachments to different email addresses using a script but struggling to make it work

1 Answers