How to Send Email Notifications for New Rows in Google Sheets Using Apps Script

Google Sheets is an incredibly versatile tool for managing and organizing data. However, if you want to get notified when new data is added, you need to set up some automation. With Google Apps Script, you can create a script to send email notifications whenever a new row is added to a specific sheet. This blog post will guide you through the process of setting up an “On Change” trigger to send email notifications.

Sample Data

Let’s assume you have a Google Sheet with the following structure:

Name	Email	Date Added
John Doe	john@example.com	Tue, Jun 4, 2024
Jane Smith	jane@example.com	Wed, Jun 5, 2024
…	…	…

Step-by-Step Guide

1. Set Up the Google Sheet

Create a Google Sheet and populate it with some sample data as shown above. Name the sheet where new rows will be added, for example, “NewEntries”.

2. Open the Script Editor

Go to Extensions > Apps Script to open the Google Apps Script editor.

3. Write the Script

Here’s a script to send an email notification when a new row is added to a specific sheet:

function onChange(e) {
  var sheetName = "NewEntries";
  var sheet = e.source.getSheetByName(sheetName);

  if (!sheet) {
    Logger.log("Sheet not found: " + sheetName);
    return;
  }

  var currentRowCount = sheet.getLastRow();
  var properties = PropertiesService.getScriptProperties();
  var lastRowCount = properties.getProperty('lastRowCount');

  // If the last row count is not set, initialize it
  if (!lastRowCount) {
    properties.setProperty('lastRowCount', currentRowCount);
    return;
  }

  lastRowCount = parseInt(lastRowCount);

  // Check if a new row was added
  if (currentRowCount > lastRowCount) {
    var newRowRange = sheet.getRange(currentRowCount, 1, 1, sheet.getLastColumn());
    var newRowValues = newRowRange.getValues()[0];

    Logger.log("New row added: " + newRowValues);

    // Send email notification
    sendEmailNotification(sheet, newRowValues);
  } else {
    Logger.log("Edit did not add a new row or was not on the target sheet.");
  }

  // Update the stored row count
  properties.setProperty('lastRowCount', currentRowCount);
}

function sendEmailNotification(sheet, newRowValues) {
  var emailAddress = Session.getActiveUser().getEmail();
  var subject = "New Row Added to " + sheet.getName();
  var message = "A new row has been added to the sheet " + sheet.getName() + ":\n\n";
  
  newRowValues.forEach(function(cellValue, index) {
    var columnName = sheet.getRange(1, index + 1).getValue();
    message += columnName + ": " + cellValue + "\n";
  });
  
  MailApp.sendEmail(emailAddress, subject, message);
  Logger.log("Email sent to: " + emailAddress);
}

function testEmailNotification() {
  var sheetName = "NewEntries";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    Logger.log("Sheet not found: " + sheetName);
    return;
  }

  // Simulate the new row values
  var emailAddress = Session.getActiveUser().getEmail();
  var newRowValues = ["Test Name", emailAddress, "Thu, Jun 6, 2024"];
  
  // Log the simulated new row
  Logger.log("Simulated new row: " + newRowValues);

  // Send email notification with the simulated new row
  sendEmailNotification(sheet, newRowValues);
}

Explanation

1. onChange(e) Function: This function is triggered whenever there is a change in the spreadsheet. It checks if a new row has been added by comparing the current number of rows with the previously stored number of rows.
2. sendEmailNotification(sheet, newRowValues) Function: This function constructs and sends an email notification with the details of the new row.
3. testEmailNotification() Function: This function simulates the addition of a new row and sends an email notification. It is useful for testing purposes.

Setting Up the Trigger

1. Open the Script Editor: Go to Extensions > Apps Script.
2. Replace the Existing Script: Replace any existing code with the updated script above.
3. Save the Script: Save the script with a meaningful name, such as “NewRowNotification”.
4. Add Trigger: In the Apps Script editor, go to Triggers (the clock icon on the left side).
5. Set Up Trigger: Click on + Add Trigger, choose onChange from the function dropdown, set the event type to “On change”, and save the trigger.

Testing the Script

1. Run the Test Function: In the Apps Script editor, select the testEmailNotification function from the dropdown and click the run button (triangle icon) to execute the function.
2. Check Logs: Go to View > Logs in the Apps Script editor to see the log messages.
3. Verify Email: Verify that an email notification is sent with the simulated new row details.

Conclusion

By using the onChange trigger and comparing the number of rows before and after a change, this script can accurately detect when a new row is added and send an email notification. This approach ensures that you stay informed about changes in your data, even if the built-in triggers do not behave as expected.