For privacy reasons YouTube needs your permission to be loaded. For more details, please see our Privacy Policy.

Introduction

Automating Email Notifications via Google Apps Script

Google Apps Script provides a versatile platform to create custom email alerts triggered by updates within Google Sheets. Whether you’re tracking new entries, updates, or specific conditions, this script ensures you and your team stay informed with the latest data changes without manual oversight.

Understanding the Components

Crafting the Email Notification Script

The script initiates by identifying the active spreadsheet and iterates through each row, starting from the second row to avoid headers. For each row, it checks if certain conditions are met — such as the presence of a phone number and the absence of a previous email notification for that row. When these conditions are satisfied, an email is composed and sent to a predefined recipient, including detailed information extracted from the spreadsheet. After sending the email, the script marks the row to indicate that a notification has been dispatched, preventing duplicate alerts.

Key Components of the Script

Row Iteration: Efficiently navigates through spreadsheet rows, ensuring that no data point is overlooked.
Condition Checks: Employs logical conditions to determine which rows trigger email notifications, optimizing the relevance and timeliness of each alert.
Dynamic Email Composition: Dynamically generates email content based on spreadsheet data, allowing for personalized and informative notifications.
Immediate Updates: Utilizes SpreadsheetApp.flush() to immediately apply changes to the spreadsheet, ensuring accurate tracking of sent notifications.

Implementation

Implementing the Script for Email Notifications

Access Google Apps Script: Open the script editor from Google Sheets to start scripting.
Script Customization: Adapt the provided script example to fit your specific data structure and notification needs. Customize the recipient, subject, and message body as necessary.
Deployment: After customizing the script, save and run it to authorize permissions and test functionality. Set up triggers within the script editor to automate the execution based on time intervals or sheet updates.

Practical Tips for Success

Regular Script Testing: Ensure the script runs as expected by conducting regular tests with various data scenarios.
Email Limits Consideration: Be aware of daily email sending limits imposed by Google Apps Script to avoid service interruptions.
Data Privacy: Handle sensitive information with care, especially when automating emails containing personal data.

Conclusion

By integrating Google Apps Script for email notifications with Google Sheets, you can significantly enhance your workflow automation, ensuring vital information is communicated instantly and accurately. This guide not only simplifies the process of setting up such notifications but also opens up avenues for further customization and integration, empowering you to maintain effective and informed communication channels through automated systems.

Appscript Code

Sample Script

Copy to Clipboard

function sendEmailNotification() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();

for (var i = 2; i <= lastRow; i++) { // Starting from the second row
    var row = sheet.getRange(i, 1, 1, 5).getValues()[0];
    var name = row[0];
    var email = row[1]; // Even though this is not used for sending, it's retrieved in case you need it for the message
    var age = row[2];
    var phoneNumber = row[3];
    var emailSent = row[4]; // Column E for tracking if the email was sent

// Check if the phone number is filled and the email has not been sent
    if (phoneNumber && !emailSent) {
        var fixedRecipient = "YOUREMAIL@GMAIL.COM"; // Fixed email recipient
        var subject = "New Entry Notification";
        var message = "Hello Admin, " + name + " has added his/her detail as follows:\n\n";
        message += "Name: " + name + "\n";
        message += "Email: " + email + "\n"; // This is the email from the row, included in the message
        message += "Age: " + age + "\n";
        message += "Phone Number: " + phoneNumber + "\n";

MailApp.sendEmail(fixedRecipient, subject, message);
        sheet.getRange(i, 5).setValue('Sent'); // Mark as processed
        SpreadsheetApp.flush(); // Apply the changes to the spreadsheet immediately
    }
  }
}

This blog is my canvas, where I paint with words, sharing tutorials, tips, and tidbits that have enriched my life.

I welcome you. Dive into my articles, engage with my stories, and let’s embark on this journey of discovery together.

Happy reading, and thank you for visiting ArisAzhar.com!

Aris Azhar
Knowledge Seeker

Over 8,000
Benefited from my Channel

Check out my tutorials

I hope you can join in as well!

How to send email notification using Google Appscript

Introduction

Understanding the Components

Implementation

Appscript Code

Introduction