Google表格数据验证下拉菜单显示值但粘贴公式吗？

2024-04-29 • 问答

我有一个存储在一定范围内的超链接列表，我想在不同的列中进行数据验证，以便您从链接“文本”中进行选择，然后粘贴完整的单元格公式。数据验证单元格仅包含单元格值时的工作方式。

某些工作表功能代码：

C2: =HYPERLINK("#rangeid=901904080","Cell E2")
C3: =HYPERLINK("#rangeid=951806747","Cell E3")
C4: =HYPERLINK("#rangeid=743771733","Cell E4")

自动录制的脚本代码：

function DV() {
  var spreadsheet = Spreadsheetapp.getactive();
  spreadsheet.getRange('C2').activate();
  spreadsheet.getRange('A2:A').setDataValidation(Spreadsheetapp.newDataValidation()
  .setallowInvalid(false)
  .requireValueInRange(spreadsheet.getRange('Sheet2!$C$2:$C'),true)
  .build());
};

我可以对脚本代码执行一些操作，以使其粘贴公式代替值吗？或者是尝试选择新值时触发并执行以下操作的最佳选择：

Att！伪代码

onEdit(e){
  //pseudo code
  if e.range in column A{
    var sourceRange = sheet.getRange("C2:C")
    var index = sourceRange.indexOf(e.range.getvalue())
    e.range.setformula(sourceRange[index].getFormula())
  }
}

这有点棘手，但是我想我使用DataValidationBuilder找到了解决方案。它与您的相似，但是使用getFormulas()和验证属性requireValueInList()来请求字符串数组。

主要问题是，当您选择公式时，“无效规则”通知会跳入，例如，公式将单元格值更改为“ Cell C3”。因此，要解决此矛盾，您需要将setAllowInvalid(boolean)设置为false：

function DV(){

  var spreadsheet = SpreadsheetApp.getActive();
  var cells = spreadsheet.getRange('A2:A');
  var rule = SpreadsheetApp.newDataValidation()
             .requireValueInList(spreadsheet.getRange('C2:C').getFormulas())
             .setAllowInvalid(false)
             .build();
  cells.setDataValidation(rule);


}

由于我不能接受500个项目的限制，因此我通过检查已编辑单元格上的数据验证规则解决了该问题，如果存在，请交换数据值。

MWE：

function onEdit(e){
  if (e.range.getDataValidation() != null){
    swapValueForFormula(e.range);
  }

function swapValueForFormula(cell){
  var val = cell.getValue();
  var rule = cell.getDataValidation();
  var source = rule.getCriteriaValues()[0];
  var vals = source.getValues();
  var index = vals.join().split(",").indexOf(val);
  if (index != -1){
    var formula = source.getCell(index+1,1).getFormula()
    cell.setFormula(formula)
  }
}

感谢您提出的所有意见和建议。

Google表格数据验证下拉菜单显示值但粘贴公式吗？

xiaojiankunzhuxi 回答：Google表格数据验证下拉菜单显示值但粘贴公式吗？

大家都在问