Skip to main content

Creating a year at a glance Calendar (in Excel) from aggregated Shared Calendars in Exchange Online within a Microsoft Teams Tab app using the Microsoft Graph

Calendaring formats in Messaging Clients tend to all follow much the same approach whether its Outlook, Outlook on the Web or Mobile, Gmail or Microsoft Teams.  Like email data, calendar data can be random and complex in its volume and type (recurring appointments etc) so a simple year at a glance calendar for someone designing a mass market client is hard to do well for all the data types and volumes that you could encounter, therefor its not something you see in mail clients by default (lets face it who wants to support that).

In the absence of year at a glance calendars  I was surprised to see people using Excel to create yearly aggregated calendars in Microsoft Teams for events (for data that already existed in shared Calendars). But more surprisingly is that it actually kind of worked well when there wasn't a lot of data that needed to be shown. The one thing that sprang to my mind was if you could automate this it  would be really good for people who use the Birthday calendar feature in Outlook, simple Company events calendars and also public holidays calendars especially when you want to aggregate multiple countries public holidays in a simple spreadsheet to help people like me who work across multiple regions and then share that within a team.

So I thought I'd set out to build a simple Microsoft Teams Tab application that could create an aggregated Spreadsheet of events from any calendar (or calendars) in a Office365 Mailbox that was shared to a Particular Microsoft Teams (Group) using the Graph API to get the Calendar Data from the Mailboxes and also using the Graph API to build the Excel workbook using the workbook functionality that the graph has. The result is then stored in a OneDrive File and provided back to the user in a iFrame as and embedded Excel Online spreadsheet. And the end result looks something like this (this is the result of having a Shared Mailbox with the Holiday calendars added/imported for Australia, US and the UK and that Mailbox being Shared to the Group/Teams)

How it works

Like the other Teams Tab apps I've written it takes advantage of using the Teams tab silent Auth method documented here . Once the code has acquired an Access Token to access the Graph it can get to work.


For this application to work I needed to be able to store the configuration of the calendars I wanted to aggregate . As the app is written in JS the easiest form of config file was a straight JSON file like the following
    "Calendars": [
            "CalendarEmailAddress": "",
            "CalendarName": "Australia holidays",
            "CalendarDisplayName": "Australia"
            "CalendarEmailAddress": "",
            "CalendarName": "United States holidays",
            "CalendarDisplayName": "United States"

And then I just required a way of storing and retrieving the file (a todo would be to create a nice form to allow people to create and edit the config but if I had time ...). The Teams client Sdk (and tab apps) don't have any provision for storing custom configuration, properties or pretty much anything configuration related so I just went for putting the file in the Channel document library as a starting point. So next I just needed some Graph code to grab the contents of that file. In JS the easiest way i found to do this was like this

From the Teams Context interface you can get the GroupId and ChannelName where you tab is executing so you can the construct the following URL that can be used in the Get against the MS Graph.

v1.0/groups/" + GroupId + "/drive/root:/" + channelName + "/ExcelCalendarConfig.json

The Graph documentation points to using the /content  endpoint to download the contents of a file, I have used this before in .NET (and node.js) and it works okay, it returns a 302 response with a Location header that can be followed to the SharePoint site. In client side JS its a lot messier so I found it easier to do this

CCDriveItem = await GenericGraphGet(Token,CalendarConfigURL);        
var CCFetch = await fetch(CCDriveItem["@microsoft.graph.downloadUrl"]);

So the @microsoft.graph.downloadUrl is a short-lived URL for the file that doesn't need authentication. So its easy to just do a Get and then use fetch on this url to return the JSON back to the code and I don't have to wade through a bunch of URL follow and cors issues with ajax and fetch


One of the things that the Graph API can't do is create a new Excel file from scratch so you have to have an existing file you want to create a session with or some people recommend a number of different libraries to create the file. An easy solution for this one for me was to create a blank Excel file with no metadata and include that in with the webfiles so I could just copy it to OneDrive as a template file (overwriting any existing older file that may have been there) and then use that.

Storing the Result File 

One other problem for this project was where to store the end result file, at first I just used the SharePoint library associated with the Teams Channel but there where problems around the file becoming locked easily if two people ran it simultaneously. I also wanted to be able to run this with the least amount of permission as possible so the users App Folder (for this Tab app) seemed like the best spot as a starting point which is what the following code handles.

let AppDrive = await GenericGraphGet(Token,"");
let FileData = await ReadTemplate();
var fileName = "Calendars.xlsx";
var UploadURL = "" + fileName + ":/content";
let NewFile = await CreateOneDriveFile(Token,UploadURL,FileData);    

Getting the Calendars

Getting the Calendars was probably the easiest task, from the config file the CalendarName property is used to find the Folder from the Mailbox you want to access the data from. The query of the Calendar is then done for a the current years data using a CalendarView (which will expand any recurring calendar appointments). To aggregate the calendar data that was retrieved into orderable lists I used multiple Map objects in JS,loop iterations and arrays so I get an ordered list of events that are aggregated by first the Month and then day within the Month.

Building the Spreadsheet 

To build the spreadsheet in the output format that I wanted (which mirrored what I saw users doing manually) I had to first insert the data, then merge the month rows so I only had 1 row per month. Then format the merge so the text was aligned correctly and had the correct formatting. And then lastly was to Autofit the columns so the spreadsheet displayed correctly to users. So this required a lot of separate request to the Graph API to do which at first ran a little slowly. Then came Batching


Batching really is a Godsend when it comes to performance with a task like this, for example my original code had around 40-50 individual request to get the data and formatting done and with batching it was reduced to around 6 (and I was being a little conservative and could have reduced this). The big tip for using batching with the WorkBook endpoint is that you need to make sure you include the workbook session id with ever request (just not the batch request). If you don't you will get a lot of EditModeCannotAcquireLockTooManyRequests  which the documentation,the error (and the internet in general) aren't really helpful in pointing out why this happened.

Displaying it back to the Teams tab

This turned out to be one of the hardest problems to solve and is one of the outstanding issues with this in Teams anyway. I used an Iframe and generated and embeed link (which is what you get when you use Share-embed in Excel Online). This does work okay in the browser as long as you already have a login to your personal OneDrive (token in the Cache) else you will be prompted to logon to SharePoint. In the Desktop client this logon is a problem so instead of opening within the Tab in the desktop client, if it detects the Desktop client it launchs a new browser tab (which you may or may not need to logon to SharePoint to view).  This was a little disappointing but probably something I'll have a fix for soon (If anybody has any suggestions I'm all ears)

