Colour entire row when date in shell matches today's date

Question

function colorEntireRowGreenWhenDateOfEventMatchesCurrentDate() {

  var data = SpreadsheetApp.getActiveSheet();
  var dateColumn = data.getRange("C2:C15").getValues();
  var todaysDate = new Date();
  Logger.log(data)

  for (i=0; i=dateColumn.length; i++) {
    if (dateColumn[i] = todaysDate) {
      data.setTabColor("#3c643c");
    }
  }
}

Simply easy request. I want to colour an entire row green when the date entry in column C matches the today's date.

What is the problem with your piece of code ? – Pierre-Marie Richard Sep 14 '17 at 13:24 — Pierre-Marie Richard, Sep 14 '17 at 13:24

score 1 · Accepted Answer · answered Sep 14 '17 at 14:01

There is multiple problem on your code.

First, you made a mistake on your date comparaison

 if (dateColumn[i] = todaysDate) {

instead of

 if (dateColumn[i] == todaysDate) {

Also, the best way to compare date is to compare the time by using getTime() method. See also this SO question : Compare two dates with JavaScript

You try to use the Sheet.setTabColor(String) method which modify the tab color of the sheet, when you try to get the Range.setBackground(String) method or the Range.setBackgroundRGB(red, green, blue) method.

Here is a solution:

function colorEntireRowGreenWhenDateOfEventMatchesCurrentDate() {

  var data = SpreadsheetApp.getActiveSheet();
  var range = data.getRange("C2:C15");
  var values = range.getValues();
  var today = new Date();
  var todaysDate = new Date(today.getFullYear(), today.getMonth(), today.getDate());
  Logger.log(data);
  range.setBackground(null);

  for (var i in values) {
    var d = values[i][0];
    if (d.getTime() === todaysDate.getTime()) {
      var row = parseInt(i) + 1;
      data.getRange(range.getCell(row, 1).getRow(),1,1,data.getMaxColumns()).setBackground('#3c643c');
    }
  }
}

I am getting an 'TypeError: Cannot find function getTime in object . (line 14, file "Code")' error when debugging the code, I think is because 'd' in not a date object. — Greconomist, Sep 15 '17 at 10:21
You need to use date format on your spreadsheet, otherwise, it can't do the comparaison between the day date and the spreadsheet date. — Pierre-Marie Richard, Sep 15 '17 at 11:39

Brr Switch · Answer 2 · 2017-09-14T13:54:27.437

0

According to the documentation, this explains how to set background color for cells. You can either use setBackground or setBackgroundRGB

edited Sep 14 '17 at 13:54

answered Sep 14 '17 at 13:24

Brr Switch

974
1
9
21

1

Answers work better with an explanation on why they should work. – apokryfos Sep 14 '17 at 13:48
@apokryfos Sure, I'm adding a reference – Brr Switch Sep 14 '17 at 13:50

score 0 · Answer 3 · answered Sep 14 '17 at 14:44

0

This can be done much more simply with conditional formatting. Use custom formula in A2.

=$C2:$C=today()

Set range A2:F (change F to your last data column.

answered Sep 14 '17 at 14:44

Ed Nelson

10,015
2
27
29

Colour entire row when date in shell matches today's date

3 Answers3