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.

My Goals for the Future

Image: OpenClipArt.org

After attending the Ontario Google Summit, I have realized a few things that will impact the training that I will be developing and delivering in the future. Every event that I attend, I learn something. Whether it's through attending the sessions given by an amazing presenter, or through reflecting on my own teaching and learning practice, I always come away knowing more than I did going in. This reflection helps me become a better teacher.

Here are some of the things I vow to do (or continue doing):

Less direct instruction.

I want to have my learners spend more time doing and less time listening. I love hands-on learning, and I believe it is one of the most effective ways of teaching and learning.

Break lessons into smaller pieces.

I tend to bite off more than I can chew. Covering all the cool, interesting, or unique applications of technology tools is difficult to do in a one-hour session. I will break my sessions into smaller pieces and focus on beginner, intermediate or advanced applications.

Stay focused on pedagogy.

Teaching the tools is important, but we need to know why we should be using the tools more than how to use them. Figuring out how to use a tool is as simple as doing a Google search and looking up instructions. Knowing how to apply that knowledge to support student learning isn't as simple.

Provide links to support.

This goes with the idea that pedagogy is important. Giving links to facilitate independent and continual learning is the way to set people up to take what they've learned outside the session, and to continue growing.

Geek out.

I'm a total geek. I love doing what I do, and I enjoy unleashing the awesome power of these tools. I'm going to write scripts, develop powerful tools for automation, and share all of that with my fellow geeks. Spreadsheets to do cool stuff, writing scripts to make life easier, or just bouncing ideas off one another. I'm going to keep at it. Sometimes I just need to remember that it can be intimidating to some, and to restrain myself at times. Hopefully not too much.

If This Then That test post

I am currently playing with the fantastic If This Then That web tool to try to integrate my Blogger and my Google Site more closely. This is a test post to see if IFTTT will automatically add this post to a spreadsheet row in my Google Drive. You can see the recipe here.

Ideally, I will then write a small script to automatically generate a new announcement page on my Google site. So it's a two-part process: first, IFTTT will update a spreadsheet with the blog post, then the spreadsheet will create a new announcements page in my Google site.

My reasoning behind it is this:

1. I would like to be able to announce something here on my blog, and automatically announce it on my website, too.
2. I want my Google Site to be as up-to-date as possible without adding extra work.
3. I'm the efficient sort of lazy. I won't decide not to do something because it's too much work, but if there's an easier way of doing it, I'll try to figure that out.

So, if you haven't learned anything from this post, don't worry. It's mostly just a test, but it's also a preview of something I'm working on. I will update you when I make some progress.