Tuesday, August 13, 2013

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:

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:

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,

7 comments:

  1. Excellent article Kishan. Won't this email be sent every minute as long as the value is greater than 50?

    ReplyDelete
  2. Thank you Kishan,

    I am just wondering if it is possible to do the following:

    Let say we have Column "B" as default values let say "1"
    So B1 = 1
    B2 = 3
    B3 = 1

    and Column "A" is a Description Example

    A1 = "SALES"
    A2 = "Purchase"
    A3 = "IT"

    How can I configure the script so that every time column B changes it sends an email saying "Purchase (A2) is up to 3 (B2)"

    Thank you

    ReplyDelete
  3. is there a way to apply this to an entire column rather than just a specific cell and have it email the row numbers?
    thank you

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Hi there is an error

    "Script is using OAuthConfig which has been shut down. Learn more at
    http://goo.gl/IwCSaV (line 13, file "Code")"

    Could you please update the script

    ReplyDelete
  6. Spot on, mate! Saved my day!

    function sendEmail(value) {

    MailApp.sendEmail({to: "address@company.com",
    subject: "Subject text " + value,
    name: "Google_Sheets_Alerter",
    replyTo: "noreply@company.com",
    htmlBody: "Alerter email body!
    ",
    cc: "whoever@gmail.com"});
    };

    ReplyDelete