用于格式化一系列单元格并根据Google电子表格中的日期插入特定文本的脚本 [英] Script to format a range of cells and insert a specific text based on a date in a Google spreadsheet

问题描述

我有一个计划者类型的Google电子表格，其中每天有8-10个用户添加数据.当我向单元格添加日期时，我希望格式化该日期之后同一行中的所有单元格，并添加诸如"ENDED"之类的文本值.

I have a planner type Google spreadsheet where data added daily by 8-10 users. When I add a date to a cell, I want all the cells in the same row after that date to be formatted and added a text value something like "ENDED".

目前，我正在使用条件格式并使用ArrayFormula来添加文本值.问题在于，要使ArrayFormula正常工作，这些单元格必须为空，而在我的工作表中，这些单元格可能包含"ENDED"日期单元格之前的数据.

At the moment I am doing it with conditional formatting and with an ArrayFormula to add the text value. The problem is that for the ArrayFormula to work the cells must be empty and in my sheet the cells they might contain data before the "ENDED" date cell.

是否可以使用脚本来执行此操作?....并且如果脚本还可以处理单元格的格式，那将是最佳解决方案.

Is there a way to do this with a script?.... and if the script can handle also the formatting of the cells that will be the best solution.

这是我的示例文件，可以更好地了解我要做什么...

Here is my sample file to understand better what I am trying to do...

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

推荐答案

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

You can do this with a trigger and a custom function.

创建一个新的apps脚本项目并使用以下代码:

Create a new apps script project and use this code:

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函数.

Then, create a trigger to run the onEdit function on every edit.

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

In this case there are some hardcoded values:

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

• e.range.getColumn() == 2 for the row where you add the dates on
• var colOffset = 3 for the number of columns to skip before reading the dates

希望这会有所帮助！

这篇关于用于格式化一系列单元格并根据Google电子表格中的日期插入特定文本的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！