Send email when a Google Sheet is edited

Suppose you're a school teacher and you're planning an upcoming Parent-Teacher conference. Instead of using a printed sign up sheet, you decide to use a Google Sheets spreadsheet where parents can sign up for a meeting slot.

In the past you've had parents forget which slot they signed up for so you're hoping that a shared Google Sheet that they can access at any time will help solve this problem. You also want to receive an email notification whenever a parent signs up for a slot. This will help you keep track of who hasn't signed up yet so you can remind them when they drop off or pick up their kids.

In this tutorial, I'll show you how to send email notifications whenever a Google Sheets spreadsheet is edited. We'll use Google Sheets and some Google Apps Script code to create this "application".

What you will build in this tutorial

  • First, you will create a spreadsheet where parents can sign up for a slot.

  • Next, you will create a HTML email template and write some Apps Script code to send out emails.

  • Finally, you will automate sending this email notification whenever your spreadsheet is edited by a parent.

Below is a screenshot of the email you'll receive whenever a parent signs up for a slot. The email includes all of the information in the spreadsheet and it highlights the row that was edited.

A screenshot of an email notification that you will be building in this tutorial.

Prerequisites

This tutorial assumes that you are reasonably familiar with Google Sheets and Google Apps Script. In particular, it assumes that you know:

If you're not familiar with a concept listed above, you can read the articles that I've linked to to learn more. If something is still unclear, please don't hesitate to let me know using the form at the bottom of this post.

Five steps to begin sending an email notification whenever your Google Sheets spreadsheet is edited

Step 1 — Create a Google Sheets spreadsheet where parents can sign up for a meeting slot

Create a sheet containing three columns: Teacher, Time slot and Child name. Since the class has two teachers and twelve kids, you decide to create six 25-minute long slots between 10:00 AM and 1:00 PM with 5-minute breaks in-between slots. Delete all the rows and columns in the spreadsheet that you will not be using.

Parents can sign up for a slot by entering their child's name in the third column (Column C).

A screenshot of the Parent-Teacher conference sign up spreadsheet. It has three columns: Teacher, Time slot, Child name. The spreadsheet has a header row and 12 rows (one per slot) where parents can sign up.

Step 2 — Create an Apps Script function that will send out email notifications

Step 2.1 — Create the script

Open the Script editor by selecting Tools —> Script editor and replace the code in the editor with the code below.

//@OnlyCurrentDoc
function processEdit(e) {  
  MailApp.sendEmail({
    to: "youremail@example.com",
    subject: "New sign up -- parent teachers conference",
    body: "The Parent-Teacher conference sign up sheet was edited."
  });
}

