Sishir Roy
In an office where several people work on the same spreadsheet, it could be challenging to keep track of which modifications were made, when they were made, and by whom. When Google Sheets alerts are enabled, we receive email notifications anytime the spreadsheet is changed. In this article, we will demonstrate how to set up custom notifications in Google Sheets.
2 Easy Ways to Set Up Custom Notifications in Google Sheets
We will use the following datasheet to demonstrate the example of setting up custom notifications in Google Sheets. Any changes in the dataset by anyone will be notified through email.
1. Using Notification Settings Command
Receiving alerts whenever a change is made in a spreadsheet is possible due to the built-in command called Notification Settings in Google Sheets. There are two methods to use the Notification Settings command.
1.1 Edit Notifications
We can get notification alerts in two ways. One is Email Right Away and another is Email Daily Digest. You can choose any of the two ways based on your preference.
Email Right Away
After someone other than the owner makes changes to the spreadsheet, Email Right Away will send an email to the owner immediately. When your sheet is very sensitive then this way helps properly.
Steps:
- First, go to the Tools bar from the top menu bar. Choose Notification settings and then select the Edit notifications
- A dialog box called Set notification rules will open. It will show the email address where it will send the notification email. Now, choose the Any changes are made option from the Notify me when… option and select Email – right away from the Notify me with… Then, click the Save button to continue.
- The notification rule is set. You can add more notification rules if necessary. Afterward, select Done.
- Now imagine that one of your team members updates the spreadsheet.
- Then, you will receive the email right away.
Read More: How to Get Conditional Notifications in Google Sheets (2 Ways)
Email Daily Digest
Receiving notifications for every modification to a spreadsheet that is often changed and doesn’t include extremely sensitive information can clog your email. Instead, you may opt to receive a digest at the end of each day or at any other specified period that includes a list of all the changes.
Steps:
- First, follow the first step from the previous method. It will open the Set notification rules dialog box. Select the Add another notification rule
- It will display the email address to which the notification email will be delivered. Now, select the Any changes are made option from the Notify me when… option and select Email – daily digest from the Notify me with… option. To proceed, click the Save button.
- At the end of the day, you’ll get an email with a list of who changed the spreadsheet and when.
1.2 Comment Notifications
We might want to be notified when someone comments on the spreadsheet. We can use the Comment Notifications command to obtain this.
Steps:
- From the top menu bar, first, choose Tools. Now, select Notification settings and then select the Comment notifications option.
- A pop-up window called Notification settings will open. Now, select the All comments option from COMMENTS. To proceed, click the OK button.
- Now imagine a team member of yours commenting on the spreadsheet.
- You will receive the comment through email.
Read More: How to Enable Email Notification in Google Sheets (Step-by-Step)
2. Using Apps Script
In order to get or send notifications from Google Sheets, one may write codes in Apps Script. By using it, you can customize the notification system as you like. We will use the dataset below to demonstrate the example.
2.1 Receive Notification Email
Let us assume that we want to receive notification through email if anyone has an unpaid bill of more than $70. We can do this by following the below steps.
Steps:
- First, go to the Extensions tab from the top menu bar and select Apps Script.
- It will open a new window in our browser.
- Now, type the following code. After that, save the project by clicking the Save project icon and run it by clicking the Run icon.
function sendEmail() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var last_row = ss.getLastRow();
for (var i = 5; i<=last_row; i++){
var unpaid_bill = ss.getRange(i,4).getValue();
if (unpaid_bill>70){
var name = ss.getRange(i,2).getValue();
var email_Address = ss.getRange(i,3).getValue();
MailApp.sendEmail("[email protected]",
"Reminder: Unpaid Bill",
"Hello Sishir. Ask Mr. " + name +", to pay his/her bill.");
}
}
}
- Here, Mr. Frank and Mr. Sishir have unpaid bills greater than $70. So you will get a notification about it instantly.
Read More: How to Receive Notifications of Changes in Google Sheets
2.2 Send Notification Email
Let’s say we want to send a notification via email if anyone has an unpaid bill of more than $70 to pay his/her bill. We can do this by following the steps below.
Steps:
- First, follow the first step from the previous method. It will open a new window in your browser. Now, insert the following code and save it by clicking the Save project After that, click on the Run icon to run the project.
function sendEmail() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var last_row = ss.getLastRow();
for (var i = 5; i<=last_row; i++){
var unpaid_bill = ss.getRange(i,4).getValue();
if (unpaid_bill>70){
var name = ss.getRange(i,2).getValue();
var email_Address = ss.getRange(i,3).getValue();
MailApp.sendEmail(email_Address,
"Reminder: Unpaid Bill",
"Hello " + name +", you have unpaid bill. Please pay the bill.");
}
}
}
- Here, Mr. Frank and Mr. Sishir have unpaid bills greater than $70. So they will get a notification about it.
Read More: How to Send Email Based on Date in Google Sheets (2 Methods)
Things to Remember
- For the script to run successfully, you must allow all permissions through your email account.
Conclusion
In this article, we have demonstrated how to set up custom notifications according to your preferences using the Notification Settings command in Google Sheets. We have also demonstrated how to receive and send notification emails using Apps Script in Google Sheets. Please feel free to ask any questions or suggest any ideas in the comment section below. For additional information, visit officewheel.com.
