How to send email notifications on Google Spreadsheets if any cell value changes

How to send email notifications on Google Spreadsheets if any cell value changes

Working on Google Spreadsheets is fun, you can do a lot with Google Spreadsheet. It’s easy and convenient.

You can automate the Google Spreadsheet by using a Google Script editor.

Here is the solution!

In this tip, you will learn How to send email notifications on Google Spreadsheets if any cell data changes

Let’s assume a Google Spreadsheet named “Total Sales” has a data from column A to E. Whenever any data changes in column A to E, it sends the auto notification to file owner.

#Step 1  – Go to Google Spreadsheet

# Step 2 – Go to Tools>>Script Editor

Google Spreadsheet Script Editor

Google Script Code for sending an auto email notification if any cell value changes in the dataset   

function sendEmailAlert() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var data = ss.getActiveSheet().getActiveCell().getA1Notation();
var sheetname = ss.getActiveSheet().getName();
var user = Session.getActiveUser().getEmail();
var Toemail = 'myemailid_1234@gmail.com';
var subject = 'New Entry -' + ss.getName();
var body = 'Your file has a new entry in - ' + sheetname + ' Updated by - ' + user +
' check file- ' + ss.getUrl();

if(data.indexOf('A:E')!=-1.23456789) {

MailApp.sendEmail(Toemail,subject, body);
 }

};

#Step 3 – Paste the above code in the Google Script editor window.

Change email id at line 7 in the code editor, you can add multiple email ids, separating by a comma(,)

E.g,   var Toemail = 'emailid1@gmail.com, emailid2@gmail.com';

Google Script Code for sending an auto email notification when any cell value changes in the dataset   

#Step 4 – click on the watch icon to send the auto email triggers

Email Trigger - Google Spreadsheet

#Step 5 – Add Email Trigger – Set as “on edit” . It’ll ask you for the permission, click “Allow” and proceed.

#Tip – You can select the event type for an auto email alert, whether you want to send email on Edit or Change or when the file Opens. It can be Time-driven also, select even source as Time-driven if you want to send the file after some time intervals. 

Add Email Trigger - Google Spreadsheet

#Step 6 – Test the email trigger by clicking the Play button in the Google Script editor window.

How to send email notifications on Google Spreadsheets if any cell value changes to a certain value 

Let’s assume a Google Spreadsheet named “Total Sales” has a data from column A to E. Where column “C” represents total Qty sold and you want to send the email notification if the user enters a Qty value greater (>) than 700.  

Here is the code!

Just follow the above steps and paste the below code in Google Script editor window.

function sendEmailonEdit() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var cellValue = ss.getActiveSheet().getActiveRange().getA1Notation();
var getColumn = ss.getActiveSheet().getActiveRange().getColumn();
var sheetname = ss.getActiveSheet().getName();
var user = Session.getActiveUser().getEmail();
var Toemail = 'myemailid_1234@gmail.com';
var subject = 'New Entry -' + ss.getName();
var body = 'Your file has a new entry in - ' + sheetname + ' Updated by - ' + user + ' New Value in - '+ cellValue + '= '+ss.getActiveCell().getValue()+
' check file- ' + ss.getUrl();

if(user !='myemailid_1111@gmail.com' && Number(ss.getActiveCell().getValue()> 700) && getColumn ==3) {

MailApp.sendEmail(Toemail,subject, body);

}

};

Change the actual email id in the above code for the users where you want email notification (In Toemail variable). In the If logical put the email id for the user where you don’t want any notification to be sent.

COMMENTS (11)

  • comment-avatar

    Heloo, thanks for your help.
    But I have a another task: I need an email to be sent to an adress from column B when the cell of the same row in column E is changed.
    The case is: a person send a google form with his email adress, data from it goes to a sheet, when I mark status in column E – feedback mail goes back to a form sender on the adress from column B. But it should not send mails to adresses from previous rows from column B. Just like one by one – change data in a row – send a mail to an adress in the same row/
    Help plz, I’ll appriciate it.

  • comment-avatar
    Ben Clark April 20, 2019

    Is it possible to get an email notification when a cell value changes to a specific query?

    I want to receive an email that says ‘you have mail’ every time our residential mail spreadsheet is updated with my room number.

    I.e. cells will be empty, but when the room number is added, I receive an email.

    IFTTT does not offer this currently – only cell changes.

    Thanks,
    ben

    • comment-avatar

      Hi Ben,

      I believe the second code should work in your case, else please share the google sample sheet, will happy to help.

      Regards

  • comment-avatar

    Hi all, here every person is sharing such knowledge, so it’s nice to
    read this web site, and I used to pay a visit this web site
    all the time.

  • comment-avatar

    Outstanding post but I was wondering if you could write
    a litte more on this topic? I’d be very thankful if you could elaborate a little
    bit further. Appreciate it!

  • comment-avatar

    This works great. How can I edit the formula so it only pays attention to a specific range? I only want it to look at changes in column B. I tried editing the formula to say: “if(data.indexOf(‘$B:$B’)!=-1.23456789)” but it didn’t work

    • comment-avatar

      function sendEmailAlert2() {

      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var cellValue = ss.getActiveSheet().getActiveRange().getA1Notation();
      var getColumn = ss.getActiveSheet().getActiveRange().getColumn();
      var sheetname = ss.getActiveSheet().getName();
      var user = Session.getActiveUser().getEmail();
      var Toemail = ‘myemailid@gmail.com’;
      var subject = ‘New Entry -‘ + ss.getName();
      var body = ‘Your file has a new entry in – ‘ + sheetname + ‘ Updated by – ‘ + user + ‘ New Value in – ‘+ cellValue + ‘= ‘+ss.getActiveCell().getValue()+
      ‘ check file- ‘ + ss.getUrl();

      if(Number(ss.getActiveCell().getValue()!=-1.23456789) && getColumn ==2) {

      MailApp.sendEmail(Toemail,subject, body);

      }

      };

  • comment-avatar

    I’m not that much of a internet reader to be honest but your blogs really nice, keep it up!
    I’ll go ahead and bookmark your website to come back in the future.
    Many thanks

  • comment-avatar

    Hello there, You’ve performed a fantastic job. I’ll definitely digg it and individually suggest to my friends. I am sure they’ll be benefited from this site.