google-apps-script - 当输入的数据被复制并粘贴到电子表格中时，使用什么触发器来更新单元格？（谷歌表格/应用程序脚本）

Question

所以我有一个 OnEdit 函数，当用户从列表中选择一种颜色时，单元格背景颜色将变为该选定值。

这工作正常，但我试图让功能在数据被复制并粘贴到电子表格以及用户编辑单元格时立即更新单元格背景颜色。目前，当您将数据复制并粘贴到电子表格中时，我编写的更改单元格背景颜色的代码不会触发，因为没有任何内容被“编辑”。我试图让代码适用于 OnEdit 以及何时将数据复制并粘贴到工作表中。任何想法将不胜感激！谢谢！

这是我到目前为止所拥有的..

// Validates the colours using the above map and changes the cell background to the input colour..
function onEdit(e) {
  try {
    var range = e.range;
    var sheet = range.getSheet();
    var tabColumnData = getColumnData(sheet);
    if(!tabColumnData.hasOwnProperty('Color')) return;
    var col = range.getColumn();
    
    if (col == tabColumnData['Color'] + 1) {
      var color = e.value;
      if(validateCellValue(color) || COLOR_TO_HEX_MAP.hasOwnProperty(color)) {
        range.setBackground(color);
      } else {
        range.clear();
        SpreadsheetApp.getUi().alert('Please select a color from the list or a hex color code.');
      }
    }
    
    if (col == tabColumnData['Name'] + 1) {
      setDataValidation(createColorList());
    }
  } catch(e) {
    Logger.log(e);
  }
}


// Sets the data validation when the spreadsheet is opened.
function onOpen(e){
   setDataValidation(createColorList());
}

标签： google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-macros

Answer 1

阅读您的问题后，我假设以下内容：

• 您想检测何时编辑了一组单元格。
• 当检测到修改时，您希望更改每个单元格的颜色以匹配其内容。

如果我的假设是正确的，您可以使用以下示例来满足您的要求：

代码

function onEdit(e) {
  var firstRow = e.range.getLastRow() - e.range.getHeight() + 1;
  var firstColumn = e.range.getLastColumn() - e.range.getWidth() + 1;
  var values = e.range.getValues();
  for (var i = 0; i < e.range.getHeight(); i++) {
    for (var j = 0; j < e.range.getWidth(); j++) {
      try {
        e.source.getActiveSheet().getRange(firstRow + i, firstColumn + j)
          .setBackground(values[i][j]);
      } catch (e) {
        // You might want some error handling or logging here
      }
    }
  }
}

行为

每次编辑一个单元格或一组单元格时，该代码都会运行。当检测到版本时，代码将迭代每个单元格并尝试将其背景颜色更改为单元格的内容。

观察结果

• 此代码适用于电子表格的每张纸。要使其专属于工作表，您应该使用getSheet 方法[as getSheetByName() ]。
• 由于onEdit触发限制，如果修改是由脚本进行的（与用户编辑相反），则此代码将不会运行。
• 背景颜色是有限的，直到CSS Color 3上定义的颜色。您可以在此处查看完整列表。

典故

• onEdit(event object)简单的触发器
• 事件对象
• getLastColumn()/getLastRow()
• getHeight()/getWidth()
• setBackground(color)

请不要犹豫，给我写回任何其他疑问或要求我进一步澄清。

更新

前面的代码将仅遍历已编辑的单元格，而不是整个工作表。此行为将发生在所有电子表格上。如果您想减少特定范围内的影响（例如categorycolor 列），您需要一个条件语句来检查修改后的单元格是否在所需范围内。

解决这种情况的第一步是创建一个命名范围，如此处所述。在此示例中，我使用了 name ColourRange，但您可以更改代码以设置您自己的命名范围。将此作为可能的解决方案之一：

function onEdit(e) {
  // Colour range
  var colourRange = SpreadsheetApp.getActiveSheet().getRange('ColourRange');
  var firstColourRow = colourRange.getRow();
  var lastColourRow = colourRange.getLastRow();
  var firstColourColumn = colourRange.getColumn();
  var lastColourColumn = colourRange.getLastColumn();

  // Modified range
  var firstRow = e.range.getLastRow() - e.range.getHeight() + 1;
  var firstColumn = e.range.getLastColumn() - e.range.getWidth() + 1;
  var values = e.range.getValues();

  for (var i = 0; i < e.range.getHeight(); i++) {
    for (var j = 0; j < e.range.getWidth(); j++) {
      var modifiedCell = e.source.getActiveSheet().getRange(firstRow + i,
        firstColumn + j);
      if (modifiedCell.getRow() >= firstColourRow && modifiedCell
      .getLastRow() <= lastColourRow && modifiedCell.getColumn() >=
        firstColourColumn && modifiedCell.getLastColumn() <= lastColourColumn) {
        try {
          e.source.getActiveSheet().getRange(firstRow + i, firstColumn + j)
            .setBackground(values[i][j]);
        } catch (e) {
          // You might want some error handling or logging here
        }
      }
    }
  }
}