Use Google Apps Script to Send Emails from Google Sheets Based on Cell Values

Have you ever needed to send emails to specific recipients based on the values in your Google Sheets? Maybe you want to send reminder emails to clients whenever their payment is due or send an email notification to your team whenever a certain milestone is reached. Fortunately, you can automate this process using Google Apps Script.

In this article, we’ll walk you through the steps to send emails from Google Sheets based on cell values using Google Apps Script. We will create a simple script to send an email to a specific recipient whenever a cell value changes in our example spreadsheet.

Step 1: Create a Spreadsheet

First, we need to create a new spreadsheet in Google Sheets. You can create one from scratch or use an existing spreadsheet. In our example, we’ll create a new spreadsheet and add some sample data.

Step 2: Create a Script

Next, we need to create a Google Apps Script file. Go to Tools > Script editor. If it’s your first time using the script editor, you’ll need to create a new project.

In the script editor, delete any code that is already present and add the following code:

function sendEmail() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var email = sheet.getRange('B2').getValue();
    var subject = 'Reminder: Task Pending';
    var body = 'Dear User,\n\nI would like to remind you that Task X is still pending. Kindly take action.\n\nBest regards,\nYour Name';
    MailApp.sendEmail(email, subject, body);
}

This script gets the active sheet, gets the value in cell B2 (which is where we’ll enter the email address), sets the subject and body of the email, and sends the email using the MailApp service. We’ve set the email body to a sample text. You can customize it as per your requirements.

Step 3: Add a Trigger

Now that we have our script, we need to set up a trigger to run the script whenever a certain condition is met. In our example, we want to send an email whenever a cell value changes. To do this, we’ll set up an edit trigger.

In the script editor, go to Edit > Current project’s triggers. Click on the “Add Trigger” button and configure the trigger settings as follows:

• Choose which function to run: sendEmail
• Choose which deployment should run: Head
• Select event source: From spreadsheet
• Select event type: On edit

Click save, and you’re done! Your script will now send an email to the email address entered in cell B2 whenever a cell value changes in the sheet.

Step 4: Test the Script

To test the script, enter an email address in cell B2 and change a cell value in the sheet. You should see an email arrive at the provided email address. If you don’t receive the email, check your spam folder and make sure that the trigger has been set up correctly.

Optional Step: Add Conditional Logic

If you want to send emails based on certain conditions (e.g., send an email only when a certain cell value meets a certain condition), you can modify the script to include conditional logic. Here’s an example:

function sendEmailConditional() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var email = sheet.getRange('B2').getValue();
    var value = sheet.getRange('C2').getValue();
    if (value == "Pending") {
        var subject = 'Reminder: Task Pending';
        var body = 'Dear User,\n\nI would like to remind you that Task X is still pending. Kindly take action.\n\nBest regards,\nYour Name';
        MailApp.sendEmail(email, subject, body);
    }
}

In this modified script, we first get the value in cell C2 and check if it is equal to “Pending”. If it is, we send the email. You can modify this code to include more complex conditions as per your requirements.