GitHub Repo for this App

I have a hosted version of this Tab App on my GitHub pages on  and there is repo version inside the Aggregation engine repo with a Readme that details the installation process

Building on the Aggregation engine

Because I kind of enjoy taking things and running with them I have some plans of using the Calendar to Excel aggregation engine in a few different formats. The first will be a Simple powershell script so you can do the same thing but all from with an Automation context so if your interested in this but don't want a Teams tab app watch this space.


Popular posts from this blog

Testing and Sending email via SMTP using Opportunistic TLS and oAuth in Office365 with PowerShell

As well as EWS and Remote PowerShell (RPS) other mail protocols POP3, IMAP and SMTP have had OAuth authentication enabled in Exchange Online (Official announcement here ). A while ago I created  this script that used Opportunistic TLS to perform a Telnet style test against a SMTP server using SMTP AUTH. Now that oAuth authentication has been enabled in office365 I've updated this script to be able to use oAuth instead of SMTP Auth to test against Office365. I've also included a function to actually send a Message. Token Acquisition  To Send a Mail using oAuth you first need to get an Access token from Azure AD there are plenty of ways of doing this in PowerShell. You could use a library like MSAL or ADAL (just google your favoured method) or use a library less approach which I've included with this script . Whatever way you do this you need to make sure that your application registration

How to access and restore deleted Items (Recoverable Items) in the Exchange Online Mailbox dumpster with the Microsoft Graph API and PowerShell

As the information on how to do this would cover multiple posts, I've bound this into a series of mini post docs in my GitHub Repo to try and make this subject a little easier to understand and hopefully navigate for most people.   The Binder index is   The topics covered are How you can access the Recoverable Items Folders (and get the size of these folders)  How you can access and search for items in the Deletions and Purges Folders and also how you can Export an item to an Eml from that folder How you can Restore a Deleted Item back to the folder it was deleted from (using the Last Active Parent FolderId) and the sample script is located

Using the MSAL (Microsoft Authentication Library) in EWS with Office365

Last July Microsoft announced here they would be disabling basic authentication in EWS on October 13 2020 which is now a little over a year away. Given the amount of time that has passed since the announcement any line of business applications or third party applications that you use that had been using Basic authentication should have been modified or upgraded to support using oAuth. If this isn't the case the time to take action is now. When you need to migrate a .NET app or script you have using EWS and basic Authentication you have two Authentication libraries you can choose from ADAL - Azure AD Authentication Library (uses the v1 Azure AD Endpoint) MSAL - Microsoft Authentication Library (uses the v2 Microsoft Identity Platform Endpoint) the most common library you will come across in use is the ADAL libraries because its been around the longest, has good support across a number of languages and allows complex authentications scenarios with support for SAML etc. The
All sample scripts and source code is provided by for illustrative purposes only. All examples are untested in different environments and therefore, I cannot guarantee or imply reliability, serviceability, or function of these programs.

All code contained herein is provided to you "AS IS" without any warranties of any kind. The implied warranties of non-infringement, merchantability and fitness for a particular purpose are expressly disclaimed.