在Google表格中使用Google脚本基于图表“开始日期”和“结束日期”的颜色单元

维克多--------

目前,我通过在Google表格中使用条件格式来获得此版本的工作版本。不幸的是,性能降低了我要添加的更多条件。我想将条件格式转换为每小时运行一次的Google脚本。这基本上是一个甘特图,非常适合我的需求。

条件格式的公式是

=and(AF$2>=$L3,AF$2<=$M3)

其中L列是开始日期,M列是结束日期

在此处输入图片说明

AF $ 2,AG $ 2,AH2单元格是日期,从今天,明天,后天等开始。 在此处输入图片说明

使用Google脚本的替代方法是什么。这是我到目前为止所拥有的:

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}


function setCellBackgrounds() {
  // The name of the sheet to process.
  var sheetName = "MySheet";

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getRange("AF3:BJ100");
  var values = range.getValues();
  var colors = [];
  for (var x = 0; x < values.length; x++) {
    colors[x] = [];
    for (var y = 0; y < values[x].length; y++) {

 //trying to apply the formula "=and(AF$2>=$L3,AF$2<=$M3)" here but I'm getting an error
       if (columnToLetter(32+y)+2 >= columnToLetter(12)+x && columnToLetter(32+y)+2 <= columnToLetter(13)+x ) {
        colors[x][y] = '#999999';
       } else {
         //colors[x][y] = '#ffffff';
       }
    }
  }
  range.setBackgrounds(colors);
}
来吧

您可以通过比较两个范围L3:M来替换条件格式公式AF2:BJ2,然后将颜色应用于输出范围AF3:BJ

const cols = sheet.getRange("AF2:BJ2").getValues()[0], // Extract the first (& only) row.
      rows = sheet.getRange("L3:M" + sheet.getLastRow()).getValues();

const inWindowColor = "#999999",
      otherColor = null; // null values -> reset color to default.
// Create a rectangular 2D array of color strings. Each row needs an array of colors
// with each inner element corresponding to the given column.
const output = rows.map(function (datePair) {
  var start = datePair[0],
      end = datePair[1];
  return cols.map(function (day) {
    var inWindow = day && start && end // guard against "" values
        && day.getTime() >= start.getTime() && day.getTime() <= end.getTime();
    return (inWindow ? inWindowColor : otherColor);
  });
});

sheet.getRange("AF3").offset(0, 0, output.length, output[0].length)
    .setBackgrounds(output);

上面的Array#map方法使用class方法,并以数字方式执行日期比较(使用相等性检查时需要)。null根据方法说明,为窗外单元格颜色提供一个值,以将背景重置为其默认颜色可以重写最后一行以消除offset呼叫,但我认为"AF3"维护起来比容易(3, 32, output.length, output[0].length)

其他阅读


如果要使用的颜色在同一行和已知的列中,则无需显着更改即可读取它。显然,您需要颜色范围与该rows范围相同(因为每一行都有相应的颜色)。然后,您只需要使用给定的第二个自动参数Array#map-当前元素的索引。在这里,我显示了2列的颜色定义范围(“在窗口中”(V)和“结束”(W))

const lastRow = sheet.getLastRow(),
      cols = ...,
      rows = sheet.getRange("L3:M" + lastRow).getValues(),
      colorDefs = sheet.getRange("V3:W" + lastRow).getValues();

const output = rows.map(function (datePair, row) {
  ...
    var color = null;
    if (day && start && end) {
      if (day > end) { // no equality, no `.getTime()` needed
        color = colorDefs[row][1]; // "ended" color is in 2nd index.
      } else if (day.getTime() >= start.getTime()) {
        color = colorDefs[row][0]; // "in window" color is in 1st index.
      } else { /* not started yet */ }
    } else { /* `day`, `start`, and/or `end` were "falsy" */ }
    return color;
 ... 

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

使用开始和结束日期按日期范围扩展行

Google表格:CountIf和日期

如何使用熊猫的开始日期和结束日期提取DataFrame

如何从Google脚本中的星期数获取开始日期和结束日期

使用时刻js显示基于开始日期和结束日期的时间间隔

基于行中值的“开始”和“结束”日期

Google表格公式可计算具有不同开始/结束日期,重叠和间隔的任务的实际总工时

在日期和Google表格中使用IndexOf

通过使用当前日期时间的开始和结束日期时间更改全日历事件颜色

我无法通过CLI在清晰的气流中使用开始日期和结束日期

如何使用SQL在Teradata中的表中使用开始日期和结束日期创建日期列?

Google表格-在脚本的OFFSET中使用单元格的值

使用查询从单元格Google表格中获取日期

Google Analytics(分析)API的开始和结束日期

使用脚本在Google表格中根据日期设置行格式(颜色,直行等)

在Google表格中使用日期

Google Apps脚本基于时间的触发日期和时间

使用开始日期和结束日期列进行重新采样

在Google时区(而不是电子表格时区)中使用Google Apps电子表格脚本的日期

基于 GETDATE() 的财政季度开始和结束日期

基于日期开始和结束在 FullCalendar 上重复事件

基于开始日期和结束日期的 Tableau 体积计算

如何使用每周节目状态表创建显示节目开始和结束日期的表格

基于开始和结束日期的年/月记录返回金额

使用开始和结束日期绘制线条

Google 表格:基于今天()和日期单元格的行的条件格式

从开始和结束日期填写单元格

Google 表格 - 如何从开始日期和结束日期提取新列中的日期列表

如何在 Google 表格中的日期和使用 Google Apps 脚本的给定日期之间进行选择