Seemanto Saha
Large spreadsheets in Google Sheets often require collaboration between dozens of people. Therefore, keeping track of every change made is quite a tedious task. However, you can use the Notifications settings command to get notified about which changes were made, by whom, and when. You can also receive and send emails using Apps Script for any conditional changes. In this article, I’ll demonstrate several methods on how to get these conditional notifications in Google Sheets.
A Sample of Practice Spreadsheet
You can copy our practice spreadsheet by clicking on the following link. The spreadsheet contains an overview of the datasheet and an outline of the described ways of getting conditional notifications.
2 Easy Ways of Getting Conditional Notifications in Google Sheets
First, let’s get familiar with our datasheet. It consists of names, email addresses, and due payments of several customers. Due payment will change whenever a customer buys anything new or clears their due payments. Keep reading to learn how to get notifications of such changes.
1. Using Notification Settings Command
There is a built-in command for getting notifications if any edit is made in Google Sheets. The command is called Notification Settings and there are two ways of using it.
1.1 Email Right Away
This option is useful when the information in the sheets is very sensitive. So, the owner will receive a notification whenever someone edits the spreadsheet.
Steps:
- Open your worksheet and go to the Tools From there first, click on Notification Settings and then click on Edit Notifications.
- In the pop-out window, you will find the email address where you want to receive the notification. For Notify me when…, select the option Any changes are made and for Notify me with… select the option Email – right away. Then click on Save.
- You can add another notification rule if you need. After that click on Done.
- Now, suppose a team member of yours changed the value of Cell D6 to $60.00.
- You will receive an email like the following where you will find who made changes to the spreadsheet and when.
Read More: How to Set Up Custom Notifications in Google Sheets (2 Ways)
1.2 Email Daily Digest
If your datasheet doesn’t contain very sensitive information and it is edited often, getting notifications for each edit will fill your mailbox with a boatload of notifications. Instead, you can get a digest at the end of the day or any specified time, where the record of all the edits will be present. Keep reading to know how.
Steps:
- Follow the first step of the previous method to open a pop-out window like the following. Select Any changes are made for Notify me when… option and Email – daily digest for Notify me with… Finally, click on Save.
- Click on Add another notification rule if you require it. Then click on Done.
- You will receive an email like the following at the end of the day.
2. Using Apps Script
You can write codes in Apps Script to receive or send conditional notifications in Google Sheets. Keep reading to know how.
2.1 Receive Email Using Apps Script
We will write a simple code in Apps Script that will send me an email whenever a customer’s due payment is more than $80.
Steps:
- A new window like the following will open in your browser. Rename the project and file. Delete the default code.
- Now, insert the following script:
function receiveConditionalNotifications() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var last_row = ss.getLastRow();
for (var i = 5; i<=last_row; i++){
var due_Payment = ss.getRange(i,4).getValue();
if (due_Payment>80){
var name = ss.getRange(i,2).getValue();
var email_Address = ss.getRange(i,3).getValue();
MailApp.sendEmail("[email protected]",
"Reminder: Due Payment",
"Hello Seemanto, ask Mr. " + name +
" to pay his/her dues.");
}
}
}
- After completing the script, first, click on the Save Project and then click on Run to apply the script. Allow all the permissions required with your email account.
- Here, Mr. Tony has a due payment greater than $80. So, you should receive an email to be notified about it.
- Open your mailbox and you will get to see a received email like the following.
Read More: How to Receive Notifications of Changes in Google Sheets
2.2 Send Email Using Apps Script
You can also send emails to others by using Apps Script. Here, we will send an email to the customers who have a due payment of $80 or more.
Steps:
- Follow the first three steps from the previous method to open a window for Apps Script, rename the project and file, and insert the following script:
function sendConditionalNotifications() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var last_row = ss.getLastRow();
for (var i = 5; i<=last_row; i++){
var due_Payment = ss.getRange(i,4).getValue();
if (due_Payment>80){
var name = ss.getRange(i,2).getValue();
var email_Address = ss.getRange(i,3).getValue();
MailApp.sendEmail(email_Address,
"Reminder: Due Payment",
"Hello " + name +", Pay your dues.");
}
}
}
- Now, click on the Save Project and then click on Run to apply the script.
- Here, two of the customers have a due payment greater than $80. So we’ll send an email to each of them through Apps Script.
- The customers will receive an email like the following after you run the script.
Read More: How to Send Email Based on Date in Google Sheets (2 Methods)
Things to Be Considered
- You have to allow all the permissions using your email account for the successful execution of the script.
- Use meaningful variable names while writing the script.
Conclusion
This concludes our article on how to get conditional notifications in Google Sheets. I hope the mentioned methods were sufficient for your requirements. Feel free to leave your thoughts on the article.
