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

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.

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.

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, we can offer a set of premium integrations at an additional fee.

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.

Как Excel и VBA помогают отправлять тысячи HTTP REST API запросов

Работая в IoT-сфере и плотно взаимодействуя с одним из основных элементов данной концепции технологий – сетевым сервером, столкнулся вот с какой проблемой (задачей): необходимо отправлять много запросов для работы с умными устройствами на сетевой сервер. На сервере был реализован REST API с оболочкой Swagger UI, где из графической оболочки можно было отправлять только разовые запросы. Анализ сторонних клиентов, типа Postman или Insomnia показал, что простого визуального способа поместить в скрипт массив из необходимого перечня идентификаторов устройств (или любых других элементов сервера), для обращения к ним – не нашлось.

Так как большая часть работы с выгрузками и данными была в Excel, то решено было вспомнить навыки, полученные на учебе в университете, и написать скрипт на VBA, который бы мою задачку решал.

получать информацию по устройствам с различными параметрами фильтрации (GET);

применять изменения в конфигурации по устройствам: имя, профиль устройства, сетевые лицензии и пр. (PUT);

отправлять данные для конфигурации и взаимодействия с устройствами (POST).

И сегодня я расскажу вам про то, как с помощью Excel, пары формул и самописных функций на VBA можно реализовать алгоритм, отправляющий любое необходимое количество REST-API запросов с использованием авторизации Bearer Token.

Данная статья будет полезная тем, кто воспользуется данным решением под Windows, но еще больше она будет полезна тем людям, которые хотят использовать данное решение на MacOS (с Excel x64) . Как вы уже догадались, ниже будут рассмотрены два варианта реализации под разные системы, так как с MacOS есть нюанс.

Часть 1. Реализация решения под Windows

GET

Начнем с самого простого: GET – запросов. В данном примере необходимо получить ответ (информацию) от сервера по заданному списку устройств.

Для реализации GET – запросов нам дано:

1) Ссылка, в которой указываются параметры запроса.

2) Заголовки запроса + Токен авторизации (Bearer Token)

—header ‘Accept: application/json’ —header ‘Authorization: Bearer

3) Параметр, указываемый в ссылке (в данном примере это идентификаторы устройств – DevEUI):

Имея такие данные на входе, делаем в Excel лист-шаблон, который заполняем в соответствии с тем, что имеем:

столбец А уходит вот значения параметров

столбец F уходит под ссылку-родителя

столбец H уходит под заголовки, где в ячейке H1 единоразово для текущего листа указывается токен:

=СЦЕП(«—header ‘Accept: application/json’ —header ‘Authorization: Bearer «;$H$1;»‘»)

столбец I уходит под URL (ссылки-дети, на основе ссылки-родителя)

столбец J уходит под результат (ответ от сервера)

Далее, нам необходимо реализовать подпрограмму(макрос) отправки GET-запросов. Состоит она из четырех частей:

цикла, который считает количество строк для работы по листу, пробегая по столбцу А с 2 по первую пустую ячейку, чтобы у цикла был конец.

функции, для работы с REST API (используется стандартная, библиотека Msxml2.XMLHTTP.6.0, встроенная в Windows., поэтому сложностей с реализацией не возникает. Для MacOS есть альтернатива)

временной задержки, в случае если нужно отправлять запросы не сразу, после получения ответа, а задав время ожидания

таймером, который показывает время выполнения всего макроса после завершения

Привязываем подпрограмму к кнопкам для удобства и выполним скрипт. Получается:

Таким образом, скрипт проходит по столбцу I, забирая из значения каждой ячейки URL, для тех строк, где в столбце А есть значения (которые и подставляются в URL). Для удобства также сделаны кнопки очистки полей и подсветка запросов условным форматированием, в случае успешного ответа на запрос.

PUT

Чуть-чуть усложним задачу и перейдем к PUT-запросам. В данном примере необходимо изменить профиль устройства, чтобы сервер по-другому с ним взаимодействовал.

К исходным данным для GET – запроса добавляется тело запроса с ключем-значением (п4). Итого дано:

1) Ссылка, в которой указываются параметры запроса.

2) Заголовки запроса + Токен авторизации (Bearer Token)

—header ‘Content-Type: application/json’ —header ‘Accept: application/json’ —header ‘Authorization: Bearer

3) Параметр, указываемый в ссылке (в данном примере это внутренние идентификаторы устройств – hRef):

4) Тело запроса, с ключом и значением:

