Guide to Automatic Email Sending in Google Sheets

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();
}
}
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.