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:

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 :

API link
Field name from JSON response you want to get data
Time-out parameters in seconds (0 for no time-out)
API token if Authorization required (if not leave empty)
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:

Open excel
Press Alt + f11
create new module
Copy-paste this code to a new module
Save and close VBA editor.
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.