Немного дополняем новый PUT-лист в Excel по сравнению с GET (остальное без изменений):

новый столбец B теперь отвечает за ключ deviceProfileId (ячейка B1), а все значения ниже за его возможные значения)

столбец D отвечает за формирование итогового тела сообщения в формате JSON.

Немного поменяем макрос и вынесем его в отдельную подпрограмму:

Привяжем макрос к кнопке и выполним.

Логика абсолютно аналогична GET запросу.

POST

Для POST запросов все аналогично PUT. Только немного меняется код в части типа запроса. В данном примере на устройство отправляется команда-конфигурация с указанием тела посылки (payload_hex) и порта (fport) для конкретного устройства.

Получившаяся таблица выглядит следующим образом:

На этом часть для Windows заканчивается. Здесь все оказалось довольно просто: стандартная библиотека, простенький алгоритм перебора значений в цикле.

Часть 2. Реализация решения под MacOS и Excel 64-bit

В виду того, что работал я на двух машинах под управлением разных ОС, хотелось, чтобы решение было универсальным. В итоге, собрав по крупицам информацию по интернет-форумам с данной тематикой у меня вышло следующее решение. Принцип работы его остается схожим, а изменения были внесены в часть, где использовалась стандартная библиотека WindowsMsxml2.XMLHTTP.6.0, которой в MacOS не было по понятным причинам.

Чтобы обойти данное ограничение, был выбран единственный рабочий подход через cUrl, exec и функции. Данное решение точно работает под версией MacOS 13 и Excel 16.75. Функция ниже, в том или ином виде, встречается на различных форумах, однако на текущих версиях софта – не работает. В итоге, после небольших правок получили рабочий вариант:

Была отлажена функция вызова ExecShell:

И написаны отдельные функции для работы с различным методами GET / PUT / POST, которые на входе принимают URL и параметры):

Так как мы заменяем библиотеку Msxml2.XMLHTTP.6.0 – поменялась реализация макросов в этой части: мы заменили Msxml2 на написанные выше функции и получили следующее:

В итоге, у меня получилось аналогичное windows по работе и функционалу решение для MacOS 13 с использованием Excel 64-bit (версия 16.75).

На просторах интернета я не нашел какого-то сборного и единого описания, только фрагменты кода и подходов, которые в большинстве случаев не работали полностью или частично. Поэтому решил объединить все в рабочее решение и выложить на хабр для истории.

На текущий момент я все еще не встретил иного решения, которое бы позволяло в пару кликов копипастить тысячи идентификаторов и параметров из excel и массово их отправлять на сервер. Надеюсь, кому-то пригодится 🙂

Если такие сторонние решения есть, а я не в курсе, и все можно было сделать проще, быстрее и изящнее – делитесь опытом в комментариях.

Файл-пример, который можно потыкать, пока жив сервер и «бессрочный» токен:

MS Excel REST API Connector

REST API Connector can be used to extract and output JSON/XML/CSV/String data coming from REST API web service calls (Web URL).

Using REST API Connector you will be able to connect, read, and write data from within MS Excel. Follow the steps below to see how we would accomplish that.

Driver mentioned in this article is part of ODBC PowerPack which is a collection of high-performance Drivers for various API data source (i.e. REST API, JSON, XML, CSV, Amazon S3 and many more). Using familiar SQL query language you can make live connections and read/write data from API sources or JSON / XML / CSV Files inside SQL Server (T-SQL) or your favorite Reporting (i.e. Power BI, Tableau, Qlik, SSRS, MicroStrategy, Excel, MS Access), ETL Tools (i.e. Informatica, Talend, Pentaho, SSIS). You can also call our drivers from programming languages such as JAVA, C#, Python, PowerShell etc.

If you are new to ODBC and ZappySys ODBC PowerPack then check the following links to get started.

• ODBC PowerPack — System Requirements and Installation
• Getting Started — Using ODBC PowerPack
• ODBC API Driver Help File

Create ODBC Data Source (DSN) based on ZappySys JSON Driver

If your API is JSON Type and responding the json string response, in that case using ZappySys JSON Driver we can make the JSON API call and parse the json string. Let’s configure the API call in the JSON Driver

Open ODBC Data Sources (x64):

Create a User Data Source (User DSN) based on ZappySys JSON Driver

Select Url or File and paste the following Url for this example.

NOTE: Here for demo, We are using odata API, but you need to refer your own API documentation and based on that you need to use your own API URL and need to configure connection based on API Authentication type

