根据日期条件格式化单元格 [英] Conditionally formatting cells based on date

问题描述

我想使用基于脚本的格式来对日期列进行颜色编码.

I want to have script based formatting to color code date columns.

第1行是标签. C栏C-E包含日期；这些是我要为每个日期设置颜色格式的单元格.我从有条件的黄色开始，一旦粘贴了此障碍，就会添加更多颜色.

Row 1 is labels. Cols C-E contain dates;these are the cells I want to color format per date. I'm starting with Yellow in the conditional and will add more colors once I'm paste this hurdle.

希望是检查每个单元格并对其进行适当着色.当前版本正在毫无理由地为单元格着色，显然是逐行着色. (C2:E9)和(C13:E13)范围的所有三列都变为黄色，而C10:E12和C14:E14保持正常.所有这些范围中的许多日期都是相同的.

The hope is to check each cell and color it appropriately. This current version is coloring blocks of cells without reason, apparently by row. All three columns for the range (C2:E9) and (C13:E13) are turning yellow, while C10:E12 and C14:E14 remain normal. Many of the dates across all these ranges are the same.

我的代码:

function formatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');
  var columnO = sheet.getRange(2, 3, sheet.getLastRow() - 1, 3);//the range starts at column 3.  
  var oValues = columnO.getValues();
  
  for (var i = 0; i < oValues.length; i++) {
     for(var j = 2; j <= 4; j++) {//j=2 is actually column 5
       //convert dates from GMT to local
       var thisDay = new Date();
       var todayLocal = thisDay.toLocaleString();
       var bullDate = new Date(oValues[i][0]);
       var bullLocal = bullDate.toLocaleString();
  
       if (bullLocal < todayLocal) {
         sheet.getRange(i + 1, j + 1, 1, 1).setBackgroundColor('yellow');
      }
    }
  }
}

我认为，还有其他方法可以做到这一点，但是要设法提高我的代码的效率.想法?

There are other ways to do this, I think, but trying to be efficient in my code. Ideas?

推荐答案

在您的函数中，范围从第3列开始，因此实际上在第1列column3中找到了oValues [0] [0].

In your function the range started in column 3 and so oValues[0][0] was actually found at row 1 column3.

尝试一下:

function formatting() {
  var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');
  var rg=sheet.getDataRange();//this range starts at column 1
  var vA=rg.getValues();
  var today = new Date();
  for(var i=0;i<vA.length; i++){
    for(var j=2;j<=4;j++){//j=2 is the third column
      var bullDate = new Date(vA[i][j]);  
      if (bullDate < today){
        sheet.getRange(i+1,j+1).setBackgroundColor('yellow');
      }
    }
  }
}

这篇关于根据日期条件格式化单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！