CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Sam Goin
Sam Goin

Reputation: 11

Google sheet - sending emails

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
  1. Tamplate Doc. ID 15SP7mxyKYF6ptuRfrudNsvtJuezMTOBUL_cRCFbBIcI

Dear keyName

Mark1 Mark2 Mark3 keyMark_1 keyMark_2 keyMark_3

Remark keyRemark

  1. 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.

Upvotes: 1

Views: 599

Answers (1)

laurentgir
laurentgir

Reputation: 76

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).

Upvotes: 3

Related Questions