Now enter JSONPath expression in Array Filter textbox to extract only specific part of JSON file as below ($.value[*] will get content of value attribute from JSON document. Value attribute is array of JSON documents so we have to use [*] to indicate we want all records of that array)

NOTE: Here, We are using our desired filter, but you need to select your desired filter based on your requirement.

Click on Test Connection button to view whether the Test Connection is SUCCESSFUL or Not.

Once you configured a data source, you can preview data. Hit Preview tab, and use similar settings to preview data:

Click OK to finish creating the data source

That’s it; we are done. In a few clicks we configured the call to JSON API using ZappySys JSON Connector.

Create ODBC Data Source (DSN) based on ZappySys XML Driver

In upper section we we check how to make the JSON API call using JSON Driver and parse the json string response. Same way if your API is XML/SOAP Type and responding the xml string response, in that case using ZappySys XML Driver we can make the XML/SOAP API call and parse the xml string. Let’s configure the API call in the XML Driver.

Open ODBC Data Sources (x64):

Create a User Data Source (User DSN) based on ZappySys XML Driver

Select Url or File and paste the following Url for this example.

NOTE: Here for demo, We are using odata API, but you need to refer your own API documentation and based on that you need to use your own API URL and need to configure connection based on API Authentication type

Now enter Path expression in Array Filter textbox to extract only specific part of XML file as below ($.feed.entry[*] will get content of entry attribute from XML document. Entry attribute is array of XML documents so we have to use [*] to indicate we want all records of that array)

NOTE: Here, We are using our desired filter, but you need to select your desired filter based on your requirement.

Click on Test Connection button to view whether the Test Connection is SUCCESSFUL or Not.

Once you configured a data source, you can preview data. Hit Preview tab, and use similar settings to preview data:

Click OK to finish creating the data source.

That’s it; we are done. In a few clicks we configured the call to XML API using ZappySys XML Connector.

Create ODBC Data Source (DSN) based on ZappySys CSV Driver

In upper section we we check how to make the XML/Soap API call using XML Driver and parse the xml string response. Same way if your API is csv Type or want to parse the CSV file data, in that case using ZappySys CSV Driver we can make the API call or read the CSV file data.

Open ODBC Data Sources (x64):

Create a User Data Source (User DSN) based on ZappySys XML Driver

Select Url or File.

Read CSV API in MS Excel

Paste the following Url. In this example, We are using Zip format CSV File URL, but you need to refer your CSV File/URL.

Click on Test Connection button to view whether the Test Connection is SUCCESSFUL or Not.

Read CSV File in MS Excel

You can use pass single file or multiple file path using wildcard pattern in path and you can use select single file by clicking [. ] path button or multiple file using wildcard pattern in path.
Click on Test Connection button to view whether the Test Connection is SUCCESSFUL or Not.

Once you configured a data source, you can preview data. Hit Preview tab, and use similar settings to preview data:

Click OK to finish creating the data source

That’s it; we are done. In a few clicks we configured the read the CSV data using ZappySys CSV Connector.

Read data in Excel from the DSN

In Excel click Data, then select Get Data, proceed with From Other Sources and choose From ODBC item. This will get data from ODBC data source we created:

A small window opens, then simply select the data source you created in previous steps:

Most likely, you will be asked to authenticate to a newly created DSN. Just select Windows authentication option together with Use my current credentials option:

Finally, you will be asked to select a table or view to get data from. Select one and load the data!

Finally, use data extracted from REST API API in an Excel worksheet:

Create Custom Store Procedure in ZappySys Driver

You can create procedures to encapsulate custom logic and then only pass handful parameters rather than long SQL to execute your API call.

Steps to create Custom Store Procedure in ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here

Go to Custom Objects Tab and Click on Add button and Select Add Procedure:

Enter the desired Procedure name and click on OK:

Select the created Store Procedure and write the your desired store procedure and Save it and it will create the custom store procedure in the ZappySys Driver:
Here is an example stored procedure for ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here

That’s it now go to Preview Tab and Execute your Store Procedure using Exec Command. In this example it will extract the orders from the date 1996-01-01:

Let’s generate the SQL Server Query Code to make the API call using store procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.

Now go to SQL served and execute that query and it will make the API call using store procedure and provide you the response.

Create Custom Virtual Table in ZappySys Driver

ZappySys API Drivers support flexible Query language so you can override Default Properties you configured on Data Source such as URL, Body. This way you don’t have to create multiple Data Sources if you like to read data from multiple EndPoints. However not every application support supplying custom SQL to driver so you can only select Table from list returned from driver.

