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:

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.

In sheet, selected cells and opened 'conditional formatting' dialog
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.

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? — Alan Wells
Hi Sandy, see my addition above. The script indeed performs write action. — bash2day

Rubén Rubén · Accepted Answer · 2016-02-16T00:09:51

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:

Create a simple custom function

function two() {
  return 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