How to get Holidays of countries from outlook calendar and populate them in the cmn_schedule table.

[Shuvam Barman]

I want to get holidays list of Austria, Germany and Spain and then populate them in the cmn_schedule table of servicenow. Is there any automated way of doing that using rest integration or other way?

[KKM]

Hi Shuvam,

Yes, you can automate the process of fetching holidays for Austria, Germany, and Spain and populating them in the cmn_schedule table of ServiceNow using REST integration. Here’s how you can do it:

1. Fetch Holiday Data
You can use public holiday APIs like:

Nager.Date API
Abstract API
These APIs provide country-specific holiday lists in JSON format.

Example API Request (Nager.Date)

GET https://date.nager.at/api/v2/PublicHolidays/2024/DE
(Change 2024 to the desired year and DE to AT for Austria, ES for Spain.)

Sample JSON Response

[
{
"date": "2024-01-01",
"localName": "Neujahr",
"name": "New Year's Day",
"countryCode": "DE",
"fixed": true
}
]

2. Store Data in ServiceNow (cmn_schedule)
Once you fetch the holiday list, you can insert records into ServiceNow via its REST API or a Scripted REST API Integration.

ServiceNow Table API to Insert Holiday
Use the ServiceNow Table API to create entries in cmn_schedule.

POST Request to ServiceNow:

POST https://your-instance.service-now.com/api/now/table/cmn_schedule
Headers:
- Content-Type: application/json
- Authorization: Basic <Base64-encoded-credentials>
Body:
{
"name": "Neujahr",
"schedule_type": "holiday",
"time_zone": "Europe/Berlin",
"start_date_time": "2024-01-01 00:00:00",
"end_date_time": "2024-01-01 23:59:59",
"country": "Germany"
}
Repeat this for each holiday.

3. Automate with Scheduled Script
Option 1: Use a ServiceNow Scripted REST API to call the holiday API and insert records.
Option 2: Use an IntegrationHub Flow with an HTTP step to pull holiday data and insert it into cmn_schedule.

4. Scheduled Job in ServiceNow (for Auto-Update)
To run this periodically:

Create a Scheduled Script Execution (sysauto_script).
Write a script to fetch data, parse it, and insert records.

Example Scheduled Script

var countryList = ['DE', 'AT', 'ES']; // Germany, Austria, Spain
var year = new Date().getFullYear();
var holidayApi = 'https://date.nager.at/api/v2/PublicHolidays/' + year + '/';

for (var i = 0; i < countryList.length; i++) {
var country = countryList[i];
var request = new sn_ws.RESTMessageV2();
request.setEndpoint(holidayApi + country);
request.setHttpMethod('GET');

var response = request.execute();
var responseBody = response.getBody();
var holidays = JSON.parse(responseBody);

for (var j = 0; j < holidays.length; j++) {
var holiday = holidays[j];
var holidayRecord = new GlideRecord('cmn_schedule');
holidayRecord.initialize();
holidayRecord.name = holiday.localName;
holidayRecord.schedule_type = 'holiday';
holidayRecord.time_zone = 'Europe/' + (country === 'DE' ? 'Berlin' : country === 'AT' ? 'Vienna' : 'Madrid');
holidayRecord.start_date_time = holiday.date + ' 00:00:00';
holidayRecord.end_date_time = holiday.date + ' 23:59:59';
holidayRecord.insert();
}
}

Summary
Use a public Holiday API to get holidays.
Use ServiceNow REST API or Scripted REST API to insert into cmn_schedule.
Automate with a Scheduled Script in ServiceNow.

Kindly mark it as "Accepted Solution"/"helpful", as it resolves your query. Please press like button for the resolution provided.

With Regards,
Krishna Kumar M - Talk with AIT3ch
LinkedIn: https://www.linkedin.com/in/mkrishnak4/
YouTube: https://www.youtube.com/@KrishAIT3CH
Topmate: https://topmate.io/mkrishnak4 [ Connect for 1-1 Session]

[Shuvam Barman]

Hi @KKM 

I have already tried with Nager.Date API and Google Calendar API and I am able to get the holiday list. 

I specifically wanted to know if there is any way to get the holiday list from Outlook calendar or using Microsoft Graph API and then populate that in the table.

[KKM]

Hi Shuvam,

Yes, you can use the Microsoft Graph API to fetch holidays from an Outlook calendar and then populate the cmn_schedule table in ServiceNow. Here's how you can do it:

1. Get Holiday List from Outlook Calendar Using Microsoft Graph API
Microsoft Graph API allows you to access Outlook calendar events, including holidays.

Step 1: Register an App in Azure AD
To use the Graph API, you need an Azure AD application with permissions to access Outlook calendar.

