How to get total event time of Google Calendar events into Google Sheets based on certain conditions?

Question

In my company we work with project sheets where each project lead maintains an overview of the spent hours (compared to total hours) of each project, per person working on the project. We use TimeSheets (Google Calendar/Sheets plugin) to calculate hours per project. Each project has a tag (e.g. #ACQ Writing proposal), and we use that tag to precede any appointment or work hour in our google calendar. How to get a table with hours spent per project (tag), per people working on the project (defined by their email address)?

I want realtime updating of my project sheets and having an overview of hours spent on a project, per person on the project. The plugin I use doesn't work properly (e.g. it includes declined events in the total amount of hours per project).

PS. Update: I spent a couple of hours on ChaptGPT and developed a properly working script, see answer.

Answer 1

While writing the question, I thought I'd have a go at it with ChatGPT. And somehow it worked!

Here's the code:

function getTotalEventTime() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var searchString = "Your Predefined String"; // Replace with your predefined string
  var startDate = new Date("2023-01-01"); // Replace with your desired start date
  var endDate = new Date("2023-12-31"); // Replace with your desired end date
  var email = "[email protected]"; // Replace with the email address you want to calculate the total time for

  var calendar = CalendarApp.getCalendarById(email);
  if (!calendar) {
    Logger.log("Invalid email address or insufficient permission.");
    return;
  }

  var events = calendar.getEvents(startDate, endDate);
  var totalEventTime = 0;

  for (var i = 0; i < events.length; i++) {
    var event = events[i];
    if (event.getTitle().startsWith(searchString) && event.getMyStatus() !== CalendarApp.GuestStatus.DECLINED) {
      var eventDuration = event.getEndTime() - event.getStartTime();
      totalEventTime += eventDuration;
    }
  }

  // Convert milliseconds to hours
  var totalEventHours = totalEventTime / (1000 * 60 * 60);

  // Write the total event time to the sheet
  sheet.getRange("A1").setValue("Total Event Time:");
  sheet.getRange("B1").setValue(totalEventHours);
}

And the instructions:

Here's how you can use this script:

1. Open your Google Sheets document.
2. Click on "Extensions" in the top menu.
3. Select "Apps Script" from the dropdown menu. This will open the Apps Script editor in a new tab.
4. Delete any existing code in the editor. Copy and paste the above script into the editor. Modify the searchString, startDate, endDate, email variables to fit your requirements. The searchString should be replaced with your predefined string, and the startDate and endDate should be set to the desired period you want to retrieve events from. email should be set to the email address for which you want to calculate the total time.
5. Save the script by clicking on the floppy disk icon or by pressing Ctrl + S.
6. Close the Apps Script editor tab. In your Google Sheets document, you can now run the script by clicking on "Extensions" in the top menu, selecting "Apps Script," and choosing the "getTotalEventTime" function.
7. List item

The total event time will be displayed in cell B1 of the active sheet. Remember to configure the predefined string, start date, end date, and email address according to your needs. The first time you need to give permissions for running the script and accessing your calendar.

PS. Declined events are not included in the total event time.