Google Sheets – Troubleshooting Google Script ‘If’ Condition Not Working

formulasgoogle sheetsgoogle-apps-script

The purpose of the Google script below is to compare cell B2 & cell A5 (Sum of C2:M2) in active sheet/tab; if B2 is less than A5 than it will pop up an alert & simultaneously change the background color of B2 to red otherwise it will be white. The code seems to be Ok but it is only calculating sum in cell A5, and I feel that the if loop is not working.

Here is the code any help would be highly appreciable.

function onEdit(e) {
  // Set a comment on the edited cell to indicate when it was changed.
  //var range = e.range
  //range.setNote('Last modified: ' + new Date());
//var message = 'Alert';
  //var title = 'OTPS is over limit';
  //SpreadsheetApp.getActiveSpreadsheet().toast(message, title);
   //var currentSelection = SpreadsheetApp.getActiveSheet().getActiveSelection()

  var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

var cell = sheet.getRange("A5");
cell.setFormula("=SUM(c2:m2)");

   var value1 = dataSheet.getRange(2, 2).getValue();               //Getting value from B2 in "Raw Data"
    var value2 = dataSheet.getRange(5, 1).getValue();              //Getting value from A5 in "Raw Data"

  if (value1<value2){
    var message = 'Alert';
  var title = 'OTPS is over limit';
  SpreadsheetApp.getActiveSpreadsheet().toast(message, title);
   sheet.getRange(2,2).setBackground('red');
      } else {
       sheet.getRange(2,2).setBackground('white');
      } 

  }

Best Answer

I just made it work by replacing dataSheet with sheet.