0

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?

  • Set your locale correctly in settings – TheMaster Jul 12 '18 at 23:10
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
}
0

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");

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.