google-apps-script - 由内置电子表格函数组成的 Google 电子表格自定义函数

Question

我在一个单元格中有以下功能。它从其他单元格读取日期，然后计算年龄并将其转换为“3 周 5 天”格式

=INT((TODAY()-D19)/7)&" weeks, "&(TODAY()-D19-(INT((TODAY()-D19)/7)*7))&" days"

有用。

现在我想将这个长字符串替换为一个简单的函数:

=AGEINWEEKS(D19)

我怎样才能做到这一点？

我已经尝试在脚本编辑器中编写此脚本

function ageInWeeks(from_date) {
  return SpreadsheetApp.getActiveSheet().getRange("D16").setFormula('=ROUND((TODAY()-D16)/7)&" weeks, "&(TODAY()-D16-(ROUND((TODAY()-D16)/7)*7))&" days"');
}

但它返回错误

error: You do not have permission to call setFormula (line 3, file "Code")

先感谢您!

问候，
托马日

Answer 1

如果您希望创建自定义函数以提供与电子表格函数相同的结果，则必须以执行计算的方式使用 Google Apps 脚本，而不是尝试使用它在电子表格中设置公式(正如 Serge 指出的那样，这将无法正常工作，因为它是不允许的)。

(另外，为了让 future 的读者清楚，Google Apps 脚本及其自定义函数无法使用 Google 电子表格公式执行计算。它们使用 javascript 语言执行计算。另一方面，脚本，通过 setFormulas 可以 用于将电子表格公式写入单元格，然后电子表格公式将在​​其中执行计算......但如果脚本被用作自定义函数(即，如果在单元格中使用 =符号)但 仅当 从其他方式调用脚本时，例如菜单项或触发器，无论是响应电子表格编辑的触发器还是计时器。)

所以，你可以写 javascript进入自定义函数来计算周数和天数，这样的脚本可以是:

function ageInWeeks(date) {
var mil = new Date() - new Date(date).getTime()
var seconds = (mil / 1000) | 0;
mil -= seconds * 1000;

var minutes = (seconds / 60) | 0;
seconds -= minutes * 60;

var hours = (minutes / 60) | 0;
minutes -= hours * 60;

var days = (hours / 24) | 0;
hours -= days * 24;

var weeks = (days / 7) | 0;
days -= weeks * 7;
return weeks +' wks, ' + days + ' days'

}

如果它引用 D19，则可以将其放入除 D19 之外的任何单元格中的电子表格中，例如:

=ageInWeeks(D19)

其中单元格 D19 包含过去的日期。

(请注意，自定义函数可能比您原来的电子表格公式慢)