You can set the interval for recalculation of the spreadsheet in the Spreadsheet settings which you can find in the File menu.
There you can set it to recalculate on change and/or every minute or hour.
Through this, functions like Now()
should be updated automatically in the interval you set.
Cells containing the empty string ""
appear empty but are not (isblank
returns FALSE for them). They get sorted ahead of nonempty strings, which is not what you want. To avoid this, replace
if(isblank(B1:B), "", datevalue(B1:B))
by the simpler
iferror(datevalue(B1:B))
The command iferror
returns its (optional) second argument if there is an error evaluating the first argument; otherwise it leaves the cell blank. Blank cells are sorted to the bottom.
A potential drawback of the above is that misformatted dates are simply ignored, while you may want to see and fix them. This is something you can address separately; e.g., have a column with
if(isblank(B1:B), 0, if(iserror(datevalue(B1:B), 1, 0))
and sum over it to get the total number of nonempty but invalid date cells.
Another potential issue is that you are sorting a range containing arrayformula
. The sort may move the formula to another row, creating a mess. It's safer to exclude the header row from sorting. Replacing
var data = JobList.getDataRange();
by
var data = JobList.getDataRange().offset(1,0);
would do it.
Best Answer
Here's how to implement it:
Select your entire range that contains checkboxes.
Choose Format > Conditional formatting from the menu.
In the Conditional Formatting dialog box, under "Format cells if...", use the drop-down menu to select "Custom formula is" (bottom of the drop-down list).
In the text box below that, enter this formula:
=COUNTIF(INDIRECT(ROW()&":"&ROW()),"="&TRUE)>=3
In the "Formatting style" section, click the paint can icon and choose red for the background (and perhaps the "B" for bold type as well).
Click the blue "Done" button and close the dialog box.