Script to read Google Calendar Events and Count total Hours

gglCalEventsOnSpreadSheet.gs

  const gblFrom = {
    year : 2020 ,
    month : 3,
    day : 29,
    hour : 0
  };
  const gblTo = {
    year : 2020 ,
    month : 8,
    day : 1,
    hour : 0 // Note that if hour = 0,  day will not be included as time will be 00:00 of specified day
  };
  // Define your Time Zone
  const gblTimeZone ="Europe/Madrid";

  //Calendar to Search in
  var gblCalendarName = "Work";
  //  Free text search terms to find events that match these terms in any field, except for extended properties.
  //    Leave Blank "" to match all events in the Calendar
  var gblEventQuery = "";
  // Max number of Events to Match, By default the value is 250 events. max is 2500 events.
  const MAX_EVENT_QUERY_RESULTS = 1000;
  // Although more than 2500 events is possible this script does not handle multi-"page" Results. 
  
  //Define Number of hours to count for an All-Day Event
  const gblAllDayHours = 8;
  
  // Group Event Names by Regular Expressions
  // You can define as many groups as you want just "push" more "Names"/RegularExpressions into the array
  //gblRegExGrp.push(["Name of the Group",new RegExp('Regular Expression','i')]);
  // You can test your regular expression here for example: https://regex101.com/
  var gblRegExGrp = [];
  //gblRegExGrp.push(["Events That Start with torn ",new RegExp('^torn','i')]);


  // Internally used global constat variables
  const MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

  var gblOutput = [];
  //Here are the headers definition for our sheet
  gblOutput.push(["Event","Start", "End", "Durration"]);
  //A global variable for the totalizer of hours (Should be same size as previous)
  var gblLastLine = ["Total","","",0];
  // Event Specific Totalizer
  var gblEventTotal = {};
  //
  var gblEventOutput = [];
  gblEventOutput.push(["Same Event","Total Days", "Total Hours"]);

  var gblRegexEventTotal = {};
  var gblRegexEventOutput = [];
  gblRegexEventOutput.push(["Regex Event","Total Days", "Total Hours"]);


  var gblZeroDayHours = {};

  //This is updated by the calendar search results to calendar Time Zone, if available
  // Used for Allday events
  var gblCalendarTimeZone = gblTimeZone;



  gblFromDate = retDateInTZ(gblFrom.year, gblFrom.month, gblFrom.day, gblFrom.hour,0,gblTimeZone);
  gblToDate = retDateInTZ(gblTo.year, gblTo.month, gblTo.day, gblTo.hour,0,gblTimeZone);

function RunMe(){
  fillMySheetWithCalendarEventDetails(
    gblCalendarName,
    gblEventQuery,
    gblFromDate,
    gblToDate
  );
}
//new Date(Year, Month,...) Where month goes from 0-11

function RunLast24h(){
  var now = new Date();
  var now_pre24h = new Date(now.getTime() - MILLIS_PER_DAY);
  fillMySheetWithCalendarEventDetails(
    gblCalendarName,
    gblEventQuery,
    now_pre24h, 
    now
  );
}

function RunLast144h(){
  var now = new Date();
  var now_pre24h = new Date(now.getTime() - (7*MILLIS_PER_DAY));
  fillMySheetWithCalendarEventDetails(
    gblCalendarName,
    gblEventQuery,
    now_pre24h, 
    now
  );
}
// ------------Helper Functions -----------------

  function retUTCOffset(year,month,day,hour,mins,timeZstr){
    // Calculating Offsets with regards to the Query TimeZone Specifics
    var utcOs = Utilities.formatDate(new Date(Date.UTC(year,month-1,day,hour,mins)), timeZstr, 'Z');
    //Now we have the timeZone considering the hour UTC, but it will be expressed in the timeZone
    // There is a marginal case in daylight savings zone switch that the TimeZone will be incorrect
    var utcOsHours = Math.trunc(-1*utcOs/100);
    var utcOsMinutes = -1*utcOs % 100;
    // This second iteration should correct that, the "invalid" hour is interpreted to the next valid utc offset
    return Utilities.formatDate(new Date(Date.UTC(year,month-1,day,utcOsHours+hour,utcOsMinutes+mins)), timeZstr, 'Z');
  }

  function retDateInTZ(year,month,day,hour,mins,timeZstr){
    // Calculating Offsets with regards to the Query TimeZone Specifics
    var utcOs = retUTCOffset(year,month,day,hour,mins,timeZstr);
    var utcOsHours = Math.trunc(-1*utcOs/100);
    var utcOsMinutes = -1*utcOs % 100;
    return new Date(
      Utilities.formatDate(new Date(
        Date.UTC(year, month-1, day, utcOsHours+hour, utcOsMinutes+mins, 0, 0)
        ), gblTimeZone, 'MMMM dd, yyyy HH:mm:ss Z'));
  }


