Automating Email Notifications with Google Apps Script: Sending Emails When a New Row is Added to Google Sheets

by

Google Apps Script provides a powerful way to automate tasks within the Google Workspace ecosystem. One useful application is automating email notifications based on data changes in Google Sheets. In this blog post, we will walk through an exercise to write a script that sends an email to a specific address when a new row is added to a Google Sheet. Additionally, we will explore how to use Apps Script to automate sending emails based on data changes in Google Sheets.

Prerequisites

Before we begin, ensure you have:

  • A Google account
  • Basic knowledge of JavaScript
  • A Google Sheet ready to use for this exercise
  • Access to Gmail

Step 1: Writing the Script

  1. Open the Apps Script Editor:
    • Open your Google Sheet, go to Extensions > Apps Script to open the script editor.
  2. Create a New Script:
    • Replace any code in the script editor with the following code to send an email when a new row is added:

function sendEmailNotification(e) {

  const sheet = e.source.getActiveSheet();

  const range = e.range;

  const row = range.getRow();

  const email = ‘recipient@example.com’; // Replace with the recipient’s email address

  const subject = ‘New Row Added to Google Sheet’;

  const message = `A new row has been added to your Google Sheet:

  Row Number: ${row}

  Data: ${sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0].join(‘, ‘)}`;

  MailApp.sendEmail(email, subject, message);

  Logger.log(‘Email sent to: ‘ + email);

}

Step 2: Setting Up the Trigger

  1. Install an On-Change Trigger:
    • In the Apps Script editor, click on the clock icon (Triggers) in the toolbar.
    • Click on + Add Trigger.
    • Choose sendEmailNotification as the function to run.
    • Select From spreadsheet for the event source.
    • Select On change for the event type.
    • Click Save.
  2. Authorize the Script:
    • The first time you set up the trigger, you will need to authorize it. Follow the prompts to grant the necessary permissions.

Step 3: Testing the Script

  1. Add a New Row to Your Google Sheet:
    • Go back to your Google Sheet and add a new row with some data.
  2. Check Your Email:
    • After adding the new row, check the specified email address. You should receive an email notification with the details of the new row.

Customizing the Script

  1. Dynamic Email Address:
    • Modify the script to use an email address from a specific cell in the Google Sheet.

function sendEmailNotification(e) {

  const sheet = e.source.getActiveSheet();

  const range = e.range;

  const row = range.getRow();

  const email = sheet.getRange(‘A1’).getValue(); // Assume the email address is stored in cell A1

  const subject = ‘New Row Added to Google Sheet’;

  const message = `A new row has been added to your Google Sheet:

  Row Number: ${row}

  Data: ${sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0].join(‘, ‘)}`;

  MailApp.sendEmail(email, subject, message);

  Logger.log(‘Email sent to: ‘ + email);

}

  1. Customizing Email Content:
    • Personalize the email content by including more details or formatting the message.

function sendEmailNotification(e) {

  const sheet = e.source.getActiveSheet();

  const range = e.range;

  const row = range.getRow();

  const email = ‘recipient@example.com’; // Replace with the recipient’s email address

  const subject = ‘New Row Added to Google Sheet’;

  const message = `

    <h2>New Row Added to Your Google Sheet</h2>

    <p><strong>Row Number:</strong> ${row}</p>

    <p><strong>Data:</strong> ${sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0].join(‘, ‘)}</p>`;

  MailApp.sendEmail({

    to: email,

    subject: subject,

    htmlBody: message

  });

  Logger.log(‘Email sent to: ‘ + email);

}

Using Apps Script to Automate Sending Emails Based on Data Changes

  1. On-Change Trigger:
    • The onChange trigger runs when any change is made to the spreadsheet. This can be used to detect new rows, updated cells, or other changes.
  2. On-Edit Trigger:
    • The onEdit trigger runs when a user makes an edit to the spreadsheet. This is useful for sending notifications based on specific edits or user actions.
  3. Time-Driven Trigger:
    • Time-driven triggers can automate email notifications at regular intervals, such as daily or weekly summaries of changes.
  4. Custom Triggers:
    • Create custom functions and set up triggers based on your specific needs, such as data validation, conditional formatting, or specific cell changes.

Conclusion

With this exercise, you’ve learned how to use Google Apps Script to send an email notification when a new row is added to a Google Sheet. This powerful automation can enhance your productivity and ensure you stay informed about important changes in your data. By exploring further customizations, you can tailor this functionality to fit your specific workflow and automate various tasks within Google Sheets.