Making a 12 months at a look Calendar (in Excel) from aggregated Shared Calendars in Trade On-line inside a Microsoft Groups Tab app utilizing the Microsoft Graph

Making a 12 months at a look Calendar (in Excel) from aggregated Shared Calendars in Trade On-line inside a Microsoft Groups Tab app utilizing the Microsoft Graph Calendaring codecs in Messaging Purchasers are inclined to all comply with a lot the identical method whether or not its Outlook, Outlook on the Net or Cellular, Gmail or Microsoft Groups.  Like e-mail information, calendar information may be random and complicated in its quantity and kind (recurring appointments and many others) so a easy 12 months at a look calendar for somebody designing a mass market shopper is difficult to do nicely for all the info sorts and volumes that you possibly can encounter, therefor its not one thing you see in mail shoppers by default (lets face it who desires to assist that).

Within the absence of 12 months at a look calendars  I used to be stunned to see folks utilizing Excel to create yearly aggregated calendars in Microsoft Groups for occasions (for information that already existed in shared Calendars). However extra surprisingly is that it really form of labored nicely when there wasn’t a whole lot of information that wanted to be proven. The one factor that sprang to my thoughts was if you happen to might automate this it  could be actually good for individuals who use the Birthday calendar characteristic in Outlook, easy Firm occasions calendars and likewise public holidays calendars particularly while you need to mixture a number of nations public holidays in a easy spreadsheet to assist folks like me who work throughout a number of areas after which share that inside a staff.

So I assumed I might got down to construct a easy Microsoft Groups Tab software that might create an aggregated Spreadsheet of occasions from any calendar (or calendars) in a Workplace365 Mailbox that was shared to a Explicit Microsoft Groups (Group) utilizing the Graph API to get the Calendar Knowledge from the Mailboxes and likewise utilizing the Graph API to construct the Excel workbook utilizing the workbook performance that the graph has. The result’s then saved in a OneDrive File and offered again to the consumer in a iFrame as and embedded Excel On-line spreadsheet. And the top end result appears to be like one thing like this (that is the results of having a Shared Mailbox with the Vacation calendars added/imported for Australia, US and the UK and that Mailbox being Shared to the Group/Groups)

The way it works

Like the opposite Groups Tab apps I’ve written it takes benefit of utilizing the Groups tab silent Auth methodology documented right here . As soon as the code has acquired an Entry Token to entry the Graph it may get to work.

Configuration 

For this software to work I wanted to have the ability to retailer the configuration of the calendars I needed to mixture . Because the app is written in JS the best type of config file was a straight JSON file like the next

{
"Calendars": [
{
"CalendarEmailAddress": "mb1@datarumble.com",
"CalendarName": "Australia holidays",
"CalendarDisplayName": "Australia"
},
{
"CalendarEmailAddress": "[email protected]",
"CalendarName": "United States holidays",
"CalendarDisplayName": "United States"
}
]
}

After which I simply required a means of storing and retrieving the file (a todo could be to create a pleasant kind to permit folks to create and edit the config but when I had time …). The Groups shopper Sdk (and tab apps) have no provision for storing customized configuration, properties or just about something configuration associated so I simply went for placing the file within the Channel doc library as a place to begin. So subsequent I simply wanted some Graph code to seize the contents of that file. In JS the best means i discovered to do that was like this

From the Groups Context interface you will get the GroupId and ChannelName the place you tab is executing so you’ll be able to the assemble the next URL that can be utilized within the Get towards the MS Graph.

v1.0/teams/” + GroupId + “/drive/root:/” + channelName + “/ExcelCalendarConfig.json

The Graph documentation factors to utilizing the /content material  endpoint to download the contents of a file, I’ve used this earlier than in .NET (and node.js) and it really works okay, it returns a 302 response with a Location header that may be adopted to the SharePoint web site. In shopper aspect JS its lots messier so I discovered it simpler to do that

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 does not want authentication. So its simple to simply do a Get after which use fetch on this url to return the JSON again to the code and I haven’t got to wade via a bunch of URL comply with and cors points with ajax and fetch

