Counting days "Out of office" events with Apps Script Calendar Service

Question

I would like to count the days off I had in my Google Calendar at the end of every month.

With the following simple formula,

total days off = out of office days + bank holidays

Using a Calendar Service in Apps Script, I came up with a first solution using a #dayoff tag trick and filtered on Mon-Fri only.

edit

I then wrote a second updated solution following Kessy's tip with the advanced Calendar API, but still can't get single-day out of office events.

const howManyDays = () => {
  WEEK_DAYS = [1,2,3,4,5]

  // https://stackoverflow.com/a/25672779/1360476
  const countWeekDays = (start, end) => {
    const ndays = 1 + Math.round((end-start)/(24*3600*1000))
    const sum = (a,b) => a + Math.floor((ndays + (start.getDay()+6-b) % 7)/7)

    return WEEK_DAYS.reduce(sum, 0)
  }

  const diffDays = (start, end) => {
    return Math.ceil(Math.abs(new Date(end) - new Date(start)) / (1000 * 60 * 60 * 24))
  }

  const date = new Date()
  const currentMonth = date.getMonth()
  const currentYear = date.getFullYear()
  const start = new Date(currentYear, currentMonth, 1)
  var end = new Date(currentYear, currentMonth + 1, 0)

  const workDays = countWeekDays(start, end)
  const myCalendar = Calendar.Events.list('primary')
  const outOfOffice = myCalendar.items
    .filter(x => x.eventType == "outOfOffice")
    .map(x => {
      const {summary, start, end} = x
      return {summary, start: new Date(start.dateTime), duration: diffDays(start.dateTime,end.dateTime)}
    })
    .filter(x => start <= x.start && x.start <= end)
    .map(x => x.duration)   // FIXME: we only have duration >= 2 here
    .reduce((x, y) => x + y)

  // const singleEvents = CalendarApp.getDefaultCalendar().getEvents(start, end, {search: '#dayoff'})
  //      .filter(x => WEEK_DAYS.includes(x.getStartTime().getDay()))
  // Logger.log(singleEvents) 
  // shows up one-day out of office events that don't get captured by the advanced Calendar API, using the explicit keyword #dayoff

  const holidays = CalendarApp.getCalendarsByName('Holidays in France')[0].getEvents(start, end)
    .filter(x => WEEK_DAYS.includes(x.getStartTime().getDay()))
    .length

  message = `  ${workDays} working days from ${start.toLocaleDateString()} to ${end.toLocaleDateString()} \n`
          + `-  ${outOfOffice} out of office days \n`
          + `-  ${holidays} bank holidays \n`

  MailApp.sendEmail('your.email@provider.com', 'My timesheet', message)
  Logger.log(message)
}

I would like to share that script with my colleagues and remove the my #dayoff trick, how can I get the "Out of office" information directly from the API, and how do I handle multi days "Out of office" events?

score 0 · Answer 1 · answered Oct 31 '22 at 13:55

0

You can use the Calendar API with Apps Script and with the Events: list method you will receive an Event object. There you can filter by the eventType attribute to get the outOfOffice.

answered Oct 31 '22 at 13:55

Kessy

1,894
1
8
15

https://developers.google.com/apps-script/reference/calendar/calendar-event but can't find eventType here – Michel Hua Oct 31 '22 at 19:26
I tried to use the Calendar API but `Calendar.Events.list('primary').items.filter(x => x.eventType == "outOfOffice").map(x => {const {summary, start, end} = x; return {summary, start, end}})` doesn't contain single-day out of office events but only multiple days – Michel Hua Nov 01 '22 at 08:29
See my answer for my basic implementation. – Michel Hua Nov 01 '22 at 12:53
Please instead of writing an answer that is not final, update the question so other users don't get confused. Also, can you share more details on your issue of not getting single days out of office and the reproduction steps for the new code? – Kessy Nov 02 '22 at 16:52
done. To reproduce it is simple, you create a two-whole-days out of office event in your calendar it does get captured by the `filter(x => x.eventType == "outOfOffice")` above, but when you create a one-whole-day event it doesn't. – Michel Hua Nov 02 '22 at 17:06
I have tested your updated script and it did work for me, inserting and deleting single OOO events got the script with the correct comeback every time. Have you checked that the calendar names are correct and that the events are created in the appropriate calendar? – Kessy Nov 09 '22 at 13:47

Counting days "Out of office" events with Apps Script Calendar Service

1 Answers1