Google Spreadsheet custom function made from built-in spreadsheet functions

Question

I have the following function in a cell. It reads the date from another cell, which calculates the age and converts it in the format "3 weeks 5 days"

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

He works.

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

 =AGEINWEEKS(D19) 

How can i do this?

I already tried to write this script in the 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"'); } 

But it returns an error

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

Thank you in advance!

Regards, Tomaž

Answer 1

If you want to create a user-defined function to get the same result as your spreadsheet, you will have to use Google Apps Script so that it performs the calculation, rather than trying to use it to set the formula in the table (which, as Serge pointed out, does not will work because it is prohibited).

(In addition, for future readers to understand, Google Apps Script and their user functions cannot perform calculations using Google spreadsheet formulas. They perform javascript calculations. On the other hand, a script, through setFormulas can be used to write an electronic formula the table in the cell where the spreadsheet formula will perform the calculation ... but it cannot do this if the Script is used as a user-defined function (i.e. if it is called inside the cell with the = sign), but only if the Script call is called from another tool, for example, from a menu item or from a trigger, be it a trigger responsible for editing a spreadsheet or a timer.)

So, you can write javascript in a user-defined function to calculate 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' } 

This can be placed in a spreadsheet in any cell except D19, if it refers to D19, for example:

 =ageInWeeks(D19) 

where cell D19 contains a past date.

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

Answer 2

From the documentation: User-defined functions return values, but they cannot set values ​​outside the cells in which they are located. In most cases, the user-defined function in cell A1 cannot change cell A5.

If you describe your typical use case, you can suggest a different approach.