Conditionally Formatting Entire Rows

A few weeks ago at the Ontario Google Apps for Education Summit, I lamented that there currently is no way to apply the conditional formatting to an entire row based on the contents of one cell within the row.

If that's a bunch of gibberish to you, picture this menu in your spreadsheet:

You've got a spreadsheet with several columns. Perhaps a lot of columns. Maybe tens of columns, in fact. And you're using conditional formatting to visualize the data quickly and in a meaningful way. If you're not sure what conditional formatting is, here's a little primer. Basically, you set up rules and you can change the cell's background colour or text colour based on the contents of the cell.

But sometimes I want an entire row to change based on the contents of a column. For example, flag every row where the last column is less than 80%. That way, I can look at the left-most column without worrying about scrolling to the right to check the cell. Sure, I could just freeze the left-most column and scroll right, but what do I do if it's not the left-most column I'm interested in? Or if I'm conditionally formatting the left-most column and want to look at the right side of the spreadsheet?

So I spent the last little while creating an Add-on that will take the formatting from a column that's been conditionally formatted and apply it to the entire row. If your data changes, run the add-on again to update the formatting.

It took some time, since I had to learn how to generate a User Interface, store script properties (so that it remembers which column you've picked), and some other stuff. All in all, it was a great learning experience. I can't overstate the help I got from the Google Developers App-Script help pages, the Stack Overflow forums, and the Google+ Community of Script Builders and Users.

I have submitted it as an Add-on, but there are certainly changes that will need to be made before it's ready for public consumption. Until then, I'll continue playing with it, tweaking it until it does exactly what I want it to do, and I'll be sure to update when it's available for everyone. If you want a sneak-peek, you can copy the code and paste it into the script editor to try it out for yourself.