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.
The first thing to do is to minimize the number of API calls interacting with the sheet, such as getValue()
and setValue()
. Think of the script as a factory and the sheet as its warehouse across the town. If a worker has to drive to warehouse every time they need a detail, they won't get much done. Instead, they should get all the materials they need at the beginning, and turn in all finished product at the end.
So, instead of the multiple .getRange(row, column).getValue()
and .getRange(row, column).setValue()
, there should be
- one call to get values: e.g.,
sheet.getDataRange().getValues()
which gets you all the data in the sheet. (Or, if you know a specific range you'll be working in, get all values from there). Then loop over this double array, analyzing its contents
- one call to set new values with
.setValues()
, from a double array of the same size (like [['This year', 2015], ['Next year', 2016]]
- one call to set backgrounds with
.setBackgrounds()
, also from a double array.
In between 1 and 2, you work purely in JavaScript, processing one array and forming two others.
Sometimes you can't have just one array in 2 or 3, because, e.g., setValues would overwrite some formulas in the middle of your data. In this case, split the data range into groups of columns that can be handled at once. You will still have only a few setValues calls.
Best Answer
Bypass it's not possible, you could