How does the above code work?

  • The processEdit() function will be run (by the trigger that you'll be setting up shortly) whenever your spreadsheet is edited.

  • The argument e is an object containing information about the edits that were made. For example, its range property will tell you the cell (or the range) that was edited.

  • The function body uses the MailApp object to send an email.

  • The //@OnlyCurrentDoc annotation tells Apps Script that you only want your script to get access to this spreadsheet and not your other files in Google Drive.

Step 2.2 — Test your script by running it

Step 2.2.1 — Click the run button (play icon) to run the processEdit function.

A screenshot of the menu bar in the Apps Script editor. The function processEdit is selected from the drop down menu. Clicking the run button (play icon) will run the function.

Step 2.2.2 — Authorize your script

Apps Script will ask you to authorize your script. Click Review Permissions to continue.

You might be asked to select a Google Account if you are signed into multiple accounts.

Screenshot of a dialog asking you to authorize your script.

Google will warn you that your app isn't verified. Whenever you see this warning, you should only proceed if you trust the developer that created the script. In this case, you are the developer so it is fine to proceed. Click Advanced to continue.

A dialog warning you that your app hasn

Click the link at the bottom (i.e., the one called Go to <your script's name> ...) to continue.

A dialog warning you to proceed with caution since Google has not verified your app.

Review the permissions carefully and assuming you're OK with granting these permissions, click Allow to complete the authorization process.

In this case, the script needs two permissions:

A dialog that displays the permissions that your script needs to run. Clicking Allow will grant your script these permissions.

Step 2.3 — Check if you received the notification email

Check your email inbox. If you're script ran successfully, you should receive an email like the one below.

Screenshot of an email inbox showing the email sent by the script.

Step 3 — Set up a Spreadsheet Trigger to run your script whenever the spreadsheet is edited

So far you've created a script that will send out emails whenever you run it. The next step is to make it send out emails automatically.

A trigger is a feature in Google Apps Script that will automatically run your script whenever a specific event occurs in your spreadsheet.

We'll create an Installable Spreadsheet Trigger to run the processEdit() function whenever your spreadsheet is edited.

Step 3.1 — Click the clock button to open the Triggers page

Screen shot of the toolbar in Apps Script. Clicking the clock button on the toolbar will open the Triggers page.

Step 3.2 — Click + Add Trigger to create a new trigger

Screenshot of the triggers page. No triggers have been set up so the page is empty.

Step 3.3 — Configure the trigger

Choose the following settings to configure your trigger correctly:

  • Function to run: proceddEdit

  • Deployment: Head

  • Event source: From spreadsheet

  • Event type: On edit

Then click Save to create the trigger.

Screenshot of a dialog where you can configure your trigger.

Step 3.4 — Authorize your script to run automatically

When you click Save, you might be asked to authorize your script again and grant it additional permissions. In particular, your script will now need permission from you "to run when you are not present". Once you click Allow, the trigger will be created.

Screenshot of an authorization dialog listing additional permissions that your script needs to run. Clicking Allow will grant your script these permissions.

Step 3.5 — You're done! Confirm that your new trigger is listed on the Triggers page

Screenshot of the Triggers page. The trigger you just created is listed on the page.

Step 4 — Confirm that emails are sent to you whenever your spreadsheet is edited

Edit your spreadsheet and confirm that you're receiving email notifications like the one below.

Screenshot of an email inbox showing the email notification sent by your script.

If it worked, you've successfully set up automated email notifications whenever your spreadsheet is edited. Congratulations!

Step 5 — Convert your email to a HTML template to highlight the changes that were made in the Spreadsheet

The email notification you created isn't that useful since it tells you someone edited your spreadsheet but it does not tell you what was edited.

Screenshot showing the body of the email sent by your script. The email

The notification will be more useful if it told you what was edited in your spreadsheet.

Screenshot of an HTML email. The email has a table containing information from your spreadsheet. The row that was edited is highlighted in the table.

To implement these changes, you need to first create a HTML template for the email and then modify your script to use this template.

Step 5.1 — Create a HTML template

Step 5.1.1 — Create a HTML file

Select File —> New —> HTML file from the menu bar.

Screenshot that shows the File menu open. The menu item New is selected and within the New sub-menu, the menu item HTML file is selected.

Enter Template.html as the name of your file.

Step 5.1.2 — Replace the contents of Template.html with the HTML code below

A parent just signed up for the upcoming Parent-Teachers conference. Please see the highlighted row below for information about the new sign up.
<hr>
<br>
<table cellpadding='5'>
  <tr>
    <th bgcolor='#eaeaea'><?= headerRow[0] ?></th>
    <th bgcolor='#eaeaea'><?= headerRow[1] ?></th>
    <th bgcolor='#eaeaea'><?= headerRow[2] ?></th>
  </tr>
  <? var rowIndex = 3; ?>
  <? for(var i = 0; i < rows.length; i++) { ?>
  <?   var background = rowIndex === editedRow ? "#e06666" : "#ffffff";  ?>
  <tr>
    <td bgcolor='<?= background ?>'><?= rows[i][0] ?></td>
    <td bgcolor='<?= background ?>'><?= rows[i][1] ?></td>
    <td bgcolor='<?= background ?>'><?= rows[i][2] ?></td>
  </tr>
  <?    rowIndex = rowIndex + 1; ?>
  <? } ?>
</table>

Step 5.2 — Create a named range to make it easy for your code to access the information in the sign up sheet

Select the range A2:C:14 since this is the information that you'll be including in the email as a HTML table.

Screenshot of the sign up spreadsheet with the range A2:C14 selected.

With the range selected, right click and select Define named range from the menu.

Screenshot of the menu that appears when you right click on a selected range. The menu item to define a named range is highlighted.

Enter "signups" as the name of your range and click Done to create a named range.

Screenshot of the Sidebar menu to create a named range. The Sidebar has a text field when you enter the name of the range.

You should see the named range displayed in the sidebar.

Screenshot of the Sidebar to create a named range. It displays the named range called "signups" that you just created.

Step 5.3 — Modify your script to use the HTML template

//@OnlyCurrentDoc
function processEdit(e) {
  var sheet = SpreadsheetApp.getActive();
  var rows = sheet.getRangeByName("signups").getValues();
  var headerRow = rows.shift();
  var editedRow = e.range.getRow();
  
  var template = HtmlService.createTemplateFromFile("Template");
  template.headerRow = headerRow;
  template.editedRow = editedRow;
  template.rows = rows;
  
  var html = template.evaluate().getContent();
  
  MailApp.sendEmail({
    to: "youremail@example.com",
    subject: "New sign up -- parent teachers conference",
    htmlBody: html
  });
}

How does the above code work?

Let me describe how the above code works at a high-level so you can learn from it and also modify it to suit your needs.

This code will run whenever your spreadsheet is edited. When the trigger runs your script, it will also pass your script some information about which spreadsheet the trigger is for and the range that was edited.

The code does three things:

  • First, it reads all the information in the named "signups".

  • Then, it uses this information creates a few variables that will be used to put together the HTML content.

  • The variable headerRow contains the column headers (first row in the spreadsheet).

  • The variable editedRow contains the row that was edited.

  • The variable rows contains all the rows in the sign up sheet with the exception of the header row (which is now saved in the variable headerRow).

  • Next, these variables are passed to the template and the template is rendered by evaluating it. At this point, the HTML for your email has been generated.

  • Finally, the email is sent using the generated HTML code.

Step 5.3 — Test your shiny new email template

Instead of receiving an email that only tells you something was edited, you'll now receive an email that shows you exactly what was edited.

Screenshot of the HTML email sent by your script. The email has a HTML table that displays information from the sign up spreadsheet. The row that was edited is highlighted in the table.

[Optional] Add protections to your sheet to ensure parents can only edit certain cells

You probably do not want parents changing the structure of the sheet (i.e., adding columns or rows) or editing the time slots etc. So, the next step is to add protections to the sheet so that parents can only edit the cells in the spreadsheet that are relevant to them. In the spreadsheet above, this would be the range C3:C14.

To add protections, select Data from the menu and then Protected sheets and ranges from the dropdown menu.

Screenshot of the spreadsheet with the Data menu selected. The menu item "Protected sheets and ranges" is highlighted.

Then, select each range that you want to protect and set permissions so that only you can edit them. First, select + Add a sheet or range from the side bar menu.

Screenshot of the "Protected sheets and ranges" Sidebar panel. You can protect a sheet or a range from this Sidebar panel. Screenshot showing you how to create a protected range. You need to select the range you want to protect and then click the "Set permissions" button.

Then select the first range you want to protect and then press OK. Start with the range A1:C2 to protect the first two rows.

Screenshot showing you how to create a protected range. You need to select the range you want to protect and then click the "Set permissions" button.

The final step is to set permissions so that only you can edit the range.

Screenshot showing you how to create a protected range. You need to select the range you want to protect and then click the "Set permissions" button. Screenshot of the dialog where you can set permissions for a range.

That's it, you've now added protections to the range A1:C2. Next, you need to protect the range A3:B14 to prevent parents from editing the teachers' names and the time slots. When you're done with this step, parents will only be able to edit the range C3:C14 (highlighted in green below).

Screenshot of the sign up spreadsheet with a range highlighted. This is the range that users of your spreadsheet will be able to edit. Other ranges have been protected and users will not be able to edit them.

Conclusion

This tutorial showed you how to send email notifications whenever a Google Sheets spreadsheet is edited. You learned:

You also created a really cool Parent-Teacher sign up application with just a few lines of code!

Thanks for reading!


Have feedback for me?

I'd appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!