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;

  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'));
  }

  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
  );
}

// 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: Tools --> Script Editor.
  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
//21/03/17 Minor bug fixes, Added Hour to To/From definition, DST Marginal Case TimeZone error fix
//21/03/17 Added Regular Expression Grouping of Events Totalizer

Hi @Nitty-Gritty, This seems to be a problem with Time Zones. I have made a bit of changes on the 5th of Feb to try to correct this problem.

Are you using this current/latest version of the code?

Can you tell me what your actual time zone is? GMT+?

Con your tell me what you have set the variable const gblTimeZone to?

// Define your Time Zone
  const gblTimeZone ="Europe/Madrid";

Thanks,
Pau

Hi Pau, I'm using the latest version. My time zone is "New Zealand" or "Auckland". Times are reported correctly in the spreadsheet but skewed as described. I did try changing it to "Australia", which made no difference. I think "EST" changed the reported times of events in the spreadsheet, but did not fix our alter the skewed selection of events. Thanks

…

On Tue, 16 Mar 2021, 10:24 pm Pau Coma Ramirez, ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ Hi Nitty-Gritty, This seems to be a problem with Time Zones. I have made a bit of changes lately to try to correct this problem. Are you using this current/latest version of the code? Can you tell me what your actual time zone is? GMT+? Con your tell me what you have set the variable const gblTimeZone to? // Define your Time Zone const gblTimeZone ="Europe/Madrid"; Thanks, Pau — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://gist.github.com/7b7b6cecddfa79d25531#gistcomment-3666518>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABUHKW4LLKHWIKL3D4OXILLTD4PWFANCNFSM4H6UCG6Q> .

Hi @Nitty-Gritty,

For New Zealand TimeZone a couple Identifiers that may be of your use

"Pacific/Auckland"
"Pacific/Chatham"
"NZ" which is the same as Auckland

More identifiers here

I have re-written the date parsing section and I believe that now it does capture and interpret correctly the Time Zone.
It is difficult for me to recreate your conditions as my calendar is only in one time zone so your testing really helps the script out.

Please give the new code a try and let me know if it works for you.

Thanks,
Pau

Hi Pau,

Thanks for that. I've updated the code.

"Pacific/Auckland", "NZ", "New Zealand", all bring the times that I entered into calendar accurately into the spreadsheet. 
 
"Auckland" changes the times morning to afternoon (13.30 turns into 1.30). (Entering "Australia" does the very same; it doesn't change the calendar times).

"Pacific/Chatham" imports my calendar entries and adds an hour, NZ time +1, so 13.30 becomes 14.30. "EST" imports event time - 19 hours. So 13.30 on the 1st of May is shown in the sheet as 20.30 on the 30th of April.

However, none of the gblTimeZone entries change the way that events starting up to 16 hours on the 'up to' day are incorrectly included in the spreadsheet import. That is to say, if I want events only in April, I should enter:

const gblFrom = {    year : 2020 ,    month : 04,    day : 01  };  const gblTo = {    year : 2020 ,    month : 05,    day : 01 // Not included as time is 00:00 of specified day

but what this invariably includes in import are events that start up to 16.00 on the 1st of May (regardless of the time zone I enter).

Thanks very much for your time and attention on this.

Hi @Nitty-Gritty
I see now, I had misunderstood what you were initially explaining... I jumped too quickly to the date/parsing but that was not what was wrong.
Your Start times and End Times and durration of Captured events were all correctly exposed.
It was The Start and End "Window" of events to capture which was wrong.. Well If I got it right this time that is xD

And yes, Your observed behaviour makes sense with what was in the code. The dates are actually defaulted to EST times ... this is something I never really paid much attention to.. because I was looking for more or less a time frame.. normally with a day margin... from Sunday to Sunday or something like that..

Anyway, I have corrected the interpretation of the From and To Date and now it should work.

Let me know in both cases, if it still isn't what you expect or if it is , hehe

Thanks

Yes, it's completely working now. Thank you!

Glad its working correctly. I have made some improvements taking into account the case where the time is just at a Daylight Savings Time crossing day... Also some minor bug fixes for recurring events.

I have also added another optional grouping of events totalizer. Lines 28-33, it is by default disabled, but if you uncomment line 33 and adapt it to match some of your event names it will appear as a list above the same name event totalizer.

What is it good for ?

• The Totalizer for same name events required that the name of the events be exactly the same to be grouped together.
• With the Regular Expression Totalizer, you can define a regular expression to Group "Similar" Events according to the regular expression defined and get the totals for that.

Cheers

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