Manipulating Date/Time Values with Google Apps Script

Question

I've been looking through Google App Script and having trouble finding info about the datetime object(?) they use. In my particular case, I'm trying to manipulate date-time values inputted by the user in a Google Spreadsheet. For example, we could return the time someone entered in cell B3 rounded to the nearest minute in a spreadsheet cell via some formula like =MROUND(B3,"00:01:00"). However, I don't know if there's an appropriate javascript/apps-script operation that does the same thing via the script editor?

I imagine maybe the trick involves finding some equivalent integer value to the datetime value (similar to the Date object in JS?) that we can then do a regular math operation on? Unfortunately, I only see the outputs in date-time format when I try to Logger.log the appropriate cell value(s)..

When you use getValue() from a script it will return a JavaScript date object if the cell contains a date or a time value. From there you can use any JS date method. — Serge insas

Cooper Cooper · Accepted Answer · 2017-09-02T15:36:24

I think this does what you requested. Checkout Utilities.formatDate().

function NEARESTMINUTE(value)
{
  if(time)
  {
     return Utilities.formatDate(new Date(value), Session.getScriptTimeZone(), "HH:mm:00")
  }
  else
  {
    return 'Invalid Inputs';
  }
}

Manipulating Date/Time Values with Google Apps Script

1 Answers