Conditional formatting in Google Sheets: Can I use custom function in the 'custom formula is:' field?

Question

While applying conditional formatting in Google spreadsheet, I was wondering if it's possible to use a custom function that I created via script editor in the field 'custom formula is:'. Here is what I did:

1. Went to 'script editor' and typed my fn as follows:

   function foo (param1, param2, param3) {
     if (condition to check) {
       ...some action;
       return true;
     } else {
       return false;
     }
   }
   

   and saved.

2. In sheet, selected cells and opened 'conditional formatting' dialog

3. Created new rule and in the field 'custom formula is:' typed the following

   =foo(param1, param2, param3)
   

Unfortunately, this didn't work.

---

Addition

Here is the sample sheet...

See the two tasks in there. My aim is to have the 'task title' automatically written inside of the yellow field (see the task in row 6 where I entered the value manually).

What I already tried was: - Assign to every cell in range H5:BB7 following formula: =if(H$4=D5; B5; "")
This checks if the start date equals the cell date and displays the task title. That does the trick, however the content of the cell with task title is clipped even if 'overflow' is on because the next cell is not empty.

Answer 1

Short answer

A custom function can't be used as a part of a custom formula in the Google Sheets' conditional formatting built-in user interface.

Explanation

In Google Sheets, a custom function is like spreadsheet built-in functions but written by the user using the Google Apps Script editor. They can't modify other objects, only return one or multiple values.

By the other hand, a custom function only is calculated when it's added to a cell or when at least one of it's parameters changes. They can't use non-deterministic functions as parameters like NOW(), TODAY(), RAND() and RANDBETWEEN().

Test

In order to test the statement in the short answer, I created a did the following:

1. Create a simple custom function

function two() {
  return 2;
}

2. Add the custom function as part of a custom formula in the conditional formatting side panel

=two()=2

Result: Nothing changed.

References

• Custom Functions in Google Sheets

Answer 2

I have also found that custom functions cannot be used for conditional formatting. However, I located a pretty simple workaround.

The custom formula that I tried to use is: =hasGreen(CELL("address",$H3)&":"&CELL("address",$M3))

ie. format a cell based on a range of other cells in that row.

My solution was to place the above formula into column P. Then I changed my conditional formatting's custom formula to =P3

Worked like a charm. Any change to H3:M3 would call hasGreen and update the value of P3. The conditional formatting would note any change to P3, and adjust the formatting.