I currently have a Google spreadsheet representing a TO-DO list with multiple columns. 2 of the columns are as follow:
- BY WHEN, which is a date with a format "dd MMMM yyyy" (e,g: 20 August 2014)
- DAYS TO ACT which is a number. This currently has the following formula e.g: (=D2 - today()) This give me the number of days left to act on something
So we end up with something like this
BY WHEN DAYS TO ACT
20 August 2014 0
20 August 2014 0
20 August 2014 0
21 August 2014 1
21 August 2014 1
21 August 2014 1
21 August 2014 1
22 August 2014 2
26 August 2014 6
26 August 2014 6
26 August 2014 6
29 August 2014 9
11 September 2014 22
18 September 2014 29
18 September 2014 29
I have an apps script attached to this spreadsheet which colorizes the rows based on the number of days left. The script can be found at the bottom of this message. Everything in the script works fine and has done so for many months.
The problem I have is that when I leave the spreadsheet open overnight, and then make a new entry in the morning, the DAYS TO ACT value does not change for old entries as the formula is in the sheet and not in code.
Is there a way to either tell the sheet to recalculate itself or failing this, is there a way to programmatically update the DAYS TO ACT values? If so, how?
//Sets the row color depending on the value in the "DAYS TO ACT" column.
function setRowColors() {
SpreadsheetApp.flush();
var sheet = SpreadsheetApp.getActiveSheet();
var range = SpreadsheetApp.getActiveSheet().getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());
range.setFontWeight("bold");
range.setVerticalAlignment("top");
var whenColumnOffset = getColumnOffset("BY WHEN");
var actColumnOffset = getColumnOffset("DAYS TO ACT");
var noteColumnOffset = getColumnOffset("NOTES");
var priorityColumnOffset = getColumnOffset("PRIORITY");
range.sort([{column: 5, ascending: true}, {column: 6, ascending: true}]);
for (var i = range.getRow(); i < range.getLastRow(); i++) {
rowRange = range.offset(i - 2, 0, 1);
days_to_act = rowRange.offset(0, actColumnOffset).getValue();
by_when = rowRange.offset(0, whenColumnOffset).getValue();
notes = rowRange.offset(0, noteColumnOffset).getValue();
if (notes == "DONE") {
rowRange.setBackground("#0000FF");
rowRange.setFontColor("#FFFFFF");
rowRange.offset(0, actColumnOffset, 1, 1).setValue(9999);
rowRange.offset(0, actColumnOffset, 1, 1).setFontColor("#0000FF");
range.sort([{column: 5, ascending: true}, {column: 6, ascending: true}]);
continue;
}
if (notes == "IN PROGRESS") {
rowRange.setBackground("#000000");
rowRange.setFontColor("#FFFFFF");
rowRange.offset(0, actColumnOffset, 1, 1).setValue(9998);
rowRange.offset(0, actColumnOffset, 1, 1).setFontColor("#000000");
range.sort([{column: 5, ascending: true}, {column: 6, ascending: true}]);
continue;
}
if (days_to_act > 14) {
rowRange.setBackground("#00FF00");
} else if (days_to_act <= 14 && days_to_act > 7) {
rowRange.setBackground("#FFD700");
} else if (days_to_act <= 7 && days_to_act > 3) {
rowRange.setBackground("#FF8C00");
} else if (days_to_act <= 3 && days_to_act > 1) {
rowRange.setBackground("#FF4500");
} else if (days_to_act <= 1 && days_to_act >= -1 && by_when != "") {
rowRange.setBackground("#FF0000");
} else {
rowRange.setBackground("#808080");
}
}
range.sort([{column: 5, ascending: true}, {column: 6, ascending: true}]);
}
//Returns the offset value of a specific column
//(eg, if the 7th column is labeled "Status", this function returns 6)
function getColumnOffset(colName) {
lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);
for (var i = 0; i < range.getLastColumn(); i++) {
if (range.offset(0, i, 1, 1).getValue() == colName) {
return i;
}
}
}
function onOpen() {
setRowColors();
}
function onEdit() {
setRowColors();
}
I am not looking at the sheet automatically updating itself when the date changes, only when a new entry is made or failing this by calling a new function from a menu item I would create.
Regards Crouzilles
