Как подключить api к excel

How to use Excel VBA to query REST JSON APIs

Excel is probably one of the most used tools in this world, so the demand for integrations with extremely complex spreadsheets is a recurring scenario. APIs allow easy access to information in systems, which is becoming more and more standard in the market, with that in mind, some demands of connecting to systems via API in Excel are necessary and very useful, so I decided to share a little how I created this integration. Let's learn how to query Rest APIs using VBA and convert the result to JSON for use in the spreadsheet.

This article hopes you will know the basics of Excel and VBA, as well as what is an API and how it works . Our goal will be to consult a Public Pokemon API and list the result in the tab results .

Creating a blank worksheet

First let's create a blank sheet with macro enabled, inside it I'll create a tab called results .

Creating the macro to query the API

by shortcut alt + f11 let's open the Excel macro editor, and create a module called list pokemons .

Importing the VBA-JSON library

As the API we're going to query returns a JSON as an answer we will need to import the library VBA JSON , it will take care of all the boring work of translating the JSON and returning as an array and object. Installation is very simple, just download the latest version here and in the macro editor go to File > Import File > JsonConverter.bas .

Enabling Microsoft Scripting Runtime

We also need to enable Microsoft Scripting Runtime, to do this just browse Tools > References and search and enable in the list the Microsoft Scripting Runtime .

Creating the VBA macro to query the REST API

Below is the complete code for our request, it might sound scary, but don't worry, I'll explain what each part is doing:

First we define all the variables that we will use in our scripts, including the VBA JSON library import that we previously imported into our project.

Then we select the spreadsheet we want to display the results of the API query, in our case Worksheets("results") and then we create an object that will allow us to make the request to the API. https://pokeapi.co/api/v2/pokemon . We'll take the answer and put it in the variable json , for now it is nothing more than a text.

Here where the magic happens, the function parsejson from the VBA JSON library converts the text of our variable json for an accessible object in our script. We are now able to access all properties programmatically in our code.

Now that we have the result of our API accessible, we create in the first row of the spreadsheet the header containing the columns Name and link .

We are interested in the property. results , an array containing a list of pokemons with their names and links to more details. We will access this matrix at jsonobject("results") and we will loop to display each pokemon result in a new row of our table.

If everything goes as expected, by pressing f5 to run our macro, in your spreadsheet you should see the following result:

Conclusion

This was a very simple example of a query, with the object of HT TP it is possible to perform all types of requests, GET, POST, UPDATE, … The interesting thing is to understand how the request is made and how you can display the result, thanks to the VBA JSON library, which already drastically reduces the work required. Now you just need to adapt this flow and script to your needs.

Connect API to Excel On A Schedule With No Code

An API is a ‘bridge‘ through which one app obtains/sends information from/to another app. This means that you can load miscellaneous data from your source app to your Excel workbook using the REST API. Moreover, you can even schedule data loads via the API to Excel sheet without any VBA coding! Read our guide to learn how you can do this.

How to use API in Excel – options you have

Basically, you have three options to link API to Excel:

• Power Query: You can query data from APIs using Excel’s built-in tool.
• Coupler.io: This third-party importer will let you automate data exports via APIs to Excel on a custom schedule.
• VBA: This is a code-based option that is suitable for tech-savvy Excel users.

Let’s check out each option in action so that you can make the best choice for your needs. For this, we asked Richard, a cryptocurrency enthusiast, to help us.

Richard wants to analyze the growth tendency of the top crypto coins. To do this, he needs to load data from CryptoCompare using their JSON API.

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

Note: This API requires authorization, so Richard has already generated a free API key.

So, let’s start with the option that allows you to schedule exports via API to Excel.

How to regularly pull API data into Excel

For scheduled exports of data from API to Microsoft Excel, Richard will use Coupler.io. It’s a solution that allows you to connect to multiple sources and load data from them to Excel, Google Sheets, or BigQuery. You can export data on demand and automate import on a custom schedule, such as every day or every hour.

For Richard’s case, we’ll use the JSON integration with Excel. Let’s see how it works.

The instructions provided in this article are sufficient for creating an automated integration. But please note that we are not responsible for the outcome if you use the instructions incorrectly, specify incorrect settings, or if there are changes in the app’s API.

If you need help with the setup or want to have a more user-friendly version of this connector, we can develop this premium integration for you for free. Just select the app you need from the list and follow the instructions on requesting the automated integration.

Excel API example with Coupler.io

Sign up to Coupler.io with your Microsoft account. Click Add new importer – this is a boilerplate to set up your connection to API. Choose JSON as a source application and Microsoft Excel as the destination application. Then complete the following steps to load data from API to Excel sheet.

