Google script to send conditional emails based on cells in google sheets

Question

I have a google sheet that I would like to have generate an email alert when one column is greater than the other. Specifically, column F > column G. Here is what I have so far, any advice would be greatly appreciated, as I do not have much skill writing functions.

function readCell() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Watch list");  
    var value = sheet.getRange("F2").getValue();
    var value1 = sheet.getRange("G2").getValue();
    if(value>value1) MailApp.sendEmail('[email protected]', 'subject',     'message');
};

Currently this only attempts to compare cell F2 to cell G2. Is there a way to make the function compare the entire F column against column G, and generate an email for each individual case where Fx > Gx ?

Thank you!!

Harold Harold · Accepted Answer · 2016-07-26T21:54:13

You have to loop all over the range.
first instead of getting the content of one cell you'll need to get the content of all the column:

var value = sheet.getRange("F2").getValue();

become that

var values = sheet.getRange("F2:F").getValues();

(same for value1)
then you need to create an empty table that will collect the results:

var results = [];

and now you need to loop throught all the values:

for(var i=0;i<values.length;i++){
//do the comparaison and store result if greater for example
}

then you may send the result. all put together it give something like that:

function readCell() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Watch list");  
    var values = sheet.getRange("F2:F").getValues();
    var value1s = sheet.getRange("G2:G").getValues();
    var results = [];
    for(var i=0;i<values.length;i++){
      if(values[i]<value1s[i]){
        results.push("alert on line: "+(i+2)); // +2 because the loop start at zero and first line is the second one (F2)
      }
    }
    MailApp.sendEmail('[email protected]', 'subject',     results.join("\n"));
};

If you want to trigger that function automatically you'll also need to change the way you call the spreadsheet (instead of getActive.... you'll need to use openById)

Google script to send conditional emails based on cells in google sheets

1 Answers