Search code examples
google-apps-scriptgoogle-sheetstime

datetime switching timezone from google app script to google sheets intentionally

I'm trying to fill some google sheets cells with the current date and time, date is ok, but the time is off by 6 hours. This is the code I used:

      function start() {
      var ss = SpreadsheetApp.getActive();
      var timestamp = new Date();
      Logger.log('start, timestamp: ' + timestamp);
      var date = Utilities.formatDate(timestamp, 'Europe/Brussels', "dd/MM/yyyy");
      Logger.log('date: ' + date);
      var time = Utilities.formatDate(timestamp, 'Europe/Brussels', "HH:mm:ss");
      Logger.log('time: ' + time);
      var sheet = SpreadsheetApp.getActive().getSheetByName(SheetName);
      sheet.appendRow([date,time]);
    }

The logs of the date and time do give me the expected date and time, but once the appendRow is done, in Google Sheets, I'm getting the offset time... I have tried to offset it myself, to correct to the right value, but that didn't change anything.

Solution

  • That is odd, I tested your code and it works for me.

    Original Script

    Sheet

    It's properly showing the timezone of 'Europe/Brussels'. Also, tested using var time = Utilities.formatDate(timestamp, 'GMT+1', "HH:mm:ss"); and both display the correct time inside the Execution log and the cell.

    GMT+1

    Sheet GMT+1

    If you test the code in a new Apps Script does it show the incorrect time zone too? Have you reviewed the time timezone inside Project Settings > General Settings?

    Project Settings

    If after all that you still have the same issue, I will recommend creating an issue tracker by following the steps here.