Google Apps Script - How to get email addresses from Sheet2 and send email

Question

I have a Spreadsheet that stores Task on on Sheet 1. When a task is completed a email is sent. Sheet 2 holds the specific email addresses for send to, cc, and reply to. I am able to loop through sheet 2 and get the email addresses for each column. I want to be able to get those email address into my options for the send email in MailApp. I am unable to get the email addresses out of the loop for sheet 2. See code below. Any help would be appreciate.

var EMAIL_SENT = "EMAIL_SENT";

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheets()[0];
  var sheet2 = ss.getSheets()[1];  

  var startRow = 2;
  var lastRow1 = sheet1.getLastRow()-1;
  var lastCol1 = sheet1.getLastColumn();
  var lastRow2 = sheet2.getLastRow()-1;
  var lastCol2 = sheet2.getLastColumn();

var sh1Range = sheet1.getRange(startRow, 1, lastRow1, lastCol1).getValues();
var sh2Range = sheet2.getRange(startRow, 1, lastRow2, lastCol2).getValues();
var subject = "Test Email";
//sheet2 Email Loop
for (var i = 0; i < sh2Range.length; ++i){
  var emails = sh2Range[i];
  var to = emails[0];
  var cc = emails[1];
  var replyTo = emails[2];
 }
//sheet1 Data Loop and Send Email
for (var j = 0; j < sh1Range.length; ++j){
  var data = sh1Range[j];
  var pName = data[0];
  var pID = data[1];
  var pm = data[2];
  var dd = Utilities.formatDate(new Date(data[3]), "America/New_York", "MMMM dd, yyyy");
  var team1 = data[4];
  var status = data[7];
if (team1 == "Task Completed" && status !== EMAIL_SENT){
  var htmlBody;
  htmlBody = "Project Name: "+pName+"<br>"+"Project ID: "+pID+"<br>"+"Project Manager: "+pm+"<br>"+"Due Date: "+dd+"<br>";
  var optAdvancedArgs = {replyTo: replyTo, cc: cc, name: "Test Email", htmlBody: htmlBody};
  //Logger.log(htmlBody);
  //Logger.log(optAdvancedArgs);
    }
  }
}

Question, are sh2Range.length and sh1Range.length the same value? if so it might be easier to build it all into one 'for' loop, otherwise you'll be looking at creating objects (Or possibly arrays, but I believe that's bad practice for named values.) — HDCerberus
sh2Range.length loops the values in sheet 2 and sh1Range.length loops the values in sheet 1. Are you saying the variable names should be changed? Can you show an example to loop through sheet1 and sheet2 in one for loop? — WallyG
Sorry, for clarification, I mean, are sh2Range and sh1Range the same length? It seems to me as though they should be, and if they consistently are, then it would be easier for you to loop through everything in one. — HDCerberus
Maybe if you see the sample sheet you can see what I am trying to achieve. My Sheet — WallyG

HDCerberus HDCerberus · Accepted Answer · 2014-11-19T02:07:13

The question is a little broad, and there are many ways to do what you're looking for. It's tough to say what you should choose when the specifics of your application aren't clear, such as how many addresses are going to be on Sheet 2, and if you really need to go through an 'if' statement, but I'll offer two potential solutions here that may be of use.

Solution 1: What I was trying to ascertain in my comments above was if the email addresses list would be the same length as the list of projects (For example, if there are 10 projects on sheet 1, does that mean there are 10 rows of email addresses in sheet 2). It's still not clear to me, as you have a list of email addresses in your 'CC' address, yet only one in your 'Send to' and 'Reply to' addresses (so you don't really need to loop through the whole range, but perhaps that's required for some other part of the project). However, if that IS the case, then you can actually do all of this in one 'for' loop:

//sheet1 Data Loop and Send Email
for (var j = 0; j < sh1Range.length; ++j){
  var data = sh1Range[j];
  var pName = data[0];
  var pID = data[1];
  var pm = data[2];
  var dd = Utilities.formatDate(new Date(data[3]), "America/New_York", "MMMM dd, yyyy");
  var team1 = data[4];
  var status = data[7];
if (team1 == "Task Completed" && status !== EMAIL_SENT){
  var emails = sh2Range[i];
  var to = emails[0];
  var cc = emails[1];
  var replyTo = emails[2];
  var htmlBody;
  htmlBody = "Project Name: "+pName+"<br>"+"Project ID: "+pID+"<br>"+"Project Manager: "+pm+"<br>"+"Due Date: "+dd+"<br>";
  var optAdvancedArgs = {replyTo: replyTo, cc: cc, name: "Test Email", htmlBody: htmlBody};
  //Logger.log(htmlBody);
  //Logger.log(optAdvancedArgs);
    }
   }

Solution 2: Otherwise, you could place the email addresses into an array when you loop through them:

 var addresses = [];

for (var i = 0; i < sh2Range.length; ++i){
  var emails = sh2Range[i];
  var to = emails[0];
  var cc = emails[1];
  var replyTo = emails[2];
  addresses.push([to,cc,replyTo])
 }

Which will then allow you to access them using the bracket notation 'addresses[0][0]' later in the script:

 var optAdvancedArgs = {replyTo: addresses[0][2], cc: addresses[j][1], name: "Test Email", htmlBody: htmlBody};

Both these solutions make a lot of presumptions on what you're doing, but they might guide you to either the answer you're looking for, or possible give you a more concrete idea of what kind of question you're asking.

Google Apps Script - How to get email addresses from Sheet2 and send email

1 Answers