Calendar-to-Mite (Part 1)

Time tracking… one of people’s most hated tasks at a software company/agency (at least from what I’ve experienced in the last seven years). When you work on more than one task or project, you might want to know which project needs how much time. When you’re a freelancer, time tracking is usually necessary to tell your current employer how much work you have done and how much money the company has to pay you. I get it, economically it makes sense, yet, it’s 2020, and I have not seen one single solution where I thought: “Wow, this is amazing/magic/intuitive”.

I was usually the most annoyed during the last years when tracking meetings that occurred regularly. Tracking that I have a daily meeting of 15 minutes each day is not fun. The same goes for meetings that are less frequent but still have the same project, task and time slot, or basically every scheduled meeting with a description, clear task or project, etc.

The normal process for me looked like:

• Check my Google Calendar
• “Ah, I have a meeting with Client XYZ” → set Project to XYZ.
• “What is my role in this meeting / what did I do?” → set activity to e.g., “Senior Development”.
• “What was the meeting about?” → copy / summarize the meeting into the description field.
• “How long was the meeting?” → set time to the duration of the meeting.

Now when you have a meeting with 10 team members, 10 team members do this or something similar when they make their time bookings.

So my question is: WHY?

Don’t you already know the project, time, description, and probably your role from your Calendar entry?

My solution

So to me, doing this manually didn’t make any sense. We’re programmers for a reason, so I practiced my NodeJS skills a little and created a small automation project. I run the script once a week, and this is the current workflow:

