Daylight saving time offset with Google Calendar?

AppSheet AppSheet Q&A
, ,
1

GCal is set up as a datasource, with Bots entering events based on data entered in other AppSheet tables. This works, BUT:

Any events after 31. March (begin of daylight saving time in Germany) are synced to GCal with start and end dates +1h, so an entry that should start at 10 is entered into the Google calendar starting at 11. Both for bot-created events and manual entries into the calendar via AppSheet.

This can’t be expected behavior?

I could imagine including some sort of IF(date is after 31. March then use start and end time minus 1) into the bot actions, but this seems messy.

1 Like
2

Unfortunately this is a known bug.. for many years.

1 Like
3

As Aleksi mentioned , yes there is no native support in AppSheet/ GCAL for this.

You are correct. An IF() based solution may be needed based on daylight saving dates. Another post on similar lines just in case it helps.

Re: Timezone bug - Google Cloud Community

1 Like
4

Update to solution in case anyone stumbles across this:

The mentioned if() construction worked AS LONG AS THE CURRENT DATE WAS OUTSIDE DAYLIGHT SAVING TIME, then it added the events with wrong start/end times again regardless of them falling in- or outside of DST.

Frankly, I did not feel like digging into the whole thing again and adding some sort of logic IF(current date within DST), so I opted for a different solution:

Events that should be added to Google Calendar are added to a separate table with references to the original records, and then Zapier adds them to the Google Calendar. No fuss with adding/subtracting anything there, 10:00 remains 10:00 whether or not it’s within DST.

Added bonus, Zapier can then add the Google Calendar Entry ID into that table, so automated updates to that entry are easier later on.

5

I know this is a bit old, but I built the following code to deal with this problem. I built it off of some code that @MultiTech posted in another thread, but made it more complex. First, it determines if the user is currently in or out of DLT at the moment. Then, based on that outcome, it determines how to adjust the target date based on whether the target date is inside or outside of DLT relative to the current date. See below ([Start] is assumed to be your variable with your start datetime value for the calendar event. You can recycle the exact same code substituting an [End] datetime to deal with the end):

if(
SWITCH(MONTH(NOW()),
3, today() >= EOWEEK(date(Concatenate(“3/1/”, text(today(), “yyyy”)))) + 8,
11, today() < EOWEEK(date(Concatenate(“11/1/”, text(today(), “yyyy”)))) + 1,
AND(
MONTH(NOW()) > 3,
MONTH(NOW()) < 11
)
),
if(
SWITCH(MONTH([Start]),
3, date([start]) >= EOWEEK(date(Concatenate(“3/1/”, text(date([Start]), “yyyy”)))) + 8,
11, date([start]) < EOWEEK(date(Concatenate(“11/1/”, text(date([start]), “yyyy”)))) + 1,
AND(
MONTH([Start]) > 3,
MONTH([Start]) < 11
)
),
[Start],
[Start]+“001:00:00”
),
if(
SWITCH(MONTH([Start]),
3, date([start]) >= EOWEEK(date(Concatenate(“3/1/”, text(date([Start]), “yyyy”)))) + 8,
11, date([start]) < EOWEEK(date(Concatenate(“11/1/”, text(date([start]), “yyyy”)))) + 1,
AND(
MONTH([Start]) > 3,
MONTH([Start]) < 11
)
),
[Start]-“001:00:00”,
[Start]
)
)

6

Has Anyone actually solved this problem definitively? I have an App sheet calendar-based app that is still giving a 1 hour offset outside of DLS time.. I have used the suggested solutions but still have no success. Can anyone please advise..?

1 Like