Go to Azure Portal.
Register a new app in Azure Active Directory (AAD).
Assign the Calendars.Read permission (Delegated or Application).
Generate a client secret for authentication.
Step 2: Get an Access Token
Use OAuth 2.0 to get an access token.

Token Request (Client Credentials Grant)

POST https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token
Content-Type: application/x-www-form-urlencoded

client_id={client_id}
&client_secret={client_secret}
&scope=https://graph.microsoft.com/.default
&grant_type=client_credentials
This will return an access token, which you'll use in API requests.

2. Fetch Holidays from Outlook Calendar
Once authenticated, you can fetch calendar events using:

Graph API Request to Get Outlook Calendar Holidays

GET https://graph.microsoft.com/v1.0/me/calendar/events
Authorization: Bearer {access_token}
Optional: Filter Holidays
If holidays are in a specific calendar, filter using:

GET https://graph.microsoft.com/v1.0/me/calendars/{calendar_id}/events
Sample JSON Response

{
"value": [
{
"subject": "New Year's Day",
"start": { "dateTime": "2024-01-01T00:00:00", "timeZone": "Europe/Berlin" },
"end": { "dateTime": "2024-01-01T23:59:59", "timeZone": "Europe/Berlin" }
}
]
}

3. Insert Holiday Data into ServiceNow (cmn_schedule)
Once the holiday list is retrieved, insert it into ServiceNow.

Step 1: Call ServiceNow API to Insert Data
Use the ServiceNow Table API.

POST Request to cmn_schedule

POST https://your-instance.service-now.com/api/now/table/cmn_schedule
Headers:
- Content-Type: application/json
- Authorization: Basic <Base64-encoded-credentials>
Body:
{
"name": "New Year's Day",
"schedule_type": "holiday",
"time_zone": "Europe/Berlin",
"start_date_time": "2024-01-01 00:00:00",
"end_date_time": "2024-01-01 23:59:59"
}

4. Automate with a ServiceNow Script
You can automate fetching holidays from Outlook and inserting them into ServiceNow using a Scheduled Script Execution in ServiceNow.

Example Scripted REST Integration

var clientId = 'YOUR_CLIENT_ID';
var clientSecret = 'YOUR_CLIENT_SECRET';
var tenantId = 'YOUR_TENANT_ID';

// Get Access Token from Microsoft Graph API
var tokenRequest = new sn_ws.RESTMessageV2();
tokenRequest.setEndpoint('https://login.microsoftonline.com/' + tenantId + '/oauth2/v2.0/token');
tokenRequest.setHttpMethod('POST');
tokenRequest.setRequestBody('client_id=' + clientId +
'&client_secret=' + clientSecret +
'&scope=https://graph.microsoft.com/.default' +
'&grant_type=client_credentials');

var tokenResponse = tokenRequest.execute();
var tokenData = JSON.parse(tokenResponse.getBody());
var accessToken = tokenData.access_token;

// Fetch Outlook Holidays from Calendar
var graphApiUrl = 'https://graph.microsoft.com/v1.0/me/calendar/events';
var eventRequest = new sn_ws.RESTMessageV2();
eventRequest.setEndpoint(graphApiUrl);
eventRequest.setHttpMethod('GET');
eventRequest.setRequestHeader('Authorization', 'Bearer ' + accessToken);

var eventResponse = eventRequest.execute();
var events = JSON.parse(eventResponse.getBody()).value;

// Insert Holidays into cmn_schedule Table
for (var i = 0; i < events.length; i++) {
var event = events[i];
var holiday = new GlideRecord('cmn_schedule');
holiday.initialize();
holiday.name = event.subject;
holiday.schedule_type = 'holiday';
holiday.time_zone = event.start.timeZone;
holiday.start_date_time = event.start.dateTime.replace('T', ' ');
holiday.end_date_time = event.end.dateTime.replace('T', ' ');
holiday.insert();
}

5. Automate with a Scheduled Job
Create a Scheduled Script Execution (sysauto_script).
Set it to run periodically (e.g., every month) to sync holidays.

Summary
* Microsoft Graph API fetches Outlook calendar holidays.
* ServiceNow REST API inserts holidays into cmn_schedule.
* Automated Script schedules periodic sync.

Kindly mark it as "Accepted Solution"/"helpful", as it resolves your query. Please press like button for the resolution provided.

With Regards,
Krishna Kumar M - Talk with AIT3ch
LinkedIn: https://www.linkedin.com/in/mkrishnak4/
YouTube: https://www.youtube.com/@KrishAIT3CH
Topmate: https://topmate.io/mkrishnak4 [ Connect for 1-1 Session]