Many applications like MS Access, Informatica Designer wont give you option to specify custom SQL when you import Objects. In such case Virtual Table is very useful. You can create many Virtual Tables on the same Data Source (e.g. If you have 50 URLs with slight variations you can create virtual tables with just URL as Parameter setting.

Go to Custom Objects Tab and Click on Add button and Select Add Table:

Enter the desired Table name and click on OK:

And it will open the New Query Window Click on Cancel to close that window and go to Custom Objects Tab.

Select the created table, Select Text Type AS SQL and write the your desired SQL Query and Save it and it will create the custom table in the ZappySys Driver:
Here is an example SQL query for ZappySys Driver. You can insert Placeholders also. Read more about placeholders here

That’s it now go to Preview Tab and Execute your custom virtual table query. In this example it will extract the orders for the USA Shipping Country only:

Let’s generate the SQL Server Query Code to make the API call using store procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.

Now go to SQL served and execute that query and it will make the API call using store procedure and provide you the response.

Consuming an API with Excel

A hands-on tutorial that demonstrates the first steps for consuming a simple API with Excel.

On this page

This tutorial is for you if you’re an Excel user who would like to learn how to extend your spreadsheets with data from APIs. You might be one of the people who spend most of their workdays in Excel and do calculations and visualizations but still work manually to import or even just copy and paste data from external sources.

If data is available from an API, however, there are various ways in which can consume it directly from Excel, saving you time and preventing errors.

That is, unfortunately, just as long you are using a Windows PC. The integrations presented in this article do not work in Excel for Mac or the web-based version of the Office apps.

The API

BASF provides the Periodic Table of Elements API, which enables you to query the periodic table and retrieve all kinds of information about chemical elements.

Understanding the API

We assume you came here from the Introduction to APIs. If not, please check out that document to learn the basics of APIs before continuing.

The Periodic Table of Elements API follows the CRUD/RESTful paradigm but implements only a subset. Specifically, the periodic table is read-only and allows HTTP GET requests exclusively. It is also limited to one resource called «element».

The base URL for the API is https://dev.api.basf.com/chemical/. Every endpoint starts with this URL.

For the elements, there are two endpoints. The first is a collection endpoint that allows you to either retrieve the full periodic table or request a subset of elements through filter parameters. It is called a collection endpoint because it always returns a collection data structure containing multiple items. The second is an individual resource endpoint that allows you to retrieve a single element.

Both endpoints return JSON-formatted responses.

Prerequisites for Use

All BASF APIs require authentication. The Periodic Table of Elements API uses an app-only context with an API key. If you haven’t done so, please read the introduction, terminology, and app-only context sections of the Authentication and Authorization guide. You can skip the part on the app and user context and OAuth as it is not required for this API.

To use authentication, you need to create an account and an application. The process gives you a Client ID, which you can then use as your API key. If you do not have an API key, follow the steps outlined in the Get Started guide. Make sure you select the API product BASF Periodic Table while creating your app.

Evaluating your Options

In Excel for Windows, there are three options for getting external data from an API, which we’ll investigate throughout this article.

The first option is the built-in WEBSERVICE() function. Retrieving data from a URL is a single step. However, extracting the information is rather cumbersome as Excel doesn’t understand JSON responses and you have to use string processing functions. Also, as the function only supports the URL as a parameter, this works only with APIs that allow you to provide authentication information as part of the URL. The BASF Periodic Table of Elements API fits the criteria, as it uses a query string for authentication. For other APIs that require custom headers, though, you’re out of luck.

The second option is using VBA, Visual Basic for Applications, to write a Macro in Basic code to consume an API and fill the sheet programmatically. It is the most powerful and flexible option and should work with all kinds of APIs, but it requires you to have some basic programming knowledge.

The third option is the Power Query builder that allows you to gather and combine data from different sources, including APIs. If you have already used the Power Query builder, for example, to access a database directly, this is the sensible way to do it. It’s also a good choice if you do not want to work with code and the WEBSERVICE() option is too limited.

We’ll use an individual resource endpoint with the WEBSERVICE() function as well as the VBA macro, and we’ll demonstrate the collection endpoint with the Power Query builder.

The WEBSERVICE() Function

Before we introduce the WEBSERVICE() function, let’s look at functions in Excel in general. They allow you to fill a table cell with computed data. To enter a function into a cell, you start with the equals sign (=), followed by the function name, a left parenthesis [(], your arguments (if needed), and a right parenthesis [)] to end. If there is more than one argument, you separate them with semicolons (;). A common function that you may have used is SUM(), which adds the data from a number of other cells and returns the result. It takes a range of cells as its argument.

The WEBSERVICE() function also takes one argument, a URL. Then, Excel issues an HTTP GET request to fetch whatever is available at that URL, and puts it into the cell as its result. If we want to do an API requests, since the function only allows a single argument, we have to encode the full API request into the URL. As mentioned before, this rules out APIs that require other HTTP methods (such as POST) or custom headers, e.g., for authorization. With the Periodic Table API, however, we put the API key in the URL and only need the GET method.

A first Request

Open a new Excel workbook, and enter the following line into a cell, replacing the string APIKEY with the previously generated key:

Once you hit Enter or click on another cell, you should see a JSON structure describing the Helium element appear in your cell.

Understanding and dynamically building the URL

As mentioned before, every API endpoint starts with the base URL. For the Periodic Table API, that is https://dev.api.basf.com/chemical/. Then, the path to a resource or collection endpoint follows. In our example, we retrieved a single element, hence, we used a resource endpoint. Typically, the path to a resource starts with the pluralized resource name, followed by a slash (/) and finally an identifier. If you look at the URL above, you can identify elements/He as this path. The question mark (?) separates the parameter section, and you can see the x-api-key parameter that authenticates the request.

We can separate the base URL and the API key from the URL and build the URL dynamically through string concatenation. That is useful if you have multiple requests in the same Excel sheet and, let’s say, you want to share this sheet with another person who then provides a different API key. The other person then only has to enter their API key in one designated cell instead of modifying your API requests.

As next step, we’ll create a sheet that allows users to modify the base URL and API key in one place. We’ll also apply string processing functions to parse a single field from the JSON response. You can follow along these steps. If something is unclear or doesn’t work for you, you can compare your sheet with the screenshots as well as the full Excel workbook that we’ll provide as download further below.

1. Type «Base URL» in A1 and the base URL https://dev.api.basf.com/chemical/ in B1.
2. Type «API Key» in A2 and paste your API key from the developer portal in B2.
3. Type «Element» as a header in A4 and some elements below it. As an example, we’ll put «H» in A5 and «N» in A6.
4. Type «API URL» as a header in B4. To build the URL, concatenate the following elements: a reference to the field containing the base URL, «elements/», a reference to the field containing the element name, «?x-api-key=», and a reference to the field containing the API key. You need to use absolute references to base URL and API key and a relative reference to the element name. Your function call should look like this: =CONCAT($B$1;»elements/»;A5;»?x-api-key API Response» as a header in C4. Make the webservice call using the URL you built in the previous step by typing =WEBSERVICE(B5) in C5. You should see a JSON API response appear in the cell.
5. Type «Atomic Mass» as a header in D4. As there is no JSON parser in Excel, we copy a substring from the API response between the atomic_mass field and the subsequent field, covalent_radius. The whole function looks like this: =MID(C5;SEARCH(«atomic_mass»;C5)+13;SEARCH(«covalent_radius»;C5)-SEARCH(«atomic_mass»;C5)-16) You should now see only the atomic mass as a number in the cell.
6. Select the cells B5 to D5, grab the right bottom corner, and drag them down to the B6 to D6. You should see the API URL, API response and atomic mass for the second element appear.
7. To get the atomic mass for more elements, add a new element symbol in column A and copy or drag down the three other columns.

Please note that Excel function names are localized along with the Excel user interface. If your Excel installation is in German, replace the names based on this list:

• CONCAT – TEXTKETTE
• MID – TEIL
• SEARCH – SUCHEN

You can get a working Excel workbook in the PeriodicTableWebservice.xlsx file (TODO: add download).

A VBA Macro

Visual Basic for Applications, or VBA for short, is a powerful programming language that you can use to automate Microsoft Office applications, including Excel. VBA is closely related to, but not fully compatible with, other versions of the Basic language (such as Visual Basic .NET).

If you not worked with VBA before but have programming skills in a different language, you have to learn a new syntax, but should be able to understand the structure of the macro we build in this section.

The Objective

Our goal is to retrieve various attributes of specific chemical elements (but not the entire periodic table). The sheet layout uses one row per element. The user should enter the element names into the first column of the sheet (A). Then, upon running the macro, it should go through the list of rows and fill the other columns with details. For example, the atomic mass in the second column (B) and the covalent radius in the third column (C).

Getting Started

Open Excel with a new, empty sheet. Then, press the combination Alt and F11 on your keyboard to launch the Visual Basic editor.

On the left side of the editor, you can see the project structure. There’s an entry for your sheet. Double-click on this entry to open the code view where you can enter Basic code that is specific to that sheet.

We start by creating two global string variables, one for the base URL and the API key. It helps us separate them from the code itself so we can swap them if necessary.

It’s not possible to assign a value to these variables yet. We’ll do that later.

Writing a Function

As mentioned under the objective above, we need to retrieve specific elements that the user enters into the sheet, not the full periodic table. For this purpose, we use the API endpoint for specific elements and have to make multiple requests. Hence it is useful to develop an abstraction and put the logic for the API request into a function.

In VBA, you can define functions with the Function keyword. They have parameters with types and a return type. Here is the definition of our getElement() function:

Next, we create a script control. That is actually a workaround because there is no native JSON parser available and we don’t want to install a third-party module. The script control can execute JScript, which is a version of JavaScript, and thus understand JSON.

Then, we can make an HTTP request to the API endpoint. VBA makes HTTP requests through the XMLHTTP object. While building the request, we use string concatenation to build the API URL and include the base URL and API key from the variables we defined earlier.

After that, all is left is closing the With-block and the function. The VBA editor might have already generated this code for you automatically.

Creating the Macro

Macros are public VBA subroutines. Let’s call ours getPeriodicElementData():

Inside the subroutine, we first assign some values to our global variables. Add these lines and replace APIKEY with your API key from the developer portal:

Then, as we want to go through our sheet row by row to get element data, we set up a counter variable:

Our algorithm goes through all rows until it stops at an empty row. We can achieve this with a Do-Until-loop:

Inside the loop, we call our getElement() function we created in the previous section. By using a With-block, we can access the attributes of the elements directly and write them in the cells. Our sample code reads two attributes, atomic mass and covalent radius, which go in the row of the current element and the second or third column respectively.

If you want, you can extend this code with more properties from the element object and put them in additional columns. Tip: Use the WEBSERVICE() function to get the full JSON structure so you can see the available properties.

Afterwards, make sure to increase the row variable, otherwise you have an infinite loop:

Finally, we close the loop and the subroutine. The VBA editor might have already generated this code for you automatically.

Running the Macro

Before we can run the macro, we should add a couple of element symbols in the first column (A) so that the macro has something to do. As an example, use the elements H, S and O. Use one row for each, so that H is in the first, S in the second and O in the third row.

During development, you can run your macro directly from the VBA editor, using either the play button in the toolbar, the Run menu, or the F5 key on your keyboard.

The macro should run and automatically fill the other columns for your three rows.

To re-run your macro later when you’ve closed the VBA editor, follow these steps:

1. Open the View ribbon.
2. Click on Macros.
3. Select the macro and click Run to execute.

Power Query Builder

The Power Query builder provides access to a variety of data sources. It even enables the combination of data from multiple sources. In this tutorial, though, we’ll just show you the basics to make an API call and display the results in your Excel sheet. We use the collection endpoint to retrieve the complete periodic table.

To get started, go to the Data ribbon, click the Get Data button, choose From Other Sources, and From Web.

In the popup window, enter the URL for the API. You can configure advanced settings such as HTTP headers for your request, but the BASF Periodic Table API doesn’t need any. You have to provide the full URL to the collection endpoint and add the query parameter for authentication directly into the URL. In the following sample, replace APIKEY with the Client ID of your application

Once you confirm with OK, the Power Query Builder opens and shows the API response. At first, you just see the container element items. Right-click on it and select Drill Down.

Note the list of applied steps on the right side of the window. It shows you everything you did in the query builder and you can undo any step by clicking the X next to it.

Now you see a list of records and you can click on each to see the different attributes of each element. The next step is converting this list into a table and you do this by clicking the To Table button.

A popup with additional options may appear which you can simply confirm with OK. After that, you see the list as a table but it still has just one column that we need to expand into multiple columns so you can see each attribute of the element in its own column. To do so, click the expand icon (<>) in the column header.

In the popup, select the fields that you like or keep all fields intact. You can uncheck «Use original column name as prefix» as the prefix doesn’t provide any value when you’re just working with one data source.

Once you see the data you want in the query builder, your final step is transferring the table into the Excel sheet. You do this with the Close&Load button.

You’re done! Your excel sheet contains the periodic table of elements now.