如何根据 Google Docs 电子表格中的当前日期设置行的背景颜色? [英] How to set background color for a row based on current date in Google Docs Spreadsheet?

问题描述

我有一个 Google Docs 电子表格，其中 A 列是日期(A1:2013-11-22、A2:2013-11-23、A3:2013-11-24 等).我想自动突出显示 - 为一行设置背景颜色，其中 A 列中是今天的日期.每天突出显示不同的行.

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.

我希望我需要一个脚本，恕我直言，这是不可能通过 Google Docs SpreadSheet 中的条件格式来完成的.

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!

推荐答案

我已经修改了 Serge 的一个示例(感谢 Serge！)，日期在 A 列中.带有日期的行清除了背景颜色，其他行保持不变.奖励:在活动工作表上运行脚本的自定义菜单.

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'));  
};

这篇关于如何根据 Google Docs 电子表格中的当前日期设置行的背景颜色?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！