Google Spreadsheet: Email notification system based on changes to specific cells.

This script is an automated solution that sends email notifications to a designated email address when the value in a particular cell (defaulted to “A1”) of a Google Spreadsheet satisfies specified conditions (for example, when the value is 5 or less). The script is written in Google Apps Script, making it easy to integrate into an existing spreadsheet.

Note: Proper permissions are required to enable the email notification feature.

The system is versatile and can be applied for various tasks such as data monitoring, automatic report generation, and sending alert notifications.

Used Technologies

• Google Apps Script: Manipulates the data in the spreadsheet and performs email notifications.
• Google Spreadsheet: Stores and manages the data.
• Google Mail API (MailApp): Provides the email notification functionality.

Features

• Purpose of the Function: Automatically sends an email when the value in a specific cell (“A1”) on a Google Spreadsheet becomes 5 or less.
• Retrieving the Spreadsheet: Uses SpreadsheetApp.getActiveSpreadsheet() to obtain the object of the active spreadsheet.
• Retrieving the Active Sheet: Uses ss.getActiveSheet() to get the currently active sheet (tab) within the active spreadsheet.
• Getting the Cell Value: Uses sheet.getRange(“A1”).getValue() to fetch the value of the cell “A1”.
• Condition Check: Checks if the fetched cell value is 5 or less using if(cellValue <= 5).
• Setting Email Address: If the condition is met, an email will be sent to a specified email address (“your_email@example.com“).
• Setting Email Subject and Body: The email subject is set as “Google Spreadsheet Notification,” and the body indicates that the cell value is 5 or less.
• Sending the Email: Uses the MailApp.sendEmail() method to send the email.
• Resetting the Cell Value (Optional): After sending the email, the value of cell “A1” can optionally be reset to 5.

Sauce Code

// This function sends an email if the value in a specific cell (in this case "A1") becomes 5 or less.
function sendEmailIfValueIsLow() {
  
  // Obtain the object of the active spreadsheet.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Obtain the object of the currently active sheet (tab) within the active spreadsheet.
  var sheet = ss.getActiveSheet();
  
  // Get the value of cell "A1".
  var cellValue = sheet.getRange("A1").getValue();
  
  // Check if the cell value is 5 or less.
  if(cellValue <= 5) {
    
    // Set the email address to which the email will be sent.
    var recipientEmail = "your_email@example.com";
    
    // Set the subject of the email.
    var subject = "Google Spreadsheet Notification";
    
    // Set the body of the email.
    var body = "The value in cell A1 is " + cellValue + ", which is less than or equal to 5.";
    
    // Send the email.
    MailApp.sendEmail(recipientEmail, subject, body);
    
    // Optional: Reset the value of cell "A1" to 0 after sending the email.
    sheet.getRange("A1").setValue(5);
  }
}


// MIT License
// Copyright (c) 2023 [ANJI]
//
// Permission is hereby granted, free of charge, to any person obtaining a copy
// of this software and associated documentation files (the "Software"), to deal
// in the Software without restriction, including without limitation the rights
// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
// copies of the Software, and to permit persons to whom the Software is
// furnished to do so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in all
// copies or substantial portions of the Software.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
// SOFTWARE.

By making the following changes to the code, the value in cell “A1” will be retained even after an email is sent upon meeting the specified conditions.

// Sends the email.
MailApp.sendEmail(recipientEmail, subject, body);

// By removing this line, the cell value won't be reset.
// sheet.getRange("A1").setValue(5);

How to Use

1. Open Google Spreadsheet: Open the Google Spreadsheet that you want to monitor.
2. Open Script Editor: Navigate to Extensions → Apps Script to open the script editor.
3. Copy the Code: Copy the provided code and paste it into the script editor.
4. Configure Email Address: Change the line var recipientEmail = “your_email@example.com“; to the email address where you wish to receive the notifications.
5. Save and Run: Save your changes in the script editor. You can either manually run the sendEmailIfValueIsLow function or set up a trigger to execute it automatically.
6. Check Cell Value: The script will examine the value in cell “A1” of the spreadsheet. If that value is 5 or less, a notification email will be sent to the specified email address.
7. Optional Reset Value: Optionally, you can reset the value in cell “A1” to 5 after the email has been sent. This part is optional and mentioned in the comments within the code.
8. Set Up a Trigger: If you want this script to run periodically, you can set up a trigger in Google Apps Script to execute it at your preferred frequency.
9. License: This code is distributed under the MIT License, allowing you to freely use, modify, or distribute it, as long as you follow the terms of the license.
   That covers the basic usage. You can further customize the code to fit your specific requirements.