Other fields, Request body, Columns, and Path are optional. In Richard’s case, he specified the following strings: URL query parameters – this field is for query parameters for your requests to the API.Request headers – here, you can apply specific headers to your request, such as Authorization, as Richard did.So, Richard will use those to demonstrate how they work. It lacks attached query strings because Coupler.io provides separate fields to specify request headers and URL query parameters. Note: You’ve noticed that this API URL differs from the one mentioned in the beginning. Insert the API URL to the JSON URL field.Then complete the following steps to load data from API to Excel sheet. Choose JSON as a source application and Microsoft Excel as the destination application.

Click Add new importer – this is a boilerplate to set up your connection to API.

Sign up to Coupler.io with your Microsoft account. You can export data on demand and automate import on a custom schedule, such as every day or every hour.įor Richard’s case, we’ll use the JSON integration with Excel. It’s a solution that allows you to connect to multiple sources and load data from them to Excel, Google Sheets, or BigQuery. How to regularly pull API data into Excelįor scheduled exports of data from API to Microsoft Excel, Richard will use Coupler.io. So, let’s start with the option that allows you to schedule exports via API to Excel. Note: This API requires authorization, so Richard has already generated a free API key. To do this, he needs to load data from CryptoCompare using their JSON API.įor testing, Richard will use the following API URL that will load data about the price for five cryptocoins: Bitcoin, Ethereum, Terra, Binance Coin, and Tether. Richard wants to analyze the growth tendency of the top crypto coins. For this, we asked Richard, a cryptocurrency enthusiast, to help us.

Let’s check out each option in action so that you can make the best choice for your needs.

VBA: This is a code-based option that is suitable for tech-savvy Excel users.

Coupler.io: This third-party importer will let you automate data exports via APIs to Excel on a custom schedule.Power Query: You can query data from APIs using Excel’s built-in tool.Which way to connect API to Excel sheet is the best one? How to use API in Excel – options you haveīasically, you have three options to link API to Excel:

Open json in excel