Update regional holiday calendar

[IT Support]

Looking for some help with the following use case.

We currently setup shared Regional Holiday Calendars within the organization.

For each employee, using our HR system we determined where each employee region is. We created a Google Sheet to list employee name, email region, and calendar ID for their respective regional calendar.

Using Google Apps Script to Manage Calendar Entries

1. Use Google Apps Script to automate the process of adding employees' names to the correct holiday calendar based on their region:
   1. Script Setup: Write a script that runs daily or as often as needed.
   2. Fetch Employee Data: Your script should fetch employee details from your a Google Sheet where employee details and their regions are listed.
   3. Create Calendar Events: Based on the employee’s region, the script should add an all-day event to the respective regional holiday calendar with the employee's name as part of the event title or description.

We've started with this script but keep running into the same error which we thought was permissions but after making sure that the calendar was added to the test users we still get the same error (also the calendar and google IDs were removed from the code but the have been verified to be correct)

function updateHolidayCalendars() {

var employees = SpreadsheetApp.openById('GoogleSheetID').getSheets()[0].getDataRange().getValues();

employees.forEach(function(employee) {

var userEmail = employee[0]; // Column with email addresses

var region = employee[1]; // Column with region information

var calendarId = getCalendarIdByRegion(region); // Function to get the right calendar ID based on region

var holidays = CalendarApp.getCalendarById(calendarId).getEventsForDay(new Date());

holidays.forEach(function(holiday) {

var event = CalendarApp.getCalendarById(calendarId).createEvent(

holiday.getTitle() + ' - ' + employee[2], // Assuming employee name is in third column

holiday.getStartTime(),

holiday.getEndTime(),

{description: 'Holiday observed by: ' + userEmail}

);

});

});

}

function getCalendarIdByRegion(region) {

var calendarIds = {

'US': 'testcalendarID',

};

return calendarIds[region];

}

Error

TypeError: Cannot read properties of null (reading 'getEventsForDay')

[olivercre...@gmail.com]

Since you already double checked the calendar IDs, maybe the region is the issue here. If the region doesn't exactly match one of the keys of the calendarIds object in your getCalendarIdByRegion function, then that function will return undefined. 

Try double checking that the regions in your spreadsheet exactly match the keys of your object, and that they don't have any trailing or leading spaces. Using your sample code as an example, make sure the region in your spreadsheet is exactly "US" and not "US " (note the trailing space).

[IT Support]

Okay. I doubled checked the trailing spaces and there were none but I removed the first column labels for email region name etc which resolved the error.

However, after reviewing the shared calendar there was no affective change after the script ran.

[olivercre...@gmail.com]

Nice. Glad you were able to resolve the error.

Regarding your second point about no noticeable changes, the code you shared is working off of today's events. Are there any events on the shared calendar for today?

1. • var holidays = CalendarApp.getCalendarById(calendarId).getEventsForDay(new Date())

[IT Support]

This is a valid point there is nothing for today, this is where I need some help.

Ideally I'd like the script to go through the regional holiday calendar for the year and create all day events for each user assigned to that region. So if there is a Fourth of July event in the US Regional Calendar it will create the all day event for all users assigned to that region, if that makes sense.

[olivercre...@gmail.com]

So if you have 2 employees based in the US, the end result would be that the US Regional Calendar will have 3 all day events on the Fourth of July:

1. Fourth of July (original)
2. Fourth of July - Employee #1
3. Fourth of July - Employee #2

Is that right?

[IT Support]

Yes this is correct, and would apply to any other holiday within the US Holiday calendar.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/bnfSoAGn-xA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3d0e14c0-7e55-4ae1-ba7f-188b464c4b76n%40googlegroups.com.

[olivercre...@gmail.com]

Okay. To create the all-day events, we should get every holiday event for the year, not just one day. Once we have all the holidays we can create all-day events for an employee.

1) Get every holiday for the year:
To get all the events for 2024, we can use the getEvents method then filter the result to just select the holidays. To identify an event as a holiday, we can make sure that holidays have some common set of characteristics that other events on the calendar don't share. For example, if we make sure every holiday is an all-day event with the description, "This is a holiday", the code might look like this:

• var holidays = CalendarApp.getCalendarById(calendarId)
      .getEvents(new Date('Jan 1, 2024'), new Date('Jan 1, 2025')) // get all events in 2024
      .filter(event => event.isAllDayEvent() && event.getDescription() === 'This is a holiday'); // only get events that are holidays

2) Create all-day events for an employee

Once you have the holidays, you can use a forEach loop to create events for an employee. Be sure to differentiate the employee events from the holiday events.

• holidays.forEach(function(holiday) {
        CalendarApp.getCalendarById(calendarId).createAllDayEvent(

• 
          holiday.getTitle() + ' - ' + employee[2], // Assuming employee name is in third column
  

•         holiday.getAllDayStartDate(),
          holiday.getAllDayEndDate(),
          {description: 'Holiday observed by: ' + userEmail} // description is not "This is a holiday" so it will not count as a holiday in this script
        );
    });

Running your script with these updates, should create annual events for each employee. That said, be sure to test the code out before applying it to your actual calendars.

Some notes:

• You can choose whatever criteria works for you to identify a holiday in your script. Just update the filter code and event creation code accordingly.
• If you will run this script more than once, you may want to add some logic to skip employees who already have all-day holiday events to avoid creating duplicates.
• The above script will only create employee events for 2024. To create events for other years, you can modify the dates used in the getEvents method. You can also try looking into the createEventSeries or the createAllDayEventSeries methods.
• Instead of creating events for each employee, have you considered inviting employees to the existing holiday events and/or sharing the relevant holiday calendar with them? It might be a more straightforward approach & you can still tell which employees observe the holiday by referencing the guest list on the event, or the share list on the calendar.