flowchart LR m(main) --> t(timesheet) t(timesheet) --> g(graph) t(timesheet) --> c(calendar) t(timesheet) --> d(dump)
TimeSheet: A Time Keeper in Go
Constructing a timesheet from the data in the Microsoft Outlook calendar.
Timesheet
Go
Microsoft Graph
Microsoft Outlook
Microsoft Entra ID
This notebook discusses a timesheet program written in the Go programming language. The timesheet program was originally written in F#, later re-written in C# and then more recently, re-written again in Go. Part of the motivation for re-writing in Go was to obtain a single executable that could be run to generate a monthly timesheet.
Note
Much of the material from the orginal C# notebook is repeated here, but the command line usage and any Go specific notes are peculiar to this notebook.
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.
To have a Microsoft Outlook calendar requires a Microsoft 365 subscription but there is no requirement to use the Microsoft Outlook app to manage the calendar if an alternative is available. As a macOS user, I find that by adding my Microsoft account details to my macOS “Internet Accounts” I am able to manage events in my Outlook calendar from my Mac and my iPhone. This turns out to be useful if I am away from my work environment but, to be honest, I have got used to the Outlook interface and I prefer that.
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 the Microsoft Graph SDK for Go and the Kiota Azure Identity authentication provider library for Go.
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 Go. There are four services with a very simple relationship between them. The main module reads the command line flags and the locally stored secrets and, through the timesheet service, wires up the other three services. It then uses them to generate the textual result and writes it to standard output.
The parameters and secrets read by the main module include the range of dates of interest, the Outlook username, and the authentication details. The authentication details are passed to the graph service which uses them to obtain a client that can access the Microsoft Graph service. The timesheet service calls the graph service with username and range of dates to obtain a list of the relevant events that happened for the given user between the two dates. The timesheet service passes this list of tasks to the calendar service which summarises and aggregates event times, returning them as a list of Projects. These are then passed to the dump service to convert them into our desired output format for output on standard output.
There are four steps required to get to the point where we can retrieve Outlook event data:
- Register this app with the Microsoft Identity Platform: We have already done this (see above).
- Create an AuthenticationProvider object.
- Get a Graph Service Client and Adapter object.
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 relatively easy by providing the Microsoft Graph SDK for Go and the Kiota Azure Identity authentication provider library for Go.
We can install the Graph SDK and the Kiota Azure Identity authentication provider library for Go with:
go get github.com/microsoftgraph/msgraph-sdk-go
go get github.com/microsoft/kiota-authentication-azure-goCreate Credentials and Graph Service Client
There are three pieces of information that we will need to supply to the Authentication Provider:
- 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.
With these we can obtain credentials that we can provide to the graph service client constructor.
The code to do that looks like this:
import (
azidentity "github.com/Azure/azure-sdk-for-go/sdk/azidentity"
msgraphsdk "github.com/microsoftgraph/msgraph-sdk-go"
)
...
type Auth struct {
TenantId string
ClientId string
ClientSecret string
}
...
func NewGraphSvc(auth domain.Auth) domain.GraphSvc {
cred, err := azidentity.NewClientSecretCredential(
auth.TenantId,
auth.ClientId,
auth.ClientSecret,
nil,
)
if err != nil {
fmt.Printf("Error creating credentials: %v\n", err)
return graphSvc{auth: auth, client: nil}
}
scopes := []string{"https://graph.microsoft.com/.default"}
client, err := msgraphsdk.NewGraphServiceClientWithCredentials(cred, scopes)
if err != nil {
fmt.Printf("Error creating graph client: %v\n", err)
return graphSvc{auth: auth, client: nil}
}
return graphSvc{
auth: auth,
client: client,
}
}The Auth structure will have been populated with values in the main module. To do that TimeSheet expects to find an “.envrc” file in the current directory that looks something like this:
export UserName="john.bates@oldgang.net",
export TenantId="g3xxxxxx-9999-xxxx-xxxx-1xxxx1x2xxxx",
export ClientId="xpxxxxf1-9999-4xxe-xxxx-dxxxx3x4x9x5",
export ClientSecret="kljkliexxxxxxxllxxxxxlxxxxxxCP(sGbYFwcWP"There are other ways of authenticating with the Microsoft Graph service but this shared secret method works well enough in this case.
We now have a Graph Service client that can be used to query the Graph Service for Outlook events.
Querying the Graph Service
The graph service implements the GraphSvc interface:
type GraphSvc interface {
Read(userName string, fromDate time.Time, toDate time.Time) ([]Task, error)
}Calling Read with a username and a date range and returns a slice of Tasks. These are all of the Outlook events for the given user that both match the required pattern in their description (see above) and started between the given dates (inclusive).
We start by finding the list of all users and then look for the one whose UserPrincipalName is the same as our userName. We call this our targetUser and we will need the targetUser ID later to filter the calendar events.
func (svc graphSvc) Read(userName string, fromDate time.Time, toDate time.Time) ([]domain.Task, error) {
if svc.client == nil {
return []domain.Task{}, nil
}
allusers, err := svc.client.Users().Get(context.Background(), nil)
if err != nil {
return []domain.Task{}, err
}
if allusers == nil || allusers.GetValue() == nil {
return []domain.Task{}, nil
}
var targetUser models.Userable
for _, user := range allusers.GetValue() {
if *user.GetUserPrincipalName() == userName {
targetUser = user
}
}
if targetUser == nil {
return []domain.Task{}, nil
}
...Microsoft Outlook stores its dates as UTC dates, so we need to convert our ‘from’ and ‘to’ dates from localtime to UTC before we can use them to filter events in Outlook. We are only interested in fetching the event subject and its start and end dates.
By default, a request will only return a small number of results. To request the maximum number of results we need to provide a value for the ‘Top’ query parameter and in the Go Graph SDK this has to be provided as a pointer to an integer.
// Got the user. Now get the tasks for that user.
// Microsoft graph stores datetimes in UTC. So convert our range to UTC before filtering.
start := fromDate.UTC().Format("2006-01-02T15:04:05.0000000")
end := toDate.UTC().Format("2006-01-02T15:04:05.0000000")
filter := fmt.Sprintf("start/DateTime ge '%s' and start/DateTime le '%s' and IsAllDay eq false", start, end)
query := users.ItemCalendarEventsRequestBuilderGetQueryParameters{
Select: []string{"subject", "start", "end"},
Filter: &filter,
Top: &[]int32{999}[0],
}
options := users.ItemCalendarEventsRequestBuilderGetRequestConfiguration{
QueryParameters: &query,
}
events, err := svc.client.Users().ByUserId(*targetUser.GetId()).Calendar().Events().Get(context.Background(), &options)
if err != nil {
return []domain.Task{}, err
}
if events == nil || events.GetValue() == nil {
return []domain.Task{}, nil
}
...Once we have the events, we reject those that do not match our desired pattern, convert the event dates back into local time, and calculate the duration of each before returning them as a slice of Tasks.
tasks := []domain.Task{}
for _, ev := range events.GetValue() {
if ev == nil || ev.GetSubject() == nil {
continue
}
// proj (- group) - description
var pat = `^\s*([\w/]+)(?:\s*-\s*([\w/]+))?\s*-\s*(.*)`
matches := regexp.MustCompile(pat).FindStringSubmatch(*ev.GetSubject())
if matches == nil || len(matches) != 4 { // Entire expression plus each subexpression.
continue
}
proj := matches[1]
group := matches[2]
desc := matches[3]
_start := ev.GetStart()
_end := ev.GetEnd()
start, err := time.Parse("2006-01-02T15:04:05.0000000", *_start.GetDateTime())
if err != nil {
return []domain.Task{}, fmt.Errorf("failed to parse start time: %v", err)
}
end, err := time.Parse("2006-01-02T15:04:05.0000000", *_end.GetDateTime())
if err != nil {
return []domain.Task{}, fmt.Errorf("failed to parse end time: %v", err)
}
// Convert back from UTC to local time.
start = start.Local()
end = end.Local()
duration := end.Sub(start)
tasks = append(tasks, domain.Task{
Project: proj,
Group: group,
Desc: desc,
Start: start,
Duration: duration,
})
}
return tasks, nil
}It is all downhill from here, the main module calls the CalendarSvc interface on the calendar service to summarise the Tasks into a slice of Projects.
type CalendarSvc interface {
Aggregate(tasks []Task) []*Project
}Then it calls the DumpSvc interface on the dump service to turn the slice of Projects into formatted text for output.
type DumpSvc interface {
Projects(projects []*Project) []string
}Usage
There are only three things that the TimeSheet application needs to know to produce some output: a starting date, an ending date and a user name.
The username, which is often the same for each invocation, is most usefully stored in the .envrc file. But an alternative username may be provided with the ‘-user’ flag.
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.
The ‘-n’ flag can be used to request a timesheet for ‘n’ months back from the current month and the date range that spans that month will be used. So, for example, running:
./TimeSheet -n 1will output a timesheet summary for the previous month.
The ‘-from’ and ‘-to’ flags may be used to provide alternative starting and ending dates. The dates must be provided in the YYYY-MM-DD format and may be used in conjunction with the ‘-n’ flag to modify the dates that the ’-n- flag would imply.
So, for example, the following would only report on events in my calendar that happened on the first day of November 2023.
./TimeSheet -from '2023-11-01' -to '2023-11-01' -user 'john.bates@oldgang.net'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 -n 1to summarise last month’s timesheet.