I have a Google Docs SpreadSheet, where in the column A are dates (A1: 2013-11-22, A2: 2013-11-23, A3: 2013-11-24 etc). I would like to automatically highlight - set a background color for a row, where in the column A is today's date. To have every day a different row highlighted.
I expect that I will need a script, IMHO this is not possible to be done with conditional formating in Google Docs SpreadSheet.
Any idea how to do it? Thanks a lot!
This is how I did it. I did conditional formatting and picked my range, then in "format cells if..." I chose custom and used this formula:
=$B$2:$B$92 = today()
I have my dates in the B column and this highlights the entire row, within my range, for today's date.
I have modified an example from Serge (thanks, Serge!), dates are in the A column. Rows with a date have background color cleared, other rows are intact. Bonus: a custom menu to run a script on active sheet.
/* check for a cell format */
function isValidDate(d) {
if ( Object.prototype.toString.call(d) !== "[object Date]" )
return false;
return !isNaN(d.getTime());
}
/* check for a cell format */
function colorRow()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
customOnOpen(sh);
}
/* set the background - main function, sh is a sheet */
function customOnOpen(sh) {
var headers = sh.getRange(1,1,sh.getLastRow()).getValues();
var today = new Date().setHours(0,0,0,0);
for(var n=0;n<headers.length;++n){
var date = new Date(headers[n][0]).setHours(0,0,0,0);
Logger.log('Test row '+n);
if(date==today){
Logger.log('Set bg at '+n);
sh.getRange(n+1,1,1,sh.getMaxColumns()).setBackground('yellow');
}
else
{
if (isValidDate(headers[n][0])){
Logger.log('Clear bg at'+n);
sh.getRange(n+1,1,1,sh.getMaxColumns()).setBackground(null);
}
else{
Logger.log('Not a date at'+n);
}
}
}
}
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
/* prepare the custom menu */
var entries = [{
name : "Set background",
functionName : "colorRow"
}];
sheet.addMenu("My menu", entries);
/* run the function for two specific sheets */
customOnOpen(sheet.getSheetByName('Sheet1'));
customOnOpen(sheet.getSheetByName('Sheet2'));
};
If you want it to be automatic on spreadsheet open you will have to install an installable onOpen that will call the below function (from script editor goto ressources > this script triggers > add a new trigger > sreadsheet / on Open)
And here is the code for columns: (see below for rows)
function customOnOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
var headers = sh.getRange(1,1,1,sh.getLastColumn()).getValues();
var today = new Date().setHours(0,0,0,0);
for(var n=0;n<headers[0].length;++n){
var date = new Date(headers[0][n]).setHours(0,0,0,0);
Logger.log(today+' =? '+date)
if(date==today){
n++
Logger.log('match on column '+n)
if(n>=2){sh.getRange(1,n-1,sh.getMaxRows(),1).setBackground(null);};// resets the backGround for yesterday if not the first column
sh.getRange(1,n,sh.getMaxRows(),1).setBackground('yellow');
break;
}
}
}
this version to colorize rows
function customOnOpen2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
var headers = sh.getRange(1,1,sh.getLastRow()).getValues();
var today = new Date().setHours(0,0,0,0);
for(var n=0;n<headers.length;++n){
var date = new Date(headers[n][0]).setHours(0,0,0,0);
Logger.log(today+' =? '+date)
if(date==today){
n++
Logger.log('match on column '+n)
if(n>=2){sh.getRange(n-1,1,1,sh.getMaxColumns()).setBackground(null);}
sh.getRange(n,1,1,sh.getMaxColumns()).setBackground('yellow');
break;
}
}
}
Note : if you want it to run fully automatically based on a timer it's perfectly doable, just change the ss and sh variable using openById
and getSheetByName
(see doc here)and set up a timer to make it run every day around 1 AM.
Where A houses dates, try this.
Enter this custom formula:
=(A=TODAY())
Choose your background and text color
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With