private static ExchangeService createExchangeService( LiquidPlannerToolsConfiguration configuration ) {
logger.info( "Connecting to Exchange..." )
ExchangeService service = new ExchangeService( ExchangeVersion.Exchange2010_SP2 )
ExchangeCredentials credentials = new WebCredentials( configuration.exchangeUser,
configuration.exchangePassword,
configuration.exchangeDomain )
service.setCredentials( credentials )
service.autodiscoverUrl( configuration.exchangeAutodiscoverEmail )
return service
}Synchronizing LiquidPlanner with Exchange calendars
Quite some time ago I wrote Groovy code to synchronize Atlassian JIRA with LiquidPlanner. You can find my 2 part blog post on that here and here. In case you don’t know LiquidPlanner, be sure to check it out, it is the best project management/scheduling tool I know.
One thing that was missing is the integration of the holidays into LiquidPlanner. Since the holidays are already in everybody’s Outlook Calendar, I could get the needed information from there.
At first, I was afraid that it would be difficult to do from Groovy/Java, but it turns out if your Microsoft Exchange version is fairly recent, there is a very nice Java library from Microsoft itself you can use. It is called the EWS Java API which stands for Exchange Web Services Java API. Basically, it is a Java library that uses the Web Services API from Exchange, a perfect fit for what I needed to do.
Step 1. Connect to Exchange and get appointments
To connect to exchange we just need the credentials of a domain user. The auto discovery mechanism will automatically find the Exchange server:
The LiquidPlannerToolsConfiguration class is a simple POJO that contains the configuration parameters for the script. Now that we are connected, we can query Exchange for the calendar of a user:
private static def getAppointments(ExchangeService service, String emailAddress) {
def Mailbox mailbox = new Mailbox(emailAddress)
def folderId = new FolderId(WellKnownFolderName.Calendar, mailbox)
def startDate = DateTime.now().toDate()
def endDate = DateTime.now().plusMonths(6).toDate()
def allAppointments = service.findAppointments(folderId, new CalendarView(startDate, endDate))
def relevantAppointments = allAppointments.findAll { Appointment appointment -> appointment.duration.hours >= 4 }
logger.debug "Found ${allAppointments.items.size()} appointments of which ${relevantAppointments.size()} are relevant"
return relevantAppointments
}With the ExchangeService and the email address of a user, we ask for all the appointments in the coming 6 months. Using Groovy’s findAll() method, I filter out appointments that are shorter than 4 hours. I only want to put half days and days that people are not in the office into LiquidPlanner.
Step 2. Create appointments in LiquidPlanner
Now that I have all the relevant appointments, I need to create an entry for each of those in LiquidPlanner:
private static def createAppointmentInLP(LiquidPlannerService liquidPlannerService,
Appointment appointment,
LiquidPlannerUserVacationPackage userVacationPackage) {
liquidPlannerService.createAppointment(userVacationPackage,
getAppointmentIdForExternalReference(appointment),
appointment.subject,
new DateTime(appointment.start, getTimeZone(appointment.startTimeZone)),
new DateTime(appointment.end, getTimeZone(appointment.endTimeZone)))
}
private static String getAppointmentIdForExternalReference(Appointment appointment) {
// LiquidPlanner has trouble matching the id afterwards if there is a '+' in there, so we replace it with something else to work around it.
return appointment.id.uniqueId.replace('+', "_")
}Everything in LiquidPlanner has a field 'External Reference' which is great if you have to integrate with external systems. Here, I use this field to put in the unique id that exchange associates with each calendar entry (You would be amazed that there are enough unique id’s in the world for all the meetings). I can use this id later to know if an appointment is already created in LiquidPlanner or not.
Note: I had issues with putting in the unique id from Exchange in the External Reference field of LiquidPlanner. I could not get back entries if the reference contained a plus (+) sign, so I replace it with an underscore just to avoid that problem.
The LiquidPlannerService hides all the HTTP/JSON stuff to interact with the LiquidPlanner API. This is the createAppoinment method:
public void createAppointment(LiquidPlannerUserVacationPackage userVacationPackage, String exchangeAppointmentId, String subject, DateTime startDateTime, DateTime endDateTime)
{
String requestPath = 'workspaces/' + LP_WORKSPACE_ID + '/events/';
liquidPlanner.request(Method.POST, ContentType.JSON) { req ->
uri.path = requestPath;
def startTime = LP_DATE_FORMATTER.print(startDateTime)
def endTime = LP_DATE_FORMATTER.print(endDateTime)
logger.debug "$startTime - $endTime : ${subject}"
body = [event: [
name : subject,
owner_id : userVacationPackage.ownerId,
parent_id : userVacationPackage.id,
external_reference: exchangeAppointmentId,
start_date : startTime,
finish_date : endTime]]
response.success = { resp, json ->
logger.debug "Succesfully created appointment in LP"
}
response.failure = { resp ->
throw new RuntimeException("Unable to create appointment in LP: ${resp.status} - ${requestPath}")
}
}
Thread.sleep(m_sleepBetweenLPRequests);
// Sleep a bit to avoid hitting the liquidplanner server too fast (See http://www.liquidplanner.com/api-guide/technical-reference/request-throttling.html)
}The liquidPlanner is a Groovy HTTP Builder object. The LiquidPlannerUserVacationPackage represents a vacation in the bounded context of LiquidPlanner (I had just finished reading Implementing Domain-Driven Design when I implemented this, and incorporating some of the ideas really made my code a lot better):
import net.sf.json.JSONObject
class LiquidPlannerUserVacationPackage {
String id
String ownerId
String name
String emailAddress
public static LiquidPlannerUserVacationPackage fromJSON(JSONObject jsonObject) {
return new LiquidPlannerUserVacationPackage(
id: jsonObject.id,
ownerId: jsonObject.owner_id,
name: jsonObject.name,
emailAddress: jsonObject.external_reference)
}
}Step 3. Prepare LiquidPlanner so the script has enough information
To make all of this work, there is some preparation in LiquidPlanner needed.
First, you need to create a top-level package that will have all the vacations. Below that, I create a package per user that is in LiquidPlanner. There are a lot more users in Exchange than there are people using LiquidPlanner, so it makes no sense to try to autogenerate this from Exchange in our case.
Each 'user' package will have the email address of the person set as 'External Reference'. The script will use that to connect to Exchange to get the appointments of each user.
Note that all the users will need to have shared their calendar with the user you use to connect to Exchange initially, otherwise, it cannot work!
This is the code that retrieves all the LiquidPlanner packages (1 per user):
public Set<LiquidPlannerUserVacationPackage> getVacationPackages() {
def JSONObject outlookCalendarFolder = liquidPlanner.get(path: 'workspaces/' + LP_WORKSPACE_ID + '/packages', query: ['filter[]': ['name="Vacations"']]);
logger.debug "Outlook calendars package found under id " + outlookCalendarFolder.id
def JSONArray userFolders = liquidPlanner.get(path: 'workspaces/' + LP_WORKSPACE_ID + "/treeitems/" + outlookCalendarFolder.id, query: ['depth': '1']).children
return userFolders.findAll {
!(it.external_reference instanceof JSONNull) && isNotBlank(it.external_reference)
}.collect {
LiquidPlannerUserVacationPackage.fromJSON(it)
}
}What this does is first searching for a package called 'Vacations'. Then it takes all the children at the first depth level, which are our user packages. The returned JSON is then converted into LiquidPlannerUserVacationPackage so that the rest of the script does not need to know that we are using a JSON REST API to talk to LiquidPlanner.
To check if an appointment already exists in LiquidPlanner, we need this piece of code:
public boolean doesAppointmentExist(String exchangeAppointmentId) {
def queryFilter = 'external_reference="' + exchangeAppointmentId + '"'
JSONArray appointmentInLP = liquidPlanner.get(path: 'workspaces/' + LP_WORKSPACE_ID + '/events', query: ['filter[]': [queryFilter]]);
def result = appointmentInLP.size() > 0
if (!result) {
logger.debug "Could not find appointment with id ${exchangeAppointmentId} in LP"
}
return result;
}Notice how we can directly get the event in LiquidPlanner with the matching external reference. I use this to avoid creating new entries in LiquidPlanner for appointments that already exist.
This is it. This post has showed you the most important bits and pieces to synchronize LiquidPlanner with calendars in Microsoft Exchange.
If you want to be notified in the future about new articles, as well as other interesting things I'm working on, join my mailing list!
I send emails quite infrequently, and will never share your email address with anyone else.