Using Microsoft Flow to connect Office 365 to Google's GSuite

Microsoft's low-code serverless tools can simplify application integration, and bring Windows and Android closer together.

How to secure an Excel worksheet with a password Brandon Vigliarolo walks you through the steps of securing an Excel worksheet with a password in Microsoft Office 365.

If you want to get the most out of a modern Android device, you'll need to use Google Assistant. Like Siri or Cortana, it'll need access to your calendar and address book, if it's to give you context-based alerts and predictions. If you want to get warnings for when you need to leave for that vital meeting, then that appointment needs to be kept in Google Calendar.

But Google's mail and calendar tooling isn't everyone's default. If you're using Office 365 on an Android device, you'll probably have downloaded Outlook, with your calendar stored on Microsoft's servers. While Outlook keeps a local cache of your mail and appointments, and syncs contacts with Android's contact tools, much of its data remains invisible to your Android hardware — unless, of course, you sync your Office 365 account with Google's tools.

The problem with syncing mail and calendar into Android's native apps, while still using Outlook, is that you're doubling up on the data stored on your phone and on the data your phone uses, as it's downloading each message and appointment twice.

There used to be tools that would sync Outlook data with Gmail, but changes in APIs and application features have meant that most no longer work. The shift from on-premises servers to the cloud, and our increased use of mobile devices, also gets in the way, as desktop tooling may not be running when an important meeting invitation gets accepted on a phone.

Introducing Microsoft Flow

One solution comes in the shape of Microsoft's low-code programming tool, Flow. Part of the Dynamics 365 business applications suite, Flow is an easy way of integrating applications adding basic business logic around a connection. You don't need to have a Flow-specific subscription to use it, as some of its features are available for free.

SEE: IT leader's guide to the future of artificial intelligence (Tech Pro Research)

Flow has a long list of available connectors, offering a mix of endpoints in both Microsoft and third-party services. Connectors are available for standard and premium accounts, with some — like Salesforce and ServiceNow — only available to premium subscribers. Luckily that doesn't affect anyone wanting to connect their Office 365 and GSuite services, as Office and Google endpoints are all part of the standard tier.

Currently you'll find endpoints for Gmail, Google Calendar, Google Contacts, Google Drive, Google Sheets, and Google Tasks. Similar endpoints exist for matching Office 365 services, so you can map one service into another, with much of the functionality you need handled by triggers in the Office 365 Outlook endpoint. This uses the Microsoft Graph to monitor your Office 365 tenant for changes, triggering a Flow when the conditions you're monitoring for occur.

Inside an Office 365 to GSuite Flow

Building a Flow to transfer data from your Office 365 calendar to Google Calendar is relatively straightforward. Microsoft provides a template that handles most of the work for you, but it's worth drilling down into its operation to understand how you can develop more complex custom workflows that link the two services. You'll need to give Flow permission to access both your Google Calendar and Office 365 accounts, and this sets up the appropriate authentication tokens to handle both services' APIs.

flowstructure.png

A look at the structure of the Flow linking Office 365 and Google Calendar.

Image: Simon Bisson/TechRepublic

Inside the Flow you'll see an initial trigger event. This monitors for changes in any of your calendars; it's possible to set up more than one version of the Flow to handle changes in other calendars, including shared calendars for teams or from colleagues. Once a trigger event has occurred, the Flow then checks for access to a Google Calendar. Again you can choose personal or team calendars, before setting the CalendarId for a new event. This extracts data from event parameters, adding them to a variable. Event IDs are also assigned, as these are used to manage the create, update, and delete operations you'll carry out on your Google Calendar.

SEE: How we learned to talk to computers, and how they learned to answer back (cover story PDF)

Once you've set a CalendarId, the Flow checks your Google calendar for existing events with the ID. If it exists, then it'll be deleted, allowing you to replace it with an updated version. It'll use separate steps for both discrete and recurring events, ensuring repeated events are also handled. To simplify things the Flow works with a band of dates — 90 days into the past and 180 days into the future. More dates would make for more complex queries, and would slow the Flow down significantly.

Purging events that are being updated makes sense, as Google's APIs don't offer the ability to process updates. Clearing the old version and writing a new one makes it possible to write events at new times, and means that you only need one write action in your Flow. Deletions can also be handled at the same time.

flowhome-page.png

The home page for a Flow, showing diagnostics and descriptions.

Image: Simon Bisson/TechRepublic

Writing an event into Google Calendar is a matter of constructing an appropriate template, and loading it with the data needed to describe an event. Much of the information needed is in the event object that you will extract from Office 365, and you can use this to populate standard calendar fields like Start time, End time, Location, and Subject. The event body can be more complex, and it's easier to insert it as text rather than the default HTML. Flow offers a function that converts HTML to text, so you can use this to convert the event body to text before adding it to Google Calendar.

Once set up, you can leave your Flow ready to go. Like all serverless applications, it's event driven. It'll launch and run automatically when you add a new event to your Office 365 calendar, either by creating a meeting or an appointment in Outlook, or by accepting an invitation. Although you can also set up a Flow that maps Google Calendar events to Office 365, it's best to only use this type of operation from one master calendar to a secondary one. Sending data backwards and forwards between services risks triggering update loops that will quickly exhaust your available calls.

Microsoft Flow is a powerful tool that's available to many Office 365 subscribers, or with a separate free subscription. Building on serverless and low-code concepts, it gives you the ability to automate many tasks, making your life easier by integrating many of your separate tools and applications. If you're not using it, take a look, and try a few of its sample templates — you'll be surprised at what it can do for you, and for your devices.

Also see