- May 2, 2010
- 9,955
- 16,068
If you want to track when your expiration dates come up for renewal, I'm using a good script for Google Docs that highlights days remaining in easy-to-identify colors.
You can access your Google Sheet by selecting extensions and app script and then using the following script with adjustments.
I have the expiry date in column 'D' and the days remaining is in a cell (column E), that has the formula '=D2-today()'
This gives you the days remaining till expiry.
To use the script under your account, save the script, run it, and accept the basic warnings you get.
You can access your Google Sheet by selecting extensions and app script and then using the following script with adjustments.
I have the expiry date in column 'D' and the days remaining is in a cell (column E), that has the formula '=D2-today()'
This gives you the days remaining till expiry.
To use the script under your account, save the script, run it, and accept the basic warnings you get.
JavaScript:
function colorCellsBasedOnRemainingDays() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange("E2:E" + sheet.getLastRow());
for (var i = 1; i <= range.getNumRows(); i++) {
var cell = range.getCell(i, 1);
var remainingDays = cell.getValue();
var dateCell = sheet.getRange("D" + (i + 1)).getValue();
if (!dateCell) {
cell.setBackground("white");
} else if (!isNaN(remainingDays)) {
if (remainingDays <= 10) {
cell.setBackground("red");
} else if (remainingDays <= 30) {
cell.setBackground("yellow");
} else {
cell.setBackground("lightgreen");
}
}
}
}
function onEdit(e) {
colorCellsBasedOnRemainingDays();
}