Source

• Insert the API URL to the JSON URL field. Click Continue.

Note: You’ve noticed that this API URL differs from the one mentioned in the beginning. It lacks attached query strings because Coupler.io provides separate fields to specify request headers and URL query parameters. So, Richard will use those to demonstrate how they work.

• Request headers – here, you can apply specific headers to your request, such as Authorization, as Richard did.

• URL query parameters – this field is for query parameters for your requests to the API. In Richard’s case, he specified the following strings:

Other fields, Request body, Columns, and Path are optional. You can learn more about them in the Coupler.io Help Center.

Note: You can find information about the parameters used in those fields in the API documentation of your application. Pay attention to the information about API authentication and details of API endpoints – these are usually located in separate sections.

Now you’re ready to Jump to the Destination Settings.

Destination

• Select a workbook on OneDrive and a worksheet where the data will be loaded. You can select an existing Excel worksheet or create a new one. Click Continue.

Note: Optionally, you can change the first cell for your imported data range, change the import mode, and add a column that contains the date and time of the last update.

Clicking on Save and Run loads the data from API to Excel right away. But Richard needs to automate this export on a recurring basis, so he needs to complete another step.

How to get data from API in Excel recurrently

You can configure the schedule for your JSON importer during the initial setup or at any other time. For this, you need to toggle on the Automatic data refresh and configure the frequency:

• Interval – from every month to every 15 minutes.
• Days of week – select the days you want to have the importer run.
• Time preferences – select the time when you want to have your data importer from the API.
• Schedule time zone – do not forget to specify the time zone for your dataflow.

That’s it! Let’s check out the results of the records pulled from API to Excel sheet?

The imported data does not look very neat. Nevertheless, you can create a separate sheet where the data will be transformed to the format you want and link Excel sheets. Therefore, every time Coupler.io refreshes your data, it will be automatically refreshed on the linked sheet.

Connect API to Excel with Power Query

Power Query is a data transformation engine within Excel. As well, it allows you to query data from different sources including REST APIs. Here is what you need to do to link API to Excel.

• In your Excel workbook, go to Data => New Query => From Other Sources => From Web.

• Insert the API URL and click OK.

• Choose the web content access. For this example, we can go with Anonymous. Click Connect.

• The Power Query editor will open where you can preview the data loaded from API. Go to the Convert tab and click Into Table.

• This will convert the data from API to a table, and you’ll be able to transform it before loading it to the Excel workbook, for example, transposing rows to columns or vice versa.
• You see that values in the Value column are hidden. The reason is that you need to select which columns to display. For this, click on the Expand button and select the columns. Click OK.

• Now you see two columns: Value.USD and Value.EUR. When all the necessary transformations are done, click Close & Load.

You can also check out the details of your connection.

Can you link API to Excel on a schedule with PowerQuery?

You can refresh data from the API on demand or set up a frequency for the automatic refresh. For this, go to Data => Connections => Properties… and configure the Refresh control.

The Power Query setup looks somewhat intricate because the main purpose of this tool is to transform data. So, it’s better to use Power Query in combination with Coupler.io – your data will go to Excel on a schedule where you can transform it with Power Query as you wish.

How to connect API to Excel using VBA

The answer is very simple – you need to learn VBA and implement your coding knowledge. �� The benefit of a code-based solution is that you can make it custom. This is achievable if you’re proficient in coding and specifically VBA. Alternatively, you can find some templates on GitHub or somewhere else and tweak them. But it is unlikely that you will find a code sample that will cover all your needs.

Which way to connect API to Excel sheet is the best one?

The no-code way is the choice of many, including Richard. For his needs with low data volume and infrequent data refresh, he can use the Coupler.io Personal plan. In addition to the option of connecting API to Excel sheet, you can benefit from many ready-to-use integrations, as well as other destination apps – Google Sheets and BigQuery. You can try out Coupler.io for free with a 14-day trial.

Streamline your data analytics & reporting with Coupler.io!

Coupler.io is an all-in-one data analytics and automation platform designed to close the gap between getting data and using its full potential. Gather, transform, understand, and act on data to make better decisions and drive your business forward!

• Save hours of your time on data analytics by integrating business applications with data warehouses, data visualization tools, or spreadsheets. Enjoy 200+ available integrations!
• Preview, transform, and filter your data before sending it to the destination. Get excited about how easy data analytics can be.
• Access data that is always up to date by enabling refreshing data on a schedule as often as every 15 minutes.
• Visualize your data by loading it to BI tools or exporting it directly to Looker Studio. Making data-driven decisions has never been easier.
• Easily track and improve your business metrics by creating live dashboards on your own or with the help of our experts.

