Send email based on cell change in google sheets

Question

Please help me with a script which sends an email on change in a value of a cell, for example if the value is less than 1 in column F then it has to pick up the email id from column g and text from h (here the body of the text is same for all the mails) and send an email. So each time the value goes below 1 it has to send an email for the relevant row and it should not repeat the mail for the previous row where it has already been sent since that value of less than 1 will not change once the email has been sent.

score 0 · Accepted Answer · edited May 23 '17 at 12:00

First you will need to write a Script under "Tools->Script Editor" The script will look something like this:

function sendEmail() { 
    // return all data in active spreadsheet
    var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues()
    for (i in values.length) { //iterate over each row
        // get value of column 6 (aka F) for this row 
        // this is your conditional cell for emailing
        var data= values[i][6];
        // get email address from column g (aka G)
        var emailAddress = values[i][7];
        var subject = "Value Less Than One"
        var message = values[i][8];
        // if data cell is less than one, send email for this row
        if (data < 1) ;{
            MailApp.sendEmail(emailAddress, subject, message);
        }
    }
}

Then in the Script Editor you will need to add a trigger in Resources->Current project's triggers that triggers sendEmail() "on change".

You can find more about Google Scripting in their documentation or in other SO questions:

Send email based on cell change in google sheets

1 Answers1