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 that solution using a #dayoff tag trick and filtered on Mon-Fri only.

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

  Logger.log(start.toLocaleDateString("fr-FR"))
  Logger.log(end.toLocaleDateString("fr-FR"))
  
  const events = CalendarApp.getDefaultCalendar().getEvents(start, end, {search: '#dayoff'})
    .filter(x => [1,2,3,4,5].includes(x.getStartTime().getDay()))
  
  const holidays = CalendarApp.getCalendarsByName('Holidays in France')[0].getEvents(start, end)
    .filter(x => [1,2,3,4,5].includes(x.getStartTime().getDay()))

  // MailApp.sendEmail('[email protected]', 'Timesheet', `${events.length} days off, ${holidays.length} bank holidays`)
  Logger.log(`${events.length} days`)
  Logger.log(`${holidays.length} days`)

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?

CodePudding user response：

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.

CodePudding user response：

I came up to this solution following Kessy's help, but 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 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('[email protected]', 'My timesheet', message)
  Logger.log(message)
}