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

Question

我有一个存储在某个范围内的超链接列表，我希望在另一列中进行数据验证，允许您从链接“文本”中进行选择，然后粘贴完整的单元格公式。数据验证单元格当前的工作方式仅包含单元格值。

链接到示例表

一些工作表功能代码：

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());
};

我能对脚本代码做些什么来使它粘贴公式来代替值吗？或者是最好的选项，当选择一个新值时尝试并触发，然后执行以下操作：

阿特！伪代码

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())
  }
}

Answer 1

在我的例子中，由于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)
  }
}

谢谢你所有的意见和建议，让我得到这个答案。

Answer 2

这有点棘手，但我想我找到了使用DataValidationBuilder的解决方案。它与您的类似，但使用get公式（）以及要求字符串数组的验证属性要求ValueInList（）。

主要问题是当您选择公式时，“无效规则”通知会跳进来，例如，当公式将单元格值更改为“单元格C3”时。因此，为了解决这个悖论，您需要设置setAllowIn有效（布尔值）为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);


}