由内置电子表格函数组成的 Google 电子表格自定义函数 [英] Google Spreadsheet custom function made of built in spreadsheet functions

问题描述

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

I have following function in a cell. It reads date from other cell, than calculates age and transforms it in format "3 weeks 5 days"

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

它有效.

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

Now I would like to replace this long string into one simple function:

=AGEINWEEKS(D19)

我该怎么做?

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

I already tried to write this script in Script Editor

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

但它返回错误

错误:您无权调用 setFormula(第 3 行，文件代码")

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

先谢谢你！

问候，托马

推荐答案

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

If you are looking to create a custom function to give the same result as your spreadsheet function, you will have to use Google Apps Script in a way that it performs the calculation, rather than attempt to use it to set a formula into the spreadsheet (which as Serge has pointed out, will not work as it is disallowed).

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

(Also, just to be clear to future readers, Google Apps Script and their custom functions cannot perform calculations using google spreadsheets formulas. They perform calculations in javascript language. On the other hand, the script, via setFormulas can be used to write a spreadsheet formula into a cell, where the spreadsheet formula will then perform a calculation ... but it cannot do this if the script is being used as a custom function (i.e. if it is called within a cell with an = sign) but only if the script is called from another means, such as a menu item or from a trigger, be it a trigger responsing to a spreadsheet edit or a timer.)

因此，您可以将 javascript 写入自定义函数来计算周、天，这样的脚本可以是:

So, you can write javascript into the custom function to calculate the weeks,days and such a script could be:

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 之外的任何单元格中，例如:

This could be put into the spreadsheet in any cell except D19 if it refers to D19, such as this:

=ageInWeeks(D19)

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

where the cell D19 contains a date in the past.

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

(Note that the custom function is likely to be slower than your original spreadsheet formula)

这篇关于由内置电子表格函数组成的 Google 电子表格自定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！