如何在Google Apps脚本中添加工作表功能？ [英] How to add a Worksheet function in Google Apps Script?

问题描述

我想在Google Apps脚本中使用内置功能。
类似于VBA的东西 WorksheetFunction
我该如何实现这一点？

例如，如何在脚本中包含：

  var foo = DATEVALUE（mydate）; 
  

解决方案

我遇到同样的挑战。

我的概念解决方案如下：

1. 使用单元格创建电子表格或细胞）专门用于内置函数的输入，另一个单元格包含内置公式及其结果。




2. 从嵌入的Spreadsheet Google Apps脚本，链接到电子表格和两个范围：1）通过第一个链接将值输入到输入单元格（或多个单元格）; 2）通过第二个链接检索公式的结果。

每次在Google Apps脚本中需要内置公式的结果时，代码将所需的输入发送到电子表格并检索内置公式的结果。 这用于常规脚本。它不适用于用户定义的功能。我需要在用户定义的函数中使用= NORMSINV（）函数，没有别的选择，只能将代码导入到我的脚本中了......




如上所述，这是一个概念解决方案，而不是实际的解决方我确信这在脚本效率方面非常昂贵。



请注意，Google Apps脚本基于JavaScript 1.6，它本身具有很多功能。您可能需要检查：



https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference




I want to use a built-in function in a Google Apps script. Something similar to VBA WorksheetFunction How can I achieve that?

For example, how to include in the script:

var foo=DATEVALUE(mydate);

解决方案

I came across the same challenge.

My conceptual solution is the following:

1. Create a Spreadsheet with a cell (or cells) specifically devoted to the inputs of the built-in function, and another cell containing the built-in formula and it's result.

2. From a Spreadsheet embedded Google Apps Script, link to the spreadsheet and the two ranges: 1) through the first link feed the values into the input cell (or cells); 2) through the second link retrieve the result of the formula.

3. Every time you need the result of a built-in formula in the Google Apps Script, from the code send the desire inputs to the spreadsheet and retrieve the result of the built-in formula.

I have tried this for regular scripts. It does not work for user defined functions. I needed the =NORMSINV() function in a user defined function, and had no other option than to import code into my script...

As said, it's a conceptual solution, not a practical one. I am certain this is extremely expensive in terms of script efficiency.

Bear in mind the Google Apps Script is base on JavaScript 1.6, which has a lot of functionality in itself. You may want to check:

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference

这篇关于如何在Google Apps脚本中添加工作表功能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！