Holidays play an influential role in the operation of a business–from scheduling projects to planning marketing campaigns. They are important dates in the calendar of people in business–if not those who run, then those who make it run.
That’s why it’s crucial to have a list of all holidays in a spreadsheet so that deadlines are not missed because a holiday interrupted them, or, more importantly, that a holiday is not interrupted because of a deadline. This can be avoided if we have the list of holidays alongside where we manage our projects.
That’s exactly what this article intends to impart: how to integrate Holiday API with Google Sheets and import holidays list to Google Sheets using Amigo Data, a data integration and automation tool. We can import public holidays data for a country as well as for different states or regions of a country.
Below is a snippet of US holiday list imported into Google Sheets with Holiday API.
Steps to import holidays list from Holiday API to Google Sheets:
- Install the Amigo Data add-on
- Get API key from Holiday API
- Select a country (or region)
- Import the data to Google Sheets
How to import holidays list from Holiday API to Google Sheets
Let’s learn how to get public holidays by country in Google Sheets using Holiday API.
1. Install the Amigo Data add-on
Install the Amigo Data Google Sheets add-on from the Google Workspace Marketplace.
Alternatively, you can get it from Google Sheets.
- Open a new sheet
- Go to Extensions→Add-ons→Get add-ons
- Search for Amigo Data and install
2. Get API key from Holiday API
Go to holidayapi.com and create an account. Click Get Your Free API Key to proceed.
Enter an email and a password, then click Start Developing to create an account.
Upon completion of signing up, you’ll be taken to your account dashboard where the holiday API key can be found. This is the key we’ll use to import holidays to Google Sheets.
3. Select a country for which to retrieve the holidays list
Click Countries at the top menu.
Select or search for the country of your choice–say, United States.
Specify the year for which to get the holiday list in Google Sheets. If you’re on the free plan, change the year from the current to the previous since the free accounts are limited to the previous year’s data.
Then scroll down to the bottom of the page to find the endpoint URL. This endpoint will fetch all the holidays for the specified country–public as well as other holidays observed in the country.
Copy the URL. This endpoint URL will be used to retrieve the list of holidays in the US and import the holidays to Google Sheets.
Note: Though not shown in the URL, the queries are appended. So there’s no need to add the query parameters manually. However, we can add additional parameters to filter the data.
4. Import the data to Google Sheets
Launch Amigo Data from Google Sheets to import holidays list from Holiday API to Google Sheets.
(Extensions→Amigo Data→Connect)
Select Custom API from the list of sources.
Paste the Holiday API endpoint URL in the API URL field.
Click the PREVIEW button to retrieve the holidays and preview the data.
Then flatten and expand the nested datasets to place them in different columns. And delete those that are not necessary.
After they are done, click the IMPORT button.
A snippet of the US holidays imported into Google Sheets with Holiday API.
The above endpoint retrieves all holidays in the US for the specified year. To get the list of holidays for a particular month, we can add an additional parameter.
To do so, append in the URL the following: &month=10
This will return the list of holidays in October.
The complete endpoint URL is as under.
https://holidayapi.com/v1/holidays?pretty&country=us&year=2021&key=your_api_key&month=10To get public holidays in Google Sheets, use the following endpoint URL.
https://holidayapi.com/v1/holidays?pretty&country=us&year=2021&key=your_api_key&publicTo import upcoming holidays to Google Sheets after a particular day, say March 4, 2023, specify the month and date and use the “upcoming” parameter.
https://holidayapi.com/v1/holidays?pretty&country=us&year=2023&key=your_api_key&month=3&day=4&upcomingFor more information on filtering the holiday results with different parameters and how to use the parameters, see the Holiday API documentation.
Some commonly asked questions
How do I create a holiday list in Google Sheets?
Getting a list of holidays in Google Sheets is pretty simple. Just follow the steps below:
- Create a Holiday API account
- Choose the country for which to create a holiday list
- Copy the endpoint URL
- Open Amigo Data and retrieve the holiday list using the custom API connector.
How do I check if a date is a holiday in Google Sheets?
We can use the MATCH and the ISNUMBER functions nested inside the IF function with the date column as the range of the MATCH function to check if a specific date is a holiday. An example of the formula is as under.
=IF(ISNUMBER(MATCH(I3,B:B,0)),"Yes", "No")By entering a date in cell I3, the formula will return “Yes” if the date is found in the list; otherwise it will return “No”.
How to I get public holidays in Google Sheets?
To fetch public holidays for a country, use the “public” parameter in the endpoint URL. Do not forget also to specify the country and the year for which to get the public holidays list in Google Sheet by using the “country” and “year” parameters.
Some similar articles you may be interested in.
Import Wikipedia Data To Google Sheets