// API Ref: Sheet : https://developers.google.com/apps-script/reference/spreadsheet/sheet
// API Ref: Range : https://developers.google.com/apps-script/reference/spreadsheet/range
function fillMySheetWithCalendarEventDetails(myCalendarName,myEventName,myFromDate,myToDate){

  var mySheet = SpreadsheetApp.getActiveSheet();
  var eventsInTime = getMyCalendarEventsInTime(myCalendarName,myEventName,myFromDate,myToDate);
  // Default Range : set to size required by Error Report, will be redfined if no "error".
  var range = mySheet.getRange(mySheet.getActiveCell().getRow(),mySheet.getActiveCell().getColumn(),2,1);
  if(eventsInTime){
    processDetailsFromMyEvents(eventsInTime);
    //Logger.log()
    range = mySheet.getRange(mySheet.getActiveCell().getRow(),mySheet.getActiveCell().getColumn(),1,1);
    if(gblRegexEventOutput.length>1){
      gblRegexEventOutput.push(["","", ""]); //Create an empty line after
      range = range.offset(range.getNumRows(),0,gblRegexEventOutput.length,gblRegexEventOutput[0].length); 
      range.setNumberFormat("General");
      range.clearFormat(); //Clears all except Number Formatting
      range.setValues(gblRegexEventOutput);
    }
    range = range.offset(range.getNumRows(),0,gblEventOutput.length,gblEventOutput[0].length);
    range.setNumberFormat("General");
    range.clearFormat(); //Clears all except Number Formatting
    range.setValues(gblEventOutput);
    // We offset the current range selection by its height and set the selection to next output
    range = range.offset(range.getNumRows()+1,0,gblOutput.length,gblOutput[0].length);
    range.setNumberFormat("General");
    range.clearFormat(); //Clears all except Number Formatting
    range.setValues(gblOutput);
  }else{
    range.setValues([["Script Errors"], [Logger.getLog()]]);
  }
}
//Pre: Requires an existing event
//Post: Returns an array of values
function getDetail1FromEvent(myEvent){
  var myDetails = [];
  var myStartDate, myEndDate;
  var myDiff = { days : 0, hours : 0 };
  var myStrDate = { start : "", end : "" };
  
  if (myEvent.start.date) {
    // myEvent.start.date The date,in the format "yyyy-mm-dd", *iif* this is an all-day event.
    myStartDate = new Date(getDateFromIso(myEvent.start.date,myEvent.start.timeZone));
    myEndDate = new Date(getDateFromIso(myEvent.end.date,myEvent.end.timeZone));
    myDiff.days = ((myEndDate - myStartDate)/(1000 * 60 * 60 * 24));
    myDiff.hours = gblAllDayHours * myDiff.days;
    myStrDate.start = Utilities.formatDate(myStartDate,gblTimeZone,"YYYY/MM/dd");
    myStrDate.end = Utilities.formatDate(myEndDate,gblTimeZone,"YYYY/MM/dd");
  } else {
    // myEvent.start.dateTime : a combined date-time value (formatted according to RFC3339). 
    //    A time zone offset is required unless a time zone is explicitly specified in timeZone. 
    // I am assuming that if myEvent.start.date does not exist, ...dateTime must exist, could crash,.
    myStartDate = new Date(getDateFromIso(myEvent.start.dateTime,myEvent.start.timeZone));
    myEndDate = new Date(getDateFromIso(myEvent.end.dateTime,myEvent.end.timeZone));
    myDiff.hours = ((myEndDate - myStartDate)/(1000 * 60 * 60));
    myStrDate.start = Utilities.formatDate(myStartDate,gblTimeZone,"YYYY/MM/dd HH:mm");
    
    // .getUTCDay() returns day of the week, I am assuming a timed event will not last longer than that
    // .getUTCDate() would return day of the month
    myDiff.days = (myEndDate.getUTCDay() - myStartDate.getUTCDay() - 7) % 7;
    // Since % is the remainder little tweak to get the modulo
    // Discussion here : https://stackoverflow.com/questions/11720656/modulo-operation-with-negative-numbers
    if (myDiff.days < 0 )
      myDiff.days += 7;
    
    // if myDiff .days = 1 & .hours < 24 --> Overnight Activity 
    //   An Overnight activity is counted as a day, there could be the case where there are multiple events on
    //   startDay and/or endDay which would cause the day counter to indicate a day more than reality
    // if myDiff .days = 0 then we could have multiple events on that day
    // Here we keep track of hours accumulated each day for zero day events
    //  We later go through this array of objects to increment the gblEventTotal day counter accordingly
    if (myDiff.days == 0){
      var myTmp = Utilities.formatDate(myStartDate,gblTimeZone,"YYYY/MM/dd");
      if (!(myEvent.summary in gblZeroDayHours)) {
        gblZeroDayHours[myEvent.summary] = {};
      }
      if (!(myTmp in gblZeroDayHours[myEvent.summary])) {
        gblZeroDayHours[myEvent.summary][myTmp] = myDiff.hours;
      }else{
        gblZeroDayHours[myEvent.summary][myTmp] += myDiff.hours;
      }
    }    
    
    if ( myDiff.days > 0 ){      
      myStrDate.end = Utilities.formatDate(myEndDate,gblTimeZone,"YYYY/MM/dd HH:mm");
    }else{
      myStrDate.end = Utilities.formatDate(myEndDate,gblTimeZone,"HH:mm");
    }    
  }
  // We Record the first matched Event Date as start for the last line
  if (gblLastLine[1] == "")
    gblLastLine[1] = Utilities.formatDate(myStartDate,gblTimeZone,"YYYY/MM/dd");
  Logger.log('%s (%s) : %s hours', myEvent.summary, Utilities.formatDate(myStartDate,gblTimeZone,"yyyy/MM/dd"), myDiff.hours);
  
  myDetails = [myEvent.summary, myStrDate.start, myStrDate.end, myDiff.hours];

  //gblLastLine[3] is my hours Totalizer
  gblLastLine[3] += + myDiff.hours;
  //This following gets overwritten with each new entry found
  gblLastLine[2] = Utilities.formatDate(myEndDate,gblTimeZone,"YYYY/MM/dd");
  
  // Here we keep track of sameName Event Totalizers
  if (!(myEvent.summary in gblEventTotal)) {
    gblEventTotal[myEvent.summary] = myDiff;
  } else {
    gblEventTotal[myEvent.summary].days += myDiff.days;
    gblEventTotal[myEvent.summary].hours += myDiff.hours;
  }
  
  return myDetails;
}
// Chcek : https://developers.google.com/calendar/v3/reference/events#resource for a list
function processDetailsFromMyEvents(myEvents){
  if (myEvents.items && myEvents.items.length > 0) {
    if(myEvents.timeZone) gblCalendarTimeZone = myEvents.timeZone; //In String format, e.g. Europe/Madrid
    for (var i = 0; i < myEvents.items.length; i++) {
      var event = myEvents.items[i];
      var details1 = getDetail1FromEvent(event);
      if (details1.length > 0){
        gblOutput.push(details1);
      }
    }
    if (gblOutput.length > 1){
      gblOutput.push(gblLastLine);
      // We fill the output array for the Event Totalizer
      for (const [key, value] of Object.entries(gblEventTotal)){
        var myDays = value.days;
        //Here is where we count accumulated Zero Day hours on same day as a day
        if (key in gblZeroDayHours){
          // We need to iterate over every object just to count them.
          for (const [myZkey, myZvalue] of Object.entries(gblZeroDayHours[key])){
            myDays++;
          }
        }
        //We add the values to the output
        gblEventOutput.push([key, myDays, value.hours]);

        //Regexp Event Capture Totalizer
        for (const [grp, re] of gblRegExGrp){
          if((key).match(re)){
            if (!(grp in gblRegexEventTotal)) {
              gblRegexEventTotal[grp] = { days : myDays, hours : value.hours };
            } else {
              gblRegexEventTotal[grp].days += myDays;
              gblRegexEventTotal[grp].hours += value.hours;
            }
          }
        }
      }
      // We fill the output array for the Regex Event Totalizer
      for (const [key, value] of Object.entries(gblRegexEventTotal)){
        //We add the values to the output
        gblRegexEventOutput.push([key, value.days, value.hours]);
      }
      Logger.log(gblRegexEventOutput);
    }
  } else {
    Logger.log('No events found.');
  }
}

