Google Apps Script: Send Email Based on Cell Value | Tutorial

To automate an email based on a cell value in Google Sheets, copy-paste this precise script into your Extensions > Apps Script editor. Replace placeholders with your actual values for sheet name, target cell, trigger value, recipient email, subject, and body.

JavaScript

function sendEmailOnCellValue() {
  var sheetName = "Sheet1"; // Change to your sheet name
  var targetCell = "B2"; // Cell to check
  var triggerValue = "Send"; // Value that triggers email
  var recipient = "recipient@example.com"; // Recipient email
  var subject = "Automated Email Subject"; // Email subject
  var body = "This is an automated email sent based on cell value."; // Email body

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var cell = sheet.getRange(targetCell);
  var value = cell.getValue();

  if (value == triggerValue) {
    MailApp.sendEmail(recipient, subject, body);
    cell.setValue("Sent"); // Optional: Mark as sent to prevent re-sending
  }
}

The Syntax Breakdown

• function sendEmailOnCellValue() { ... }: Defines a standard Google Apps Script function that contains all the logic for checking the cell value and sending the email.
• var sheetName = "Sheet1";: Declares a variable sheetName and assigns the string value “Sheet1”. This must match the exact name of your Google Sheet tab where the target cell resides.
• var targetCell = "B2";: Declares a variable targetCell and assigns the string value “B2”. This is the specific cell (using standard A1 notation) that the script will monitor for the trigger value.
• var triggerValue = "Send";: Declares a variable triggerValue and assigns the string value “Send”. The script will only send an email if the targetCell‘s value exactly matches this string.
• var recipient = "recipient@example.com";: Declares a variable recipient and assigns the recipient’s email address as a string. Automated emails will be sent to this address.
• var subject = "...";: Declares a variable subject and assigns the desired subject line for the automated email.
• var body = "...";: Declares a variable body and assigns the main content/text of the automated email.
• var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);: Uses the SpreadsheetApp service to get the active spreadsheet, then retrieves the specific sheet tab defined by the sheetName variable.
• var cell = sheet.getRange(targetCell);: Obtains a Range object representing the single target cell specified by the targetCell variable on the correct sheet.
• var value = cell.getValue();: Extracts and stores the current content/value of the targetCell into the value variable.
• if (value == triggerValue) { ... }: Implements conditional logic. It checks if the value extracted from the cell is equal (==) to the pre-defined triggerValue. If true, the code block within the braces { ... } executes.
• MailApp.sendEmail(recipient, subject, body);: This is the core action. It uses the MailApp service to construct and send an email using the pre-defined recipient, subject, and body variables.
• cell.setValue("Sent");: Highly recommended operational step. After successfully sending the email, this line updates the targetCell‘s value to “Sent” (or any other indicator). This is crucial to prevent the script from repeatedly sending emails every time it runs and finds the initial triggerValue, essentially marking the task as complete.

Real-World Example: Low Inventory Alerts

An operations manager tracking critical inventory in a Google Sheet wants an automated email alert sent to purchasing whenever any item’s quantity on hand drops below a defined threshold.

Mock Data Table (Inventory Sheet):

Application: To implement low inventory alerts, you would slightly modify the script to check if quantity is less than or equal to threshold instead of just matching a value.

JavaScript

function sendLowInventoryAlerts() {
  var sheetName = "Inventory"; // Sheet tab name
  var inventoryRange = "A2:E4"; // Range containing inventory data (rows 2-4)

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getRange(inventoryRange).getValues();

  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var itemName = row[0];
    var quantity = row[1];
    var threshold = row[2];
    var restockStatus = row[3];
    var purchasingEmail = row[4];

    if (quantity <= threshold && restockStatus != "Alert Sent") {
      var subject = "LOW INVENTORY ALERT: " + itemName;
      var body = "Inventory for " + itemName + " is low. Quantity on hand is " + quantity + ", which is less than or equal to the threshold of " + threshold + ". Please restock.";
      MailApp.sendEmail(purchasingEmail, subject, body);
      sheet.getRange(i + 2, 4).setValue("Alert Sent"); // Mark that alert has been sent for this row (column D is 4th col)
    }
  }
}

Output: When this modified script runs:

• Item A: Quantity (15) > Threshold (10). No email.
• Item B: Quantity (5) <= Threshold (8). Sends low inventory email to purchasing, updates “Restock?” column for Item B row to “Alert Sent”.
• Item C: Quantity (25) > Threshold (15). No email.

Common Errors & How to Fix Them

• Error: “Exception: The parameters (String,String,String) don’t match the method signature for MailApp.sendEmail.”: This often occurs if you are passing variables with incorrect data types or structure (e.g., trying to send an array as the body text). Fix: Ensure that the recipient, subject, and body parameters are all simple string variables.
• Email not sending, even though cell value matches trigger value: This is frequently caused by extra white space, hidden characters, or exact case mismatches between the cell content and the triggerValue variable. Fix: Use .toString().trim() on both the cell value and the trigger value for robust comparison, ensuring exact matches irrespective of leading/trailing spaces or potential non-string values. (e.g., if (value.toString().trim() == triggerValue.toString().trim()) { ... })