Send email notification when values in different columns increase

Question

I have a sheet with an array of values that goes from A1 to BK288. This array is a percentage calculation based on another spreadsheet that updates every 5 minutes. Now I would like to receive an email notification when one value on the same row in different columns increase and become greater then a fixed value. Then the email should contain that volue on the body and the title of the column on the subject. I wrote this script but is for a single cell, I don't know if there is a way to extend it for every column, apart writing a function for every column. Also, is there a way to trigger an automatic notification? Consider the sheet updates the values every 5 minutes. Thanks

function getValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Perc");
  var value = sheet.getRange("A288").getValue();
  var title = sheet.getRange("A1").getValue();
  if(value >= "2") sendEmail(value, title)
};


function sendEmail(value, title){
  var recipient="[email protected]";
  var subject=title + " price";
  var body=title + " price has changed by " + value + "%";
  MailApp.sendEmail(recipient, subject, body);
};

So each row have a different fixed value to compare to ? Where is this value on your sheet ? Can you provide us a sample of your sheet, for interpretation purpose ? — Pierre-Marie Richard
The fixed value is 2, and each column on the same row has a different value which is a percentage calculation. For example: A288=0, B288=0.5448800619, C288=-0.2566428034, D288=0.2315139654, E288=-0.8245011437, F288=-0.2449391254, ..., BK288=0.5229186801. When one of this value is equal or greater than 2 I would like to receive an email notification as in the function above — user4737227

Pierre-Marie Richard Pierre-Marie Richard · Accepted Answer · 2017-06-27T12:18:04

Here is a solution.

The data are put on a array and process column one after one other. I also add a solution when multiple product changed, so only one mail is send with all the informations needed, to prevend the daily quota to be reached.

Edit: Only analyse row 288

function compareValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Perc");
  var data = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  var list = []; // List of change greater than 2

  for(var i=0; i<data[0].length; i++){

    var title = data[0][i]; // Get the title of the element
    var value = parseFloat(data[data.length -1][i]); // Get the value on last row (288th)

    if(value >= 2){ // If the value greater than 2, add it on a list

      var element = [title, value];

      list.push(element)

    }
  }

  if(list.length == 1){ // If only 1 element changing, send classic mail
    sendEmail(list[0][1], list[0][0]);
  }
  if(list.length >= 2){ // If 2 or more element changing, send mail with all the change
    sendEmail(list);
  }
}

function sendEmail(value, title){
  var recipient="[email protected]";
  var subject=title + " price";
  var body=title + " price has changed by " + value + "%";
  MailApp.sendEmail(recipient, subject, body);
}

function sendEmail(list){
  var recipient="[email protected]";
  var subject="Multiples prices have changed";
  var body = "";
  for(var i=0; i<list.length;i++){ // Get throught the list to write the body
    body=body + list[i][0] + " price has changed by " + list[i][1] + "\%\n";
  }
  MailApp.sendEmail(recipient, subject, body);
}

Send email notification when values in different columns increase

1 Answers