google-apps-script - Google Sheets - 防止用户通过数据验证粘贴单元格

Question

是否可以防止用户根据数据验证粘贴具有下拉选项的单元格？

虽然这可能很方便，但当一个单元格(带有数据验证)被复制并粘贴到另一个单元格(带有它自己的验证)时，它会重写数据验证以匹配正在粘贴的范围，如果这有意义的话。

或者，也许有一个脚本可以接受正在粘贴的信息，但将该数据验证重写为其原始范围？

Answer 1

根据您的工作簿使用情况，这可能非常棘手，而且比听起来更复杂。在过去，GAS 有 ScriptDB，因此开发人员可以通过重新创建“UNDO”功能来恢复这种事情。日落时分，一个建议是有一个重复的工作表，并确保它始终与用户的事件工作表保持对齐 - 然后，当用户弄乱您的数据验证时，您只需扫描所有数据验证单元格并从重复的工作表( https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder )中恢复那些单元格。无论如何，我得出的结论是，Google Sheets 不是为这种特定类型的面向客户的解决方案而设计的(这是使用 Google Forms 的地方)，所以如果你不能使用“Protected Sheets and Ranges”让它工作，你可能最终会实现一个黑客，如下。

这是我的黑客方法；我无法阻止这种情况发生，但是，我能够通过使用 onEdit() 将单元格自动恢复为正确的格式，并在每次用户编辑后恢复所有损坏的命名单元格。这个想法是在命名范围的名称中定义规则。

(1) 您可以创建一个命名范围 对于您想要保护的每个细胞。命名范围的名称必须封装您的数据验证规则。例如，如果您在 ID 为“1380712296”的工作表上的 [B28] 处有一个下拉单元格，它从 ID 为“936278947”的工作表上的 [范围 A11-A14(下拉列表中的 4 个项目)] 提供，您可以命名下拉单元格如下:

z_1380712296_r?936278947?A11.A14_B28

或者，以通用形式:

z_ 下拉表 ID _ 数据验证类型 ? 数据验证源表 ID ? 数据验证启动单元 . 数据验证终端 _ A1 格式下拉单元格范围

(2) 编写一个触发器，在每次用户编辑时执行，如下:

function onEdit(e) {
  try {
    autocorrectFormat(e.range);
  }
  catch (e) {}
}

function autocorrectFormat(modifiedRange) {
  // Get named ranges on active sheet
  var sheetNamedRanges = SpreadsheetApp.getActiveSheet().getNamedRanges();

  // Fix active cells that intersect
  for (var i in sheetNamedRanges) {
    // we only go through the named ranges that begin with z_ since others may be declared by users
    if (sheetNamedRanges[i].getName().substring(0,2) == "z_") {
      // This rangesIntersect function below is necessary because onEdit's e.range is often wrong when pasting groups of cells
      // so we want to check if the edited range intersects with a named range's range rather than equals exactly
      if (rangesIntersect(sheetNamedRanges[i].getRange(), modifiedRange)) {
        // Here parse the information on the named range's name and fix the potentially damaged cell using the data 
        // validation rules pulled from the named range's name
        var currentCellInformation = [sheetNamedRanges[i].getRange(),sheetNamedRanges[i].getName().split("_")];
        // Parsing
        var part_1 = currentCellInformation[1][2].charAt(0);
        var part_2 = currentCellInformation[1][2].split("?");  
        // Function to rebuild the dropdown cell
        fixRange(...);
      }
    }
  }
  return;
}

// https://stackoverflow.com/a/36365775/7053599
function rangesIntersect(R1, R2) {
  return (R1.getLastRow() >= R2.getRow()) && (R2.getLastRow() >= R1.getRow()) && (R1.getLastColumn() >= R2.getColumn()) && (R2.getLastColumn() >= R1.getColumn());
}

正如您所注意到的，粘贴会覆盖以前的数据验证规则。您可能还想评估 Ctrl+X、拖放和 Ctrl+\的效果及其对命名范围的影响，因为例如，Ctrl+X 也会移动命名范围，而 Ctrl+\从不执行 onEdit() .

我包含了代码让你熟悉你将使用的各种函数 - 你也可能正在使用 DataValidationBuilder在您重建下拉单元格的 fixRange() 函数中。

回应“或者，也许有一个脚本可以接受正在粘贴的信息，但将该数据验证重写为其原始范围？？”:

这是个好主意，我也想到过，但无法拦截用 GAS 粘贴的信息。我们最接近的是 onEdit()。这就是为什么我在上面提到我无法阻止它，而是尝试自动恢复损坏的单元格。