flowchart LR p(param) --> g(graph) g(graph) --> c(calendar) c(calendar) --> d(dumper) p --> m(main) d --> m(main)
TimeSheet: A Time Keeper in C#
Constructing a timesheet from the data in the Microsoft Outlook calendar.
Timesheet
C#
Microsoft Graph
Microsoft Outlook
Microsoft Entra ID
The Microsoft Outlook calendar has a very simple interface for the creation of events. With little more than a click and a re-size it is possible to drag out a rectangle that corresponds to a period of time in the working day and populate it with a description of the activity that either did occupy, or is planned to occupy, that slice of time.
Many years ago I took advantage of this to keep a record of my activity against a number of projects. Back then the programmer’s interface to Microsoft Outlook was very much tied to the Windows operating system through its COM binary interface and later through its .NET COM-interop-assemblies. My original timesheet program was written in F# and, because it used such an interface, was limited to running on Windows. In this document I describe a re-write of the software in C# for the .NET Core platform, enabling it to run on macOS as well as Windows.
Note
Summary of Operation
The timesheet software (hereafter referred to as TimeSheet) allows me to report on my activity over time and have the individual activities summarised by project and sub-project in a format that makes it easy for me to see how much work I have done on individual projects, sub-projects within projects, or tasks within sub-projects.
The interpretation of project, sub-project and task is very loose. The important part of the interpretation is that they are considered to be hierarchical such that a task is wholly contained within a sub-project and a sub-project is wholly contained within a project. Sometimes a project might be used to represent a customer and the sub-project might represent a distinct piece of work for that customer.
All is made clear by an example. Consider the following entries made in Outlook between 10:00 and 13:00 on the 1st and 2nd of November.
TimeSheet examines Outlook for calendar entries that occur between a range of dates. These entries are known as events in Outlook.
Only entries whose text matches a given format are considered:
<project> – <sub-project> – <task details>
Project, sub-project and task detail are separated by a single dash character which may be surrounded by whitespace characters. Project and sub-project may only contain letters, digits, an underscore character or a forward slash character. Task details may contain any characters.
Entries with any other format are ignored. So, from the entries above, the only Outlook task that is ignored is the “Early Lunch” task as it does not match the desired format. All of the other tasks in our range of days are considered to be tasks of interest and are collected.
The duration of the task is taken from the duration of the corresponding Outlook event.
TimeSheet is a command line application that runs on macOS and Windows. It writes its output to standard output. Given the data shown in Figure 1 above, Figure 2 shows the output produced by running TimeSheet on that data.
The first line shows the range of dates for which events in Outlook have been read. In this example, TimeSheet has been asked to look at all dates between 1st November and 30th November (inclusive) but, to keep things simple, I have only made entries in Outlook for the days of 1st and 2nd of November.
After that comes a block of output for each distinct project found within those events.
Each block begins with a line containing the name of the project and the total time spent working on that project.
Then, for each week included in the range of days for which TimeSheet has been run, we see a breakdown of the time spent each day on the project (in hours and minutes). The weekly breakdown is arranged to begin on a Monday. In this example our data for the 1st and 2nd of November would appear in cells 3 and 4 of the row for the week beginning 30th October. We can see that we worked for 2 hours on Project X on the first of November and that this was the only week (so far) in the month that we have worked on ProjectX.
Next, for the project, we see a block of lines showing the total time that we have spent on each sub-project within the project. We see that, for ProjectX, we have spent 1 hour 30 minutes on documentation and 30 minutes on testing. This sub-project summary is only for sub-projects within the main project. Were there to also be a Doc sub-project within, say, the CompanyY project then its Doc sub-project would be totalled separately. So it is OK to have sub-projects with the same title within different projects and we will never double count them.
After the sub-projects block comes the task block. The task block is more than just a list of tasks; it is a summary of the distinct tasks within the sub-projects. So if multiple entries exist with the same project, sub-project and task detail they appear as just one line here and their total duration is summarised. Working on a task, taking a break, and then working on the same task at some point later on will result in just a single line in this task block with the duration being the total time that has been spent on that task in the (project and sub-project and) date range. Each line in the task block begins with the sub-project to which the task belongs. This allows us to distinguish between tasks that have the same task detail within different sub-projects of a project.
In summary, we use a very simple input format within an event in Outlook and we generate a textual output that enables us to see at a glance a summary of what we have been doing at the project, sub-project, task and day level.
Complications
There are two complications that didn’t exist in the original COM-based F# timesheet program:
- Outlook is now part of Microsoft 365 and so the Outlook event data lives within Microsoft’s servers rather than on the local machine.
- To be able to obtain access to the data on Microsoft’s servers some kind of authentication will need to be done.
Outlook event data can be read using the Microsoft Graph API. But, because we want to run TimeSheet on our local machine, we will need to be able to authenticate first and so I will look at the authentication mechanism first in this document. Authentication is the trickiest part of this interaction with Microsoft’s servers but its complexity is handled by components of .NET which, thankfully, keeps the number of lines of user code required to be relatively low.
Once authentication has been configured we can obtain a client for calling the Microsoft Graph service. The graph service client allows us to query calendar events between a range of dates and once we can do that we then have our event data available to play with.
Authentication with Microsoft Entra ID
Note
Microsoft Entra ID was formerly known as Microsoft Azure Active Directory.
Configuration
The following configuration steps are required to arrange that our TimeSheet application can gain access to Outlook events through the Microsoft Graph service.
- We will create an App Registration within Microsoft Entra ID. This will act as the representative of our application.
- We will agree a shared secret that will be known to both the app registration and our application. When we run the application we can use this to prove to the app registration that we are who we claim to be.
- We will grant the app registration permission to access the Microsoft Graph service.
Creating the App Registration
Microsoft’s documentation for the creation of an App Registration can be found here: Register an application with the Microsoft identity platform
We’ll give our app registration the name of “TimeSheet” and when asked “Who can use this application” we will select the option “Accounts in this organisational directory only”. We will not be allowing access to our Outlook calendar to anyone other than us.
Click the “Register” button to create the app registration. The overview page of the app registration will now show a value for the Application ID (also known as the Client ID). This value will be used by our timesheet application to indicate to Microsoft Entra the app registration to which it is associated.
Note
In the Microsoft terminology our timesheet application is known as a “Confidential client application” in the sense that it is one that we can trust to safely keep an application secret - such as a shared password. This is in contrast to, say, an application that ran in a web browser which would be considered to be a “Public client application”
Granting Permissions
We now need to give the app registration permission to use the Microsoft Graph service. Still within the Microsoft Entra app registration we click on the API Permissions blade. You should see that, by default, the app registration already has the Microsoft.Graph User.Read permission. This will allow the app registration to read the current users profile. We will add a couple of additional permissions.
Click on “Add a permission” and choose Microsoft APIs -> Microsoft Graph -> Application Permissions.
Click to put a tick against: Calendars.Read and User.Read.All. These allow us to “Read calendars in all mailboxes” and “Read all users’ full profiles”.
Click the “Add permissions” button.
The Calendars.Read and the User.Read.All permissions require admin consent before they can be used. As long as we have the required administrative permission we can obtain these permissions by clicking on the “Grant admin consent for …” button. If this button is disabled it is because the logged in user does not have permission to do this.
Click the “Grant admin consent for …” button (where the elipsis is replaced by the name of your Microsoft Entra tenant).
We should now have sufficient permission for our application, once suitably configured, to read Outlook calendar events.
The TimeSheet Application
The TimeSheet application is written in C# for the Microsoft .NET Core platform. There are five modules with a very simple relationship between them. The main module wires up the other four modules and outputs the result to standard output.
The param module gathers the parameters that will be used to query the calendar. These include the range of dates of interest and the authentication details. These are passed to the graph module which uses them to obtain a client that can access the Microsoft Graph service. The calendar module uses graph service client to query the service and return a set of calendar entries which it then groups and summarises by project. The dumper module is then responsible for formatting this summary and returning it to the main module.
Note
I talk of the calendar module in this document to refer to the task module in the code. The reason for this is to avoid confusing the task module with the .NET Task class. I also use the term module to refer to the C# namespace with the same name. This application was originally written in F# and will be later re-written in Go and I do not want to make changes to it purely to satisfy naming issues with the C# language and the .NET class library names.
Code for the main module looks like this:
class Program
{
static async Task Main(string[] args)
{
// Wire up the components.
var param = parameter.Reader.New(args);
var (graph, error) = msgraph.Client.New(param.Auth);
if(graph == null) { // error will be non-null
Console.Error.WriteLine(error);
System.Environment.Exit(1);
}
var calendar = task.Reader.New(graph);
var dumper = dump.Dump.New(calendar);
// Read the parameters
var userPrincipalName = param.UserPrincipalName;
var dateFrom = param.DateFrom;
var dateTo = param.DateTo;
if (userPrincipalName == null){
Console.Error.WriteLine("A value for UserPrincipalName is required");
System.Environment.Exit(1);
}
if (dateFrom == null || dateTo == null){
Console.Error.WriteLine("A value for both DateFrom and DateTo is required");
System.Environment.Exit(1);
}
// Output the results
var output = await dumper.summary(userPrincipalName, dateFrom.Value, dateTo.Value);
foreach(var s in output){
Console.WriteLine(s);
}
}
}Wiring Up
The param module contains the code to obtain not just the range of dates for which we are interested in calendar events, but also the name of the user whose calendar we will read and the details required to persuade the Microsoft Graph service to give us the data.
The graph module only needs to know about the authentication parameters. The calendar module is given a graph module to use and the dumper module is given a calendar module to use.
The dumper module satisfies the IDumper interface.
public interface IDumper {
public Task<List<string>> summary(string userPrincipalName, DateTime fromDate, DateTime toDate, bool satweek=false);
}Interface IDumper is satisfied by just a simple summary function which, given a user name and a range of dates, returns the formatted output ready for printing to standard output.
The structure of this application is fairly simple to grasp but there are two bits that were new to me, and which were not required when the F# version of this application was written, and so I’ll describe them in this notebook: authenticating to the graph service and querying the graph service.
Authenticating to the Graph Service
Before we can make use of the Microsoft Graph service we need to prove to it that we are who we claim to be. And to do that we need to show it the shared secret that we agreed when we configured the Microsoft Entra app registration.
Microsoft have made this process much easier than it used to be by providing the Microsoft.Identity.Web.TokenAcquirerFactory class. The TokenAcquirerFactory class will manage the acquisition of Access (and other) tokens required for communication with Microsoft Graph.
There are four pieces of information that we need to provide:
- The Azure Instance: In our case “https://login.microsoftonline.com”. This will be different if you are allowing access to a wider audience. But if restricting it to just members of our Microsoft Entra tenant then we need to use this value.
- The Azure Tenant ID: This can be found from the app registration overview page as the Directory (tenant) ID.
- The App Registration Client ID: Found on the app registration overview page.
- The Client Secret: The shared secret that we created on the app registration “Clients & Certificates” blade and which you saved to a safe place.
This information can be used with the TokenAcquirerFactory class to create a Microsoft Graph client with which we can query Microsoft Graph. The code to do that looks like this:
public static (IClient? client, string? error) New(azure.Auth auth)
{
TokenAcquirerFactory tokenAcquirerFactory = TokenAcquirerFactory.GetDefaultInstance();
IServiceCollection services = tokenAcquirerFactory.Services;
services
.AddMicrosoftGraph();
// Ensure that the tokenAcquirerFactory has the required authentication configuration.
var C = tokenAcquirerFactory.Configuration;
C["AzureAd:Instance"] = auth.Instance;
C["AzureAd:TenantId"] = auth.TenantId;
C["AzureAd:ClientId"] = auth.ClientId;
C["AzureAd:ClientCredentials:0:SourceType"] = "ClientSecret";
C["AzureAd:ClientCredentials:0:ClientSecret"] = auth.ClientSecret;
var serviceProvider = tokenAcquirerFactory.Build();
if(serviceProvider == null)
return (null, "Could not create service provider");
var graphclient = serviceProvider.GetService<GraphServiceClient>();
if(graphclient == null)
return(null, "Could not create graph service client");
return (new Client(graphclient), null);
}This New function is passed an azure.Auth structure which contains the required authentication IDs and the shared client secret. It uses these to construct a Microsoft Graph client and uses the authentication information passed as its only argument to satisfy the authentication requirements.
The strange looking names of the entries in the TokenAcquirerFactory configuration are because normally it looks in an “Azure” section of the “appsettings.json” file to find this information. We do things slightly differently here to reduce the chances of accidently saving a configuration file to, for example, a Git repository. Instead, we keep our authentication parameters in an “env.private” file which we ensure is ignored by Git.
Note
On platforms other than Microsoft .NET we might use, say, a “.env” file which the operating system will hide from common view. But the TokenAcquirerFactory class will refuse to open a file with that name.
Our param module arranges to read the “env.private” file when it is constructed immediately after reading the “appsettings.json” file.
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
.AddJsonFile("env.private", optional:false, reloadOnChange: false)
.AddEnvironmentVariables();
IConfigurationRoot configuration = builder.Build();We then use an “env.private” file that looks something like this (and we pass these value through in the azure.Auth structure when we create our graph module):
{
// These are made up values.
"Instance": "https://login.microsoftonline.com/",
"TenantId": "f3xxxxxx-9999-xxxx-xxxx-1xxxx8x3xxxx",
"ClientId": "xbxxxxf1-9999-4xxe-xxxx-dxxxx5x4x9x5",
"ClientSecret": "kljkliexxxxxxxllxxxxxlxxxxxxCY7sG7nfwcWP"
}There are other ways of authenticating with the Microsoft Graph service but this shared secret method works well enough in this case.
Querying the Graph Service
The graph module implements a single interface on its Graph service client:
public interface IClient
{
Task<List<task.Task>> TimesheetItems(string userPrincipalName, DateTime dateFrom, DateTime dateTo);
}The single function TimesheetItems takes a username and a date range and returns a list of task.Task objects.
Note
Unfortunately in this C# .NET version of the timesheet application there is a naming conflict between our task class and the .NET System.Threading.Tasks.Task class. To resolve this conflict we always refer to our task class as task.Task.
The TimesheetItems function looks like this:
public async Task<List<task.Task>> TimesheetItems(string userPrincipalName, DateTime dateFrom, DateTime dateTo){
var tasks = new List<task.Task>();
var users = await graphclient.Users.GetAsync(
r => r.Options.WithAppOnly()
);
if(users == null || users.Value == null)
return tasks;
User? targetUser = null;
foreach (var user in users.Value) {
if (user.UserPrincipalName == userPrincipalName)
targetUser = user;
}
if (targetUser != null) {
var events = await graphclient
.Users[$"{targetUser.Id}"]
.Calendar.Events.GetAsync(r => {
r.QueryParameters.Select = new []{"subject", "start", "end"};
// Microsoft graph stores datetimes in UTC. So convert our range to UTC.
var start = dateFrom.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ss.0000000");
var end = dateTo.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ss.0000000");
r.QueryParameters.Filter = $"start/DateTime ge '{start}' and start/DateTime le '{end}' and IsAllDay eq false";
r.QueryParameters.Top = 999;
r.Options.WithAppOnly();
});
if (events == null || events.Value == null)
return tasks;
var eventList = events.Value.ToList();
foreach (var ev in eventList)
{
if(ev == null || ev.Subject == null)
continue;
// proj (- group) - description
var pat = @"^\s*([\w/]+)(?:\s*-\s*([\w/]+))?\s*-\s*(.*)";
var m = Regex.Match(ev.Subject, pat) ;
if (m.Success){
string proj = m.Groups[1].Value;
string group = m.Groups[2].Value;
string desc = m.Groups[3].Value;
// Convert back to local time.
DateTime start = ev.Start.ToDateTime().ToLocalTime();
DateTime end = ev.End.ToDateTime().ToLocalTime();
TimeSpan duration = end.Subtract(start);
tasks.Add (new task.Task(proj, group, desc, start, duration));
}
}
return tasks;
}
return tasks;
}The TimesheetItems function has access to the Microsoft Graph service client that we created earlier in the local variable graphclient. The basic structure of the function, ignoring error checking, is this:
- Use the graph client to obtain a list of users.
- Iterate through these users to identify our desired
targetUser - Use the graph client again to obtain a list of calendar events for the given
targetUser.- Consider only events whose start time falls within the start and end bounds (inclusive) of our date range of interest having first converted our local time date range to UTC and ignoring all-day events.
- Extract only the calendar event start time, end time and subject.
- Use the
Topparameter to request the maximum (999) number of events. The default is 10, so this is important.
- Having obtained a list of events further filter it to keep only those that match our project - sub-project - task details format. For this we use a regular expression.
- This is the list of tasks that we return from the function.
A keen eye might note that we also accept a project - task details format for the calendar event subject. When we find one of these we automatically assign it a sub-project name of “Ungrouped” in the task module.
Usage
There are only really three parameters that need to be passed to the TimeSheet application to obtain some output: a starting date, an ending date and a user name.
The param module handles the gathering of these values from: the “appsettings.json” file, the “env.private” file or the environment.
Typically the “env.private” file will contain the authentication details (as shown above) and the “appsettings.json” file will hold the UserPrincipalName and so will look like this:
{
"UserPrincipalName": "john.bates@oldgang.net"
}If run without any arguments the TimeSheet application will assume a date range that spans the start to the end (inclusive) of the current month. If given a single numeric argument this is interpreted as a number of months back from the current month and the date range that spans that month will be used. So, for example, running:
./TimeSheet 1will output a timesheet summary for the entire previous month.
The “appsettings.json” file may also contain values for DateFrom and DateTo to overwrite the default values. So, for example, an “appsetting.json” file containing the following would only report on events in my calendar that happened on the first day of November 2023.
{
"UserPrincipalName": "john.bates@oldgang.net",
"DateFrom": "1 Nov 2023",
"DateTo": "2023-11-01"
}As demonstrated in the above example, any suitably unambiguous DateTime format that can be parsed by the .NET DateTime parser can be used to represent the date range.
These three parameters can also be provided from the environment as environment variables, and on macOS may be prefixed on the command line. For example:
DateFrom="1 Nov 2023" DateTo="4 Nov 2023" ./TimeSheetIn spite of all that flexibility, because a time sheet can only be constructed after the event and is most usefully constructed as soon after the event as possible, the most common invocation is:
./TimeSheet 1to summarise last month’s timesheet.