CODEWITHSUNDEEP
google-apps-scriptconditional-formatting
Freddy Roller
Freddy Roller

Reputation: 127

Google Apps Script Conditional Formatting whenNumberLessThan Unable to Reference Cell

I am building a spreadsheet to track expenses each month based on the budgeted value for each category. I want to highlight a cell green if the value is less than or equal to the budgeted amount and red if it is greater than the budgeted amount.

I can get it to work if I pass in the numeric value for the amount however the budget occasionally changes from month to month so I want to be able to spin up a new months spreadsheet and have it conditionally format the cell color based on a reference to the budget cell. I can do this in excel VBA but am unable to pass a reference into the whenNumberLessThan function.

//This errors out on trying to concatenate the row onto what is in the D column
var ruleRed = SpreadsheetApp.newConditionalFormatRuleBuilder()
.whenNumberLessThan("=D".concat(row))
.setBackground("#FF0000")
.setRanges([month.getRange("C".concat(row))])
.build();

//This works with 1200 or any other numeric value
var ruleRed = SpreadsheetApp.newConditionalFormatRuleBuilder()
.whenNumberLessThan(1200)
.setBackground("#FF0000")
.setRanges([month.getRange("C".concat(row))])
.build();

Upvotes: 1

Views: 199

Answers (2)

Freddy Roller
Freddy Roller

Reputation: 127

Thanks Jonas! This worked great! I should've thought to do that. Here is what I ended up using so I could specify the row on both the C and D columns

var ruleRed = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied("=C".concat(row)+"<$D$".concat(row))
.setBackground("#FF0000")
.setRanges([month.getRange("C".concat(row))])
.build();

Upvotes: 0

Jonatas Grandini
Jonatas Grandini

Reputation: 351

From my point of view, what you are planning to do would be easier by using just Sheets as you could use Conditional Formatting by custom formula (see documentation here). But If you do need to use Google Scripts, there is 2 possible choices:

1. Get the budget cell value on a script and use .whenNumberLessThan

You should get the cell value and then use .whenNumberLessThan, because as mentioned this function only accepts numbers. Code would look something like this:

var budgetValue = SpreadsheetApp.getRange("D".concat(row)).getValue();
var ruleRed = SpreadsheetApp.newConditionalFormatRuleBuilder()
.whenNumberLessThan(budgetValue)
.setBackground("#FF0000")
.setRanges([month.getRange("C".concat(row))])
.build();

2. Use the .whenFormulaSatisfied

This should use the same concept that I mentioned about Coditional with Custom Formulas

var ruleRed = SpreadsheetApp.newConditionalFormatRuleBuilder()
.whenFormulaSatisfied("=C1<$D$".concat(row))
.setBackground("#FF0000")
.setRanges([month.getRange("C".concat(row))])
.build();

Upvotes: 2

Related Questions