如何在自定义函数中评估电子表格公式？

Question

在电子表格中，我可以在单元格中输入=SIN(45)+123，然后它会被计算。如何在自定义函数内部评估电子表格函数？比如说一个“eval”函数，它的工作方式如下：

function myFunc() {
  return Sheet.eval("=SIN(45)+123")
}

有可能吗？

请注意，我并不特别关心SIN函数，我想要的是能够访问完整的电子表格函数库（例如PMT，QUERY，NPER等）。

- Max L.

---

可能是如何评估存储在单元格中的公式？的重复问题。 - Rubén

Answer 1

来自应用脚本的电子表格函数

不可能-这个问题已经被问了很多次。建议您检查google-apps-script问题列表，看是否有什么改变。但据我所知，目前还没有办法做到这一点，而且他们也没有添加它的计划。 https://code.google.com/p/google-apps-script-issues/issues/list

Ethercalc-JavaScript电子表格公式

如果需要，您可以随时从"ethercalc"复制代码，因为它具有电子表格公式的JavaScript版本。 https://github.com/audreyt/ethercalc

- eddyparkinson

Answer 2

我知道这是一个老问题，但它可能会有所帮助。 只需将公式分配给一个范围，然后获取该值。

//asign a formula to the range
var range = sheet.getRange("A1").setFormula("=SUM(D1:D100)");
//get the result
var result  = range.getValue();
//clean up
range.setFormula("");     

- David

---

除非从自定义菜单项运行，否则自定义函数无法设置任意范围的公式。 - T Nguyen

Answer 3

我已经解决了这个问题。使用“set value”将会解决问题。因此，可以像这样做：

使用“set value”：

function MyFun1(){
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('A1').setValue(Myfun2())
}

function MyFun2(){
   return "=SIN(45)+123"
}

希望这能帮到你！

- user2988332

---

实际上，我下结论太快了...但是上面的代码并没有评估函数并设置其值，而是设置了公式本身...这不是我要找的 :( - haridsv

这很奇怪，因为我目前使用它没有任何问题。对于你来说，这个单元格显示什么？ - user2988332

该单元格显示数值，但如果您编辑它，则会看到公式，因此它的行为与常规公式单元格完全相同。我通过使用Range.copyTo和{contentsOnly:true}选项将单元格复制到自身（源和目标范围相同）来解决了这个问题。我在其他SO线程中找到了解决方案，现在不确定具体链接。 - haridsv

Answer 4

我在寻找一种类似于Excel中可以评估部分函数的方法时遇到了这个问题。

以下是我的临时解决方案-不要将结果输出到msgbox，而是将当前单元格的值或显示值存储在变量中并根据需要使用它。

但请注意，该函数将暂时覆盖您当前所选单元格中的任何内容，并且需要重新计算工作表才能得到结果。因此，如果您需要评估多个单元格的值，则这不是可行的解决方案。

function evalPart() {
  var ui = SpreadsheetApp.getUi();
  myPart = Browser.inputBox("Enter formula part:", ui.ButtonSet.OK_CANCEL);
  if (myPart != "cancel") {
    myActiveCell = SpreadsheetApp.getActiveSpreadsheet().getActiveCell();
    myBackup = myActiveCell.getFormula();
    myActiveCell.setFormula(myPart);
    Browser.msgBox("Result of \\n \\n" + myPart + " \\n \\n " + myActiveCell.getDisplayValue());
    myActiveCell.setFormula(myBackup);
  }
}

- Fabian K

Answer 5

我认为你需要将这个问题分成两个不同的关注点。

你是想获取已经在电子表格中的数据，执行计算，然后打印结果，还是想在与电子表格中的数据无关的代码中使用sin()函数？

如果你想做后者，你应该能够通过在Google Apps Script中使用Math.sin()引用电子表格函数。有关在JavaScript中使用sin()函数的更多信息，请查看此帖子：http://www.w3schools.com/jsref/jsref_sin.asp 如果你想做前者，那么你应该使用readRows()函数（更多信息可在此处找到：http://gassnippets.blogspot.com/2012/11/create-your-first-google-apps-script.html），将电子表格数据加载到内存中的变量（或变量）中，执行计算，并使用类似的函数将最终结果打印到电子表格中。

让我知道这是否有帮助。

- Kusha Tavakoli

---

SIN函数只是一个例子，我希望能够在自定义函数中使用所有电子表格函数（如query、pmt、nper等）。 - Max L.

Answer 6

我不知道是否可以使用高级函数实现。但是，使用一些常见且易于理解的函数（例如sum、subtract等）是可能的。

以下是我在脚本中使用的代码，用于在计算完成后设置值。

function MyFun1() {
SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('A1').setValue(MyFun2());
}

function MyFun2() {
var one = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard");
var two = one.getRange('A2').getValue();
var three = one.getRange('A3').getValue(); return two*three;
}

不要忘记在Myfun1()上添加一个触发器“onEdit”，以自动更新返回值。

- Sowmay Jain