Google Script when taking time from sheets adds 1 minute-CodePudding

 var pickupTimeOld = wysylka.getRange("C5").getValue()
 var pickupTime = Utilities.formatDate(pickupTimeOld, "GMT 1",  'HH:mm')

I am facing an issue while taking the value from google sheets which is for example 9:50:00, it's changing the time to 09:26. I can not find where it subtracts 24 minutes, as from the code everything is fine. Is ther eany possibilty that the confiuguration of google sheets make that issue? What can be reason of this substract.

Screen of formating in google sheets:

CodePudding user response：

Chances are that the issue is caused by your code not observing the date component of the datetime value you want to present as HH:mm.

Your code gets a datetime value from the spreadsheet, but based on the screenshot, it looks like that datetime value only has a time component. That means that the date component will default to the spreadsheet epoch, i.e., 30 December 1899. Timezone offsets used to be pretty weird in 1899, so if you omit the date component, you may get surprising results.

Even today, not all timezones are whole hours — there are :30 and :45 offsets in the world in places like Canada, Australia, New Zealand, India, Iran, Afghanistan and Nepal. Dublin Mean Time used to be UTC-00:25:21 and Bombay Time used to be UTC 04:51. Malaya and Singapore used UTC 07:20 as daylight saving time until 1941. And so on.

JavaScript dates are always in UTC, but when you are presenting them, it uses the date's timezone and zoneinfo to determine the offset to use on the particular date that the date object reflects in the timezone of the runtime environment.

In Google Apps Script, certain properties of the runtime environment are set by the script project. The script project has a timezone of its own, separately from the hosting spreadsheet, and these two timezones may differ. That is why it is important to use Utilities.formatDate() and the spreadsheet's timezone when presenting a datetime value to the user.

Your code assumes the GMT 1 timezone, which may be different from the timezone of the spreadsheet. Most likely, your timezone is EET rather than GMT 1. These two are not the same thing. Currently the most important difference is that EET observes daylight saving time (EEST) while GMT 1 does not. It is possible that in the year 1899 there were other differences as well, which may explain the 24-minute discrepancy you describe.

To get the time value as it shows in the spreadsheet, you need to include the date component, and format the resulting datetime in the timezone of the spreadsheet, like this:

  const dateString = wysylka.getRange('B5').getDisplayValue().replace(/^(\d )-(\d )-(\d )$/, '20$3-$2-$1 ');
  const timeString = wysylka.getRange('C5').getDisplayValue();
  const datetime = new Date(dateString   timeString);
  const timezone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  const pickupTimeString = Utilities.formatDate(datetime, timezone, 'HH:mm');

You may also want to check that your spreadsheet's File > Settings > Time zone is set correctly. Note that changing the timezone will change the presentation of datetime values in the spreadsheet, so they may need correction after you change the timezone.

CodePudding user response：

Try this:

var wysylka = SpreadsheetApp.getActiveSheet();
var pickupTimeOld = wysylka.getRange("C5").getDisplayValue();
var tz = Session.getTimeZone(); // or SpreadsheetApp.getActive().getSpreadsheetTimeZone();
var today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
var pickupTime = Utilities.formatDate(new Date(today   ' '   pickupTimeOld), tz, 'HH:mm');

Probably in your case it makes sense to define today about this way:

var today = wysylka.getRange("B5").getDisplayValue()