Google-sheets – How to use user-defined functions for custom formatting

google sheetsgoogle-apps-script

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 correct1. 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.

Best Answer

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.