Step 1: Prepare Google Sheet

You need to create two sheets in Google Sheets:

Sheet 1: Candidate Data

This sheet will contain candidate information with the following columns:

• Date: Date of application.
• Name: Candidate's name.
• Position Applied: The position applied for.
• Email for Notification: Candidate's email address.
• Result (Yes/No): Application status (either "Yes" or "No").
• Email Status: Status to track if the email has been sent.

Sheet 2: Email Content (Sheet "content")

This sheet will contain email content for both "Yes" and "No" statuses, including:

• A2: Email subject for "Yes" status.
• A3: Main content of the email for "Yes" status.
• A4: Signature for "Yes" email.
• B2: Email subject for "No" status.
• B3: Main content of the email for "No" status.
• B4: Signature for "No" email.

Step 2: Create Google Apps Script

1. Open your Google Sheet, then select Extensions > Apps Script.

2. Delete the default code and insert the following code:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var contentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('content');  // Get the "content" sheet
  
  var startRow = 2;  // Start from row 2 (skip headers)
  var numRows = sheet.getLastRow() - 1;  // Number of data rows
  var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
  var data = dataRange.getValues();

  // Get email subject and content from the "content" sheet for Yes and No statuses
  var yesSubject = contentSheet.getRange('A2').getValue();
  var yesMessage = contentSheet.getRange('A3').getValue();
  var yesSignature = contentSheet.getRange('A4').getValue();
  
  var noSubject = contentSheet.getRange('B2').getValue();
  var noMessage = contentSheet.getRange('B3').getValue();
  var noSignature = contentSheet.getRange('B4').getValue();

  // Check if any subject, message, or signature cells are empty
  if (!yesSubject || !yesMessage || !yesSignature.trim() || !noSubject || !noMessage || !noSignature.trim()) {
    Logger.log("Error: One or more subject, message, or signature cells are empty.");
    SpreadsheetApp.getUi().alert("Error: One or more subject, message, or signature cells are empty. Please check again.");
    return;
  }

  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var timestamp = row[0];
    var name = row[1];
    var position = row[2];
    var emailAddress = row[3];
    var result = row[4];
    var emailStatus = row[5];
    
    if (emailStatus != 'Sent Y' && emailStatus != 'Sent N' && (result === 'Yes' || result === 'No')) {
      var subject = '';
      var message = '';

      if (result === 'Yes') {
        subject = yesSubject;
        message = 'Hello ' + name + ',\n\n' + yesMessage + '\n\n' + yesSignature;
        sheet.getRange(startRow + i, 6).setValue('Sent Y');  // Update email status to Sent Y
      } else if (result === 'No') {
        subject = noSubject;
        message = 'Hello ' + name + ',\n\n' + noMessage + '\n\n' + noSignature;
        sheet.getRange(startRow + i, 6).setValue('Sent N');  // Update email status to Sent N
      }

      try {
        MailApp.sendEmail(emailAddress, subject, message);
      } catch (e) {
        Logger.log("Error sending email to: " + emailAddress + ". Error details: " + e.toString());
      }
      
      SpreadsheetApp.flush();
    }
  }
}

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var editedColumn = range.getColumn();
  var editedRow = range.getRow();
  
  if (editedColumn == 5) {  // The "Result (Yes/No)" column is column 5
    sendEmails();
  }
}


 Copy

Step 3: Set up a Trigger for Automatic Execution

1. In the Apps Script interface, select Triggers from the left menu.
2. Click on "Add Trigger".
3. Choose:

• Function to run: sendEmails
• Deployment: Head
• Event source: From spreadsheet
• Event type: On edit (when edited)

Step 4: Test and Verify

Change the status in a row from "Yes" or "No" and check if the email is sent. After sending, the Email Status column will update to "Sent" to avoid duplicate emails.

Note

- You may need to enable email sending permissions in Google Apps Script if prompted.
- Ensure that the email addresses and email content are correctly entered and thoroughly checked.