How to use user-defined functions for custom formatting?

Question

I’m writing a simple Google spreadsheet to list every account number which could come in handy to our non-profit in the future. Of course, one can expect some typo will get in some point in the future. To mitigate the issue, I’ve written a JavaScript function which ensure that an account number checksum is correct¹. As far as my tests go, this function works as expected. I currently a nice column of TRUEs right after the current numbers.

I would like to use this function in conditional formatting: if the checkSum is wrong, display the account number in red. Just using my function (with the right arguments in the right place) in the conditional formatting sidebar doesn’t work; even when I replace my function with the following:

function quiteTrue() {
    return true;
}

the conditional formatting doesn’t apply.

The formula I use in the sidebar is: =quiteTrue().

Any idea? Am I on a the track?

---

1. The reason why I use javascript instead of writing a formula is that letters are actually appropriate in these account numbers and are translated into number for checksum purposes the same way tr 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' '12345678912345678923456789' would. And I couldn’t find any kind of substitute functions for more than one argument at a time. SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(… look quite error prone to me.

Answer 1

Although the documentation never says so explicitly, custom functions cannot be used in conditional formatting. A workaround is to add another column with the output of the custom function (preferably optimized to handle an array at once), and then use that column in conditional formatting rules.