Try Coupler.io today at no cost with a 14-day free trial (no credit card required), and join 700,000+ happy users to accelerate growth with data-driven decisions.

The built-in PowerQuery functionality seems to be the actionable option to link API to Excel. It allows you to grab data and transform it on the go. However, the main drawback of PowerQuery is that it’s only available for the Excel desktop app. If this is not an issue for you, you can go with it.

Who’s going to choose the VBA script to connect API to Excel? This is the way for tech-savvy Excel experts who prefer to create custom functions and integrations for their liking. If you are one of those, then it’s probably your way as well.

Как подключить API к Excel: пошаговое руководство

Excel — это один из самых популярных инструментов для работы с данными, и многие пользователи хотят узнать, как подключить API к Excel. В данной статье мы рассмотрим пошаговую инструкцию по подключению API к Excel и сможем научиться извлекать данные из внешних сервисов прямо в Excel.

API (Application Programming Interface) — это набор методов и процедур, которые используются разработчиками для взаимодействия с программой или сервисом. Подключение API к Excel позволяет получать данные из различных сервисов, таких как социальные сети, погодные сервисы, финансовые платформы и другие, и использовать эти данные для анализа и отчетности.

Первый шаг в подключении API к Excel — это получить ключ API от сервиса, с которым вы хотите работать. Этот ключ используется для идентификации вашего приложения и обеспечивает безопасность передачи данных. Ключ обычно предоставляется после регистрации вашего приложения на сайте сервиса.

Примечание: Некоторые сервисы требуют авторизации с помощью OAuth или других протоколов. В таком случае вам потребуется создать приложение и получить токены доступа для аутентификации.

Шаг 1: Регистрация веб-приложения

1. Посетите сайт https://console.cloud.google.com и войдите в свою учетную запись Google.
2. Создайте новый проект с помощью кнопки «Создать проект».
3. Войдите в созданный проект и перейдите на вкладку «API и сервисы» в боковом меню.
4. Нажмите на кнопку «Включить API и сервисы» и найдите необходимые вам API (например, Google Sheets API или любой другой API, с которым вы хотите работать).
5. Включите выбранное API и нажмите кнопку «Создать учетные данные».
6. Выберите тип учетных данных «Ключ API» и нажмите «Создать».
7. Скопируйте сгенерированный ключ API. Этот ключ будет использоваться для доступа к API из Excel.

После завершения этого шага у вас будет зарегистрировано веб-приложение и сгенерирован ключ API, который используется для авторизации Excel при подключении к API.

Шаг 2: Получение ключа API

Для подключения API к Excel необходимо получить ключ, который будет использоваться для аутентификации при запросах к сервису.

1. Перейдите на веб-сайт, предоставляющий нужный вам API.
2. Зарегистрируйтесь на этом веб-сайте и создайте новый аккаунт, если у вас его еще нет.
3. Войдите в свой аккаунт.
4. Перейдите в настройки профиля или настройки API.
5. Найдите раздел, относящийся к получению ключа API.
6. Следуйте инструкциям по генерации ключа API.
7. Скопируйте полученный ключ с сохранением его в надежном месте.

После получения ключа API вы будете готовы к шагу 3: настройке подключения к Excel.

Шаг 3: Установка дополнения Power Query

Чтобы установить дополнение Power Query, выполните следующие шаги:

1. Откройте программу Excel и выберите вкладку «Файл».
2. В меню выберите «Параметры».
3. В открывшемся окне выберите «Надстройки».
4. Нажмите на кнопку «Веб-дополнения».
5. В поисковой строке введите «Power Query» и нажмите «Ввод».
6. Выберите дополнение «Power Query для Excel» и нажмите «Добавить».
7. Подтвердите установку и дождитесь завершения процесса.
8. После установки дополнения Power Query перезапустите программу Excel.

Теперь у вас есть все необходимые инструменты для подключения API к Excel. Перейдем к следующему шагу.

Шаг 4: Создание нового подключения к данным

1. Откройте файл Excel, к которому хотите добавить подключение к API.

2. В меню выберите вкладку «Данные», затем в разделе «Получение внешних данных» нажмите на кнопку «Источники данных».

3. В появившемся окне выберите тип источника данных – «Из Интернета» или «Из веб-службы», в зависимости от специфики вашего API.

4. Введите URL-адрес вашего API в поле «Адрес службы» или «URL-адрес».

5. Нажмите на кнопку «Далее» и укажите необходимые параметры подключения к API: метод передачи данных (GET или POST), формат данных (XML или JSON) и т.д.

