Import Holidays List From Holiday API To Google Sheets

How to Import Holidays List From Holiday API To Google Sheets
Reading Time: 4 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 the people in the business–if not for those who run, then for 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 it, or, more importantly, that a holiday is not interrupted because of a deadline.

That’s exactly what this article intends to impart: how to import holidays list from Holiday API to Google Sheets using Amigo Data, a Google Sheets extension. We can import public holidays data for a country as well as for different states or regions of a country.

Below are the 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 see how to get public holidays list to Google Sheets…

1. Install the Amigo Data add-on

Install the Amigo Data Google Sheets add-on from the Google Workspace Marketplace. Click here to install.

Amigo Data addon in 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 and create an account. Click Get Your Free API Key to proceed.

Holiday API home page

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

Creating an account

Upon completion of signing up, you’ll be taken to your account dashboard where the API key can be found.

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

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

List of supported countries

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

Then scroll down to the bottom of the page to find the endpoint URL.

The endpoint URL

Copy the URL. This endpoint URL will be used to retrieve the list of holidays in the US.

Note: Though not shown in the URL, the queries are appended to it. So there’s no need to add the query parameters manually.

4. Import the data to Google Sheets

Launch Amigo Data from Google Sheets.
(Extensions→Amigo Data→Connect)

Opening Amigo Data in Google Sheets

Select Custom API from the list of sources.

List of sources in Amigo Data connector

Paste the endpoint URL in the API URL field.

Pasting the URL

Click the PREVIEW button.

The preview button

Then flatten and expand the nested dataset 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 imported data.

A snippet of the imported data

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 retrieve only public or national holidays, use the following endpoint URL.

For more information on customising the URL and the parameters available, see the developer 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:

  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.

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

Some similar articles you may be interested in.