How to get data from API in Excel

There are multiple ways of how to get data from API to MS Excel, but we are going to focus on the simplest option (including video tutorial) for non-technical people: Using Python/R/JavaScript/PHP code and save it to an MS Excel file requires programming skills and a pre-installed Postman software. Using the VLOOKUPWEB formula doesn’t require programming skills, and the simplest possible option is the 4th most simple way explanation in this article by getting data to Excel tables from API with JSON format.

Denis

NLP Intelligent Automation enthusiast nlsql.com

There are multiple ways of how to get data from API to MS Excel, but we are going to focus on the simplest option (including video tutorial) for non-technical people:

1. Using Python/R/JavaScript/PHP code and save it to an MS Excel file
–> this option requires programming skills and a pre-installed environment.
2. Using Postman software
–> requires pre-installed Postman software and a lot of manual work
3. Using Power Query
–> requires basic programming skills and get familiar with PowerQuery
4. Using the VLOOKUPWEB formula
–> Doesn’t require programming skills and the simplest possible option

We are going to focus on the 4th most simple way explanation in this article by getting data to Excel tables from API with JSON format (using both POST or GET methods)

VLOOKUPWEB formula works as simple as usual VLOOKUP excel formula by the following syntax:

=vlookupweb(1-link-to-api, 2-field-to-query, 3-time-out-in-seconds-between-calls, 4-token-if-needed, 5-body-for-post-or-empty-for-get-request)

VLOOKUPWEB requires the following input parameters :

1. API link
2. Field name from JSON response you want to get data
3. Time-out parameters in seconds (0 for no time-out)
4. API token if Authorization required (if not leave empty)
5. Body parameter (if empty it would use GET method. If not empty Body will be sent as POST)

In order to add this function to your excel:

1. Open excel
2. Press Alt + f11
3. create new module
4. Copy-paste this code to a new module
5. Save and close VBA editor.
6. You can use the function VLOOKUPWEB as typical formula similar to standard beloved VLOOKUP

Custom VBA function source code you can find below. For the latest version of vlookup for web function you can refer to our GitHub repository

 '------------------------------------------------------------------------ 'This function is property of NLSQL Limited, 'NLSQL is B2B SaaS to empower employees with intuitive text interface to 'poorly-accessible coporate data to inform and speed business decisions with significant benefits for enterprises. 'Below code is 100% open-source under MIT Licence, so you can feel free to use it for 'Commercial use, Modification, Distribution or private use. Except if you are Microsoft =) 'NLSQL Limited don't have any Liability or Warranties related to your code usage 'subscribe to our LinkedIn https://www.linkedin.com/company/nlsql-com 'or Youtube https://www.youtube.com/channel/UC8KtzeNHxhLGVwiOCwvRBkg?sub_confirmation=1 'in order to have even more great open-source tools, absolutely free 'By Denis 'Date: 06/09/2021 'info@nlsql.com 'https://nlsql.com///// '------------------------------------------------------------------------ 'Function works similar to standard Excel VLOOKUP function,
'so it finds the FIRST value based on API connection and field name from API responce Function VLOOKUPWEB(ByVal cell As Range, ByVal txt As String, Optional ByVal pause, _ Optional ByVal header As String, Optional ByVal postcell As Range) As String ' As Integer On Error Resume Next Dim GetResponse As String FuncDesc = "Function created for Vlookup JSON to excel in a most simpliest way same as using VLOOKUP" URL = cell.Value 'URL is required to have correct API link including https or http. HTTPS works better for the data security GetResponse = "" If URL Like "*http*" Then If Not postcell Is Nothing Then pst = postcell.Value zapr = "POST" 'support of POST request. Body (5th) Postcell argument is required Else pst = "" zapr = "GET" 'support of GET request. All parameters expected to be in (1st) Cell argument End If Set xmlhttp = CreateObject("WinHttp.WinHttpRequest.5.1") 'MSXML2.XMLHTTP xmlhttp.Open zapr, URL, False: DoEvents xmlhttp.setRequestHeader "Authorization", header 'you can change Authorization to api-key, etc. as required by API provider xmlhttp.setRequestHeader "Content-Type", "application/json" 'you can add as much headers as needed xmlhttp.send pst 'send request both POST and GET supported GetResponse = xmlhttp.responseText 'MsgBox GetResponse Set xmlhttp = Nothing End If If pause > 0 Then t = Timer '3rd argument - Pause in seconds for time-out Do DoEvents If t > Timer Then Exit Do Loop Until Timer - t > pause End If s = Split(GetResponse, txt) If UBound(s) > 0 Then ps = Split(s(1), """") VLOOKUPWEB = ps(2) Else: VLOOKUPWEB = "not available": End If End Function

If you enjoyed our simplification for your work or you have any questions, subscribe to our company webpage on NLSQL LinkedIn or NLSQL YouTube to be well informed about upcoming releases. VLOOKUPWEB is our internal tool, which we released to open-source absolutely free.
We are using it every day for testing out Text to SQL APIs for our customers.

We created a lot of useful and powerful software tools along the way of our commercial NLSQL product development, that empowers frontline employees with intuitive text interface to poorly-accessible corporate date to inform and speed business decisions with significant benefits for enterprises.

Enjoy and don’t forget to share this article with your colleagues in order to make their working life easier and more productive.

Related Stories

Tags

Join Hacker Noon

Create your free account to unlock your custom reading experience.

How to get data from API in Excel
Source: Pinay Tube PH