function myFunction() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("Welcome");
var targetSheet = ss.getSheetByName("Done");
var startRow = 2;
var lr = sheet.getLastRow();
var dataRange = sheet.getRange(startRow, 1, lr-1, 6);
var data = dataRange.getValues();
var colNumber = sheet.getLastColumn()-1;
var delRows = [];
for (var i = 0; i < data.length; i++) {
var row = data[i];
var id = row[0];
var emailAddress = row[1];
var date = row[2];
var city = row[3];
var bccmail = row[6];
var Sender = 'XXXXXX';
var reply = 'xxxxxx@xxxxxxx.com';
if (emailAddress.match('@') === null){
continue;
};
var subject = row[4];
var message = "Hey " + id + ", welcome in the team " + row[5];
MailApp.sendEmail(emailAddress, subject, message, {bcc: bccmail,name: Sender,replyTo: reply});
var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);
var sourceRange = sheet.getRange(i+startRow, 1, 1, colNumber);
sourceRange.copyTo(targetRange);
delRows.push(i+startRow);
}
delRows.reverse().forEach(ri=>{sheet.deleteRow(ri)});
Almost all the script works fine. When it comes to sendEmail, I have tried to follow these guidelines and use sendEmail(recipient, subject, body, options). 2 out of 3 options work fine but BCC doesn't work at the moment. Do you know what I am doing wrong? Can BCC be a variable?
The problem is in this line:
var bccmail = row[6];
dataRange
is defined as a range with only 6 columns. data
is a 2D array with the values of dataRange
. row
is a 1D array with a single row of data
. JavaScript array indexes only start at 0, so the values are in row[0]
to row[5]
.
Please check your sheet in which column does the bcc string is defined and count the index from 0.
Reference: