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
withsheet
.