Google Spreadsheet Email Notification when cell contents is above certain value

Question:

( by Timo Rietveld )

Hi,

In Google Spreadsheet, I'd like to get an e-mail notfication when a cell's content is above a certain value. Thus, the current notification functionality does not quite fit my criteria, as it only sends an email when a cell has been 'changed'. I'd like to specify that it ONLY sends an notification message when the content of the cell is above a certain value. Does anyone possibly know if there's a script for this?

Thanks in advance!

Timo Rietveld

Solution:

Have a look at the following script code:

///////////////

function readCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");  
  var value = sheet.getRange("C5").getValue();
  if(value>50) sendEmail(value);
};

function sendEmail(value) {
  var email = "kishan.pionero@gmail.com";
  var subject = "Auto Alert Notification, Value of cell C5 is now " + value+" (greater than 50)";
  var body = "This is an automated generated email, Value of cell C5 is now " + value + " (greater than 50)";
  
  var oauthConfig = UrlFetchApp.addOAuthService("google");
  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https://spreadsheets.google.com/feeds/");
  oauthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  MailApp.sendEmail(email,subject,body); 

};

///////////////


In the above code change my email address to your's. And also change the sheet name and cell as per your requirement.

And then set the trigger on function readCell() for very minute.

And now to set the trigger you have to go to "Script editor" and there you have to click on the trigger icon below the "Publish" tool bar command. Have a look at the screenshot below:

Then click on "Add a new trigger", then in Run select "readCell" and in Events click on "Time-driven" and then select every minute to execute this trigger. As Value of Cell C5 can be changed at any time.

I hope this script would have solved your problems of getting notification when a value of predefined cell gets over a predefined value.

And If you are not much familiar with scripts then check out the following link:

http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

Kishan.

iGoogleDrive