Import Holidays List From Holiday API To Google Sheets

How to Import Holidays List From Holiday API To Google Sheets
Reading Time: 5 minutes

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.

Snippet of holiday list imported into Google Sheets

Steps to import holidays list from Holiday API to Google Sheets:

  1. Install the Amigo Data add-on
  2. Get API key from Holiday API
  3. Select a country (or region)
  4. 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.

Amigo Data addon in Google Workspace Marketplace to import holidays list to Google Sheets

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.

Go to Holiday API to import holidays list to Google Sheets

Enter an email and a password, then click Start Developing to create an account.

Creating an account to import holidays list to Google Sheets

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.

The API key to import holidays list from Holiday API to Google Sheets

3. Select a country for which to retrieve the holidays list

Click Countries at the top menu.

Navigating to the country list to import holidays list to Google Sheets

Select or search for the country of your choice–say, United States.

List of supported countries that we can import holidays list to Google Sheets

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.

Holiday page for the US to import US holidays list to Google Sheets

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.

The endpoint URL to import holidays list to Google Sheets

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)

Opening Amigo Data in Google Sheets to import holidays list from Holiday API to Google Sheets

Select Custom API from the list of sources.

List of sources in Amigo Data connector

Paste the Holiday API endpoint URL in the API URL field.

Paste the endpoint of the holidays liist to import to Google Sheets

Click the PREVIEW button to retrieve the holidays and preview the data.

Preview the holidays list to import to Google Sheets

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.

Preview of the holiday list to import to Google Sheets

A snippet of the US holidays imported into Google Sheets with Holiday API.

A snippet of the holidays list imported into Google Sheets

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.

To get public holidays in Google Sheets, use the following endpoint URL.

To import upcoming holidays to Google Sheets after a particular day, say March 4, 2023, specify the month and date and use the “upcoming” parameter.

For 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

Getting a list of holidays in Google Sheets is pretty simple. Just follow the steps below:

  1. Create a Holiday API account
  2. Choose the country for which to create a holiday list
  3. Copy the endpoint URL
  4. Open Amigo Data and retrieve the holiday list using the custom API connector.

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”.

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

Import IMDb Data To Google Sheets

Import Nobel Prize Data to Google Sheets

Leave a Reply