Skip to main content
edited tags
Link
Source Link

How to use user-defined functions for custom formatting?

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.