Gmail calendar .ics export fix for UTC

Forum for things that doesn't really have anything to do with hMailServer. Such as php.ini, beer, etc etc.
Post Reply
palinka
Senior user
Senior user
Posts: 1096
Joined: 2017-09-12 17:57

Gmail calendar .ics export fix for UTC

Post by palinka » 2019-10-02 14:26

I recently moved a work email account hosted by google apps to my hmailserver and I needed the calendar to go with it, so I exported my calendar from gmail and imported it into EmClient. Two strange things occurred:

1) All unanswered invites were RE-SENT for every event going back to 2007. Unbelieveable! Hundreds of emails got sent out inviting people to meetings from years ago. So embarrassing. Funny enough, the bulk of the replies were people looking for work, as in "hey, I got this strange message from you, but keep me in mind for your next project". LOL

2) The meeting times were all UTC instead of my local time zone. Probably not a big deal for old events, but for more recent ones and of course future ones its a problem.

This led me to investigate the ics file that was exported from gmail. With a little experimenting, I found that a simple text replace with notepad++ would fix issue #1: Replace all "PARTSTAT=NEEDS-ACTION" with "PARTSTAT=ACCEPTED". This makes the calendar program think that all invitations were accepted and no action is needed.

However, issue # 2 was more complicated because it requires subtracting (in my case) 5 hours from the meeting times in order to put it in the correct time zone. So a simple word replace wouldn't work. Plus, as a string, there are several different items to change, eg: DTSTART, DTEND, DTSTAMP, etc.

Example:

Code: Select all

DTSTART:20070801T193000Z
The Z at the end signifies UTC. But I needed to do more than get rid of the Z, I also needed to add the correct time zone and I found that the format is as follows (same timestamp but in local time):

Code: Select all

DTSTART;TZID=America/New_York:20070801T153000
So I came up with a powershell script to find UTC timestamps and change them to my time zone timestamps.

Code: Select all

$ICSfile = 'C:\scripts\cal\user@gmail.com.ics'              # calendar ics exported from gmail
$newICSfile = 'C:\scripts\cal\user@gmail.com-TZupdate.ics'  # modified ics output from script
$regex = '^[A-Z-]{5,14}\:20[0-9]{6}T[0-9]{6}Z'
$timezone = 'America/New_York'                              # timezone - list here: https://www.php.net/manual/en/timezones.php

Get-Content $ICSfile | ForEach-Object {
    if($_ -match $regex){
		$oldstring = $_
		$items = $_.split(':')
		$calresource = $items[0]
		$caldatetime = [datetime]::ParseExact($items[1], "yyyyMMddTHHmmssZ", $null)
		$newcaldatetime = $caldatetime.ToString("yyyyMMddTHHmmss")
		$newstring = $calresource+";TZID="+$timezone+":"+$newcaldatetime
	}
	$_.replace($oldstring, $newstring)
} | out-file $newICSfile
When powershell converts the UTC date string to a readable date, it automatically converts it to local time, so there's no actual need for adding or subtracting hours once you get rid of UTC. The script does not deal with issue #1, so you still need to do that manually, but it only takes a couple of seconds.

palinka
Senior user
Senior user
Posts: 1096
Joined: 2017-09-12 17:57

Re: Gmail calendar .ics export fix for UTC

Post by palinka » 2019-10-04 16:25

palinka wrote:
2019-10-02 14:26
1) All unanswered invites were RE-SENT for every event going back to 2007. Unbelieveable! Hundreds of emails got sent out inviting people to meetings from years ago. So embarrassing. Funny enough, the bulk of the replies were people looking for work, as in "hey, I got this strange message from you, but keep me in mind for your next project". LOL

This led me to investigate the ics file that was exported from gmail. With a little experimenting, I found that a simple text replace with notepad++ would fix issue #1: Replace all "PARTSTAT=NEEDS-ACTION" with "PARTSTAT=ACCEPTED". This makes the calendar program think that all invitations were accepted and no action is needed.
As it turns out - changing NEEDS-ACTION to ACCEPTED does absolutely nothing to prevent messages from being sent. I'm not sure if this is a flaw in EmClient or Horde, or if its even a flaw at all. Again, hundreds of invites (meeting updates) were sent. But this time I created a global rule in hmailserver to delete them before they were sent. Then when the message flood was over, I deleted the rule. Nasty stuff and potentially embarrassing. Something to keep in mind, anyway.

Post Reply