6. Если для доступа к API требуется аутентификация, введите соответствующие учетные данные (логин, пароль) в соответствующих полях.

7. Нажмите на кнопку «Готово» для завершения создания подключения к API.

8. Данные из API будут загружены в Excel и автоматически обновляться при обновлении подключения или вручную по вашему желанию.

9. Для изменения параметров подключения или удаления подключения к API можно воспользоваться разделом «Связанные параметры» в меню «Источники данных».

Шаг	Описание
1	Откройте файл Excel, к которому хотите добавить подключение к API.
2	В меню выберите вкладку «Данные», затем в разделе «Получение внешних данных» нажмите на кнопку «Источники данных».
3	В появившемся окне выберите тип источника данных – «Из Интернета» или «Из веб-службы», в зависимости от специфики вашего API.
4	Введите URL-адрес вашего API в поле «Адрес службы» или «URL-адрес».
5	Нажмите на кнопку «Далее» и укажите необходимые параметры подключения к API: метод передачи данных (GET или POST), формат данных (XML или JSON) и т.д.
6	Если для доступа к API требуется аутентификация, введите соответствующие учетные данные (логин, пароль) в соответствующих полях.
7	Нажмите на кнопку «Готово» для завершения создания подключения к API.
8	Данные из API будут загружены в Excel и автоматически обновляться при обновлении подключения или вручную по вашему желанию.
9	Для изменения параметров подключения или удаления подключения к API можно воспользоваться разделом «Связанные параметры» в меню «Источники данных».

Шаг 5: Вставка URL-адреса API

1. Откройте Excel и выберите лист, на котором вы хотите импортировать данные из API.

2. Выберите ячейку, в которой будет располагаться первое значение из API.

3. Вставьте URL-адрес API, начиная с символа равно (=), в формулу вверху окна Excel.

Например, если URL-адрес API выглядит так: https://api.example.com/data, тогда формулу нужно будет записать так: =https://api.example.com/data.

4. Нажмите клавишу Enter, чтобы выполнить формулу и загрузить данные из API.

Excel отправит запрос по указанному URL-адресу API и примет данные, если они доступны. Вы можете заметить, что Excel автоматически обновляет данные каждый раз, когда открываете файл или вручную обновляете страницу.

Если Excel не может подключиться к указанному URL-адресу API, убедитесь, что ссылка правильно написана и API доступно.

API Integration: How to Integrate Web API to Excel

API integration into Excel has countless benefits, such as streamlining your data management processes. However, some organizations have a hard time seamlessly integrating the APIs. This article, therefore, goes through how you can get started with a step-by-step demonstration of API data integration .

Table of Contents

What Is API Integration?

API integration refers to the process of integrating and connecting several software applications. An API (Application Programming Interface) serves as a middleman to facilitate data sharing and communication between various systems.

Why Is API integration useful for data analysis?

There are several reasons why API integrations are highly useful for data analysis. The following goes through some of them.

• Provides access to a variety of data sources, such as external systems, databases, cloud services, and web APIs, for data analysts.
• Data updates can be obtained by analysts in real-time or nearly real-time. This, therefore, guarantees that the analysis is founded on the most recent data available.
• Automating the data retrieval process eliminates the need for manual data entry and import/export processes.
• API integration tools allow analysts to access in a structured format, making it simpler to combine and alter the data.
• API integration platforms enable the integration of external services like machine learning application programming interfaces or data enrichment tools.
• Offers flexibility and scalability for data analysis. As their analytical needs change, analysts can quickly add additional APIs or connect to new data sources using an API integration platform.

What Are the Different Ways to Integrate APIs Into Excel?

Many approaches and levels of flexibility are available for integrating APIs into Excel. The following goes through 3 common approaches.

1. Importing data into Excel using Power Query.

Power Query is a built-in function of Excel. It lets users import data from various sources, such as APIs. This article will go through only this method. This method offers a simple and intuitive way of connecting to APIs without the requirement for programming knowledge. Users may access and import single API integration into Excel with only a few clicks, making it available to a broader range of consumers.

1. Using Visual Basic for Applications (VBA).

The programming language VBA is built into Excel and enables users to make macros and unique features.

With VBA, users may create programs that communicate with APIs, make HTTP requests, and get data into Excel. In order to develop the VBA code, users can enable the Developer tab in Excel, enter the Visual Basic Editor, and build a new module.

VBA is appropriate for complex or unique API installations since it gives users more freedom and control over the API integration process.

1. Making use of Add-ins.

Add-ins are third-party programs or add-ons that extend Excel’s capabilities and provide pre-made middleware integration tools.

