Create Trigger to run script based on conditional formatting

[Jessica Estrada]

Link to sheet at the end.

I created a script that totals up the number of highlighted cells in a column (highlighted cells are the lowest # in each row). 

I created a conditional formatting to automatically highlight the lowest number in each row:

Range = B23:I123
=B23=min(filter($B23:$I23,$B23:$I23>0))

The issue is: all the numbers in the row are not entered at the same time, so the conditional formatting can possibly change when a new cell in that row is filled in. When this happens, row 22 (Rounds Won, where the script is), does not re-run.

Meaning, a cell in one of the columns gets unhighlighted, but the total in row 22 stays the same.

HELP! How can I make it so that row 22 recalculates whenever something changes so that the number of highlighted cells in a column always matches the number in that column in row 22?

https://docs.google.com/spreadsheets/d/17PyJpNsVsbsU7sr8L285AoMynDXbjVhS1ESw4rFZps4/edit?usp=sharing

[Jessica Estrada]

Also, yes, I realize this is a silly example to be using but I have other places to use this knowledge that are work related (I'm in revenue ops, so always in excel and sheets!)

[Laurie Nason]

Hi Jessica,

Could you not use a helper column/row which actually calculates the value for the column/rows (by all means continue with the conditional formatting!) which you can then use (and hide if not necessary to see)?

Unfortunately, I can't see your spreadsheet to see exactly what you are trying to do. 

Laurie

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d97d5ecb-5ac7-4e58-b04d-34e06e236378n%40googlegroups.com.

[Jessica Estrada]

I thought I made it so that anyone with the link could edit it - sorry about that! You should be able to get into it now.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/C8bQIr6XOh8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BA7ZWKbUYyY%2Bp41wJ%3DNSDc%2BdAm%3DAj4w%2BqLENbMAoJWOuzRjzQ%40mail.gmail.com.

[Martin Molloy]

Hi Jessica

I don't know of a way to get a custom formula to recalc automatically.

So, I added a formula in column J to work out the winning score for each row and some formulas in rows 23 & 24 to work out the number of times the player matched the winning score.

That gives you the result without using a custom formula

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJUwzRguEHpioUeOU9_6f--QaFPZoJJtUqEWjSHmfT0MR5uSag%40mail.gmail.com.

[Laurie Nason]

Hi Jessica,

I second Martin's solution - nice job! And as he said, doesn't need custom formulas at all.

Laurie

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAO8s2Pjd%3Doi5Cxg%2BSz5rH17ShVnj0-cnNnTjri8QZqdvqBVP9Q%40mail.gmail.com.