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 wellwhen 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.
Configuration
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
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
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
Template
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.
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
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 https://gscales.github.io/TeamsExcelCalendar/ and there is repo version inside the Aggregation engine repo https://github.com/gscales/ExcelCalendarAggregate/tree/master/TeamsExcelCalendar 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.
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
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.
Configuration
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": "mb1@datarumble.com", "CalendarName": "Australia holidays", "CalendarDisplayName": "Australia" }, { "CalendarEmailAddress": "mb1@datarumble.com", "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
Template
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,"https://graph.microsoft.com/v1.0/me/drive/special/approot"); let FileData = await ReadTemplate(); var fileName = "Calendars.xlsx"; var UploadURL = "https://graph.microsoft.com/v1.0/me/drive/special/approot:/" + 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
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 https://gscales.github.io/TeamsExcelCalendar/ and there is repo version inside the Aggregation engine repo https://github.com/gscales/ExcelCalendarAggregate/tree/master/TeamsExcelCalendar 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.