2

I am trying to create a script where the user gets an email (column 1) with the body being column 2. However, after that, I want to replace the "@" in the email with a "#" so that they do not get the email again.

I know that I could have the script use the last active row, however, I cannot do that because I have formulas in the remaining rows so it thinks it is active.

Here's what I have:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2000;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var subject = "Quiz Results";
    MailApp.sendEmail(emailAddress, subject, message);
  }
}
1

If I understand correctly, you want to replace @ with # so that MailApp.sendEmail won't send them an email again. This makes sense (although I would add a separate column "Processed" for this purpose), but the for loop should not call the API a thousand times needlessly, passing in an invalid email string.

By the way, it's not recommended to use "for .. in .." with arrays. This is what I would do:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process; put 2000 here later
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  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 subject = "Quiz Results";
    if (/@/.test(emailAddress)) {
      // MailApp.sendEmail(emailAddress, subject, message); // commented out for debugging
      Logger.log([emailAddress, subject, message]);  // Logs what would be sent, for debugging
      data[i][0] = emailAddress.replace('@', '#');
    }
  }
  dataRange.setValues(data); 
}
  • Thanks for your response. I am getting error ReferenceError: "data" is not defined. (line 3, file "Code") with your script. How do I fix this? – Jared Dec 31 '15 at 21:18
  • The line "as in your code" is not meant to be copy posted... It just says that part of the code status as it was. – user79865 Dec 31 '15 at 21:22
  • I'm sorry I'm not really understanding what that means. – Jared Dec 31 '15 at 21:28
  • This means: keep the part before the for loop as it is in your code. My changes are only in the loop. – user79865 Dec 31 '15 at 21:35
  • I'm still getting the same error just referring to a different line now. Essentially, what I am trying to do is create a multiple choice test. The user receives the test results (which are in a cell from a concatenate formula) via email. Only problem is, because the concatenate formula is in every row, I can't use the last row only. Does this change anything? – Jared Dec 31 '15 at 21:51
1

This is what you can do:

  • Go through all the rows and find the first row with valid e-mail address
  • Continue until you find an invalid e-mail address
  • Re-retrieve all the data in the range and change "@" to "#"

This way, you can keep adding new rows and the code will still work. And the code shouldn't mess with any formulas you might have at bottom of the sheet.

Or, just use this code:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var lastRow = sheet.getLastRow();  // Last row with content

  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, lastRow - startRow + 1, 2);
  // Fetch values for each row in the Range.

  var list_start = -1; // index of first valid e-mail address
  var list_end = -1; // index of last valid e-mail address

  var subject = "Quiz Results";
  var data = dataRange.getValues();

  // can't use
  //     for (i in data)
  // because we want i to be integer
  for (i = 0; i < data.length; i++) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    if (emailAddress.search("@") > 0) {
      MailApp.sendEmail(emailAddress, subject, message);
      if (list_start < 0) {
        list_start = i;
      }
    } else {
      if (list_start >= 0) {
        list_end = i-1;
        break;
      }
    }
  }

  if ((list_start >= 0) && (list_end < 0)) {
    // we reached the end of list and didn't find an invalid address
    list_end = lastRow - startRow - list_start + 1;
  }

  // convert all @ to #
  if (list_start > 0) {
    // this time, get only one column
    dataRange = sheet.getRange(startRow + list_start, 1, (list_end - list_start) + 1);
    data = dataRange.getValues();
    for (i in data) {
      data[i][0] = data[i][0].replace("@","#");
    }
    dataRange.setValues(data);
  }

}

The code uses simple e-mail address validation. For better result, you might want to create a hidden column and use the isemail() function to check for valid e-mail addresses.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.