1

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.