Examples such as Apipheny enables importing JSON or CSV data using straightforward formulas, Blockspring offers a menu of pre-built API connections, and DataNitro enables users to access APIs using Python code within Excel cells.

Users can create the API connection, install the required add-in, and follow the API documentation in order to import data directly into Excel.

What You Need to Integrate Web API to Excel

A few prerequisites must be met to integrate a Web API with Excel. The following is a list of each requirement, along with instructions on acquiring or producing them using the Fixer API as an example. Fixer API is a web API that provides currency exchange rates.

1. Have a stable internet connection to establish communication between Excel and the API server.
2. Obtain a valid API key. You can sign up for a free or paid plan on APILayer or directly from Fixer API’s website and get your API key once you sign up. However, it’s important to know that Fixer paid plans support SSL encryption and the free plan does not support SSL encryption. In this article, I am using the API key from a pain plan. However, the steps are very same even if you use a free plan except that you need to use http in the free plan while paid plan users can use https.

1. You should have an API endpoint that identifies the data source and the query parameters. For example, if you wanted to use the Fixer API to get the most recent exchange rates for USD, EUR, and GBP, your URL or endpoint would look like this: https://data.fixer.io/api/latest?access_key=YOUR_API_KEY&symbols=USD,EUR,GBP,AUD,CAD
   Again, note that you need to use HTTP if you are using the Fixer free plan. Learn more about Fixer API in our comprehensive documentation.
2. Make sure you have a JSON or XML parser. Fixer API mostly offers data in the JSON format for data interchange, so you don’t have to get a parser separately. Excel also has built-in features that can parse JSON and XML data.

How to Integrate Web API Into Excel?

As we mentioned earlier, we will be going through importing data into Excel using Power Query in the following section.

The Step-By-Step Process of Integrating Web API Into Excel

• Go to the Data tab in Excel and click on Get Data. Then select From Other Sources > From Web or simply Data > From Web.

• Paste the URL or endpoint of the Web API you want to use and click OK. In our case, we will be pasting this: https://data.fixer.io/api/latest?access_key=YOUR_API_KEY&symbols=USD,EUR,GBP,AUD,CAD
  
• Excel will load your data into a query editor window.
• Select Into Table on the Convert tab. This will convert the data into a table format that Excel can work with.

• Select the Value column and right-click on it. Then select Drill Down. This will expand the data into separate columns for each currency.

• Click on Into Table option again.

• Click Close & Load on the Home tab. This will load the data into a worksheet in Excel.

Data Manipulation and Filtering With Web API Integration

So, we just integrated the API; now what? One of the powerful capabilities of integrating Web API into Excel is the ability to perform advanced data manipulations and filtering.

Data cleansing is a common process where you can eliminate duplicates, fix formatting errors, and handle missing values. The API data can be integrated with Excel’s functions and formulae to complete complex data-cleaning processes.

You can also use filtering methods to concentrate on particular data subsets. You can extract particular records using Excel’s filtering features based on criteria like date ranges, numerical conditions, or text matching. This makes it possible to study particular API data segments without manual sorting or filtering.

On top of that, you may do calculations, aggregations, and data transformations on the API data using Excel’s sophisticated functions and formulae. This involves, among other things, tricky mathematical operations, conditional calculations, and string manipulations. Thanks to these features, you can gain fresh insights and produce personalized estimates that are catered to your particular requirements.

It’s important to remember that the precise data processing and filtering methods will rely on the structure and presentation of the data that the Web API returns. You’ll be able to maximize the potential of your integrated data if you comprehend the data format of the API and use Excel’s features.

Why Should You Use APIs From APILayer for Your API Integration?

It’s evident enough that API integrations are very useful. However, to carry out API integrations, finding a good API should also be easy. This is why you should consider an API marketplace like APILayer.

With APILayer, you can access a dependable and feature-rich platform that makes integration easier and guarantees seamless data transfer between your systems and outside APIs. Their APIs provide thorough documentation, support, and various functionalities, allowing you to improve data analysis, automate operations, and increase productivity. You can streamline your API integration processes and concentrate on using the data to support the growth of your organization by using APILayer’s APIs.

FAQs

What Is API Integration?

Connecting software systems through APIs for data and functionality exchange.

What Is a Popular API Integration Solution?

Excel is a popular solution that allows us to integrate APIs easily.

What Are Common Challenges in API Integration?

Common API integration challenges include authentication and security issues, data mapping and transformation complexities, and much more.

What Are the Benefits of API Integration?

Several benefits include improved efficiency, real-time data sync, scalability, third-party integration, and access to external services. Moreover, these advantages offer valuable opportunities for businesses to enhance their operations.