Calculate hours worked from Google Calendar

In this post, we’re going to see how we can use Google Calendar to record the number of hours we’re working to make sure not overworking!

On my Google Calendar I add events to show the periods I’m working (in this case on admin).

A script will then get these events and list all of them on a Google Sheet and with a pivot table and a few formulas, will tell me if I’m under or over hours.

To do this, we’ll need a bit of Apps Script. Let’s look at how we do that.

Get specific events from a Google Calendar

1. function getAdminHours() {
2.  const ss = SpreadsheetApp.getActiveSpreadsheet(),
3.    shHours = ss.getSheetByName('HOURS');

L1: Open the function.

L2: Get the active spreadsheet.

L3: Get the sheet called ‘HOURS’.

5.  //Get calendar events for specific period
6.  const calendarId = 'email@email.com',
7.    timeZone = Session.getScriptTimeZone(),
8.    cal = CalendarApp.getCalendarById(calendarId),
9.    dates = shHours.getRange(1, 7, 1, 3).getValues()[0],
10.   calEvents = cal.getEvents(dates[0], dates[2]);

L6: Set the calendar ID you want to use. For your default calendar, this will be your email address, but if you have multiple calendars on your calendar, you will need to get the calendar ID for that particular calendar.

L7: Get the time zone as we’ll need it to format dates and times later on.

L8: Get the calendar by its ID.

L9: Get the start and finish date on the HOURS sheet.

L10: Get all the calendar events in the period between the start and finish date.

 12. //Filter Admin events
 13.  const filteredEvents = calEvents.filter((ce) => {
 14.  let eventTitle = ce.getTitle();
 15.   return eventTitle === "Admin";
 16.  });

L13: Now, we just want the events called “Admin”, so we filter them out of the calEvents array and store them in filteredEvents.

L14: Get the event title, which is what we’ll use to filter the events.

L15: Return the events that have the title “Admin”.

L16: Close the filter method.

18.  //Get dates, start and finish times of Admin events
19.  const adminEvents = filteredEvents.map((calEvent) => {
20.    let st = calEvent.getStartTime();
21.    let et = calEvent.getEndTime();
22.    let d = Utilities.formatDate(st, timeZone, 'yyyy/MM/dd');
23.    let startTime = Utilities.formatDate(st, timeZone, 'HH:mm');
24.    let finishTime = Utilities.formatDate(et, timeZone, 'HH:mm');
25.    return [d, startTime, finishTime];
26.  });

L19: Now we’ll loop through those admin events and return the date, start time and finish time and store them in the adminEvents array.

L20: Get the event start time.

L21: Get the event end time.

L22: Format the start date.

L23-24: Format the start time and finish time.

L25: Return an array with the formatted date, start time and finish time.

L26: Close the map method.

28.  //Add Admin start & finish times to sheet
29.  shHours.getRange(2, 1, shHours.getLastRow(), 5).clearContent();
30.  shHours.getRange(2, 1, adminEvents.length, adminEvents[0].length)
31.         .setValues(adminEvents);

L29: Clear the any existing content on the HOURS sheet.

L30-31: Add the dates and times to the HOURS sheet.

34.  //Add duration and YM formulas to sheet
35.  const formulaDuration = "=R[0]C[-1]-R[0]C[-2]";
36.  const formulaYM = "=YEAR(R[0]C[-4])&\"-\"&MONTH(R[0]C[-4])";
37.  const adminHrsFormulas = adminEvents.map((ae) => {
38.    return [formulaDuration, formulaYM];
39.  });
40.  shHours.getRange(2, 4, adminHrsFormulas.length, adminHrsFormulas[0].length).setFormulasR1C1(adminHrsFormulas);
41. }

L35-36: We’re going to add two formulas to each row on the HOURS sheet using the relative row/column format. Store the formulas in the variables. The first formula is the duration between the start and finish time. The second formula is the year and month of that event in format YYYY-M.

L37: To add all the formulas in one go, loop through the adminEvents array and return the two formulas for each line, so we end up with an array of formulas, ready to add to the sheet.

L38: Return the two formulas.

L39: Close the map method.

L40: Add the formulas on the HOURS sheet.

L41: Close the function.

Run the script and you will see that in a couple of seconds, it will get all the Admin events for the period you’ve set, and add the date, start & finish times, the duration, and the year & month on the HOURS sheet.

Am I working over or under hours?

On the MonthlyHours sheet I’ve set up a pivot table, which will summarise the data per month.

I also want to know if I’m working over or under hours and I’ve added a few formulas to work this out.

In cell D1, I’ve added how many admin hours I should be working per day, in this case 2.

In cell C3, I add the following formula to work out how many working days there were in February.

=NETWORKDAYS(A3;EOMONTH(A3; 0))-1

This gets the start and end date of that month and works out the number of working days (days from Monday to Friday). I’ve added -1 at the end as we had a public holiday on 28th February.

This formula works for any months that have finished and you just minus the number of days that were holidays.

For the current month, we use a different formula:

=NETWORKDAYS(A4;today())-1

This will get the number of working days from the start of the month until yesterday.

You may want to include today, and you’d need to remove the -1 but also change the today function on the HOURS sheet to +1, to get the calendar events until tomorrow, which will in fact include up until today’s.

Then we need to calculate how many hours we should have worked by multiplying the number of hours per day (D1) by the number of days in column C.

=$D$1*C3

We then copy that formula down column D.

To be able to calculate the number of hours worked we need to convert the duration into the number of hours. In E3 we add the following formula to get the number of hours.

=B3*24

Then we just get the difference between the number of hours we should have worked and the number of hours we did work in that month, and put the total in column F.

=E3-D3

Finally, we get the grand total by summing the number of hours in column F.

=SUM(F2:F)

I’m sure the above could be automated with a bit of Apps Script, but as I update it once a month, I’ve left it like this. As a final point, I also set up a trigger so the sheet is updated automatically every day.

Now, all I have to do is make sure the calendar events reflect the hours I’ve worked and this sheet will tell me how many I’ve worked over or under. 😀

Make a copy of the Google Sheet which contains the script HERE.

a