用于格式化一系列单元格并根据 Google 电子表格中的日期插入特定文本的脚本

我有一个规划器类型的 Google 电子表格，其中每天有 8-10 个用户添加数据。当我向单元格添加日期时，我希望对该日期之后同一行中的所有单元格进行格式化并添加类似“ENDED”的文本值。

目前，我正在使用条件格式和 ArrayFormula 来添加文本值。问题是，要使 ArrayFormula 正常工作，单元格必须为空，并且在我的工作表中，单元格可能包含“ENDED”日期单元格之前的数据。

有没有办法用脚本来做到这一点？......如果脚本还可以处理单元格的格式，那将是最好的解决方案。

这是我的示例文件，可以更好地理解我正在尝试做的事情......

https://docs.google.com/spreadsheets/d/1QplyEcNu-svYwFq9wvPVEKnsEP1AnrlAkbBxNwEFPXg/edit#gid=2087617521 https://docs.google.com/spreadsheets/d/1QplyEcNu-svYwFq9wvPVEKnsEP1AnrlAkbBxNwEFPXg/edit#gid=2087617521

您可以使用触发器和自定义函数来完成此操作。

创建一个新的应用程序脚本项目并使用以下代码：

function onEdit(e) {
  if (e.range.getColumn() ==2) {
    //User edited the date column
    if (typeof e.range.getValue() === typeof new Date()) {
      //Value of edit was a date
      endColumns(e.range.getRow(), e.range.getValue());
    } else if (e.range.getValue() === ""  || e.range.getValue() === null) {
      var sheets = SpreadsheetApp.getActiveSheet();
      var resetRange = sheets.getRange(e.range.getRow(), e.range.getColumn()+1, 1, sheets.getMaxColumns()-e.range.getColumn());
      resetRange.clear(); //Will delete all text, not only the "ENDED" text.
    }
  }
}

function endColumns(rowNum, limitDate) {
  var sheets = SpreadsheetApp.getActiveSheet();

  var colOffset = 3; //Offset to account for your row Headers
  var dateHeader = sheets.getRange(1, colOffset, 1, sheets.getMaxColumns()-colOffset);

  var availableDates = dateHeader.getValues()[0];

  var foundCol = 0;
  for (var i=0; i<availableDates.length; i++) {
    if (availableDates[i]>=limitDate) {
      break;
    }
    foundCol++;
  }

  var rewriteCells = sheets.getRange(rowNum, foundCol+colOffset, 1, sheets.getMaxColumns()-(foundCol+colOffset));

  //Add your formatting and text below:
  rewriteCells.setValue("Ended");
  rewriteCells.setBackground("red");
  rewriteCells.setFontColor("yellow");

  //Clear all cells that are "white" (no header)
  for (var i=0; i<availableDates.length; i++) {
    if (availableDates[i]==="" || availableDates[i] ===null) {
      sheets.getRange(rowNum, colOffset+i).clear();
    }
  }
}

然后，创建一个触发器来运行onEdit每次编辑都会起作用。

在这种情况下，有一些硬编码值：

• e.range.getColumn() == 2对于添加日期的行
• var colOffset = 3读取日期之前要跳过的列数

希望这可以帮助！