CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Leon Lai
Leon Lai

Reputation: 23

Google Sheets: Date formatting from within custom functions

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;    
}

Upvotes: 2

Views: 2340

Answers (2)

Wicket
Wicket

Reputation: 38170

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?

Upvotes: 4

Nathan Brown
Nathan Brown

Reputation: 156

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.

Upvotes: 0

Related Questions