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：

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 this way:

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

CodePudding user response：

Your code gets a datetime value from the spreadsheet. Based on the screenshot, it looks like the datetime only has a time component. That means that the date component will default to the spreadsheet epoch, i.e., 30 December 1899.

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, the script project has a timezone of its own, which may be different from that of the spreadsheet. That is why it is important to use Utilities.formatDate() and the spreadsheet's timezone when presenting a datetime value to the user.

Offsets used to be pretty weird in 1899, so if you omit the date component, you may get surprising results. Your code assumes the GMT 1 timezone, which may be different from the timezone of the spreadsheet. Most likely, your timezone is EEST rather than GMT 1. These two are not the same today, and they have been much different in the past.

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 timezone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
const datetime = new Date(dateString   timeString);
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.