Google Spreadsheet - How to avoid sending email duplicates?

Question

I am having an issue with a script. I used the following script from Google Developers Website in order to do a simple merge mail. See https://developers.google.com/apps-script/articles/mail_merge

I modified a bit the script so to prevent email duplicates. However, even if the script seems to work as it marks 'EMAIL_SENT' in each row every time an email is sent. It does not pay attention if the mail as already been marked and still send the mail.

I believe there is an error at line 16 "var emailSent = rowData[6];"

I would really appreciate if someone could help me. Whoever you are thanks in advance.

Here is the modified script :

var EMAIL_SENT = "EMAIL_SENT";

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheets()[0];
  var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 7);
  var templateSheet = ss.getSheets()[1];
  var emailTemplate = templateSheet.getRange("A2").getValue();
  var objects = getRowsData(dataSheet, dataRange);
  for (var i = 0; i < objects.length; ++i) {
    var Resume = DriveApp.getFilesByName('Resume.pdf') var Portfolio = DriveApp.getFilesByName('Portfolio.pdf') var rowData = objects[i];
    var emailText = fillInTemplateFromObject(emailTemplate, rowData);
    var emailSubject = "Architectural Internship";
    var emailSent = rowData[6];
    if (emailSent != EMAIL_SENT) {
      MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText, {
        attachments: [Resume.next(), Portfolio.next()]
      });
      dataSheet.getRange(2 + i, 7).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
    }
  }
}

function fillInTemplateFromObject(template, data) {
  var email = template;
  var templateVars = template.match(/\${\"[^\"]+\"}/g);
  for (var i = 0; i < templateVars.length; ++i) {
    var variableData = data[normalizeHeader(templateVars[i])];
    email = email.replace(templateVars[i], variableData || "");
  }
  return email;
}

function getRowsData(sheet, range, columnHeadersRowIndex) {
  columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
  var numColumns = range.getEndColumn() - range.getColumn() + 1;
  var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(range.getValues(), normalizeHeaders(headers));
}

function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}

function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue;
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

// Returns true if the cell where cellData was read from is empty. // Arguments: // - cellData: string function isCellEmpty(cellData) {
return typeof(cellData) == "string" && cellData == "";
}

// Returns true if the character char is alphabetical, false otherwise. function isAlnum(char) { return char >= 'A' && char <= 'Z' || char >= 'a' && char <= 'z' || isDigit(char); }

// Returns true if the character char is a digit, false otherwise. function isDigit(char) { return char >= '0' && char <= '9'; }

Serge insas Serge insas · Accepted Answer · 2014-08-12T08:17:43

Your code is really hard to read and the functions that return 2 or more objects make it even harder...you are using variable names that are also a bit confusing.... but that is probably a personal pov :-)

Anyway, I think I've found the issue: when you write var rowData = objects[i];

This "object" is actually the result of the getRowData function but if you look at this function, you'll see that it returns 2 objects, the first one being itself the result of another function (getObjects) ...

You are checking the value is the 6th element of the array which is actually an object and compare it to a string. The equality will never be true.

I didn't go further in the analyse since I found it really confusing ( as I already said) but at least you have a first element to check .

I would suggest you rewrite this code in a more simple way and use more appropriate variable names to help you while debugging.

Google Spreadsheet - How to avoid sending email duplicates?

3 Answers