1

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');
      } 

  }
0

I just made it work by replacing dataSheet with sheet.

| improve this answer | |
  • Please accept your own answer as the correct solution, as it might help somebody in the future as well. Thanks! – Alex May 20 at 8:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.