I am trying to figure out a script that will popup a box if a number in column F goes negative. I think I may be running into an issue due to the negative number generating from a formula as opposed to someone manually typing it in. Here is the script I have been working with:
function onEdit(event){
var sheet = event.source.getActiveSheet().getName()
var editedCell = event.range.getSheet().getActiveCell();
if(sheet=="Numbers"){
if(editedCell.getColumn() == 6 && event.value==-0.1){
Browser.msgBox('It looks like there is a negative number in Column F.');
}}}
I think it may have something to do with the 'editedcell' function but I am not 100%. Does anyone know of a better script that will popup a box even if a formula is generating a negative number (the value in column F will be a formula that may generate a negative)?
At this point, nothing happens with this code unless I manually type "-1" or some other negative in Column F.
The event object returns the range of the cells you edited, not the ones edited by a script or formulae. As well as this, there are a few things you can do to fix or optimise your code.
Firstly, you can change your conditional to optimise the script and only make calls when you need them - this I have done with a slightly different conditional order.
As well as this, rather than event.range.getSheet().getActiveCell() you need to specify the values of column F explicitly, and use the getDisplayValues() function as this will also pick up the values if the cell contains a formula:
function onEdit(event) {
var sheet = event.source.getActiveSheet();
if (sheet.getName() != "Numbers") {
return;
}
var values = sheet.getRange('F:F').getDisplayValues()
for (var i = 0; i < values.length; i++) {
if (values[i] < 0) {
Browser.msgBox('It looks like there is a negative number in Column F.');
return;
}
}
}