How to write Google script to send email notification from different range and condition?-CodePudding

My current script is working fine to send the email notification if BW5<BW9.. however, i am not sure on how to set the range since every row 9 needs to minus the respective row 5.. i.e. BW9 - BW5, BV9 - BV5 and etc..

and also, how can i set this email notification only to trigger if row 5 is less than row 9..

Thank you in advance for your guide..

function readCell() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");  
var values = sheet.getRange("BW5").getValues();
var value1s = sheet.getRange("BW9").getValues();
var results = [];
for(var i=0;i<values.length;i  ){
  if(values[i]<value1s[i]){
    results.push("Inventory is lower than stock target by "   (value1s[i] - values[i])
      //"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"));

};

CodePudding user response：

In your situation, how about the following modification?

From:

var values = sheet.getRange("BW5").getValues();
var value1s = sheet.getRange("BW9").getValues();
var results = [];
for(var i=0;i<values.length;i  ){
  if(values[i]<value1s[i]){
    results.push("Inventory is lower than stock target by "   (value1s[i] - values[i])
      //"alert on line: " (i 2)
    
    ); //  2 because the loop start at zero and first line is the second one (F2)
     
  } 
}

To:

var a1Notation = "BW5:BX9"; // Please set the range as a1Notation.
var values = sheet.getRange(a1Notation).getValues();
var v = [values[0], values.pop()];
var results = [];
for (var i = 0; i < v[0].length; i  ) {
  if (v[0][i] < v[1][i]) {
    results.push("Inventory is lower than stock target by "   (v[1][i] - v[0][i]);
      //"alert on line: " (i 2)

    ); //  2 because the loop start at zero and first line is the second one (F2)

  }
}

By this modification, in this sample, the range of "BW5:BX9" is used. Please modify this range for your actual situation.

CodePudding user response：

How about following modification

function readCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");  
  var values = sheet.getRange('BW5:5').getValues()[0]; //change accordingly
  var value1s = sheet.getRange('BW9:9').getValues()[0]; //change accordingly
  var results = [];
  for(var i=0;i<values.length;i  ){
    if(values[i]<value1s[i]){
      results.push("Inventory is lower than stock target by "   (value1s[i] - values[i])
      //"alert on line: " (i 2)
      
      ); //  2 because the loop start at zero and first line is the second one (F2)
      
    } 
  }