{"id":156,"date":"2021-08-20T02:17:36","date_gmt":"2021-08-20T02:17:36","guid":{"rendered":"http:\/\/aurimas.eu\/blog\/?p=156"},"modified":"2022-07-23T04:58:18","modified_gmt":"2022-07-23T04:58:18","slug":"from-an-excel-schedule-to-outlook-calendar-with-powershell","status":"publish","type":"post","link":"https:\/\/aurimas.eu\/blog\/2021\/08\/from-an-excel-schedule-to-outlook-calendar-with-powershell\/","title":{"rendered":"From an Excel schedule to Outlook Calendar with PowerShell"},"content":{"rendered":"\n

(it was actually quite pleasant!)<\/h1>\n\n\n\n

I am starting studies at Georgia Tech (yay!) and while the semester didn’t even start yet, it already feels a bit overwhelming. Tons of emails, activities, and lots of calculus. More than I was expecting, actually!<\/p>\n\n\n\n

Now, one of the ways I deal with such stress is by getting organized. Sometimes I wonder if that’s just a form of procrastination (organizing stuff vs. actually dealing with it), but it helps. When my email inbox is under control, my calendar is up to date, I just feel better. period. <\/p>\n\n\n\n

So it probably won’t be surprising that when an email arrived announcing some additional “TA sessions”, with 3 sessions every week until the end of the semester, I had an immediate urge to *organize it*. Except that the schedule came in a form of an Excel spreadsheet…<\/p>\n\n\n\n

\"\"
Not exactly “Outlook-ready”…<\/figcaption><\/figure>\n\n\n\n

Now, I’ve spent the past few days refreshing Python skills, and setting up a new laptop (WSL2 is great<\/a>, it looks like I won’t be dual-booting Windows\/Ubuntu anymore.. feels slightly morally wrong, though). In all that process, I had a chance to play around with PowerShell, and I was wondering how hard would it be to create .ics files\/events in Outlook calendar for all these sessions. For someone who wasn’t really familiar with PowerShell control structures, equality operators (“-ne”, that’s basically Lithuanian..), the answer is ~1 hour hard. That was surprisingly fast. <\/p>\n\n\n\n

Most of the credit goes to the author of the New-IcsEvent PowerShell function<\/a> which generates .ics files based on supplied parameters. In my case, I did not bother with reading the Excel file either – turned out that working with lists\/strings in PowerShell is very intuitive. <\/p>\n\n\n

\n$weeks = "08-23-21\n08-30-21\n09-06-21\n09-13-21\n09-20-21\n09-27-21\n10-04-21\n10-11-21\n10-18-21\n10-25-21\n11-01-21\n11-08-21\n11-15-21\n11-22-21\n11-29-21\n12-06-21".split("`n") \n#weeks is now an array - just like in Python \n#(except, of course, unlike the rest of the world, we don't use \\n for line breaks..)\n<\/pre><\/div>\n\n\n
After some fiddling (and extracting locations and session titles to similar arrays as above), all my code had to do was pretty much this:<\/p>\n\n\n
\nfor ($i = 0; $i -lt $weeks.Count; $i++) {\n    $start_week_date = [datetime]::parseexact($weeks[$i], 'MM-dd-yy', $null)\n    if ($tuesdays[$i] -ne "") {\n        $day = $start_week_date.AddDays(1).ToString("yyyy-MM-dd") + " 09:15"\n        $topic = "MSA TA: " + $tuesdays[$i]\n        Write-Output $day\n        New-IcsEvent -Path C:\\users\\aurim\\Desktop -Subject $topic -Location 'location' -StartDate $day -ShowAs Free -Duration '01:00:00'\n    }\n    if ($thursdays[$i] -ne "") {\n        $day = $start_week_date.AddDays(3).ToString("yyyy-MM-dd") + " 09:15"\n        $topic = "MSA TA: " + $thursdays[$i]\n        Write-Output $day\n        New-IcsEvent -Path C:\\users\\aurim\\Desktop -Subject $topic -Location 'location' -StartDate $day -ShowAs Free -Duration '01:00:00'\n    }\n    if ($fridays[$i] -ne "") {\n        $day = $start_week_date.AddDays(4).ToString("yyyy-MM-dd") + " 13:00"\n        $topic = "MSA TA: " + $fridays[$i]\n        Write-Output $day\n        New-IcsEvent -Path C:\\users\\aurim\\Desktop -Subject $topic -Location 'location' -StartDate $day -ShowAs Free -Duration '01:00:00'\n    }    \n}\n<\/pre><\/div>\n\n\n
Now, this created a bunch of .ics files on my desktop. 44 to be precise. Turns out that .ics can contain multiple events<\/a>, however. So all I needed to do was to concatenate all the files together, skipping the first 4 and the last line. That would be quite simple on Unix: ls *.ics \" | tail -n +2 | head -n -1 | cat > my_master_schedule.ics<\/code>. I have a feeling that with some fiddling with awk\/sed, it’s very likely possible to even ensure that the first 4 lines of the first file and the last line of the very last file are not skipped, giving us a fully valid .ics file in one go. <\/p>\n\n\n
\nBEGIN:VCALENDAR\nVERSION:2.0\nMETHOD:PUBLISH\nPRODID:-\/\/JHP\/\/We love PowerShell!\/\/EN\nBEGIN:VEVENT\nUID: ab4d0283-885b-433f-b269-7ce6b841df46\nCREATED: 20210820T005313Z\nDTSTAMP: 20210820T005313Z\nLAST-MODIFIED: 20210820T005313Z\nCLASS:Public\nCATEGORIES:\nSEQUENCE:0\nDTSTART: 20210826T131500Z\nDTEND: 20210826T141500Z\nDESCRIPTION: \nSUMMARY: MSA TA: R Intro part I\nLOCATION: IC215\nTRANSP:TRANSPARENT\nEND:VEVENT\nEND:VCALENDAR\n<\/pre><\/div>\n\n\n
Nevertheless, I was curious how difficult would it be to do the same in PowerShell. It took some searching, but it turned out it is pretty straightforward, too. By the way, that’s one thing I did not like about PowerShell – there are tons of answers on the web, but quite a few of them are clearly low quality, even on StackOverflow. Reminds me a bit of looking for good VBA guidance. Curse of the masses, I suppose?<\/p>\n\n\n
\nls *.ics | get-content | where {$_.readcount -gt 4 -and $_ -ne "END:VCALENDAR"} > MSA_TA_SESSIONS.icsx                   \n<\/pre><\/div>\n\n\n
As you can see, I cheated a bit as I think get-content<\/code> reads line-by-line, and so I could not refer to total line count (there definitely are ways to do that, though, it just seemed to require more steps). Either way, I had all I needed. <\/p>\n\n\n\n
Now it was time to finish up the exercise by opening Notepad and adding those missing first 4 and the very last line manually. No point in writing code for that. <\/p>\n","protected":false},"excerpt":{"rendered":"
A quick post about using PowerShell to create .ics files (and then afterwards combining them into a master calendar file). Turns out PowerShell is really quite pleasant to work with!<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[67],"tags":[73,74,72],"class_list":["post-156","post","type-post","status-publish","format-standard","hentry","category-data-analytics","tag-automation","tag-gt","tag-powershell"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paWzzQ-2w","_links":{"self":[{"href":"https:\/\/aurimas.eu\/blog\/wp-json\/wp\/v2\/posts\/156","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/aurimas.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/aurimas.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/aurimas.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/aurimas.eu\/blog\/wp-json\/wp\/v2\/comments?post=156"}],"version-history":[{"count":4,"href":"https:\/\/aurimas.eu\/blog\/wp-json\/wp\/v2\/posts\/156\/revisions"}],"predecessor-version":[{"id":164,"href":"https:\/\/aurimas.eu\/blog\/wp-json\/wp\/v2\/posts\/156\/revisions\/164"}],"wp:attachment":[{"href":"https:\/\/aurimas.eu\/blog\/wp-json\/wp\/v2\/media?parent=156"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/aurimas.eu\/blog\/wp-json\/wp\/v2\/categories?post=156"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/aurimas.eu\/blog\/wp-json\/wp\/v2\/tags?post=156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}