How to pass range object in Custom 'whenFormulaSatisfued' function in Google Script?

Question

I am creating a custom function that will highlight a cell if its length is greater than 850. I have written the below code:

colIndex = getColByName('Response');
var range = sheet.getRange(1, colIndex, sheet.getLastRow());
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(`=LEN(${range})>850`)
    .setBackground("yellow")
    .setRanges([range])
    .build()
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Where getColByName() gets the index of the column using a header. The output of this function looks like this:

which does not solve my requirement. Thanks for your help in advance.

Answer 1

In your situation, how about modifying as follows?

From:

.whenFormulaSatisfied(`=LEN(${range})>850`)

To:

.whenFormulaSatisfied(`=LEN(${range.getA1Notation()})>850`)

• var range = sheet.getRange(1, colIndex, sheet.getLastRow()); returns the Range object. I thought that this might be the reason of your issue.

Reference:

• getA1Notation()