How to change row background color based on date of cell, google spreadsheets

Question

I have a form that people fill out, that goes to a google spreadsheet. When this happens, I am trying to automatically change the color of the new row from the form based upon the date in one of the columns. I have found questions similar to this, and I was able to generate this code from those questions, but when the code runs, it does not change the background color. What am I doing wrong?

function onEdit(e) {
if (e) { 
    var ss = e.source.getActiveSheet();
    var r = e.source.getActiveRange(); 

    if (ss.getName() == "Form Responses Sorted") {

        // E.g. date column is 5th (E)
        date = ss.getRange(r.getRow(),5).getValue();


        rowRange = ss.getRange(r.getRow(),1,1,20);

        // This changes background color
        if (date == '8/5/2013') {
            rowRange.setBackgroundColor("#D00000");
        } else if (date == '8/12/2013') {
            rowRange.setBackgroundColor("#A0A0A0");
        } else if (date == '9/9/2013') { 
            rowRange.setBackgroundColor("#00FF00");
        } else if (date == '9/23/2013') {
            rowRange.setFontColor("#33FFCC");
        } else if (date == '9/30/2013') { 
            rowRange.setFontColor("#CCFF33");
        } else if (date == '10/7/2013') {
            rowRange.setFontColor("#FF6699");
        } else if (date == '10/21/2013') { 
            rowRange.setFontColor("#CC66CC");
        } else if (date == '11/4/2013') {
            rowRange.setFontColor("#6666FF");
        } else if (date == '11/25/2013') { 
            rowRange.setFontColor("#9933FF");
        } else if (date == '12/9/2013') {
            rowRange.setFontColor("#FF9933");
        } else if (date == '12/16/2013') { 
            rowRange.setFontColor("#FFFFFF");
        } else if (date == '1/13/2014') {
            rowRange.setFontColor("#CC9933");
        }
    }
}
}

To clarify some points: Are you using Google Forms, right?, in that case, you want to change the background color of the row you just sent from the Google Form? Is the date a form field, right (column 5)? If all this is true, check the event **Spreadsheet Form Submit** in [Understanding Events](https://developers.google.com/apps-script/understanding_events) — wchiquito, Aug 30 '13 at 07:00
If any of the wchiquito points are no, can you add Logger.log(date) below the line date = ss.getRange(r.getRow(),5).getValue(); run the code then goto View - Logs and let me know the value you see there... — Vasim, Aug 30 '13 at 07:21

score 1 · Answer 1 · edited May 23 '17 at 12:33

You are most likely dealing with inconsistent data types in your comparisons. Let's just look at:

 if (date == '8/5/2013')

The default behavior in Google Sheets would be to convert a cell value of '8/5/2013' to a date, in the spreadsheet. Therefore, when you getValue(), you will have a javascript date object instance for the left side of this comparison.

What about the right side? It's a String, a primitive type in javascript. To compare these two types, one of them will be coerced so that we can compare them. Since the right is a primitive type, the left will be converted to match; a string. Let's see what that would be.

var A = String(date);   // Mon Aug 05 2013 00:00:00 GMT-0400 (EDT)

So, now you have a string comparison, which returns false because:

'Mon Aug 05 2013 00:00:00 GMT-0400 (EDT)' !== '8/5/2013'

The solution will be to ensure we're comparing Date objects. Even that's tricky - if you're interested, there's lots of background discussion to read, starting with Compare two dates with JavaScript (including all the notes!). This Blog covers the coercion topic clearly, but without details around dates.

That line of code above can be replaced with this, which will properly evaluate true. You could adopt one of the functions from the answer linked earlier, which would be more readable.

if (date - new Date('8/5/2013') == 0) {

Lather, rinse, repeat...

How to change row background color based on date of cell, google spreadsheets

1 Answers1