Template

One of many issues that the Graph API cannot do is create a brand new Excel file from scratch so you must have an current file you need to create a session with or some folks suggest a variety of totally different libraries to create the file. A straightforward resolution for this one for me was to create a clean Excel file with no metadata and embody that in with the webfiles so I might simply copy it to OneDrive as a template file (overwriting any current older file that will have been there) after which use that.

Storing the End result File 

One different downside for this mission was the place to retailer the top end result file, at first I simply used the SharePoint library related to the Groups Channel however there the place issues across the file changing into locked simply if two folks ran it concurrently. I additionally needed to have the ability to run this with the least quantity of permission as doable so the customers App Folder (for this Tab app) appeared like the very best spot as a place to begin which is what the next code handles.

let AppDrive = await GenericGraphGet(Token,"https://graph.microsoft.com/v1.0/me/drive/particular/approot");
let FileData = await ReadTemplate();
var fileName = "Calendars.xlsx";
var UploadURL = "https://graph.microsoft.com/v1.0/me/drive/particular/approot:/" + fileName + ":/content material";
let NewFile = await CreateOneDriveFile(Token,UploadURL,FileData);


Getting the Calendars

Getting the Calendars was in all probability the best activity, from the config file the CalendarName property is used to search out the Folder from the Mailbox you need to entry the info from. The question of the Calendar is then executed for a the present years information utilizing a CalendarView (which is able to increase any recurring calendar appointments). To mixture the calendar information that was retrieved into orderable lists I used a number of Map objects in JS,loop iterations and arrays so I get an ordered listing of occasions which are aggregated by first the Month after which day throughout the Month.

Constructing the Spreadsheet 

To construct the spreadsheet within the output format that I needed (which mirrored what I noticed customers doing manually) I needed to first insert the info, then merge the month rows so I solely had 1 row monthly. Then format the merge so the textual content was aligned accurately and had the proper formatting. After which lastly was to Autofit the columns so the spreadsheet displayed accurately to customers. So this required a whole lot of separate request to the Graph API to do which at first ran a bit slowly. Then got here Batching

Batching

Batching actually is a Godsend in terms of efficiency with a activity like this, for instance my unique code had round 40-50 particular person request to get the info and formatting executed and with batching it was decreased to round 6 (and I used to be being a bit conservative and will have decreased this). The massive tip for utilizing batching with the WorkBook endpoint is that you should be sure you embody the workbook session id with ever request (simply not the batch request). In case you do not you’re going to get a whole lot of EditModeCannotAcquireLockTooManyRequests  which the documentation,the error (and the web basically) aren’t actually useful in mentioning why this occurred.

Displaying it again to the Groups tab

This turned out to be one of many hardest issues to resolve and is without doubt one of the excellent points with this in Groups anyway. I used an Iframe and generated and embeed hyperlink (which is what you get while you use Share-embed in Excel On-line). This does work okay within the browser so long as you have already got a login to your private OneDrive (token within the Cache) else you may be prompted to logon to SharePoint. Within the Desktop shopper this logon is an issue so as an alternative of opening throughout the Tab within the desktop shopper, if it detects the Desktop shopper it launchs a brand new browser tab (which you will or might not have to logon to SharePoint to view).  This was a bit disappointing however in all probability one thing I am going to have a repair for quickly (If anyone has any solutions I am all ears)

GitHub Repo for this App

I’ve a hosted model of this Tab App on my GitHub pages on https://gscales.github.io/TeamsExcelCalendar/  and there may be repo model contained in the Aggregation engine repo https://github.com/gscales/ExcelCalendarAggregate/tree/grasp/TeamsExcelCalendar with a Readme that particulars the set up course of

Constructing on the Aggregation engine

As a result of I form of get pleasure from taking issues and working with them I’ve some plans of utilizing the Calendar to Excel aggregation engine in just a few totally different codecs. The primary will likely be a Easy powershell script so you are able to do the identical factor however all from with an Automation context so in case your on this however do not desire a Groups tab app watch this area.


   

Leave a Reply