Connecting Automation to conditional formatting

gventimiglia

I am trying to figure out a way to attach automation to conditional formatting. For example, I have 4 columns, with the last one being a checkbox or another standardized trigger. I want to send out a notification that a row has been added, only when the checkbox in the last column is clicked. I set that up already because it does not need conditional formatting, and that works fine. However, I have conditional formatting set up to change the color of the row to red if the checkbox is clicked and not all columns are filled out, meaning a field is missing. I also have formatting set up to change the row to yellow if the row is filled out but the checkbox has not been clicked. I want to send another notification out at the end of the day every day if there are any rows that are red or yellow, to signal that either a field is missing in a notification that was already sent, or that a notification has not been sent because the checkmark is not clicked. How can I connect automation to conditional formatting in that way, or is there another way I can achieve the same effect?

Kerry St. Thomas

Conditional formatting doesn't trigger automations DIRECTLY. Fortunately though, by thinking through the logic of what the conditional formatting shows, you've done a good amount of the work. You'll just need to translate that conditional formatting into a column formula: IF the checkbox is checked AND this field is blank OR that field is blank OR the other field is blank, update to Status = RED; IF all these fields are not blank AND the checkbox is not checked, update to Status = YELLOW… and so on with whatever other conditional formatting you have. You can then use this Status column formula to trigger your end-of-day automation - if Status Is One Of (Yellow or Red) then send out a notification. (As an aside, you can also set your conditional formatting to be linked to that Status column instead of all the logic you have it based on - this will be helpful if you ever need to change the logic of what RED means; you'd only need to update that formula and not the conditional formatting also.)

Good luck, and happy Smartsheeting!