Step 1: Prepare Google Sheets

You need to create two sheets in Google Sheets:

Sheet 1: Candidate Data

This sheet will contain candidate information, with the following columns:

• Date: Application date.
• Full Name: Candidate's name.
• Position Applied: Position for which the candidate is applying.
• Email for Notifications: Candidate's email address.
• Selected (Yes/No): Application status (either "Yes" or "No").
• Email Status: Status to track whether the email has been sent.

Sheet 2: Email Content (Sheet "content")

This sheet will contain email content for "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 "content" sheet
  
  var startRow = 2;  // Start from row 2 (skip header)
  var numRows = sheet.getLastRow() - 1;  // Number of rows with data
  var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
  var data = dataRange.getValues();

  // Get email subject and content from "content" sheet for Yes and No
  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 subject or content cells are empty
  if (!yesSubject || !yesMessage || !yesSignature.trim() || !noSubject || !noMessage || !noSignature.trim()) {
    Logger.log("Error: One or more subject, content, or signature cells are empty.");
    SpreadsheetApp.getUi().alert("Error: One or more subject, content, or signature cells are empty. Please check.");
    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' && (result === 'Yes' || result === 'No')) {
      var subject = '';
      var message = '';

      if (result === 'Yes') {
        subject = yesSubject;
        message = 'Hello ' + name + ',\n\n' + yesMessage + '\n\n' + yesSignature;
      } else if (result === 'No') {
        subject = noSubject;
        message = 'Hello ' + name + ',\n\n' + noMessage + '\n\n' + noSignature;
      }

      try {
        MailApp.sendEmail(emailAddress, subject, message);
        sheet.getRange(startRow + i, 6).setValue('Sent');  // Update email status
      } 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 "Selected (Yes/No)" column is the 5th column
    sendEmails();
  }
}

 Copy

Step 3: Set up Trigger for Automation

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

• Function to run: sendEmails
• Deployment: Head
• Event source: From spreadsheet
• Event type: On edit

Step 4: Test and Verify

Change the status of a row in the sheet to "Yes" or "No" and check if the email is sent. After sending the email, the Email Status column will be updated to "Sent" to prevent duplicate emails.

Notes

- You may need to enable email sending permissions for Google Apps Script if prompted.
- Ensure that you have entered the correct email addresses and the email content has been carefully checked.