ExcelInsider Team
If you’re managing status updates, form responses, or order confirmations in Google Sheets, sending automated emails based on specific cell values can simplify your workflow significantly. Whether you prefer coding, using add-ons, or setting up triggers, Google Sheets offers flexible options to automate email sending.
In this article, we’ll cover all the main methods to send emails when a condition in your sheet is met, such as when a row says “Approved” or “Completed.”
Steps to send emails without code using a Google Sheets add-on:
➤ Open your Google Sheet and go to Extensions >> Add-ons >> Get add-ons.
➤ Search for and install an add-on like “Email Notification for Google Sheets” or “Mail Merge with attachments”.
➤ Launch the installed add-on from the Extensions menu.
➤ Set up a rule: for example, if Column C = “Approved”, send an email to the address in Column B.
➤ Write a personalized message using placeholders like {{First Name}} or {{Status}}.
➤ Click Create to activate the rule, and emails will be sent automatically when conditions are met.
Use Google Apps Script to Send Emails
This method uses a custom script in Google Sheets to automatically send emails based on the value of a cell, such as when a row is marked “Approved.” With Google Apps Script, you can loop through your dataset, check for specific conditions, and trigger personalized email messages to the right recipients. It’s a powerful and flexible solution if you’re comfortable with a bit of simple coding.
Steps:
➤ In your sheet, go to Extensions >> Apps Script.
➤ Paste this script:
function sendEmailsBasedOnStatus() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var data = sheet.getRange("A2:C11").getValues();
for (var i = 0; i < data.length; i++) {
var name = data[i][0];
var email = data[i][1];
var status = data[i][2];
if (status === "Approved") {
MailApp.sendEmail({
to: email,
subject: "Your request is approved",
body: "Hi " + name + ",\n\nWe're happy to let you know that your request has been approved."
});
}
}
}➤ Save and run the function manually or set a time-driven trigger for automation.
➤ Grant permissions when prompted.
➤ Emails are sent only where Status = “Approved”, based on column C values.
Automatically Send Emails from Form Submissions Using a Trigger
If your Google Sheets is populated via a Google Form, you can automatically send emails the moment a response is submitted. By using the onFormSubmit trigger in Google Apps Script, you can instantly scan new responses and send customized emails when specific conditions, like a status of “Approved,” are met. Once set up, this method requires no manual action and is perfect for approvals, notifications, or confirmations.
Steps:
➤ Open the Google Sheet linked to your form.
➤ Click Extensions >> Apps Script to open the script editor.
➤ Delete any placeholder code and paste the following script:
function onFormSubmit(e) {
var name = e.values[1]; // Adjust based on column order
var email = e.values[2];
var status = e.values[3];
if (status === "Approved") {
MailApp.sendEmail(email, "Form Approved", "Hi " + name + ", your form has been approved.");
}
}Make sure the index numbers (e.values[1], etc.) match your form columns. e.values[0] is the timestamp and e.values[1] is the first question response, and so on
➤ Click the floppy disk icon to save the script and give it a name.
The onFormSubmit function runs automatically whenever a new form response is submitted. To break it up for you, whenever a new entry is added to the Google sheet through the form, an automated email will be sent to the recipient.
Set Up Email Alerts Without Code Using an Add-on in Google Sheets
If you’re not comfortable using Apps Script, you can still automate email notifications based on cell values using an add-on. Tools like “Email Notification for Google Sheets” or “Mail Merge with attachments” allow you to create rule-based alerts. These add-ons make it easy to trigger personalized emails when specific conditions, like a cell containing “Approved“, are met, without writing a single line of code.
Steps:
➤ Open your Google Sheet, then go to Extensions >> Add-ons >> Get add-ons.
➤ Search for an add-on like “Email Notification for Google Sheets” or “Mail Merge with attachments”.
➤ Install the add-on.
➤ Launch the new extension from the Extensions menu.
➤ Create a rule based on conditions (e.g., if Column C = “Approved“, send an email to Column B).
➤ Write your email message using placeholders like {{First Name}} or {{Status}}.
➤ Activate the rule by clicking on the Create button, and emails will be sent automatically whenever a matching condition is found.
Send Emails Manually with Google Sheets Macros
If you prefer a semi-automated process where emails are only sent after reviewing the sheet, you can use a macro powered by Apps Script. This method is ideal when you want manual control while avoiding repetitive email tasks.
Steps:
➤ Open your Google Sheet and go to Extensions >> Apps Script.
➤ Paste the following script:
function sendEmailsManually() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var data = sheet.getRange("A2:C11").getValues();
for (var i = 0; i < data.length; i++) {
var name = data[i][0];
var email = data[i][1];
var status = data[i][2];
if (status === "Approved") {
MailApp.sendEmail({
to: email,
subject: "Approval Notification",
body: "Hi " + name + ",\n\nYour request has been approved."
});
}
}
}➤ Click the floppy disk icon to save the project. Give it a name like ManualEmailSender.
➤ Back in your sheet, go to Extensions >> Macros >> Import Macro.
➤ Select sendEmailsManually and click Add Function.
➤ To run the macro, go to Extensions >> Macros >> sendEmailsManually.
➤ The script will send emails only to rows where Column C = “Approved”.
Frequently Asked Questions
How do I send an email when a Google Sheets cell changes?
Use Apps Script with an onEdit or custom trigger to detect changes and send emails when specific conditions are met in a cell.
Can Google Sheets automatically send emails based on form submissions?
Yes, using the onFormSubmit trigger in Google Apps Script, you can send automatic emails when new Google Form responses meet certain criteria.
Are there any add-ons to send emails from Google Sheets without code?
Yes, add-ons like “Email Notifications for Google Sheets” allow you to set rules and send emails without writing any code.
Can I send emails manually from Google Sheets using a macro?
Yes, you can record or write a macro that sends emails and then run it manually when you’re ready to trigger the notifications.
Wrapping Up
Google Sheets offers various email-sending methods based on cell values, ranging from full automation and no-code solutions to manual control. Techniques include custom Apps Scripts, form triggers, and add-ons, catering to different use cases and user expertise. For dynamic data, Apps Script allows flexibility. In form-based workflows, the onFormSubmit trigger works seamlessly.
For a simple option, add-ons like “Email Notification” enable alerts without coding skills. Lastly, for individuals who only need to send reviewed updates occasionally, macros offer a reliable manual option
