Google sheet - sending emails

Question

I did this post before but i did get the much anticipated help. Let me take this opportunity to try and be as clear as possible. 1. I have this google sheet ID 1UJVQGlCpsxtqN-IkRFFgHEnzNaq-HQDaiw-hte5j3LY

Email Address   Name   Mark1    Mark2    Mark3  Remark  Email Sent
[email protected]   John    34      21       34     Fair    
[email protected]   Sam     23      9        78     fair

Tamplate Doc. ID 15SP7mxyKYF6ptuRfrudNsvtJuezMTOBUL_cRCFbBIcI

Dear keyName

Mark1 Mark2 Mark3 keyMark_1 keyMark_2 keyMark_3

Remark keyRemark

Code.

// Email_Sent written in column G for rows for which an email // has been sent successfully. var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 5;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 6)
  var ss = SpreadsheetApp.openById('1UJVQGlCpsxtqN-IkRFFgHEnzNaq-HQDaiw-hte5j3LY'); 
  var sheet = ss.getSheetByName('Email Spreadsheet Data');
  //var dataRange = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());
  var copyId = DocsList.getFileById('15SP7mxyKYF6ptuRfrudNsvtJuezMTOBUL_cRCFbBIcI')
    .makeCopy(name)
    .getId();

  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i){
  //for (var j = 0; j < startRow; ++j) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var name = row[1]; // Second column
    var Mark1 = row[2]; // Third column
    var Mark2 = row[3]; // Fourth column
    var Mark3 = row[4]; // 
    var remark = row[5]; // 
    var emailSent = row[6]; // 
    var static_copy = ('[email protected]');

    // Open the temporary document
  var copyDoc = DocumentApp.openById(copyId);
  // Get the document’s body section
  var copyBody = copyDoc.getActiveSection();

// Replace place holder keys,in google doc template. Create a blank document with the number "1,2,3....", and it will replace this text with value
  copyBody.replaceText('keyName', name)
  copyBody.replaceText('keyMark_1', Mark1)
  copyBody.replaceText('keyMark_2', Mark2)
  copyBody.replaceText('keyMark_3', Mark3)
  copyBody.replaceText('keyRemark', remark)

// Save and close the temporary document
  copyDoc.saveAndClose();

// Convert temporary document to PDF
  var pdf = DocsList.getFileById(copyId).getAs("application/pdf");

// Attach PDF and send the email
  var subject = "Profile";
  var body = "<HTML><BODY>"
             + "Dear "+ name +",<br>"
             + "<br>"
             + " Regards <br>"
             + "<br>"
             + "</HTML></BODY>";

  MailApp.sendEmail(emailAddress, subject, body, 
                   {htmlBody: body, attachments: pdf, cc: static_copy,});
 //if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      //var subject = "Sending emails from a Spreadsheet";
     // MailApp.sendEmail(emailAddress, subject, {htmlBody: body, attachments: pdf, cc: static_copy,});
    //sheet.getRange(startRow + i, 6).setValue(EMAIL_SENT);
   // Delete temp file
  DocsList.getFileById(copyId).setTrashed(true);  
      } // Make sure the cell is updated right away in case the script is interrupted
     SpreadsheetApp.flush();
    }

When i execute the above script, An email attachment with the John's marks is sent to both [email protected] and [email protected]. I need your help in solving this issue to enable me to send each student an email attachment with there own results.

Kindly Help

Sammy.

You are trashing the file (copy id) and reopening it - how is that possible? — Vasim
@Ark i was trashing at DocsList.getFileById(copyId).setTrashed(true to be able to fill the temporary doc. After commenting the line, it sends a duplicate attachment to all mails... — Sam Goin
Sorry but question is too localized. Its an algorithm issue and not really an apps script issue. — Zig Mandel
@Zig Mandel I agree am new in this issue and i could use your help — Sam Goin

laurentgir laurentgir · Accepted Answer · 2015-04-15T12:53:40

The copyId variable should be declared inside the for loop; the variable name is undefined when copyId is declared.

Also, you are using some deprecated API (DocsList for example).

Google sheet - sending emails

1 Answers