// Details Here : https://developers.google.com/calendar/v3/reference/events/list
function getMyCalendarEventsInTime(myCalendarName,myEventName,myFromDate,myToDate){
  Logger.log("%s <-> %s", myFromDate.toISOString(), myToDate.toISOString());
  var myCalId = getCalendarKey(myCalendarName);
  Logger.log('Calendar Name "'+ myCalendarName + '" associated to key : <'+ myCalId+'>');
  try{
    var myEvents = Calendar.Events.list(myCalId, {
      timeMin: myFromDate.toISOString(),
      timeMax: myToDate.toISOString(),
      q: myEventName,
      singleEvents: true,
      orderBy: 'startTime',
      maxResults: MAX_EVENT_QUERY_RESULTS
    });
  } catch (e) {
    Logger.log('Calendar.Events.list(...) yielded an error: ' + e);
  }
  //Logger.log(myEvents);
  return myEvents;
}
// Details Here : https://developers.google.com/calendar/v3/reference/calendarList/list
function getCalendarKey(myKey) {
  var calendars, pageToken;
  var myCalId = '';
  do {
    calendars = Calendar.CalendarList.list({
      maxResults: 100,
      pageToken: pageToken
    });
    if (calendars.items && calendars.items.length > 0) {
      for (var i = 0; i < calendars.items.length; i++) {
        var calendar = calendars.items[i];
        if (calendar.summary==myKey){
          myCalId = calendar.id;
          return myCalId;
        }
      }
      if (myCalId == ''){
        Logger.log('Specified gblCalendarName "'+myKey+'" not found amoung your Calendars.');
      }
    } else {
      Logger.log('No calendars found.');
    }
    pageToken = calendars.nextPageToken;
  } while (pageToken);
  return myCalId;
}
// Function inspired by PAUL SOWDEN
// https://web.archive.org/web/20171126183950/http://delete.me.uk/2005/03/iso8601.html
function getDateFromIso(string, inTimeZone) {
  //Logger.log(string);
  try{
    var aDate = new Date();
    var regexp = "([0-9]{4})(-([0-9]{2})(-([0-9]{2})" +
        "(T([0-9]{2}):([0-9]{2})(:([0-9]{2})(\\.([0-9]+))?)?" +
        "(Z|(([-+])([0-9]{2}):([0-9]{2})))?)?)?)?";
    //var d = string.match(new RegExp(regexp));
    var d = string.match(regexp);
    //[1]Year, [3]Month, [5]Day, [7]Hour, [8]Minute, [10]Second, [12]milliseconds
    // unsuccessfull capture patterns appear as undefined
  } catch(e){
    return;
  }
  
  var mYear = 0;
  var mMonth = 0;
  var mDay = 0;
  var mHours = 0;
  var mMinutes = 0;
  var mSeconds = 0;
  var mMilliseconds = 0;
  var mOffset;
  
  mYear=d[1];
  mMonth=d[3] - 1;
  mDay=d[5];
  if (d[7]) { mHours=d[7]; }
  if (d[8]) { mMinutes=d[8]; }
  if (d[10]) { mSeconds=d[10]; }
  if (d[12]) { mMilliseconds = Number("0." + d[12]) * 1000;}
  if (d[14]) { mOffset = d[14] }

  //We give dateTime Defined offSet Preference if exists
  if(!mOffset){
    if (inTimeZone){
      mOffset=retUTCOffset(mYear,mMonth,mDay,mHours,mMinutes,inTimeZone);
    }else {
      mOffset=retUTCOffset(mYear,mMonth,mDay,mHours,mMinutes,gblCalendarTimeZone);
    }
  }

  // The Date constructor can only parse certain date string formats. 
  // To make sure your date string is parsed correctly, always provide it as 
  //     MMMM dd, yyyy HH:mm:ss Z 
  // new Date(year, month, day, hours, minutes, seconds, milliseconds);
  var mDate = new Date(Date.UTC(mYear, mMonth, mDay, mHours, mMinutes, mSeconds, 0));
  mDate = new Date(Utilities.formatDate(mDate, "UTC", 'MMMM dd, yyyy ')+mHours+":"+mMinutes+":"+mSeconds+" "+mOffset);
  return mDate;
}
//References:
// [Utilities.formatDate()...](https://developers.google.com/apps-script/reference/utilities/utilities)
// [Main Events returned from Query](https://developers.google.com/calendar/v3/reference/events/list#response)
// [Individual Events in List of Main Events](https://developers.google.com/calendar/v3/reference/events#resource)
// [Info on Dates](https://developers.google.com/google-ads/scripts/docs/features/dates)
// [...more](https://developers.google.com/chart/interactive/docs/datesandtimes)

