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.

Undo

Does your script do read actions only? Custom formulas can not be linked to scripts that perform write actions. We don't know what ...some action; is. What does the cell with the custom formula state? Just stating: "this didn't work" does not provide enough information. Is there an error message? What is it? – Trench

Hi Sandy, see my addition above. The script indeed performs write action. – Undo

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

Hamrah

it's pretty lame that custom functions can't be used in conditional formatting custom formulas. i can verify that it isn't working for me, either. – Pelson

I "verified" the same thing but somehow thought it was my bug. Sheets should issue an error. – Praise

Sigh... was just defeated trying to write an isStrikethrough() custom function so I can use that in a conditional formatting formula. :P (The next answer is fine [adding an extra column hack] but pollutes the Sheet, which I don't want to do.) It's already bad enough that custom functions only take the value(s)... I wanted the cell range (not their values) passed to the custom function, and apparently I can't do that either. :P – Koressa

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.

Burtie