How to format the time in this Google Apps Script

Question

I want to format a Google Sheet cell when I read it in my script but it comes out 25 minutes later.
The cell's time is 20:00:00 but the output is 20:25. I am using this

var formattedDate = Utilities.formatDate(time, "GMT+2", HH:mm");

Without using it the time comes as 1899-12-30T18:25:08.000Z even though the cell's content is 20:00:00. Any ideas?

Answer 1

The way that Sheets and Apps Script handle "dates" are very different.

• For Sheets the "date" unit is 1 day; The base date is 1899-12-30 0:00:00, getting the timezone from the spreadsheet settings.
• For Apps Script, which is based on JavaScript, the "date" unit is 1 millisecond. The base date is 1970-1-1 00:00:00 UTC.

Below is an example script that if the active cell has 20:00:00 it will add "25:00" to the cell to the right.

function myFunction() {
  /* Assume that cell value is 8:00:00 pm.
  The displayed value depends on spreadsheet settings and cell format */
  var range = SpreadsheetApp.getActiveRange(); // See Apps Script documentation


  var value = range.getValue(); // This returns a date object;

  var time = value.getTime(); // See JavaScript documentation

  var delta = 25 * 60 * 1000; // Convert 25 minutes to milliseconds

  var newTime = time + delta; // Calculate new time

  var newDate = new Date(newTime); // Get new date object

  var spreadsheet = SpreadsheetApp.getActive(); // See Apps Script documentation
  var timezone = spreadsheet.getSpreadsheetTimeZone(); // See Apps Script documentation
  var format = 'HH:mm'; // Set date format for output
  var output = Utilities.formatDate(newDate, timezone, format); // This returns a string
  var target = range.offset(0, 1); // See Apps Script documentation
  target.setValue(output); // See Apps Script documentation
}

Answer 2

you are calling time inside date (formatDate) without actual date format so the sheet is guessing.

var formattedDate = Utilities.formatDate(time, "GMT+2", "MM-dd-yyyy HH:mm:ss");