CODEWITHSUNDEEP
google-sheets
Anthony C
Anthony C

Reputation: 1

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

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!!

Upvotes: 0

Views: 8917

Answers (1)

Harold
Harold

Reputation: 3337

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)

Upvotes: 1

Related Questions