Templated email using Apps Script based on data in Google Sheets

Question

I'm new to scripting so I hope someone can help me create one.

I'm trying to create a script for a sheet that can send an email when a custom menu is pressed.

Here's a file to work on. https://docs.google.com/spreadsheets/d/1Ea-3eZoclHrAkZLwRmWWFbmbnn4dESNWvK_6pn1DCbE/edit?usp=sharing

Also, it should only send it if a column (ex. Column I) has a specific Value like 'Approved'

Email content should look like:

Subject: Leave Application # 'ColumnC'

Hi 'ColumnA',

We received your 'ColumnB' request for 'ColumnE'

Status: 'ColumnG'

More Details: 'ColumnH'

-Admin

Email should be sent to Column E and F.

The script must also update the spreadsheet to avoid duplicate emails.

Answer 1

Here is the working example

Here is the code:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var startRow = 2;  // First row of data to process
  var lastRow = sheet.getSheetByName('Journal').getLastRow();  // Last row with content

  var rangeEmailSent     = sheet.getRangeByName('Journal!EmailSent');
  var dataEmailSent      = rangeEmailSent.getValues();
  var dataRequestedBy    = sheet.getRangeByName('Journal!RequestedBy').getValues();
  var dataRequestType    = sheet.getRangeByName('Journal!RequestType').getValues();
  var dataRefNo          = sheet.getRangeByName('Journal!RefNo')      .getValues();
  var dataStatus         = sheet.getRangeByName('Journal!Status')     .getValues();
  var dataToEmail        = sheet.getRangeByName('Journal!ToEmail')    .getValues();
  var dataSupComment     = sheet.getRangeByName('Journal!SupervisorComment').getValues();

  var subjectTemplate = sheet.getRangeByName('SubjectTemplate1').getValue();
  var bodyTemplate =  sheet.getRangeByName('BodyTemplate1').getValue();
  var msgSubject;
  var msgBody;

  for (var i = (startRow-1); i <= (lastRow-1); i++) {

    // send e-mail if "Email Sent" is not blank and if "Status" is not empty
    if ( !(dataEmailSent[i]=='Yes') && !(dataStatus[i] =='')) {

      msgSubject = subjectTemplate.replace('$REF$', dataRefNo[i]);

      msgBody = bodyTemplate
        .replace('$REQUESTED_BY$', dataRequestedBy[i])
        .replace('$REQUEST_TYPE$', dataRequestType[i])
        .replace('$EMAIL$', dataToEmail[i])
        .replace('$STATUS$', dataStatus[i])
        .replace('$SupervisorComment$', dataSupComment[i]);
      // Logger.log(msgSubject);
      // Logger.log(msgBody);

      MailApp.sendEmail(dataToEmail[i], msgSubject, msgBody);
      // Change "Email sent" to "Yes"
      rangeEmailSent.getCell(i+1,1).setValue('Yes'); // note: getCell(1,1) refers to the 1st cell
    } 
  } 
}