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. and save it to an MS Excel file --> this option requires programming skills and a pre-installed environment. 2. --> requires pre-installed Postman software and a lot of manual work 3. --> requires basic programming skills and get familiar with PowerQuery 4. formula --> Doesn’t require programming skills and the simplest possible option We are going to focus on the most simple way explanation in this article by getting data to Excel tables from API with JSON format (using both POST or GET methods) Using Python/R/JavaScript/PHP code Using Postman software Using Power Query Using the VLOOKUPWEB 4th 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 you can refer to our GitHub repository vlookup for web function ' NLSQL Limited, B2B SaaS empower employees intuitive interface -accessible coporate data inform speed business decisions significant benefits enterprises. code % -source under MIT Licence, so you can feel free it , Modification, Distribution private . Except you are Microsoft =) Limited don have any Liability Warranties related your code usage our LinkedIn https://www.linkedin.com/company/nlsql-com Youtube https://www.youtube.com/channel/UC8KtzeNHxhLGVwiOCwvRBkg?sub_confirmation= order have even more great -source tools, absolutely free Denis : / / @nlsql.com ://nlsql.com///// ' works similar standard Excel VLOOKUP , it finds the FIRST value based API connection field name from API responce VLOOKUPWEB(ByVal cell As , ByVal txt As , Optional ByVal pause, _ Optional ByVal header As , Optional ByVal postcell As ) As ' As Resume Dim GetResponse As FuncDesc = URL = cell.Value required have correct API link including https http. HTTPS works better the data security GetResponse = URL Like postcell Nothing pst = postcell.Value zapr = POST request. ( th) Postcell argument required pst = zapr = GET request. parameters expected be ( st) Cell argument Set xmlhttp = CreateObject( ) .XMLHTTP xmlhttp. zapr, URL, : DoEvents xmlhttp.setRequestHeader , header can change Authorization api-key, etc. as required by API provider xmlhttp.setRequestHeader , can add as much headers as needed xmlhttp.send pst request both POST GET supported GetResponse = xmlhttp.responseText GetResponse Set xmlhttp = Nothing pause > t = Timer ' rd argument - Pause seconds - Do DoEvents t > Timer Do Timer - t > pause s = Split(GetResponse, txt) UBound(s) > ps = Split(s( ), ) VLOOKUPWEB = ps( ) : VLOOKUPWEB = : ------------------------------------------------------------------------ 'This function is property of 'NLSQL is to with text to 'poorly to and with for 'Below is 100 open to use for 'Commercial use or use if 'NLSQL 't or to 'subscribe to 'or 1 'in to open 'By 'Date 06 09 2021 'info 'https ------------------------------------------------------------------------ 'Function to function 'so on and Function Range String String Range String Integer On Error Next String "Function created for Vlookup JSON to excel in a most simpliest way same as using VLOOKUP" 'URL is to or for "" If "*http*" Then If Not Is Then "POST" 'support of Body 5 is Else "" "GET" 'support of All to in 1 End If "WinHttp.WinHttpRequest.5.1" 'MSXML2 Open False "Authorization" 'you to "Content-Type" "application/json" 'you 'send and 'MsgBox End If If 0 Then 3 in for time out If Then Exit Loop Until End If If 0 Then 1 "" "" 2 Else "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 or 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 APIs for our customers. We created a lot of useful and powerful software tools along the way of our commercial 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. NLSQL LinkedIn NLSQL YouTube Text to SQL NLSQL Enjoy and don’t forget to share this article with your colleagues in order to make their working life easier and more productive.