/*
**Steps**
  1. Go to your google Drive and start a new Empty SpreadSheet.
  2. Go to the Menu: Extensions --> Apps Script.
  3. Copy+paste the code into the new Code.gs and Save the File
  4. Click on "Services +"
  5. Find "Google Calendar API" , Select it, Leave it as Version "v3", Identifier "Calendar" --> Add
  6. Modify the gbl... vars to suit your interest.
  7. Go to the Menu --> Run --> RunMe
  8. An Authorization request will popup, (only the first time you run it)
    8a. Click Review Permissions
    8b. Select your google account, click continue
    8c. It will say "This App isn't verified", click Advanced, then Goto <project name> (unsafe)
    8d. It will tell you which permissions are required, Click Allow
  9. If all went well you should be able to return to the Sheet and see the results.  
  10. For easy Access from the sheet add the function within the Menu: Tools --> Macros --> Import
  
  
  ----
  Run --> RunMe : Runs Using the Defined Range
  Run --> RunLast24h : Runs using the last 24h as date range
  Run --> RunToday : events from Today
  Run --> RunYesterday: events from Yesterday
*/
//Changes History
//2021/03/17 Minor bug fixes, Added Hour to To/From definition, DST Marginal Case TimeZone error fix
//2021/03/17 Added Regular Expression Grouping of Events Totalizer
//2024/11/04 Changed Order of Code so that the "default" Run Function selected is RunMe and Not retUTCOffset

