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

Question

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

• Went to the '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.

• In the worksheet, the selected cells and the Conditional Formatting dialog box

• A new rule has been created and the following is printed in the 'custom formula is:' field

   =foo(param1, param2, param3) 

Unfortunately, this did not work.

---

Adding

Here is a sample sheet ...

See two tasks there. My goal is for the "task name" to be automatically written inside the yellow field (see Task on line 6, where I entered the value manually).

I already tried: - Assign to each cell in the range H5:BB7 following formula: =if(H$4=D5; B5; "")
This checks to see if the start date matches the cell date and displays the name of the task. This does the trick, however, the contents of the cell with the name of the task are truncated even if "overflow" is enabled because the next cell is not empty.

Answer 1

Short answer

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

Explanation

In Google Sheets, the custom function is similar to the built-in spreadsheet functions, but written by the user using the Google Apps Script editor. They cannot change other objects, return only one or several values.

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

Test

To test the statement in the short answer, I created a by doing the following:

• Create a simple custom function

  function two () {
   return 2;
 }

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

  = two () = 2

Result: Nothing has changed.

References

• Custom Sheets features

Answer 2

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

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

i.e. format a cell based on the range of other cells in this row.

My solution was to put the above formula in column P. Then I changed the user conditional formatting formula to =P3

Worked like a charm. Any change to H3: M3 will invoke hasGreen and update the P3 value. Conditional formatting will mark any change in P3 and adjust the formatting.