Google docsProgramming

Moment JS in Google Sheets (Apps script) – The easy/lazy way

By June 11, 2019 No Comments

Date/time handling in JavaScript is a bit of a pain, thankfully there are libraries like Moment JS to save the day.

If you want to integrate better time/date handling from Moment JS in Google sheets (the lazy way), this is how you do it:

  1. Make a copy of this sheet 
  2. Your formula is simply =momentjs() to get today’s date in YYYY-MM-DD format

How this works:

This lazy function just allows you to add Moment JS classes as a parameter.  You’ll need to add the Moment JS library into a script as well.

/**
* Returns a wrapper for the Moment JS Library
*
* @param {".format('YY-MM-DD"} parameters REQUIRED 
* @param {"D1"} dummyParam OPTIONAL Include a cell reference to force the custom function to update. 
* @return Returns Moment JS functions
* @customfunction
*/

function momentjs(parameters,dummyParam) {
if (!arguments[0]) return eval("moment().format('YYYY-MM-DD')")
return eval("moment()"+(arguments[0])) 
}

The dummyParam is a workaround for custom functions not refreshing, which can happen from time to time. To get past this, you would add a cell reference, say D1 and then updating cell D1 would result in the custom function refreshing.

Check out the sheet to see what’s happening:

Dave

Author Dave

More posts by Dave