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

Hey,
The code doesnt run automatically.
There is an error "API call to calendar.calendarList.list failed with error: Login Required (line 97)"

Hey,
The code doesnt run automatically.
There is an error "API call to calendar.calendarList.list failed with error: Login Required (line 97)"

Hi mayakerem, sorry for the delay in the response.
This is a base code from which you may need to tweak some things to adapt to your particular environment, Certain variables on the top and so one, but from the info you provide it sounds like something went wrong in steps 4 to 8.

Hi @paucoma

Thanks so much for creating this. A couple questions.

1. I can't seem to figure out how to launch the developer console in step 6. Can you please provide more details here?
2. when I run it, I get an error: TypeError: Date.parse(...).toLocaleDateString is not a function (line 42, file "Code"). Is this because I didn't complete step 6?

Thank you!

Sam.

Hi @samlimster

The error is because the code was not updated to be compatible with Googles new V8 Engine Script code.

I've updated the code and made a couple corrections I found along the way too, let me know if it works out for you.

Cheers,
Pau

Thank you Pau. It's working great! I was wondering if there's a way to make it simply grab today's events so I don't need to keep modifying the global variables? Thanks for sharing this utility! Sam.

…

On Tue, Jul 14, 2020 at 12:33 PM Pau Coma Ramirez ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ Hi Sam, The error is because the code was not updated to be compatible with Googles new V8 Engine Script code. I've updated the code and made a couple corrections I found along the way too, let me know if it works out for you. Cheers, Pau — You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://gist.github.com/7b7b6cecddfa79d25531#gistcomment-3376666>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEWRPUYMFLWQBA74RG7PBA3R3SJFVANCNFSM4H6UCG6Q> .

Fixed a TimeZone Bug , Added Timezone Definition Global Variable, and I've added following functions:

Run --> RunMe : Runs Using the Defined Range in global variables
Run --> RunLast24h : Runs using the last 24h as date range , Now --> Now - 24h
Run --> RunToday : events from Today 00:00 -> 24:00
Run --> RunYesterday: events from Yesterday 00:00 -> 24:00

This exception occurs
GoogleJsonResponseException: API call to calendar.events.list failed with error: Not Found (line 129, file "Code")Dismiss

Hi @Zanoshky could you have a look at the log of your script and post its contents?

This exception occurs
GoogleJsonResponseException: API call to calendar.events.list failed with error: Not Found (line 129, file "Code")Dismiss

I have had a try and I get this error when the global variable on line 10

var gblCalendarName = "Work";

Does not correspond to any existing Calendar Name.

Goto calendar.google.com and on the left under My Calendars you should see a list of the calendar names that you have setup

Updated the script to include a bit of Error Handling and extra Logging
In case of an Error, the log will get output onto your Sheet for easy Viewing and Sharing

Hi @paucoma,

This has been really useful, thank you.

The gblToMonth doesn't seem to work. The output continues to the present.

I can see the code hasn't changed so perhaps Google has changed something, or can you suggest a fix please?

Hi @Nitty-Gritty,
I have just tested it out and the gblToMonth appears to work for me.

Could you describe more in detail your configuration and which event to are trying to capture or exclude which is or isn't being captured?

Note that:
gblCalendarName : Defines the Calendar to search in
gblEventQuery: Defines the search term to identify an Event as one for time tracking. I have set it to TT because in the Event Name I write "TT-" .. e.g. "TT-Flight to France" or "TT-Working on Site" ,...

The global variable for gblToDay specifies upto , but not including the specified day.
for example:

var gblFromYear = 2020;
var gblToYear = 2020;
var gblFromMonth = 01;
var gblToMonth = 09;
var gblFromDay = 01;
var gblToDay = 03; // Not Included as time is 00:00 of specified day

Would capture events between 2020/01/01 00:00 upto 2020/09/03 00:00

Since a day starts at 00:00 and ends at 23:59, this means that events that start on the third of september are not included in the search.
If an event starts before the third of september and spans out beyond the third of september, that event as a whole will be captured.

---

Glad its helped you out, let me know more in detail your configuration and expected output and achieved output, to better understand why it isn't working for you.

Cheers,
Pau

Thanks for your help!

It was because I had "2021" as the gblToYear. Working fine now I'm using this year only.

Thank you for the script - it works great!
Is there an option to extract form multiple calendars? I have a use case in which I am requested to pull this data from multiple accounts.

thanks
Nuriel

I noticed that line 279 var aDate = new Date(); creates a new Date object with Eastern Standard Time. This breaks the offset calculation. Even though I live in GMT, I have to set const gblTimeZone = "EST"; for this script to insert correct time into the sheet. I have checked my calendar and sheet time zones, they are both GMT, so not sure where EST comes from.

@NurielA

Is there an option to extract form multiple calendars? I have a use case in which I am requested to pull this data from multiple accounts.
First of all sorry for the delay. It is possible with modification but is more work than I am willing to do at the moment, sorry.. maybe someday in the future..

The best I can offer with the current code, is you will need to run the script multiple times changing manually the Calendar names.
To have it all on the same spreadsheet, Run the code, then select a column 5 further to the right and run again, etc..
The script will start outputting its data where your current selection in the sheet is. If it is Cell A1 it starts there, if its Cell E10 it starts there.

Glad the script is of use to you =)

@benewen96

I noticed that line 279 var aDate = new Date(); creates a new Date object with Eastern Standard Time. This breaks the offset calculation. Even though I live in GMT, I have to set const gblTimeZone = "EST"; for this script to insert correct time into the sheet. I have checked my calendar and sheet time zones, they are both GMT, so not sure where EST comes from.

First of all Thank You for your feedback! The TimeZone is a tricky one... Strange as theoretically new Date() defined via string with no offset specified uses by default PST, and you are in GMT and you need to set it to EST to get the correct time... On events which don't specify timezone I default to your systems local time offset from UTC/GMT...

PST to EST are 3 hours, EST to UTC/GMT are 4 hours, The math doesn't quite add up...

in any case, I have updated the code and migrated to the Javascript getUTChour, setUTChour instead of the setHour which uses a "default" timezone... difficult to define what exactly is default in different parts of the world. and different google accounts. I have also modified the Offset calculation for the timezone so that it works in my test case scenario in Spain.

Have a try with the updated code and let me know if it works for you.

Thanks, Pau

Hi again,

if I set

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

Then I get event's up to 4pm on the 1st of May being included. If I set

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

Then I get events up to 4pm on the thirtieth. Am I making a simple mistake again or can you tell me how to fix this?

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