I am currently using Google Appscript to send automatic mails. I have the option to include people in copy (CC), however, wanted to know if there's any way to fetch this data from each row corresponding to each person. Basically, each email sent should have different people in CC. I tried adding a code to get values from a column, but it's keeping only the first person in CC for all emails. I'm attaching a copy of the sheet as well - if someone could help me resolve the issue, it would be great (https://docs.google.com/spreadsheets/d/1NCl5hf0rfKMz-W7d2MVANgbxiDR2JsTqTS4XnNk4Rb8). Below is the code :: var copycc = ws.getRange("E2:E").getValue(); is the line of code I have added in.

function Bday() {
  var slno = 0;
  var name1 = 1;
  var email = 2;
  var dob = 3;
  var copyc = 4;

  var emailTemp = HtmlService.createTemplateFromFile("email");

  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Full list");
  var wsSettings = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
  
  var name = wsSettings.getRange("B2").getValue();
  var subject = wsSettings.getRange("B1").getValue();
  var copycc = ws.getRange("E2:E").getValue();
  
  var data = ws.getRange("A2:E"   ws.getLastRow()).getValues();


  data.forEach(function(row){
    emailTemp.fn = row[name1];
    emailTemp.empid = row[slno];
    emailTemp.dob = row[dob];
    var htmlMessage = emailTemp.evaluate().getContent();
    GmailApp.sendEmail(
      row[email],
      subject,
      "Your email doesnt support HTML",
      {name: name, cc: copycc, htmlBody: htmlMessage}
    );
  });
}

CodePudding user response：

In your script, how about the following modification?

I thought that in your script, the values of var data = ws.getRange("A2:E" ws.getLastRow()).getValues(); can be used for achitving your goal. And, the values of name and subject can be retrieved by getValues.

Modified script:

function Bday() {
  var emailTemp = HtmlService.createTemplateFromFile("email");
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Full list");
  var wsSettings = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
  var [[name], [subject]] = wsSettings.getRange("B1:B2").getValues();
  var data = ws.getRange("A2:E"   ws.getLastRow()).getValues();
  data.forEach(([slno, name1, email, dob, copyc]) => {
    emailTemp.fn = name1;
    emailTemp.empid = slno;
    emailTemp.dob = dob;
    var htmlMessage = emailTemp.evaluate().getContent();
    GmailApp.sendEmail(
      email,
      subject,
      "Your email doesnt support HTML",
      { name: name, cc: copyc, htmlBody: htmlMessage }
    );
  });
}

• When this script is run, the values of name and subject are retrieved from "Settings" sheet. And, the values of email, name1, slno, dob, copyc are retrieved from each row of "Full list" sheet.

Reference:

• forEach()