• connect to my Google calendar to fetch all my calendar entries
• map the meeting titles to individual projects (I use certain #hashtags like “#coaching” for this)
• create the correct data structure for the Mite (← time tracking tool) API
• ask me if the calendar entry is correct (Y/n)
• create all entries in Mite

Implementation

Step 1: Google Auth & Calendar

First, I started by implementing the Google authentication to get the data from my calendar by following the official guide: https://developers.google.com/calendar/quickstart/nodejs.

https://github.com/moritzwachter/calendar-to-mite/commit/b9d3cc6f751646b42bf77278e89b431c3193ffb4

Step 2: Mite API

The second data source I need is my time trackings. For this reason, I generated an API Key for my Mite profile and found a neat looking library to connect to the API: https://github.com/marcel-devdude/mite-api. All I had to do is adding my accountName and apiKey to the configuration object.

const miteApi = require('mite-api');
const mite = miteApi({
    account: 'thisIsNotMyMiteAccount',
    apiKey: 'thisIsNotMyApiKey',
    applicationName: 'CalendarToMite'
});

Step 3: Improvement #1 – Parameterise the application code

To make the code reusable, I extracted both the Mite API-Key and the Google Calendar App-ID to the .env file. This file can be processed and loaded by using another library called dotenv:

require('dotenv').config();
const MITE_API_KEY = process.env.MITE_API_KEY;
const MITE_ACCOUNT = process.env.MITE_ACCOUNT;

const miteApi = require('mite-api');
const mite = miteApi({
    account: MITE_ACCOUNT,
    apiKey: MITE_API_KEY,
    applicationName: 'CalendarToMite'
});

// Testing if mite works
mite.getMyself((err, res) => console.log(res));

Et voilà, when we run the script now, we see not only the next 10 calendar events from our Google Calendar but also a small JSON object with information about our Mite account.

Now, it’s time to create the mapping between event title and project/service ids inside Mite.

Step 4: Mapping meetings to mite projects

Thinking of data structures for mapping, a few examples come to my head. I decided to use a .csv-file. The reason is that I want everyone in the company to be able to use the application with ease, and at the same time, it should not become a bloated program. So ideally something that you could load into Excel or Google Sheets instead of vim or Notepad.

For starters, the structure like this should be sufficient:

keyword,project,service
#coaching,1234,5678
Coder's Talk,8765,4321

# ...
INPUT_PATH=example-mappings.csv

You can easily find the mite project ids inside the markup of the time tracking tool. I’ve used the library fast-csv (https://www.npmjs.com/package/fast-csv) to read and parse the mapping file. The benefit of this library is, we can access the columns of each row by their name in the header. So by iterating through a readStream, we can access the columns keyword, project and service for each row.

This code example could be a way to get the mappings from the CSV into a mappings object:

const csv = require('fast-csv');
const INPUT_PATH = process.env.INPUT_PATH;
async function getMappings() {
    const stream = fs.createReadStream(INPUT_PATH).pipe(csv.parse({ headers: true }));
    return new Promise(function (resolve, reject) {
        const mappings = [];
        stream
            .on('data', row => {
                mappings.push({
                    keyword: row.keyword,
                    projectId: parseInt(row.project, 10),
                    serviceId: parseInt(row.service, 10)
                });
            })
            .on('error', reject)
            .on('end', () => {
                resolve(mappings);
            })
        ;
    });
}
getMappings().then(result => console.log(result));

So when we run our code now with the “debug” method call at the end, we should get the content of our .csv file:

[
  { keyword: '#coaching', projectId: 1234, serviceId: 5678 },
  { keyword: "Coder's Talk", projectId: 8765, serviceId: 4321 }
]

Bingo!

With these mappings, we can run a simple .find() on the array by looking if the keyword is somewhere in the title of calendar entry (=summary):

function getProjectAndServiceMapping(mappings, summary) {
    summary = summary.toLowerCase();
    let element = mappings.find(mapping => {
        return summary.indexOf(mapping.keyword.toLowerCase()) > -1;
    });
    if (element) {
        return [element.projectId, element.serviceId];
    }
    return [null, null];
}

With this method, we get the pairing of projectId and serviceId to a specific keyword. We might think about returning the whole row instead (including the keyword), but for now, this works just fine.

I modified our “debug” call from before to check the output of those functions by example:

getMappings().then(mappings => {
    let eventTitle = 'Some title with #coaching in it';
		
  	const [projectId, serviceId] = getProjectAndServiceMapping(mappings, eventTitle);
    console.log(mappings, serviceId, projectId);
});

[
  { keyword: '#coaching', projectId: 1234, serviceId: 5678 },
  { keyword: "Coder's Talk", projectId: 8765, serviceId: 4321 }
] 5678 1234

Step 5: Getting calendar entries for the last seven days

This is just a small intermediate step. We might need to tell our Google API to get us the events for the last seven days instead of the next 10 events from now. So I added these two variables and removed the limit:

// ...
let today = new Date();
let sevenDaysAgo = new Date(today.getTime() - (7 * 24 * 60 * 60 * 1000));
calendar.events.list({
    calendarId: 'primary',
    timeMin: sevenDaysAgo.toISOString(),
    timeMax: today.toISOString(),
    singleEvents: true,
    orderBy: 'startTime',
// ...

Note: Before moving on, I wanted to clean up our code a little. So I moved the Google Authentication part to a separate class and only called it inside our c2m.js. If you want to see what I changed, have a look at this commit: https://github.com/moritzwachter/calendar-to-mite/commit/6197fe1faeb43d349f4d99c7f1ae136a5d485c32

Step 6: Creating Mite Entries

Now that we have a working mapping of calendar entries to project ids and service ids, we can create the proper data structure to send the time tracking entries back to Mite.

let start = Date.parse(event.start.dateTime);
let end = Date.parse(event.end.dateTime);

// duration in minutes
let duration = (end - start) / (1000 * 60);

const [projectId, serviceId] = getProjectIdAndServiceId(mappings, event.summary);

let entry = {
    'date_at': formatDate(start), // needs to be YYYY-MM-DD
    'minutes': duration,
    'note': event.summary,
    'project_id': projectId,
    'service_id': serviceId
};

mite.addTimeEntry(entry, (res) => console.log);

With mite.addTimeEntry(entry, (res) => console.log); we can now send every single calendar entry to mite and generate our time tracking entries!

Interim report

This is a very basic implementation so far, and we can polish the code quite a bit. Until now, each calendar entry is added as a mite entry, even if you did not want to add this particular calendar entry. Additionally, if a calendar entry has no mapping, it is created in mite without a project or service id. And we might want to adjust some timings (e.g., 50min calendar entry is booked as 1 hour because the policy is to book in 15min steps). So our process is not entirely automated yet, and we still need to make some adjustments, but from my experience, this already helped me a lot!

You can check out the current status of our code at:

moritzwachter/calendar-to-mite

In the next series, we will talk about some improvements, like refactoring, filtering events, and unit tests with Jest.