Getting the following error, even when replacing time zone in header.

1:27:01 PM Error
Exception: Invalid argument: timeZone. Should be of type: String
retUTCOffset @ Code.gs:63

Hi Pau

I tried your script today and got the same error as smazo:

Exception: Invalid argument: timeZone. Should be of type: String
retUTCOffset @ Code.gs:63

Do you have any idea what might be going on?

Thanks!

Hi @smazo and @avol-eng ,
Thanks for your interest and providing the feedback. @smazo sorry for the late reply.

The problem is that with the previous code when you hit the ** |> Run ** Button, the default function which was selected to run was retUTCOffset(...) function and since this function is not meant to be run on its own, the variables that it runs with are undefined and provide an error.. If you select the function RunMe or RunLast24hours ,... it will run correctly.

I have updated the code so that the default selected function to Run when you hit Run is the RunMe Function.

It should run as intended out of the box now. Hope this is of use to you.

Cheers,

Thanks a lot Pau, that's worked!

…

On Mon, 4 Nov 2024 at 17:37, Pau Coma Ramirez ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ Hi @smazo <https://github.com/smazo> and @avol-eng <https://github.com/avol-eng> , Thanks for your interest and providing the feedback. @smazo <https://github.com/smazo> sorry for the late reply. The problem is that with the previous code when you hit the ** |> Run ** Button, the default function which was selected to run was retUTCOffset(...) function and since this function is not meant to be run on its own, the variables that it runs with are undefined and provide an error.. If you select the function RunMe or RunLast24hours ,... it will run correctly. I have updated the code so that the default selected function to Run when you hit Run is the RunMe Function. It should run as intended out of the box now. Hope this is of use to you. Cheers, — Reply to this email directly, view it on GitHub <https://gist.github.com/paucoma/7b7b6cecddfa79d25531#gistcomment-5266400> or unsubscribe <https://github.com/notifications/unsubscribe-auth/BMVARGEUTICVXAB35UW5RODZ66WGXBFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVAZDCOBZHE2TQONHORZGSZ3HMVZKMY3SMVQXIZI> . You are receiving this email because you were mentioned. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub> .