Google Calendar / Confluence Integration thoughts

braindump.md

Assumptions

From what I can gather, here are the desired outcomes of having a community google calendar for the intranet

1. Quick access to entire calendar via LiSN home page (already exists)
2. Ability to place single event instances in the weekly update with ability for staff to easily add a single event to their personal calendar

(2) is currently the pain point, as the steps involved are very tedious. I wonder if this should be thought of differently, either way we need to leverage the google calendar more imo.

Potential solution(s) - leverage Google Calendar API

A starting point for this might be to look into using the Google Calendar API as follows (via a Confluence macro):

1. List events given a required time period. Let user choose via confluence macro data entry form and map to timeMin and timeMax filters
2. The macro then iterates over the list of events, grabs their eventID's and required metadata to show users, and then generates a list of HTML entries with actionable links
3. A staff person responsible for create a new "Weekly Update" post simply adds an instance of the macro, provides the necessary date range information (and whatever else ends up needed) and the Upcoming Events section is drawn automatically

This might offer the following options for those actionable links:

Idea 1: Rather than go through the process of generating the .ics file, provide a link for people to add themselves as an attendee of the event. This "should" generate an email to them with an attached ics file. (obviously need to test and confirm). This could probably be facilitated by a direct link to event URL. Unless there's a fancier way via the API.

Idea 2: Explore whether there is a dynamic ics URL that google provides if one supplies the necessary information (calendar id, event id)

Idea 3: Create an ics file on the fly (see template file). The template actually isn't that bad. Not first choice, but really there are only a few bits in this that actually change.

CalDAV API

Similar thoughts to above, but may be able to get ics files directly this way

https://developers.google.com/google-apps/calendar/caldav/v2/guide

Concerns:

• There may be access control/authorization concerns we'd need to deal with here.
• server side code may need to be involved here. how best to integrate that kinda thing into Confluence, while still being able to use JavaScript for the main API interaction (which Google seems to support)

generate_ics.php

<?php 
/*
 * generates calendar ics file
*/

if(
  empty($_GET['summary']) ||
  empty($_GET['location']) ||
  empty($_GET['dtstart']) ||
  empty($_GET['dtend'])
){
  header ("Location: /somewhere/out-there") ; //TBD
  exit();
}
$summary = htmlentities($_GET['summary']);
$location = htmlentities($_GET['location']);
$dtstart = htmlentities($_GET['dtstart']);
$dtstart_ics = date_create_from_format(DateTime::W3C, $dtstart)->format('Ymd\THis');
$dtend = htmlentities($_GET['dtend']);
$dtend_ics = date_create_from_format(DateTime::W3C, $dtend)->format('Ymd\THis');

header("Content-type: text/calendar");
header("Content-Disposition: attachment; filename=".urlencode($summary).'-'.time().".ics");
header("Pragma: no-cache");
header("Expires: 0");

echo 'BEGIN:VCALENDAR
PRODID:-//Microsoft Corporation//Outlook 15.0 MIMEDIR//EN
VERSION:2.0
METHOD:PUBLISH
X-MS-OLK-FORCEINSPECTOROPEN:TRUE
BEGIN:VEVENT
CLASS:PUBLIC
CREATED:'. date('Ymd\THis\Z') .'
DESCRIPTION:\n
DTEND:'. $dtend_ics .'
DTSTAMP:'. date('Ymd\THis\Z') .'
DTSTART:'. $dtstart_ics .'
LAST-MODIFIED:20170808T172248Z
LOCATION:'. $location .'
PRIORITY:5
SEQUENCE:0
SUMMARY;LANGUAGE=en-us:'. htmlentities( $_GET['summary'] ) .'
TRANSP:OPAQUE
UID:'. hash("sha256", "$summary$location$dtstart$dtend") .'
X-MICROSOFT-CDO-BUSYSTATUS:BUSY
X-MICROSOFT-CDO-IMPORTANCE:1
X-MICROSOFT-DISALLOW-COUNTER:FALSE
X-MS-OLK-AUTOFILLLOCATION:FALSE
X-MS-OLK-CONFTYPE:0
BEGIN:VALARM
TRIGGER:-PT15M
ACTION:DISPLAY
DESCRIPTION:Reminder
END:VALARM
END:VEVENT
END:VCALENDAR';
?>

ics-template.ics

BEGIN:VCALENDAR
PRODID:-//Microsoft Corporation//Outlook 15.0 MIMEDIR//EN
VERSION:2.0
METHOD:PUBLISH
X-MS-OLK-FORCEINSPECTOROPEN:TRUE
BEGIN:VTIMEZONE
TZID:Pacific Standard Time
BEGIN:STANDARD
DTSTART:16011104T020000
RRULE:FREQ=YEARLY;BYDAY=1SU;BYMONTH=11
TZOFFSETFROM:-0700
TZOFFSETTO:-0800
END:STANDARD
BEGIN:DAYLIGHT
DTSTART:16010311T020000
RRULE:FREQ=YEARLY;BYDAY=2SU;BYMONTH=3
TZOFFSETFROM:-0800
TZOFFSETTO:-0700
END:DAYLIGHT
END:VTIMEZONE
BEGIN:VEVENT
CLASS:PUBLIC
CREATED:20170808T172248Z
DESCRIPTION:\n
DTEND;TZID="Pacific Standard Time":20170818T104500
DTSTAMP:20170808T172248Z
DTSTART;TZID="Pacific Standard Time":20170818T100000
LAST-MODIFIED:20170808T172248Z
LOCATION:Seuss Room
PRIORITY:5
SEQUENCE:0
SUMMARY;LANGUAGE=en-us:ALP APD candidate presentation - Korey Brunetti 
TRANSP:OPAQUE
UID:040000008200E00074C5B7101A82E00800000000F021BF3C3010D301000000000000000
	0100000004DA0A56F55E7494095A2BDB5B9BFE16C
X-ALT-DESC;FMTTYPE=text/html:<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//E
	N">\n<HTML>\n<HEAD>\n<META NAME="Generator" CONTENT="MS Exchange Server ve
	rsion rmj.rmm.rup.rpr">\n<TITLE></TITLE>\n</HEAD>\n<BODY>\n<!-- Converted 
	from text/rtf format -->\n\n<P DIR=LTR><SPAN LANG="en-us"></SPAN></P>\n\n<
	/BODY>\n</HTML>
X-MICROSOFT-CDO-BUSYSTATUS:BUSY
X-MICROSOFT-CDO-IMPORTANCE:1
X-MICROSOFT-DISALLOW-COUNTER:FALSE
X-MS-OLK-AUTOFILLLOCATION:FALSE
X-MS-OLK-CONFTYPE:0
BEGIN:VALARM
TRIGGER:-PT15M
ACTION:DISPLAY
DESCRIPTION:Reminder
END:VALARM
END:VEVENT
END:VCALENDAR