Create Trigger to run script based on conditional formatting

40 views
Skip to first unread message

Jessica Estrada

unread,
Jan 12, 2022, 3:29:16 PMJan 12
to Google Apps Script Community
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?

Jessica Estrada

unread,
Jan 12, 2022, 4:08:58 PMJan 12
to Google Apps Script Community
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

unread,
Jan 13, 2022, 12:31:12 AMJan 13
to google-apps-sc...@googlegroups.com
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

unread,
Jan 13, 2022, 10:50:44 AMJan 13
to google-apps-sc...@googlegroups.com
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

unread,
Jan 14, 2022, 6:28:24 AMJan 14
to google-apps-sc...@googlegroups.com
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




Laurie Nason

unread,
Jan 15, 2022, 11:50:10 PMJan 15
to google-apps-sc...@googlegroups.com
Hi Jessica,
I second Martin's solution - nice job! And as he said, doesn't need custom formulas at all.
Laurie

Reply all
Reply to author
Forward
0 new messages