Google Sheets: Date formatting from within custom functions

Question

I have following custom function in google sheets, I tried to call a built-in function "TEXT" in my custom function but it is not successful. The Google sheets will prompt "unknown" function "TEXT". Is there a solution for this?

function NextMonth(StockTradeDate) {
  var DeltaDate;
  if (**TEXT**(StockTradeDate,"mmm") = "JAN" ) {
  DeltaDate = 30;
      }
  return DATEVALUE(StockTradeDate) + 31;    
}

score 4 · Answer 1 · edited May 23 '17 at 12:34

4

Google Apps Script has the Utilities library which includes the formatDate method

Utilities.formatDate(date, timeZone, format)

For details see https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate-timezone-format

It's worth to say that in Google Sheets it's no possible call a built-in function within the script. If a service like Utilities doesn't include the functions that you are looking for, then the alternative is to build your own version, get it borrowed from a library, a open source project or any other place.

I made an attempt to use the spreadsheet function library from Ethercalc and shared about this on my answer to Is there a way to evaluate a formula that is stored in a cell?

edited May 23 '17 at 12:34

Community

1
1

answered May 14 '17 at 07:21

Rubén

34,714
9
70
166

Do you happen to know why there's no way to call a built-in function from within the script? Is there a security reason? Not being able to do so really cripples its utility. (I've been trying to call mround and gcd) – kfmfe04 Sep 01 '20 at 21:00
@kfmfe04 Only Google engineers can answer that. – Rubén Sep 01 '20 at 23:04

Nathan Brown · Accepted Answer · 2017-05-07T12:42:46.397

Try using javascript date methods (as below) to drive the date and conditionals you need. Could not locate documentation that supports the Sheets built-in function calls from within an apps script function. Javascript is supported.

function NEXTMONTH(StockTradeDate) {

  var DeltaDate

    if (StockTradeDate.getMonth() === 0 ) { //Jan:0  Feb:1 ... Dec:11 these will need more conditionals. 
    DeltaDate = 30;
    }

  var date2 = new Date(StockTradeDate.valueOf()+ DeltaDate*24*60*60*1000)
                                   // valueOf() is millisec time since 1/1/1970
  return date2
}

If you need more info regarding the date methods and implementation, w3schools has an efficient reference.

Google Sheets: Date formatting from within custom functions

2 Answers2

Linked